September 15, 2013

Profile MDX & SQL queries from SSRS


Disclaimer : I have published the article in the TechNet Wiki as well.

One of the challenging aspect of integrating MDX queries with SSRS is debugging.  This is mainly because SSRS ignores the #Error messages and simply returns the empty cells. Another area of concern is parameterisation 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.


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

SELECT {[Measures].[Internet Sales Amount], MEASURES.AXISText} ON COLUMNS
                   ,[Geography].[Geography].[State Province],self_and_after)DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME on Rows
FROM [Adventure Works]
                            , 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 favourite. 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 are using windows login or trying to access from Microsoft visual Studio then this would be your name

                  No comments:

                  Post a Comment