Profile MDX and SQL Queries from SSRS

  • Article

Profile MDX & SQL queries from SSRS 

One of the challenging aspects of integrating MDX queries with SSRS is debugging.  This is mainly because SSRS ignores Error messages and simply returns the empty cells. Another area of concern is parameterization and query building. If you are using parameters for the MDX queries then even a simple mistake would be very hard to analyse without seeing the actual final query.

E.g.

Check the below query I am trying to display all the descendants set in column axis and resulted in error. 

WITH MEMBER MEASURES.AXISText AS

Descendants([Geography].[Geography].currentmember,3,self_and_before)

SELECT {[Measures].[Internet Sales Amount], MEASURES.AXISText} ON COLUMNS

     ,Descendants([Geography].[Geography].[Country].&[Australia]

                   ,[Geography].[Geography].[State Province],self_and_after) DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME on Rows

FROM [Adventure Works]

CELL PROPERTIES VALUE,

                              BACK_COLOR

                            , FORE_COLOR

                            , FORMATTED_VALUE

                            , FORMAT_STRING

                            , FONT_NAME

                            , FONT_SIZE

                            , FONT_FLAGS

SQL Server management studio Output sample:

 

I built a simple report on top of it and got the below result. As you can see the “#Error” has been replaced by empty cells. This is confusing, isn’t it?

So knowing the direct SQL would have easily highlighted the issue

Now in order to easily trace the query from SSRS follow the below steps

     1. Create a common Template.

    2. Select the "Query End Event" as mentioned below

    3. Create a new trace and make sure to use the recently created template

    4. Now this will trace both SSAS main Data set query and Parameter query from the SSRS in the computed form
           

E.g. For the Parameter

 

E.g. For the Main Dataset query

 

Include Filters

In real life the server will host multiple databases and even development server will get accessed by multiple team members. So it is better to customise your template based on the filters to focus on the query of your interest.

** **

 

Best candidate for column filters

  • Database name
    • If you have multiple SSAS database then use this filter to narrow it down
  • NTUsername
    • If you using windows login or trying to access from Microsoft visual Studio then this would be your name  
  • Success flag
    • This is one of my favorite. If you are receiving an error in the SSRS window and you want to investigate it. Then please set a filter to “Success like 0”

 

 

Tracing queries in the SQL server database instance

Select the below events for tracing SSRS queries against SQL server instance

Note: Irrespective of using the Stored procedure from SSRS, RPC completed should be chosen

If you are connecting to SQL database engine instead of SSAS instance then you must use the filter to reduce the number of traces.
**
**

Best filter candidate

  • Application Name
    • If you are debugging a solution which is connecting to remote server then use “.Net SqlClient Data Provider” as your filter against Application name
  • Database name
    • Use the name of the database against which the query has been issued
  • NTUsername
    • If you using windows login or trying to access from Microsoft visual Studio then this would be your name

 

 

See Also :

 SQL Server Analysis Services Portal
 SQL Server Reporting Services Portal
 List of Award Winning TechNet Guru Articles