Cascading parameters performance Issue
Hi All,
I have a SSRS report which is using AS cube as its datasource.
Ther report contains cascading parameters. Each parameter is having its own dataset.
The parameters are taking a lot of time in loading.
Cascading parameters and their queries are as follows:
Parameter 1: Selling Dept
WITH
MEMBER [Measures].[ParameterCaption] AS [SalesDiv].[SalesDivName].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterValue] AS [SalesDiv].[SalesDivName].CURRENTMEMBER.UNIQUENAME MEMBER
[Measures].[ParameterLevel] AS [SalesDiv].[SalesDivName].CURRENTMEMBER.LEVEL.ORDINAL
SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [SalesDiv].[SalesDivName].ALLMEMBERS ON ROWS FROM [AS_Cube]
Parameter2: Customers
WITH
MEMBER [Measures].[ParameterValue] AS [Customer].[CustomerName].CURRENTMEMBER.UNIQUENAME
MEMBER [MEASURES].[Filter] AS SUM(STRTOSET(@SalesDivName,CONSTRAINED),[Measures].[Total_Cost])
SELECT {[Measures].[ParameterValue]} ON COLUMNS,
FILTER(DESCENDANTS ([Customer].[CustomerName].[CustomerName].ALLMEMBERS ) ,[MEASURES].[Filter] <> 0)
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM [AS_Cube] CELL PROPERTIES VALUE
After selecting 1st parameter which is multi-valued, the second parameter takes approximately 5 mins to load.
What can be done to improve the performace for MDX point of view.-Nitin Pawar
January 19th, 2011 1:08pm
Can you try the query below and see first, whether the results are the same... and then could you write down the time also
WITH
MEMBER [Measures].[ParameterValue] AS [Customer].[CustomerName].CURRENTMEMBER.UNIQUENAME
MEMBER [SalesDiv].[SalesDivName].[Total Sales] as sum(strtoset(@SalesDivName))
SELECT {[Measures].[ParameterValue]} ON COLUMNS,
FILTER([Customer].[CustomerName].[CustomerName].ALLMEMBERS* [SalesDiv].[SalesDivName].[Total Sales], [Measures].[Total_Cost] <> 0)
ON ROWS
FROM [AS_Cube]
If you have a lot of customers, try the query below also and tell me the time
WITH
MEMBER [Measures].[ParameterValue] AS [Customer].[CustomerName].CURRENTMEMBER.UNIQUENAME
MEMBER [SalesDiv].[SalesDivName].[Total Sales] as sum(strtoset(@SalesDivName))
SELECT {[Measures].[ParameterValue]} ON COLUMNS,
FILTER(nonempty([Customer].[CustomerName].[CustomerName].ALLMEMBERS,[Measures].[Total_Cost]) * [SalesDiv].[SalesDivName].[Total Sales], [Measures].[Total_Cost] <> 0)
ON ROWS
FROM [AS_Cube]
Cheers,
Jason
P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)
Free Windows Admin Tool Kit Click here and download it now
January 19th, 2011 2:15pm
Hi Jason,
I tried your solution, but the queries are not giving same number of records as I get from my original query for parameter 2.
My query returns 421 rows & 2 columns.
Solution given by you returns 24320 rows & 3 columns.
This is due to cartesian join.
Can you suggest any changes that I can implement to get exact number of records.
Thanks.-Nitin Pawar
January 20th, 2011 1:34am
Hi Jason,
I tried your solution. However, I am not getting same no. of records with the modified qurey.
My query returns 421 rows & 2 columns.
Solution proposed by you returns 23358 rows & 3 columns.
Can you suggest how can I implement the queries suggested to get exact records.
Thanks.
-Nitin Pawar
Free Windows Admin Tool Kit Click here and download it now
January 20th, 2011 3:40am