MDX Query to Find Latest Data Date For Measure Groups in a Cube.

SSAS Cube has integrates data from multiple fact tables, with one or more fact tables per subject area.

Often we are required to show on dashboard the latest data update date for each subject area.

The following MDX query gives the latest data date for the different measure groups in Adventure Works cube.

WITH

  MEMBER [Measures].[InternetSales Date] AS

    Max

    (

      nonempty(

         [Date].[Date].Members

        ,( [Measures].[Internet Sales Amount])

      )

     , [Date].[Date].CurrentMember.Member_Key

    )

MEMBER [Measures].[ResellerSales Date] AS

    Max

    (

      nonempty(

         [Date].[Date].Members

        ,([Measures].[Reseller Sales Amount] )

      )

     , [Date].[Date].CurrentMember.Member_Key

    )

MEMBER [Measures].[Finance Date] AS

    Max

    (

      nonempty(

         [Date].[Date].Members

        ,( [Measures].[Amount])

      )

     , [Date].[Date].CurrentMember.Member_Key

    )

SELECT

  {

     [Measures].[InternetSales Date],

        [Measures].[ResellerSales Date],

        [Measures].[Finance Date]

  } ON COLUMNS

FROM [Adventure Works]

Results

Last InternetSales Update	Last ResellerSales Update	Last Finance Update
20080731	20080601	20080630

The query uses a calculated measure with Max function to figure out the latest data date for measure group. We need one such calculated measure for each measure group.