Calculated measure in SSAS Cube either very slow or doesn't have all functionality

Question

I have a SSAS Cube with many calculated measures written in MDX.

One specific is causing me trouble - the Running Totals.

Purpose of the calculated measure

The calculated measure should sum all movements from B.O.T. until the end of time, Meaning when there is no more movements because we have reached present day, then the value should be repeated until there is no more dates in my time dimension. Example:

FY13  0.00
FY14  10.00
FY15  13.00
FY16  14.00
FY17  20.00
FY18  20.00
FY19  20.00
FY20  20.00
FY21  20.00

Also the measure should work along all my date hierarchies (there is four). I actually got it to work as intended with the following code:

CREATE MEMBER CURRENTCUBE.[Measures].[Actual Balance LCY]
AS AGGREGATE(
  {NULL:[Posting Date].[Calendar Year Y-Q-M-D].CurrentMember}
   * {NULL:[Posting Date].[Calendar Year Y-M-D].CurrentMember}
   * {NULL:[Posting Date].[Fiscal Year Y-M-D].CurrentMember}
  * {NULL:[Posting Date].[Fiscal Year Y-Q-M-D].CurrentMember}
,[Measures].[Actual LCY]
), 
FORMAT_STRING = "#,##0.00;-#,##0.00", 
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'Actual'; 

The problem:

When the measure is added to a PivotTable in Excel and e.g. two dimensions are added on the row and one date hierarchy on columns the query becomes very slow. 40-60 seconds ( I know this is slow, cause we have a legacy cube build in timeXtender that does the same Pivot table in 5-10 seconds on the same amount of data)

I added NON_EMPTY to the MDX. Then the query is fast, but there is no more values after FY17 which is not intended behavior. In the legacy solution NON_EMPTY is also not typed, but it is still faster.

The question:

What to do, to get both the intended behavior of the measure and a reasonable query speed?

Answer 1

Your performance stopper here:

{NULL:[Posting Date].[Calendar Year Y-Q-M-D].CurrentMember}
* {NULL:[Posting Date].[Calendar Year Y-M-D].CurrentMember}
* {NULL:[Posting Date].[Fiscal Year Y-M-D].CurrentMember}
* {NULL:[Posting Date].[Fiscal Year Y-Q-M-D].CurrentMember}

This cross-joining operation is simply too heavy. There are two ways I'd use. Pick which you like more:

1: Depend on which hierarchy you pick you get the result (no need to cross-join everything).

Aggregate(
   case 
      when not [Posting Date].[Calendar Year Y-Q-M-D].CurrentMember is [Posting Date].[Calendar Year Y-Q-M-D].DefaultMember 
      then {NULL:[Posting Date].[Calendar Year Y-Q-M-D].CurrentMember}
      when not [Posting Date].[Calendar Year Y-M-D].CurrentMember is [Posting Date].[Calendar Year Y-M-D].DefaultMember 
      then {NULL:[Posting Date].[Calendar Year Y-M-D].CurrentMember}
      when not [Posting Date].[Fiscal Year Y-M-D].CurrentMember is [Posting Date].[Fiscal Year Y-M-D].DefaultMember 
      then {NULL:[Posting Date].[Fiscal Year Y-M-D].CurrentMember}
      when not [Posting Date].[Fiscal Year Y-Q-M-D].CurrentMember is [Posting Date].[Fiscal Year Y-Q-M-D].DefaultMember 
      then {NULL:[Posting Date].[Fiscal Year Y-Q-M-D].CurrentMember}
      else NULL
   end,
   [Measures].[Actual LCY]
)

2: Add a max day measure (so it knows last day of the current member (hierarchy-agnostic):

Aggregate(
   NULL:StrToMember('[Posting Date].[Day].[' + cint([Measures].[Max Day]) + ']')
   [Measures].[Actual LCY]
)