CODEWITHSUNDEEP
filtermdxcross-join
Cody
Cody

Reputation: 3

MDX Query with Crossjoin and Filter Times Out

I keep timing out every time I try to run this query. It seems to work until I add in the "([Finished Product].[Primary].MEMBERS," section into the rows section of the query. Any ideas?

SELECT NON EMPTY {[Measures].[Retailer Event Margin Pcnt (Actual, WB Total, LE)], [Measures].[Incr Cnsmp Units (Actual)]} ON COLUMNS,

NON EMPTY {[Finished Product].[Primary].MEMBERS * [Promotion Plan].[Promotion Plan].[Event].MEMBERS} HAVING LEFT([Promotion Plan].[Promotion Plan].CurrentMember.Name, 6) = "Anchor" ON ROWS FROM [PEA] WHERE ( [Time].[Fiscal].[Fiscal Year].&[2017] )

Upvotes: 0

Views: 565

Answers (2)

GregGalloway
GregGalloway

Reputation: 11625

Try filtering before the Crossjoin:

SELECT NON EMPTY {[Measures].[Retailer Event Margin Pcnt (Actual, WB Total, LE)], [Measures].[Incr Cnsmp Units (Actual)]} ON COLUMNS,

NON EMPTY [Finished Product].[Primary].MEMBERS 
* Filter([Promotion Plan].[Promotion Plan].[Event].MEMBERS, LEFT([Promotion Plan].[Promotion Plan].CurrentMember.Name, 6) = "Anchor")
ON ROWS FROM [PEA] 
WHERE ( [Time].[Fiscal].[Fiscal Year].&[2017] )

Upvotes: 1

Tom Huang
Tom Huang

Reputation: 192

Try the "nonempty" key word , which evaluated the set on axis level, "non empty" is evaluated on top of the query which may have a performance issue for a large set.

nonempty( nonempty([finished product].[primary].members,[your measure])*[promotion plan].[promotion plan].members,[your measure])

It just a reference, and you need do some changs on it for your case.

hope it helps.

MDXHelper : IDE to Write, Analyze, Tuning, Debug MDX efficiently

Upvotes: 1

Related Questions