Home > Analysis Services, Tips, Utilities > Working with SSAS traces and the dreaded Textdata in SSMS

Working with SSAS traces and the dreaded Textdata in SSMS

February 4, 2012 Leave a comment Go to comments

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

image

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 &gt 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.

image

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.

image

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.

About these ads
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 73 other followers

%d bloggers like this: