1

I am trying to build an SSRS report with SSAS as the data source. It has 4 cascading parameters. I used the SSRS Query designer to generate MDX because I am not very familiar with Cubes/MDX.

It works but takes too long to render the report (3 minutes or more in both Visual Studio and on the Reporting Server). I also tried to generate MDX using Excel OLAP Pivot table extensions and modified the MDX to use cascading parameters, but the performance isn't getting any better.

If I use SQL queries and SQL server as the data source for the same report, I get the output in a few seconds. But implementing this using cubes should make this scale much better.

Does anyone know what steps I can take to improve the performance of this report?

This is the MDX from SSRS query designer -

 SELECT NON EMPTY { [Measures].[FactCount], [Measures].[Rate], [Measures].[Benchmark1], [Measures].[Benchmark2] } ON COLUMNS, 
        NON EMPTY {{[DimOrg].[DivisionName].[All]} 
                    * {[DimMeasure].[MeasureName].[All]} 
                    * {[DimOrg].[FacilityName].[All]} 
                    * {[DimLoadDate].[RunDate].[All]} 
                    * [DimOrg].[DepartmentName].[DepartmentName].ALLMEMBERS, 
                {[DimOrg].[DivisionName].[All]} 
                    * {[DimMeasure].[MeasureName].[All]} 
                    * {[DimOrg].[FacilityName].[All]} 
                    * {[DimLoadDate].[RunDate].[All]} 
                    * {[DimOrg].[DepartmentName].[All]}, 
                ([DimOrg].[DivisionName].[DivisionName].ALLMEMBERS 
                    * [DimMeasure].[MeasureName].[MeasureName].ALLMEMBERS 
                    * [DimOrg].[FacilityName].[FacilityName].ALLMEMBERS 
                    * [DimLoadDate].[RunDate].[RunDate].ALLMEMBERS 
                    * [DimOrg].[DepartmentName].[DepartmentName].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_VALUE, MEMBER_UNIQUE_NAME ON ROWS 
FROM (  SELECT ( STRTOSET(@DimOrgFacilityName, CONSTRAINED) ) ON COLUMNS 
        FROM (  SELECT ( STRTOSET(@DimEmployeeCategory, CONSTRAINED) ) ON COLUMNS 
                FROM (  SELECT ( STRTOSET(@DimOrgDivisionName, CONSTRAINED) ) ON COLUMNS 
                        FROM (  SELECT ( STRTOSET(@DimLoadDateLoadDate, CONSTRAINED) ) ON COLUMNS 
                                FROM (  SELECT ( { [DimLoadDate].[SomeFlag].&[True] } ) ON COLUMNS 
                                        FROM (  SELECT ( { [DimLoadDate].[someColumn].&[SomeValue] } ) ON COLUMNS 
                                                FROM (  SELECT ( { [DimMeasure].[MeasureName].&[someValue1], [DimMeasure].[MeasureName].&[someValue2] } ) ON COLUMNS 
                                                        FROM [DW]))))))) 
WHERE ( [DimLoadDate].[someColumn].&[somevalue], [DimLoadDate].[someFlag].&[True], 
        IIF( STRTOSET(@DimLoadDateLoadDate, CONSTRAINED).Count = 1, STRTOSET(@DimLoadDateLoadDate, CONSTRAINED), [DimLoadDate].[LoadDate].currentmember ), 
        IIF( STRTOSET(@DimEmployeeCategory, CONSTRAINED).Count = 1, STRTOSET(@DimEmployeeCategory, CONSTRAINED), [DimEmployee].[Category].currentmember ) ) 
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

I built another SSRS report using cubes with the same tables with a higher granularity - used 3 parameters instead of 4 and that worked faster (only a few seconds) for the report generation. Here is the MDX of that -

SELECT NON EMPTY {  [Measures].[FactCount], [Measures].[Rate], [Measures].[Benchmark1], [Measures].[Benchmark2] } ON COLUMNS, 
        NON EMPTY {{[DimOrg].[DivisionName].[All]} 
                     * {[DimMeasure].[MeasureName].[All]} 
                     * [DimOrg].[FacilityName].[FacilityName].ALLMEMBERS 
                     * {[DimLoadDate].[RunDate].[All]}, 
                {[DimOrg].[DivisionName].[All]} 
                     * {[DimMeasure].[MeasureName].[All]} 
                     * {[DimOrg].[FacilityName].[All]} 
                     * {[DimLoadDate].[RunDate].[All]}, 
                ([DimOrg].[DivisionName].[DivisionName].ALLMEMBERS 
                     *[DimMeasure].[MeasureName].[MeasureName].ALLMEMBERS 
                     * [DimOrg].[FacilityName].[FacilityName].ALLMEMBERS 
                     * [DimLoadDate].[RunDate].[RunDate].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_VALUE, MEMBER_UNIQUE_NAME ON ROWS 
FROM ( SELECT ( -{ [DimOrg].[FacilityName].&[someValue] } ) ON COLUMNS 
         FROM ( SELECT ( STRTOSET(@DimEmployeeCategory, CONSTRAINED) ) ON COLUMNS 
                FROM ( SELECT ( STRTOSET(@DimOrgDivisionName, CONSTRAINED) ) ON COLUMNS 
                        FROM ( SELECT ( STRTOSET(@DimLoadDateLoadDate, CONSTRAINED) ) ON COLUMNS 
                                FROM ( SELECT ( { [DimLoadDate].[SomeFlag].&[True]  } ) ON COLUMNS 
                                        FROM ( SELECT ( { [DimLoadDate].[someColumn].&[SomeValue] } ) ON COLUMNS 
                                                FROM ( SELECT ( { [DimMeasure].[MeasureName].&[someValue1], [DimMeasure].[MeasureName].&[someValue2] } ) ON COLUMNS 
                                                        FROM [DW]))))))) 
WHERE ( [DimLoadDate].[someColumn].&[somevalue], [DimLoadDate].[someFlag].&[True], 
         IIF( STRTOSET(@DimLoadDateLoadDate, CONSTRAINED).Count = 1, STRTOSET(@DimLoadDateLoadDate, CONSTRAINED),  [DimLoadDate].[LoadDate].currentmember ), 
         IIF( STRTOSET(@DimEmployeeCategory, CONSTRAINED).Count = 1, STRTOSET(@DimEmployeeCategory, CONSTRAINED), [DimEmployee].[Category].currentmember ) ) 
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Thanks,

Gopika

  • Hey @gopika rajan, Thanks for joining Stack exchange. Please check out stackoverflow.com/help/how-to-ask on how to ask a good question. Detail what has worked, what is currently not working. What you have tried. What you consider "too long" and what you are expecting for in performance results. – Anthony Genovese Nov 6 '18 at 16:57
  • @AnthonyGenovese thank you for the input. – Gopika Rajan Nov 6 '18 at 18:29
  • What is the MDX query? What is the sql query? – Anthony Genovese Nov 6 '18 at 19:32
  • Thanks for providing the MDX you are using. It would be helpful for us all to see an example of the table of results you would like to see. What headings are on the rows, and the columns, what numbers are you expecting in the middle? – Magnus Smith Apr 7 '19 at 14:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.