Performance improvement considerations for cube calculations
In this article you will find examples of original cube calculations as well as new cube calculations that generally will improve performance.
Using SUM { [ ] , [ ] } instead of [ ] + [ ]
Good to know
The SUM function can only be used on the summation of members or tuples not on substractions.
The SUM function can only be used on tuples with the same dimensions.
Example 1 Calculation with 1 dimension
Original cube calculation:
([ACC].[ACC].[OperatingIncome] + [ACC].[ACC].[TotalDeprAmort])
New cube calculation:
SUM({[ACC].[ACC].[OperatingIncome], [ACC].[ACC].[TotalDeprAmort]})Example 2 Calculation (including substraction) with 1 dimension
Original cube calculation:
([ACC].[ACC].[OperatingExpenses] + [ACC].[ACC].[Administrative] - [ACC].[ACC].[Depreciation] - [ACC].[ACC].[Amortization])
New cube calculation:
SUM({[ACC].[ACC].[OperatingExpenses], [ACC].[ACC].[Administrative]}) –SUM({[ACC].[ACC].[Depreciation], [ACC].[ACC].[Amortization]})Example 3 Calculation with multiple dimensions
Original cube calculation:
([VIW].[VIW].[YTD],[YER].[YER].PrevMember,[PER].[PER].Dec,[MEA].[MEA].[[None]]])-
([VIW].[VIW].[YTD],[YER].[YER].PrevMember,[PER].[PER].CurrentMember,[MEA].[MEA].[[None]]])+
([VIW].[VIW].[YTD],[YER].[YER].CurrentMember,[PER].[PER].CurrentMember,[MEA].[MEA].[[None]]])
New cube calculation:
SUM({([VIW].[VIW].[YTD],[YER].[YER].PrevMember,[PER].[PER].Dec,[MEA].[MEA].[[None]]]),([VIW].[VIW].[YTD],[YER].[YER].CurrentMember,[PER].[PER].CurrentMember,[MEA].[MEA].[[None]]])})-
([VIW].[VIW].[YTD],[YER].[YER].PrevMember,[PER].[PER].CurrentMember,[MEA].[MEA].[[None]]])
Replacing InStr with properties
The InStr statement is used frequently at customers that have split dimensions (SAP, Essbase).
Example 4 InStr on Time dimension
Original cube calculation:
Instr([Time].[H1].currentmember.name,“2017”) <>0
When the Properties of the Year dimension are properly populated, the above cube calculation can be replaced by the following cube calculation:
[Time].[H1].currentmember.Properties(”Year”) = “2017”