Why to use it

De-Parameterize traced queries

The primary purpose of the tool beyond simplifying the process of identifying the longest running queries from an existing profiler trace of Analysis Services is to help de-parameterize and re-parameterize MDX or DMX queries captured in the trace. The parameterized version of the query captured in a profiler trace cannot be run directly in a query window in a client tool (i.e., SQL Management Studio). Its parameters must first be replaced with their intended values, and then the de-parameterized version of the query can be executed directly.

Improve the poorly designed queries directly

With the de-parameterized version of the query, one may directly execute and then experiment with changes to improve its performance, all within a Management Studio window. This avoids needing to run the source reports from which the queries often originate, possibly having numerous other data sources and datasets extraneous to the problem being investigated. It also avoids overhead of continuously selecting parameter values, etc., from a reporting interface to test changes to the query. Management Studio is a much more flexible environment in which to work directly with the MDX or DMX query, with the added benefit of being able to lookup function, measure, dimension and member names using the rich UI provided there for that purpose.

Re-parameterize to update original source

Once a modified query has been designed using the de-parameterized query obtained from the original trace, it is typically necessary then to update the report or source from which it was executed. Unfortunately, this process also frequently requires that the query again be re-parameterized, to substitute into a dataset for a report. This can again be a tedious and error prone process and so the tool also supports re-parameterizing a modified query. Then the new modified query, fully parameterized, can be directly pasted back into the report dataset, and the report used as is.

Last edited May 26, 2014 at 1:20 AM by jburchel, version 1