MDX

Introduction

There are a lot of great examples out there on how to build your own custom Time Intelligence into Analysis Services (MD). Just have a look at this, this, this, this and this. All good sources for solid Time Intelligence in SSAS.
One thing they have in common though, is that they all make the assumption that there is and will always be 52 weeks in a year. The data set I am currently working with is built on ISO 8601 standard. In short, this means that there is an (re-) occurrence of a 53rd full week as opposed to only 52 in the Gregorian version which is defined by: 1 Gregorian calendar year = 52 weeks + 1 day (2 days in a leap year).

The 53rd occurs approximately every five to six years, though this is not always the case. The last couple of times  we saw 53 weeks in a year was in 1995, 2000, 2006, 2012 and 2015. Next time will be in 2020. This gives you enough time to either forget about the hacks and hard-coded fixes in place to mitigate the issue OR bring your code in a good state, ready for the next time. I urge you do the latter as suggested by the work philosophy of the late Mærsk McKinney Møller: Constant Care.

The Real Issue

So why is this such a big deal? After all, it’s only a problem every say five-six years.

For starters, some built-in functions in Analysis Services will go bunkers over this sudden alienated week.

What is the ParallelPeriod of Week 53?
It doesn’t exist!

or

Which weeks are Cousins to Week 53?
None!

Your otherwise solid calculations will suddenly have wholes, blanks and nulls in them. The perfect line chart broken. Not to mention the Pie chart, where a perhaps crucial piece is missing!

In my line of work there have been a great deal of discussion about how to treat this troublesome Week 53. One suggestion was to just distribute the sale on Week 53 to all other weeks across the year. Every week thereby containing a fraction (1/52) more sale than usually – this way, comparison across years will even out. But what about companies that have a huge spike in sales around New Years Eve (think Liquor) – they would then not be able to compare the real sale around New Years Eve, because this would be disguised by the massive sale over the rest of the year.

Our working solution is to compare the same number of weeks as the current year you are operating with. In 2016 that’s 52 weeks, in 2015 it was 53 weeks.
The tricky part about this is to identify when to calculate what, and for this we need assistance from additional attributes in our calendar dimension.
New attributes to support this type of calculation are [Is Leap Week] and [Is Leap Year].

Is Leap Week has the value 1 whenever the current week is the 53rd week of the year. All other weeks are represented by a 0.

Is Leap Year has a value of 1 whenever the current year consists of 53 weeks. All other years are represented by the value 0. Arguably the name Leap Year could be considered confusing, as this normally means something else. Alternative names could be: Has53Weeks, HasExtraWeek or something along those lines.

Getting Set Up

You database table should look something along the lines of this:

Another table is needed for the Time Intelligence to work it’s magic – This one is for the members of the something along the lines of the Date Tool Dimension by Alberto Ferrari (b|l|t) and Marco Russo (b|l|t) which can be found here. My implementation differs a little bit, here’s how.

I have one dimension in the cube, named Date Tool. This dimension has two attributes with members accordingly. For one part I’d like to control the calculation in terms of overall scope/length of the calculation, i.e. 4 weeks aggregated or is it 8 weeks? This attribute I have named Aggregation Tool. The other element is when I want the calculation to occur, i.e. Today, Yesterday or Last Year. This attribute I have named Comparison Tool.

Members of the Aggregation Tool are: YTD, YTD LY, Last 4 Weeks, Last 8 Weeks, …, Last 52 Weeks.
Members of the Comparison Tool are: Current Period (N), Last Period (N-1), Previous Period (N-2) and some the we actually don’t use.

The fact that the two attributes can be combined behind the scenes in the cube, makes this a very powerful ally.

In the Cube

In the cube we need to address the time intelligence by adding a bit of MDX script. This relies on SCOPE assignments which Chris Webb (b|l|t) has been kind enough to blog about here, present about at SqlBits VIII here, and Pragmatic Works has a video on here.

Now, reminded that we need to address the Week 53 issue and calculate a similar number of weeks to compare with for, in particular, Last Year calculations that stretch across Week 53. Let’s say 2016 Week 1 through 20, what’s the equivalent sale last year? In our case, its 2015 Week 2 through 21.

With a SCOPE statement, it’s possible to bend and twist the calculations as you see fit, and in this case, to right shift the calculation, so to speak. Here is how the calculation should look like.

Note: Id 18 => Year To Date Last Year

SCOPE( [DateTool].[Aggregation Tool].&[18] );
    SCOPE( DESCENDANTS( [Time].[Year Week Date].[All] ) );
        SCOPE([Time].[Year Week Date].[Date]);
            THIS = SUM( YTD( [Time].[Year Week Date].CurrentMember.Lag(364) ), ( [DateTool].[Aggregation Tool].&[1], [DateTool].[Comparison Tool].&[1] ) )                        
                        - IIF( [Time].[Year Week Date].CurrentMember.Parent.Parent.PrevMember.Properties( "Is Leap Year" )
                            , ( [Time].[Year Week Date].CurrentMember.Parent.Parent.PrevMember.FirstChild, [DateTool].[Aggregation Tool].&[1], [DateTool].[Comparison Tool].&[1] )
                            , NULL
                        )
                    ;
        END SCOPE;
        SCOPE([Time].[Year Week Date].[Week]);
            THIS = SUM( YTD( [Time].[Year Week Date].CurrentMember.Lag(52) ), ( [DateTool].[Aggregation Tool].&[1], [DateTool].[Comparison Tool].&[1] ) )
                        + IIF( [Time].[Year Week Date].CurrentMember.Properties("Is Leap Week")
                            , SUM( {[Time].[Year Week Date].CurrentMember.Parent}, ( [DateTool].[Aggregation Tool].&[1], [DateTool].[Comparison Tool].&[10] ) )
                                + ( [Time].[Year Week Date].CurrentMember.Parent.FirstChild, [DateTool].[Aggregation Tool].&[1], [DateTool].[Comparison Tool].&[1] )
                            , NULL                        
                        )
                        - IIF( [Time].[Year Week Date].CurrentMember.Parent.PrevMember.Properties( "Is Leap Year" )
                            , ( [Time].[Year Week Date].CurrentMember.Parent.PrevMember.FirstChild, [DateTool].[Aggregation Tool].&[1], [DateTool].[Comparison Tool].&[1] )
                            , NULL
                        ) 
                                                 
                    ;                
        END SCOPE;       
    END SCOPE;
END SCOPE;

[Date Tool].[Aggregation Tool].&[1] and [Date Tool].[Comparison Tool].&[1] are the default values, Current Week and Current Period respectively.

The above SCOPE statement is invoked every time the [Date Tool].[Aggregation Tool].&[18] ~ [Date Tool].[Aggregation Tool].[Year To Date Last Year] member is present in a an MDX query. So if this is active in any slicer, this piece of code will be run and the aggregation will be calculated accordingly.

Wrap Up

Before entering the domain of Retail, I would have never thought that periods could vary over time. Except maybe for my time in Government Healthcare where a 13th month was introduced, to right all the wrongs of the year. So, in other words, I guess there are many examples out there, where the good old faithful calendar simply does not cut it. In those cases, the SCOPE assignment in SSAS really does some magic for you. But beware, SCOPE assignments done wrong can get you into serious trouble, leading to all kinds of odd- or subtle miss-calculations that you don’t detect right off the back.

A final word on this approach is, that you should test every corner of your cube, before you trust any of your calculations, when dealing with SCOPE assignments. More times than I cared to count I have been fooled by a SCOPE assignment.

 

What to make of it?

Currently I am conducting a review on all existing solutions. These solutions have seen their share of developers over the years. Hence there are several “trademarks” to be found. One such trademark threw me off a bit. It seemed as if the same method had been applied to all queries in the solution. For every expression in the where clause, there would be a similar sub select statement. So if there was a slice by [Product].[Category].&[4], there would be a Sub Select eg. ( SELECT [Product].[Category].&[4] ON COLUMNS FROM… )

I hadn’t seen this construct before, so I felt challenged to find out, if this had some purpose or it was poorly written MDX. I acknowledge, that sometimes development is rushed, and you leave behind unwanted code. But this looked as if it was written to stay. There was almost no complexity to the MDX, other than the odd construct… An example of a statement converted into Adventure Works lingo, would look something like this:

SELECT 
    {[Measures].[Internet Sales Amount]} ON COLUMNS
    , NONEMPTY {[Promotion].[Promotion].[Promotion]} ON ROWS
FROM
    ( SELECT [Scenario].[Scenario].&[1] ON COLUMNS FROM (
        SELECT [Sales Territory].[Sales Territory].[Group].&[Europe] ON COLUMNS FROM (
            SELECT [Product].[Category].&[4] ON COLUMNS FROM
                [Adventure Works] ) ) )
WHERE (
    [Scenario].[Scenario].&[1]
    , [Sales Territory].[Sales Territory].[Group].&[Europe]
    , [Product].[Category].&[4]
)

I looked up Sub Cubes in Analysis Services 2008 Unleashed, which is a ressource I would highly recommend it, if you don’t already know of it. In this book, a sub cube is described as an arbitrary shaped discret and limited sub space of the cube. This illustrated by the following query (Query 1) creating the following sub space (Figure 1 – grey area):

One of the important Things to notice about sub cubes are, that the Default member can change, if the “normal” default member is not a part of the sub space. Then the first member of the sub space will become the default member.

I was reconfirmed in most of my knowledge on sub cube when I dug into the documentation on the subject, and nothing would lead me to believe, that the construct was other than a misunderstanding. So I tweetet a question using hashmark #ssashelp, and Brent Ozar (blog|twitter), Chris Webb (blog|twitter) and Robert L Davis (blog|twitter) replied, after twisting some arms 🙂 This lead to this blog post, describing the issue, in just a little more than 140 characters…So lets see what replies are in store! To my best knowledge, the first query in this blog post, is more confusing than brilliant.