Slow SSAS cumulative sum calculated measure

Question

I have a cumulative sum of a measure structured as:

Aggregate (
    { NULL : [Date].[Year - Month - Date].CurrentMember } 
    ,[Measures].[Applications] )

From the date of the first application to the current date, the days of the date range must be contiguous.

The Date dimension however contains dates ranging from 1900-01-01 to well into the future.

I've attempted to eliminate dates before the first application, and future dates by structuring the calculated measure as follows:

CREATE MEMBER CURRENTCUBE.[Measures].[Applications TD] AS
CASE 
WHEN   
    /* Eliminates dates before first applications, i.e. year 1900-01-01 */
    Aggregate (
        { NULL : [Date].[Year - Month - Date].CurrentMember } 
        ,[Measures].[Applications] ) < 0   
THEN NULL
WHEN
    /* Eliminates dates after today */
    [Date].[Year - Month - Date].CurrentMember.MemberValue >= StrToMember('[Date].[Date].&['+Format(Now(),"yyyy-MM-ddT00:00:00")+']').MemberValue 
THEN NULL
ELSE
    Aggregate (
            { NULL : [Date].[Year - Month - Date].CurrentMember } 
            ,[Measures].[Applications] )           
END

I have been unsuccessful in any attempt to optimizing this by aggregating only where needed using a SCOPE as an alternative to the case statement, utilizing EXISTS and EXCEPT functions and many others.

When browsing the cube and dimensioning [Measures].[Applications TD] by [Date].[Year - Month - Date] user-defined hierarchy it is terribly slow.

do you need to use `Aggregate` or is it ok to use `Sum` ? – whytheq Nov 28 '17 at 19:24 — whytheq, Nov 28 '17 at 19:24
Sum should work in this case. – samb0x Nov 30 '17 at 13:50 — samb0x, Nov 30 '17 at 13:50

whytheq · Accepted Answer · 2017-12-02T15:36:49.343

IIF is generally faster than CASE, and SUM is often faster than AGGREGATE.
Although your main problem is the second part of your condition using membervalue - is it required or will the following not do the same thing? :

CREATE MEMBER CURRENTCUBE.[Measures].[Applications TD] AS
IIF(
     SUM (
        { NULL : [Date].[Year - Month - Date].CurrentMember } 
        ,[Measures].[Applications] 
     ) < 0 
   , NULL
   , 
   SUM (
         { NULL : [Date].[Year - Month - Date].CurrentMember } 
          ,[Measures].[Applications] 
    ) 
)

I'd separate this out as a custom member:

CREATE MEMBER CURRENTCUBE.[Date].[Date].[All].[Today] AS //<< a  little of syntax for this create 
    StrToMember('[Date].[Date].&['+Format(Now(),"yyyy-MM-ddT00:00:00")+']')

Then try a nested IIF:

CREATE MEMBER CURRENTCUBE.[Measures].[Applications TD] AS
IIF(
    [Date].[Year - Month - Date].CurrentMember.MemberValue >= [Date].[Date].[All].[Today].MemberValue
   , NULL
   , IIF(
       SUM (
          { NULL : [Date].[Year - Month - Date].CurrentMember } 
          ,[Measures].[Applications] 
       ) < 0 
     , NULL
     , 
     SUM (
           { NULL : [Date].[Year - Month - Date].CurrentMember } 
            ,[Measures].[Applications] 
      ) 
  )
)

BUT

Rather than bothering with the "Today" member it will be a lot more efficient if you add an isToday column to DimDate - then have an attribute of the cubes date dimension using the column. That way you should be able to simplify this [Date].[Year - Month - Date].CurrentMember.MemberValue >= [Date].[Date].[All].[Today].MemberValue

Thank you for the recommendations. With this measure I can build visuals against Date to see the cumulative total to each day. Without the second part with membervalue, essentially the value up to today is repeated for each day until 2027 (DimDate contains dates to 10 years in the future). Perhaps that should be another question entirely? — samb0x, Dec 01 '17 at 15:35
@samb0x ok - I now understand your problem, our cubes only have dates up to yesterday in DimDate - this is the reason I didn't understand. Is the measure quick now with the changes ? Do you have access to make changes to the warehouse / cube ? — whytheq, Dec 02 '17 at 15:19
I've implemented a version of this and it's already a big improvement. I'm unclear on how to construct "[Date].[Date].[All].[Today]" though. I have a new column "Today" in DimDate that has today's date, then use [Date].[Year - Month - Date].CurrentMember.MemberValue >= [Date].[Today].item(0).MemberValue It works much better but am interested on how you mean to construct "[Date].[Date].[All].[Today]" — samb0x, Dec 04 '17 at 16:29
@samb0x - my answer is two different approaches - either you need to construct Today in the mdx OR what I suggested in the `BUT` section which is what you have implemented — whytheq, Dec 04 '17 at 21:36

Slow SSAS cumulative sum calculated measure

1 Answers1