Search code examples
optimizationmdx

Optimizing SUM MDX

I have a large MDX query that has historically ran in 25 seconds; this has been fine. I have recently made the change outlined below, and the run time now exceeds 90 seconds.

I am worried that if I need to perform similar filters, this is only going to exacerbate the problem. Any recommendations on how to perform this sort of a filter more efficiently for MDX?

// My simple aggregation of all hours runs in 25 seconds
MEMBER [Measures].[Calc_TotalHours] AS
    ([Measures].[PostedHours] + [Measures].[UnpostedHours])

// The same aggregation that excludes a certain type of hours
MEMBER [Measures].[Calc_TotalHours] AS
    SUM ( -{ [Activity].[ProjectCategoryName].&[Overtime] }
             , ([Measures].[ActualHours] + [Measures].[TimeSheetHours_Unposted]) 
        )

I am wondering if SUM is the wrong function to use here?

Solution

  • You are right, the Sum() function is heavy against big sets. Try to reuse pre-aggregated All member:

    ([Activity].[ProjectCategoryName].[All],[Measures].[ActualHours]) 
+ ([Activity].[ProjectCategoryName].[All],[Measures].[TimeSheetHours_Unposted])
- ([Activity].[ProjectCategoryName].&[Overtime],[Measures].[ActualHours]) 
- ([Activity].[ProjectCategoryName].&[Overtime],[Measures].[TimeSheetHours_Unposted])