Pages

Wednesday, December 31, 2014

Working with System date + SSAS + MDX

The purpose of following MDX query is to create the CompleteDataMonthFlag flag which can be used to identify the latest month with complete data. For example, if we receive data with granularity less than a month (i.e. weekly or daily), we could use this flag to identify the latest month with complete data.

WITH 
// Following set is to get the system year and month
SET [System Month - Operational] as 
    StrToMember("[Period].[Operational Year - Quarter - Month].[Month].&[" + Format(now(), 'yyyy') + Format(now(), 'MM')+"]")

// Following set is to get the month with latest data
SET [Latest Data Month - Operational] AS Tail(Nonempty([Period].[Operational Year - Quarter - Month].[Month].members), 1)
//Member [Measures].[LatestDataMonth] AS LatestDataMonth.item(0).name
//Member [Measures].[CurrentMonth] AS CurrentMonth.item(0).name
SET LatestMonth AS
    CASE
    WHEN [Latest Data Month - Operational].item(0)  = [System Month - Operational].item(0)
        THEN [Latest Data Month - Operational].item(0).lag(1)  
    WHEN [Latest Data Month - Operational].item(0)  <> [System Month - Operational].item(0)
        THEN [Latest Data Month - Operational].item(0) 

    END      
MEMBER [Measures].[CompleteDataMonthFlag] as
    IIF ([Period].[Operational Year - Quarter - Month] is LatestMonth.Item(0),1, 0)

SELECT {
[Measures].[CompleteDataMonthFlag]
}
ON 0 ,
NON EMPTY
[Period].[Operational Year - Quarter - Month].[Month].members
ON 1
FROM [Cube Name];


IF the [CompleteDataMonthFlag] needs to be implemented within the SSAS cube as a calculated member, the following script can be used

Create Set CurrentCube.[System Month - Operational] AS 

    StrToMember("[Period].[Operational Year - Quarter - Month].[Month].&[" + Format(now(), 'yyyy') + Format(now(), 'MM')+"]");
Create Set CurrentCube.[Latest Data Month - Operational] AS 

    Tail(Nonempty([Period].[Operational Year - Quarter - Month].[Month].members), 1);
Create Set CurrentCube.LatestMonth AS
    CASE
    WHEN [Latest Data Month - Operational].item(0)  = [System Month - Operational].item(0)
        THEN [Latest Data Month - Operational].item(0).lag(1)

    WHEN [Latest Data Month - Operational].item(0)  <> [System Month - Operational].item(0)
        THEN [Latest Data Month - Operational].item(0)

    END;     
CREATE MEMBER CURRENTCUBE.[Measures].[CompleteDataMonthFlag] as
    IIF ([Period].[Operational Year - Quarter - Month] is LatestMonth.Item(0),1, 0);


Ref: 
http://sqlblog.com/blogs/mosha/archive/2007/05/23/how-to-get-the-today-s-date-in-mdx.aspx
http://sqljoe.wordpress.com/2011/07/22/dynamically-generate-current-year-month-or-date-member-with-mdx/