How to improve performance of SSRS report using Cubes as data source?

1 vote
0 answers
458 views
                          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

                        
Asked by Gopika Rajan (11 rep)
Nov 6, 2018, 12:35 AM
Last activity: Nov 6, 2018, 09:20 PM
How to improve performance of SSRS report using Cubes as data source?

Related Questions
