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