MDX Query with Crossjoin and Filter Times Out

Question

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] )

Answer 1

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

Answer 2

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] )