Welcome to EMC Consulting Blogs Sign in | Join | Help

Christian Wade's Blog

Days Since Last Event: TSQL vs. MDX vs. DAX

 

I have often had to report the “days since last event”.  I’m going to do it here using a variety of query languages and compare them.

 

Consider the following TSQL query on AdventureWorksDW - and the output below.

 

SELECT d.FullDateAlternateKey, COUNT(dt.OrderDateKey) AS OrderCount

FROM dbo.DimDate d

LEFT JOIN

(SELECT s.OrderDateKey, p.Color

 FROM dbo.FactInternetSales s

 INNER JOIN dbo.DimProduct p ON s.ProductKey = p.ProductKey) dt

    ON d.DateKey = dt.OrderDateKey AND dt.Color = 'Yellow'

WHERE d.CalendarYear = 2003

GROUP BY d.FullDateAlternateKey

ORDER BY FullDateAlternateKey

 

 

SQL Output 1

 

 

What if we need a DaysSinceLastOrder column that would return this?

 

SQL Output 2

 

 

 

In TSQL, we could do it using a recursive CTE that iterates backwards by one date at a time.

 

WITH BaseQuery(FullDateAlternateKey, OrderCount) AS

(

    SELECT d.FullDateAlternateKey, COUNT(dt.OrderDateKey) AS OrderCount

    FROM dbo.DimDate d

    LEFT JOIN

    (SELECT s.OrderDateKey, p.Color

     FROM dbo.FactInternetSales s

     INNER JOIN dbo.DimProduct p ON s.ProductKey = p.ProductKey) dt

        ON d.DateKey = dt.OrderDateKey AND dt.Color = 'Yellow'

    WHERE d.CalendarYear = 2003

    GROUP BY d.FullDateAlternateKey

 

), DaysSinceLast_Recursive(FullDateAlternateKey, OrderCount, DaysSinceLastOrder) AS

(

    -- anchor members

    SELECT FullDateAlternateKey, OrderCount, 0 AS DaysSinceLastOrder

    FROM BaseQuery b

    WHERE OrderCount > 0

 

    UNION ALL

 

    -- recursive members

    SELECT b.FullDateAlternateKey, b.OrderCount,

           d.DaysSinceLastOrder + 1 AS DaysSinceLastOrder

    FROM BaseQuery b

    INNER JOIN DaysSinceLast_Recursive d ON

        DATEADD(day, -1, b.FullDateAlternateKey) = d.FullDateAlternateKey

        AND NOT b.OrderCount > 0

)

SELECT * FROM DaysSinceLast_Recursive

ORDER BY FullDateAlternateKey

 

 

 

Or we could just get the max date with at least one order prior to the current date - and then use DATEDIFF to count the number of days.

 

WITH BaseQuery(FullDateAlternateKey, OrderCount) AS

(

    SELECT d.FullDateAlternateKey, COUNT(dt.OrderDateKey) AS OrderCount

    FROM dbo.DimDate d

    LEFT JOIN

    (SELECT s.OrderDateKey, p.Color

     FROM dbo.FactInternetSales s

     INNER JOIN dbo.DimProduct p ON s.ProductKey = p.ProductKey) dt

        ON d.DateKey = dt.OrderDateKey AND dt.Color = 'Yellow'

    WHERE d.CalendarYear = 2003

    GROUP BY d.FullDateAlternateKey

 

)

SELECT FullDateAlternateKey, OrderCount,

CASE WHEN OrderCount > 0 THEN 0

     ELSE

        DATEDIFF(day,

            (SELECT MAX(FullDateAlternateKey) FROM BaseQuery b2

             WHERE b2.FullDateAlternateKey < b.FullDateAlternateKey AND

             b2.OrderCount > 0),

            FullDateAlternateKey)

     END DaysSinceLastOrder

FROM BaseQuery b

ORDER BY FullDateAlternateKey

 

 

 

What about MDX?  We might be tempted to do it recursively in MDX.

(This code is just illustrating the concept – it doesn’t check the current level of the Calendar hierarchy, aggregate up the Calendar hierarchy, etc.)

 

WITH MEMBER [Measures].[Days Since Last Order] AS

    //check if current date member has order count that is empty or 0

    IIf( ([Date].[Calendar].CurrentMember,

          [Measures].[Internet Order Count]) = 0,

         ([Date].[Calendar].CurrentMember.PrevMember,

          [Measures].[Days Since Last Order]) + 1,

         Null

       )

SELECT

    {[Measures].[Internet Order Count],

     [Measures].[Days Since Last Order]} ON 0,

    [Date].[Calendar].[Date].Members ON 1

FROM [Adventure Works]

WHERE ([Product].[Color].&[Yellow],

       [Date].[Calendar Year].&[2003])

 

which returns what we want …

 

MDX Output 1

 

 

No faffing around with dates.  We leverage the inherent ordering of members in Analysis Services using .PrevMember.

 

But you may be aware that recursive MDX is often prone to performance problems.  It works OK on AdventureWorks, but take my word for it – it would run like a 3-legged dog on large volumes.

 

The following MDX version would perform much faster ...

 

WITH MEMBER [Measures].[Days Since Last Order] AS

   Count( Tail( NonEmpty( {Null : [Date].[Calendar].CurrentMember},

                          [Measures].[Internet Order Count] ),

                1).Item(0) : [Date].[Calendar].CurrentMember

        ) - 1

SELECT

    {[Measures].[Internet Order Count],

     [Measures].[Days Since Last Order]} ON 0,

    [Date].[Calendar].[Date].Members ON 1

FROM [Adventure Works]

WHERE ([Product].[Color].&[Yellow],

       [Date].[Calendar Year].&[2003])

 

and returns the same results (almost)

 

MDX Output 2

 

 

The above MDX again depends on the inherent ordering of members in Analysis Services, but this time it gets the last non null date (using the Tail function) and then counts the number of members between that and the Calendar current member.

 

 

How about DAX?  A DAX measure can't call another measure (which seems like a major limitation - maybe that will change as they enhance the language), let alone call itself recursively.

 

But the following does work.  Note: I had to convert the OrderDateKey in FactInternetSales to type datetime to get it to work.


=CALCULATE( COUNTROWS( DimDate ) - 1,

    DATESBETWEEN( DimDate[FullDateAlternateKey],

        LASTNONBLANK( DATESBETWEEN( DimDate[FullDateAlternateKey],

                    BLANK(),

                    LASTDATE( DimDate[FullDateAlternateKey] )

                    ),

                CALCULATE( COUNTROWS( FactInternetSales ) )

            ),

        LASTDATE( DimDate[FullDateAlternateKey] )

    )

)

 

DAX

 

 

I think the DAX version is somewhere in between the 2nd TSQL version and the 2nd MDX version.

  • Using BLANK() as the 2nd parameter for (the inner) DATESBETWEEN tells PowerPivot to use the first available date (similar behaviour to " Null :  " in MDX).
  • It then gets the last non-empty date using LASTNONEMPTY instead of the MAX function.
  • But it still has to faf around with dates because cannot depend on member ordering.

 

Update: here is a simpler version of the DAX calculation.

=LASTDATE( DimDate[FullDateAlternateKey] ) -
 LASTNONBLANK( DATESBETWEEN( DimDate[FullDateAlternateKey],
                             BLANK(),
                             LASTDATE( DimDate[FullDateAlternateKey] )
                           ),
               CALCULATE( COUNTROWS( FactInternetSales ) )
             )

It simply subtracts one date from the other and returns the right number.  Problem is the default formatting on the PivotTable is of type date, so the user has to change the format manually.  This will likely change in the future allowing formatting to be defined in the measure definition.

 

I have attached a zip of the Excel file to this post (see below) in case you want to take a look at it.

 

 

Which is the most elegant?  Which is the most intuitive?  I’ll let you decide …

 

 

Published Saturday, January 15, 2011 2:17 AM by christian.wade
Filed under: , , , , , ,

Attachment(s): Days Since Last in DAX.zip

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Ian Yates said:

I like your 2nd MDX example - very elegant.

The problem is similar to one you previously outlined (I just happened to read it before this article)

http://consultingblogs.emc.com/christianwade/archive/2006/04/30/MDX-Sprocs-and-Scripting_3A00_-An-Interesting-Example.aspx

There the MDX scripting approach was also elegant but took a different line of reasoning.  Do you have any preference for one over the other?

February 3, 2011 2:25 AM
 

rashmin said:

I want customer list which has value for last nonempty date in month.

Example.

Customer id is 1,2,3,4

Month : March

For customer 1,2,3 has value on 30-march.

For customer 4 has not value on 30-march but has value on 20-march.

I want customer 1,2,3 as result.

thanks in advance..

April 27, 2011 9:22 AM
 

TV said:

Your MDX examples helped med a lot, but i also need the previous member - the member before the meber i Your example. Hope You can help me on my way on this.

Best Regards.

October 13, 2011 2:21 PM

Leave a Comment

(required) 
(optional)
(required) 
Submit
Powered by Community Server (Personal Edition), by Telligent Systems