Working with SSAS traces and the dreaded Textdata in SSMS
For good or bad i regularly work with SSAS traces that i have saved to a table and many years ago i was regularly frustrated by the fact that the contents of the textdata column containing the query is always truncated at 65535 characters.
You may think 65535 characters should cover most queries but once Excel gets to work it generates some huge queries!
Fortunately i came across a solution by MVP Adam Machanic that uses a very nifty trick to convert the output to XML and this has the benefit that XML column data is presented as hyperlinks in grid view!
So, to demonstrate this in action below is a short code snippet to pull data from a trace i saved to a table and display the textdata as hyperlinks..
SELECT (select [processing-instruction(q)]=TextData for xml path(''),type) As Query, StartTime, EndTime, CPUTime, Duration FROM dbo.My_Trace_Table Where EventClass = 10
The output is shown below
As you can see all the queries are presented as hyperlinks and you can read what you can in the cell or click the link for another surprise.
The surprise is that Adam Machanic uses the processing-instruction() xpath function which bypasses encoding so characters like > are not changed to > and all the formatting is preserved. So, if the client submitted a formatted query that will be preserved in the link. Below is an example output from one of the links i clicked.
The eagle eyed amongst you will notice the <?q & ?> at the beginning and end, this is a small price to pay.
If your unfortunate and find the query is not formatted and you just get one long string then i always turn to the trusty online MDX formatter here by Mosha Pasumansky.
Finally, you may ask yourself if there is a limit to the data size and the answer is its configurable. By default XML data is restricted to 2mb but you can set it higher if required.
I have been using this technique for a good few years now and have used it to solve many different challenges however it was only recently that i was reminded that this may not be a widely known solution so i hope you find this as useful as i have.
