SSAS: Using MDX to Calculate Snapshot Measures

In a data warehouse, Periodic Snapshot table is a type of fact table that “freezes” the measure value at recurring reporting periods. This enables reporting of measures AS AT a point in time. Periodic Snapshots are usually created from Transaction Fact tables that support reporting FOR the period. Using MDX it is possible to create snapshot values for measures from transaction fact without creating Periodic Snapshot tables.

For this example I am using Adventure Works cube. Let’s  determine the Outstanding Deliveries AS AT a reporting period.

Outstanding Deliveries AS AT Reporting Period.

Let’s first define Outstanding Deliveries as:

Outstanding Deliveries AS AT reporting period = Sales AS AT Reporting Period- Deliveries AS AT Reporting Period
Where, Sales AS AT Reporting Period =SUM(Sales FOR Reporting Period) from start to current reporting period

Deliveries AS AT Reporting Period

 =SUM(Deliveries FOR Reporting Period) from start to current reporting period

The MDX for the snapshot calculation would be:

WITH MEMBER [Measures].[Deliveries FOR Reporting Period]
AS [Internet Order Count]

MEMBER [Measures].[Sales FOR Reporting Period]
AS (LinkMember([Delivery Date].[Fiscal Year].CurrentMember,[Date].[Fiscal Year]),[Measures].[Internet Order Count],Root([Delivery Date]))

MEMBER [Measures].[Sales AS AT Reporting Period]
AS SUM(NULL: [Delivery Date].[Fiscal Year].CurrentMember,[Measures].[Sales FOR Reporting Period])

MEMBER [Measures].[Deliveries AS AT Reporting Period]
AS SUM(NULL: [Delivery Date].[Fiscal Year].CurrentMember,[Measures].[Deliveries FOR Reporting Period])

MEMBER [Measures].[Outstanding Deliveries AS AT Reporting Period]
AS [Measures].[Sales AS AT Reporting Period] - [Measures].[Deliveries AS AT Reporting Period]
SELECT {[Measures].[Sales FOR Reporting Period]
,[Measures].[Deliveries FOR Reporting Period]
,[Measures].[Sales AS AT Reporting Period]
,[Measures].[Deliveries AS AT Reporting Period]
,[Measures].[Outstanding Deliveries AS AT Reporting Period]} ON COLUMNS,
NON EMPTY{[Delivery Date].[Fiscal Year].Children} ON ROWS
FROM [Adventure Works]

The result of this MDX query would be:

Here’s how the MDX calculations work

Deliveries FOR Reporting Period

  • The reporting period in this example is Delivery Date.
  • Internet Order Count over Delivery Date gives the Deliveries FOR Reporting Period

Sales FOR Reporting Period

  • Since Delivery Date is used as reporting period, sales in the reporting period has to be evaluated over the corresponding Order Date member. For this reason LinkMember() function is used. Haven’t found a better post than this one from Richard Lees for a good explanation of LinkMember() function
  • Internet Order Count over Order Date (thru Delivery Date) gives the Sales FOR Reporting Period

Sales AS AT Reporting Period

  • Having derived Sales FOR Reporting Period, the snapshot measure Sales AS AT Reporting Period is calculated as Sales ever made from the start to the current reporting period.
  • Please note since Sales FOR Reporting Period calculation uses LinkMember() to get the Order Date equivalent thru the Delivery Date, in this calculation Delivery Date is used instead of Order date. That’s why the date range is (NULL: [Delivery Date].[Fiscal Year].CurrentMember) instead of (NULL: [Date].[Fiscal Year].CurrentMember)

Deliveries AS AT Reporting Period

  • This snapshot measure is calculated as Deliveries ever made from start to the current reporting period

Outstanding Deliveries AS AT Reporting Period

  • The difference between the sales snapshot at the reporting period and the delivery snapshot at the reporting period gives the deliveries in hand which is the snapshot of the outstanding deliveries.

The above example demonstrates the capability of MDX to create calculated snapshot measures. Some implementation may require a materialized periodic snapshot table, but it is worthwhile to consider the options MDX provides before arriving at design decision to create materialized periodic snapshot fact tables.

Benny Austin

5 thoughts on “SSAS: Using MDX to Calculate Snapshot Measures”

  1. Thanks Benny. I separated the data into a different fact table. We are a firm selling time so time is like items produced or inventory waiting to be sold…we will get revenue for some but not all of it. WIP is time worked not converted into revenue. I believe it is best practice to distinguish between revenue and inventory/WIP. This way I can better show what my WIP was at any point in time. It will also allow for reviewing of aged WIP, etc.

    Reply

  2. The tsql is more like the below:
    select sum(ToBill_Amt) AS WIP from Fact.Time where (BillDateKey is null or BillDateKey >= 20130131) and WIPDateKey <=20130131 and ClientKey = 9940

    Reply

  3. I want to see WIP data in my cube. My fact table has a WIP_Date and a Bill_Date. If the item is not billed hence no Bill_Date, then it is in WIP. WIP is a snapshot…so at any point in time I should be able to tell what my WIP was. The below SQL query will give me this in tsql for period 201307:

    select * from Fact.Time where BillDateKey is null or BillDateKey >= 20130731 and WIPDateKey <=20130731

    How do I write this in MDX?

    Thanks for your help.

    Reply

    1. Sophia, if your fact table is a snapshot based on WIP date then there is no need to use MDX expression to get WIP. Instead you could add a calculated column in your fact view (or named calculation in dsv). If BillDateKey is null or BillDateKey >= WIP DateKey then WIP=ToBillAmt else 0. This new column will become a regular measure in cube.

      Reply

Leave a comment