I am new at The MDX game and need some help. I have the following MDX that executes a wee bit to slow when I specify one of the following members (See MDX below) [Measures].[Prio Shp Unt], [Measures].[Unit Variance], [Measures].[Prio Cst Ttl], [Measures].[Prio Awp Ttl], [Measures].[BM Variance] in the TOPCOUNT statement. I have arrived at the conclusion that the problem is because I speicfy a period at the member selection that is different to the period on my where clause.
Now the problem is that both the periods are user driven and can be anything from current back for the last three years. In other words the users have the option to specify which two periods they want to compare. Also the TOPCOUNT measure is user driven and can be any one of the measures in the select statement.
Please HELP somebody.
MDX Code
WITH
MEMBER [Measures].[Prio Awp Ttl] AS '( [Measures].[Birxm Cur Awp Ttl], [Period].[All Period].[07].[02].[06].[2006-08-12])'
MEMBER [Measures].[Prio Cst Ttl] AS '( [Measures].[Birxm Cur Cst Ttl], [Period].[All Period].[07].[02].[06].[2006-08-12])'
MEMBER [Measures].[Prio Shp Unt] AS '( [Measures].[Birxm Cur Shp Unt], [Period].[All Period].[07].[02].[06].[2006-08-12])'
MEMBER [Measures].[Unit Variance] AS '( [Measures].[Birxm Cur Shp Unt] - [Measures].[Prio Shp Unt])'
MEMBER [Measures].[Prio BM] AS '( [Measures].[Prio Awp Ttl] - [Measures].[Prio Cst Ttl])'
MEMBER [Measures].[BM Variance] AS '( [Measures].[Cur BM] - [Measures].[Prio BM])'
Select {
[Measures].[Birxm Cur Shp Unt],
[Measures].[Prio Shp Unt],
[Measures].[Unit Variance],
[Measures].[Birxm Cur Cst Ttl],
[Measures].[Birxm Cur Awp Ttl],
[Measures].[Prio Cst Ttl],
[Measures].[Prio Awp Ttl],
[Measures].[BM Variance]
}
ON COLUMNS,
TOPCOUNT( {nonemptyCROSSJOIN ([NDC].[Birxn Ndc].AllMembers, [Product].[NDCLabel].[Birxp Lbl Nm].members)}, 050, [Measures].[Prio Awp Ttl])
ON ROWS FROM BIRXMR
WHERE ([Period].[All Period].[07].[02].[06].[2006-08-19], [Brand Generic].[All Brand Generic].[BRAND], [Shipped From].[All Shipped From], [Store].[All Store], [Product].[Short Code].[All Product])
Now the problem is that both the periods are user driven and can be anything from current back for the last three years. In other words the users have the option to specify which two periods they want to compare. Also the TOPCOUNT measure is user driven and can be any one of the measures in the select statement.
Please HELP somebody.
MDX Code
WITH
MEMBER [Measures].[Prio Awp Ttl] AS '( [Measures].[Birxm Cur Awp Ttl], [Period].[All Period].[07].[02].[06].[2006-08-12])'
MEMBER [Measures].[Prio Cst Ttl] AS '( [Measures].[Birxm Cur Cst Ttl], [Period].[All Period].[07].[02].[06].[2006-08-12])'
MEMBER [Measures].[Prio Shp Unt] AS '( [Measures].[Birxm Cur Shp Unt], [Period].[All Period].[07].[02].[06].[2006-08-12])'
MEMBER [Measures].[Unit Variance] AS '( [Measures].[Birxm Cur Shp Unt] - [Measures].[Prio Shp Unt])'
MEMBER [Measures].[Prio BM] AS '( [Measures].[Prio Awp Ttl] - [Measures].[Prio Cst Ttl])'
MEMBER [Measures].[BM Variance] AS '( [Measures].[Cur BM] - [Measures].[Prio BM])'
Select {
[Measures].[Birxm Cur Shp Unt],
[Measures].[Prio Shp Unt],
[Measures].[Unit Variance],
[Measures].[Birxm Cur Cst Ttl],
[Measures].[Birxm Cur Awp Ttl],
[Measures].[Prio Cst Ttl],
[Measures].[Prio Awp Ttl],
[Measures].[BM Variance]
}
ON COLUMNS,
TOPCOUNT( {nonemptyCROSSJOIN ([NDC].[Birxn Ndc].AllMembers, [Product].[NDCLabel].[Birxp Lbl Nm].members)}, 050, [Measures].[Prio Awp Ttl])
ON ROWS FROM BIRXMR
WHERE ([Period].[All Period].[07].[02].[06].[2006-08-19], [Brand Generic].[All Brand Generic].[BRAND], [Shipped From].[All Shipped From], [Store].[All Store], [Product].[Short Code].[All Product])