SSAS(MD) Time Intelligence w/ a Twist

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.

 

Loading

T-SQL Tuesday #86 : SQL Server Bugs & Enhancement Requests : [SSMS] Intellisense for MDX

This months T-SQL Tuesday is hosted by Brent Ozar (b|l|t) and the invitation is found following this link.

T-SQL Tuesday was started by Adam Machanic (b|l|t) and this is the SQL Server community’s monthly blog party where everyone is invited to write about a common topic. Actually Adam just published a complete version of the rules of engagement here.
This time the topic is SQL Server Bugs & Enhancement Requests.

Introduction

I think this topic is a really good selection. I have previously worked on an idea to make connect items visible at the SqlSaturdays I helped organize here in Denmark. I haven’t come up with a really good solution yet, but I hope that we maybe next time will have a way to make the attendees aware of how they can impact the evolution of SQL Server.

[SSMS] Intellisense for MDX

The Connect Item I have chosen to write about is an old one and is about getting Intellisense for MDX in SQL Server Management Studio [SSMS]. Despite the fact that it was created back in 2009 by Jamie Thomson (b|l|t), it is still active and there has been a public acknowledgement back then, by the Analysis Service Team, that they will consider this request for an upcoming release. 2009, still active… True story.

Are You Done Considering Yet?

Meat n’ Potatoes

This connect item is about a functionality many of you SQL Server Database Developers and Administrators take for granted, namely code completion. Either you have had it through a third-party product (here, here or here) for years or have been blessed by the SQL Server Team with the built-in functionality in SSMS, supported since, drum roll please, 2008.

I can’t stress enough how much productivity would go up by adding such a feature to Management Studio. If you don’t believe me, try turning off your T-SQL code completion for just one (1) hour – I bet you’ll be screaming by then. And with all I hear about MDX being hard to master, it really doesn’t invite new people in to join the party, not having code completion. So please vote here.

Yes, you may argue that MDX is slowly being replaced by DAX. But I predict (seems oh so popular these days) that SSASMD cubes and MDX will stick around for quite some time still. And you may have a point in that this might not even be up to the Analysis Services Team anymore – since the break up of SSMS and SQL Server releases, the responsibility for this feature request was probably dropped between two chairs – that’s why one could hope that the MSSQL Tiger Team was made aware of this request, because it really seems to have been forgotten. If not for this reply I got on Twitter:

Help make them become aware by voting here or shouting it out on Twitter or by any other means you have. I urge you to vote, as a fellow compassionate database developer, as a struggling BI Developer, as a project manager wanting to see his developers pick up pace. Even if you don’t care, please vote.

Vote

Please, if I could just get you to vote. Thank you!

Vote

Is it getting pathetic? 😉

Jamie Thomson who initially created the request doesn’t seem to think this stands a chance. I hope we can prove him wrong – Even if he seems to have moved onto other technologies and wouldn’t really benefit from this massive upgrade of the developing experience with SSAS and MDX.

 

 

Power BI – Ideas

On a side note, there is a similar forum for feature requests for Power BI, which I would like to advertise for that – in case you didn’t know.

Please see this link.

Loading

Same members declared in both Sub Select and Where clause of same MDX

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):

Sub Cube Figure 3
Query 1
Sub Cube Figure 2
Figure 1

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.

Loading

SSMS Quick Tip

If you ever need to test Analysis Services roles, you can easily do so either by browsing the cube or by means of a query.

When browsing a cube through SQL Server Management Studio (SSMS) or Bisuness Intelligence Developer Studio (BIDS), you can select one or more roles to be enforced on data, by clicking the little “user” icon in the top left corner of the cube browser:

You will then be able to select on or more of the roles available on the database via a dialog:

This way of browsing roles has the following pros and cons:
Pros: Visual confirmation of PC Hierarchies
Cons: A lot of clicking top verify permission integrity

The other way of testing roles is through SSMS is by creating an MDX query. The trick is to click the options button when the connection dialog is active. The last tab allows you to specify additional connection parameters. The Roles property is described in detail at technet. Basically it’s a comma seperated list of roles (case sensitive). The trick is showed in the screendump below:

Pros: Custom queries
Cons: No visual confirmation of integrity, can be hard to visually confirm permissions on a PC hierarchy as an example

Loading

How to get SSAS to return the correct currency format.

Although setting the language and collation correctly, it’s not easy to see why a specific MDX would not return the right formatting, in regards to monetary, thousands seperator etc.
There is however a way of forcing the language, through a property available on the calculated measure. In the following example, I’ll show that even though the server settings are da-DK (Danish), the results are displayed as en-US, unless we provide a property value.

First, a screenshot of the server settings:

Then the query, that returns en-US formatting:

For this to work, we need to add: Language=1030 as property to the calculated member, then we get:

See more on FORMAT_STRING and LANGUAGE @ http://msdn.microsoft.com/en-us/library/cc879322.aspx

Loading