How to Test SSRS MDX Queries in SQL Server Management Studio
I often need to capture an MDX query that a report sends to Analysis Services and execute it in SQL Server Management Studio (SSMS). I usually do this to understand what parameters the report passes to the query and to troubleshoot the query itself. Unfortunately, SSMS doesn’t support parameterized MDX queries so you have to resort to the following technique:
Capturing MDX Queries
The easiest way to capture an MDX query is to use the SQL Server Profiler.
- Start SQL Server Profiler from the Microsoft SQL Server <version>-> Performance Tools program group.
- Click File -> New Trace or press Ctrl+N.
- In the Connect To Server dialog box, choose Analysis Services, enter your server name, and click Connect.
- In the Trace Properties dialog box, click the Event Selection tab. Check the Show All Events and Show All Columns checkboxes.
- Optionally, if you want to see only your events, that is events triggered by your actions only, click the Column Filters button. In the Edit Filter dialog box, select NTUserName. Expand the Like tree node on the right and enter your Windows login surrounded by %, e.g. %t_lachev%.
- Back to the Trace Properties dialog box, click Run to start the trace.
- Now run the report whose queries you want to capture and watch the profiler output for a Query Begin event.
Typically, a report would execute several MDX queries because there will be queries for the report parameters. You can use the profiler find function (Ctrl+F) to search for the query text you need in the TextData column in order to find the report main query (that one that supplies the report with data).
- Selec t the Query Begin event of the query you want. In the lower pane of the profiler, you will see the query MDX statement followed by Parameters and PropertyList nodes, such as:
WITH MEMBER MyKPI as StrToMember( @KPISelector)
SELECT NON EMPTY {MyKPI} ON COLUMNS,
NON EMPTY StrToSet(@Dataset)
* StrToMember(@StartPeriod) : StrToMember(@EndPeriod) ON ROWS
FROM Viking
WHERE (StrToMember(@Organization) )
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
<Parameters xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:xsd=”http://www.w3.org/2001/XMLSchema” xmlns=”urn:schemas-microsoft-com:xml-analysis”>
<Parameter>
<Name>KPISelector</Name>
<Value xsi:type=”xsd:string”>[Measures].[Return On Average Assets]</Value>
</Parameter>
<Parameter>
<Name>StartPeriod</Name>
<Value xsi:type=”xsd:string”>[Date].[Month].&[20060101]</Value>
</Parameter>
<Parameter>
<Name>EndPeriod</Name>
<Value xsi:type=”xsd:string”>[Date].[Month].&[20061201]</Value>
</Parameter>
<Parameter>
<Name>Dataset</Name>
<Value xsi:type=”xsd:string”>{ [Dataset].[Dataset].&[-2],[Dataset].[Dataset].&[-1] }</Value>
</Parameter>
<Parameter>
<Name>Organization</Name>
<Value xsi:type=”xsd:string”>[Organization].[Organization Hierarchy].&[-1]</Value>
</Parameter>
</Parameters>
<PropertyList xmlns=”urn:schemas-microsoft-com:xml-analysis”>
<Catalog>VANTAGE_CUBE_M_BASELINE DEV</Catalog>
<LocaleIdentifier>1033</LocaleIdentifier>
<Format>Tabular</Format>
<Content>SchemaData</Content>
<Timeout>0</Timeout>
<ReturnCellProperties>true</ReturnCellProperties>
<DbpropMsmdFlattened2>true</DbpropMsmdFlattened2>
</PropertyList>
- Click anywhere in the lower pane and press Ctrl+A to select all text, then Ctrl+C to copy it to the clipboard.
Testing MDX Queries
Now that you have the query, you are ready to test it in SSMS.
- Open SSMS and connect to the Analysis Services server.
- Right-click on the SSAS database and click New Query -> MDX to open and create a new MDX query. Press Ctrl+V to paste the query text that you captured in the profiler.
- Since the profiler escapes certain characters, first you need to unescape them. For example, replace (Ctrl+H) all & with &.
- One by one, go through the parameters listed in Parameters node and replace the parameter placeholder in the MDX query with the actual value. For example, press Ctrl+H to search for
@KPISelector (note that the parameters are prefixed with @ in the MDX query) and replace all occurrences (Replace All option) with the actual parameter value. Since typically SSRS MDX queries use StrToMember or StrToSet functions, you need to surround the parameter values with quotes, e.g. “[Measures].[Return On Average Assets]”. - Once you replace all parameters, comment the Parameters and PropertyLists nodes by selecting all text in these nodes and clicking the Comment Out the Selected Lines toolbar button. The only text that it not commented should be the MDX query text.
At this point, you can execute the query by pressing Ctrl+E or clicking the Exclamation toolbar button.
UPDATE
When things get tedious, the developer writes a program. As Greg Galloway pointed out, Darren Gosbell has already written a small but incredibly useful utility, SSAS Query Capture, that automates the process of capturing the queries and replacing the parameters. Moving forward to Visual Studio 2008 and SQL Server 2008, you need to:
- Upgrade the source code to Visual Studio. To do so, just open the C# source project in Visual Studio 2008 and accept the defaults.
- Delete the Microsoft.AnalysisServices and reference Microsoft.AnalysisServices.dll which you can find in the \Program Files\Microsoft SQL Server\100\SDK\Assemblies\ folder.
- Build the project and run QueryCapture.exe in the bin\debug folder.
A cautionary note: The utility always encloses the parameter values in quotes because it assumes that they will be inside the StrToMember and StrToSet functions which SSRS MDX Query Designer generates when you add parameters. If you reference parameters literally, as I demonstrated in this blog, remove the quotes surrounding these parameter values. Otherwise, the chances are that the query will return no data.
I couldn’t imagine how much time Darren’s Query Capture utility could have saved me if I only knew about it! I hope he adds it to the BIDS Helper project.
