Slow SSAS cumulative sum calculated measure

Viewed 1455

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.

1 Answers
Related