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])
MDX Performance Issue with TOPCOUNT Using Calculated/Derived by Time Specific Members
Robin E Davies
Deepak,
Yes the numeric expression argument of the Topcount is determined via user input. Currently we are using AS 2000 and will be changing to AS 2005 over the next couple of months.
The member count for NDC is 11998 and for Product it is 12064.
Any Ideas
Luka Ivkic
Assuming that the NonEmptyCrossJoin() is intended to eliminate tuples with empty values of [Measures].[Birxm Cur Awp Ttl] in the prior period, and that [Birxm Cur Awp Ttl] is an intrinsic, not calculated, measure, you could try restructuring the NECJ(), like:
NonEmptyCrossJoin([NDC].[Birxn Ndc].AllMembers, [Product].[NDCLabel].[Birxp Lbl Nm].members,
{[Measures].[Birxm Cur Awp Ttl]}, {[Period].[All Period].[07].[02].[06].[2006-08-12]}, 2)
KDeepak
"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."
So, are you saying that, if you restructure the above query to use just a single period, it runs faster If so, could you post such a version of the query, and the time difference between versions of the query
Jordan Terrell
Deepak, your assumption is not completely accurate the [measures].[Birxm Cur Awp Ttl] or member [Measures].[Prio Awp Ttl] is the "value of Numeric Expression " of the TOPCOUNT function. In other words return the top 50 based on member [Measures].[Prio Awp.Ttl]. to make it worse the user have the ability to say I want the top 50 based on calculated member [Measures].[BM Variance]. See mdx code.
Hope that Clarifies it a bit more for you.
I tried the TOPCOUNT with the following changes and it actually ran slower by 11 seconds
Method 1:
TOPCOUNT( {nonemptyCROSSJOIN ([NDC].[Birxn Ndc].AllMembers, [Product].[NDCLabel].[Birxp Lbl Nm].members,2)}, 050,
([Period].[All Period].[07].[02].[06].[2006-08-12], [Measures].[Prio Awp Ttl]))
Method 2:
TOPCOUNT( {nonemptyCROSSJOIN ([NDC].[Birxn Ndc].AllMembers, [Product].[NDCLabel].[Birxp Lbl Nm].members,2)}, 050,
( [Measures].[Prio Awp Ttl], [Period].[All Period].[07].[02].[06].[2006-08-12] ))
Method 3:
TOPCOUNT( {nonemptyCROSSJOIN ([NDC].[Birxn Ndc].AllMembers, [Product].[NDCLabel].[Birxp Lbl Nm].members)}, 050,
( [Measures].[Prio Awp Ttl], [Period].[All Period].[07].[02].[06].[2006-08-12] ))
I appreciate all your help so far. Let me know what you think.
LLeuthard
Well, I would re-iterate my previous suggestion, provided that there is only 1 fact table/cube (with [Birxm Cur Awp Ttl] as an intrinsic measure), and that the numeric expression should return a null whenever there is no corresponding fact data for the prior period. The TopCount() would then look like:
TOPCOUNT( NonEmptyCrossJoin([NDC].[Birxn Ndc].AllMembers, [Product].[NDCLabel].[Birxp Lbl Nm].members,{[Measures].[Birxm Cur Awp Ttl]}, {[Period].[All Period].[07].[02].[06].[2006-08-12]}, 2)}, 050, [Measures].[Prio Awp Ttl])
VuaCorona
Deepak, Yes that seem to be the case. The TOPCOUNT seems to be causing it with a different period. I ran the problem query thru MDX Sample Application and timed it. It takes 1 minute 14 seconds. Changed the query to have the same period at the member level as the where clause and it returned Instantaniously. See code below. I also would like to mention that the CUBE is partitioned on Period (as you see in the where clause). This problem is really becoming a major issue.
Deepak the web page that executes this MDX executes a total of three MDX queries to build the statistical data that the users require. Added together they exceed the server time out limit.
I really appreciate any suggestions and help that you can provide.
Changed MDX Code.
WITH
MEMBER [Measures].[Prio Awp Ttl] AS '( [Measures].[Birxm Cur Awp Ttl], [Period].[All Period].[07].[02].[06].[2006-08-19])'
MEMBER [Measures].[Prio Cst Ttl] AS '( [Measures].[Birxm Cur Cst Ttl], [Period].[All Period].[07].[02].[06].[2006-08-19])'
MEMBER [Measures].[Prio Shp Unt] AS '( [Measures].[Birxm Cur Shp Unt], [Period].[All Period].[07].[02].[06].[2006-08-19])'
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])
Intikhab
Are you saying that the Numeric Expression argument of TopCount is determined via user input, and is also dynamic My suggestion relates to common scenarios where there may be multiple empty tuples in a cross-joined set, and NonEmptyCrossJoin() can be used to efficiently eliminate them. However, the use of NonEmptyCrossJoin() may not necessarily be valid with an arbitrary calculated measure, because it could have non-empty values, even without underlying fact table data (which is what NonEmptyCrossJoin() filters out). So it may be harder to optimize a query where the relation of the results to the underlying cube data is not known in advance.
By the way, are you using AS 2000 or AS 2005, what are the sizes of the crossjoined dimensions [NDC] and [Product], and what percentage of the crossjoined set is empty