Search code examples
ssasmdx

Scope Statement Alternative to Slow CrossJoin Count MDX

The below MDX works for my purpose but is extremely slow. Is there a scope statement Essentially I want to count the remaining cross joined contact/purchases combinations where the summed amount is $>5000.

The cross product is a total of 290M rows but I am not sure how to structure this differently to improve performance. Thank you for any help.

 CREATE HIDDEN STATIC SET [Over 5K Plus Test 2] 
 AS NONEMPTY (([Contact].[Contact ID].[Contact ID],[Fund Sold].[Fund Sold ID].[Fund Sold ID]),
[Measures].[FA And Team Gross Sales with FAs Including All Vehicles]); 

CREATE MEMBER CURRENTCUBE.[Measures].[FA and Team Product Count]
AS COUNT(EXISTING((Filter([Over 5K Plus Test 2], [Measures].[FA And Team Gross Sales with FAs Including All Vehicles] >= 5000)))),
Solution

  • Try this which avoids the Filter:

    CREATE MEMBER CURRENTCUBE.[Measures].[FA and Team Product Count]
AS SUM(
 Existing [Contact].[Contact ID].[Contact ID].Members
 * Existing [Fund Sold].[Fund Sold ID].[Fund Sold ID].Members,
 IIF([Measures].[FA And Team Gross Sales with FAs Including All Vehicles] >= 5000, 1, Null)
);

    If that is still slow then post the calculation behind FA And Team Gross Sales with FAs Including All Vehicles

    The more efficient way to accomplish this requires a bit more effort but will perform better because it avoids the Existing function. First you have to create a column in the DSV which is a calculated column in the fact table using this expression:

    CAST(null as int)

    Then create a new measure called “FA and Team Product Count” on this column. Expand the column binding and choose NullHandling=Preserve. This has to be a physical measure not a calculated measure because only scoped assignments to physical measures aggregate up.

    Then add the following statement to the MDX script (instead of the calculated measure mentioned at the top):

    ([Measures].[FA and Team Product Count],
 [Contact].[Contact ID].[Contact ID].Members,
 [Fund Sold].[Fund Sold ID].[Fund Sold ID].Members) =
 IIF([Measures].[FA And Team Gross Sales with FAs Including All Vehicles] >= 5000, 1, Null);