Date Order

I had a request recently to change the order of the date dimension in my SSAS cubes to descending, so that users could easily find the most recent dates. Users are using COGNOS Analysis Studio to slice and dice the data. The users of course could have simply dragged the date hierarchy onto the “canvas”, right-clicked and selected “Sort Descending” but this was considered too much of an inconvenience.

My friend and colleague Boyan Penev has a post on how to do ordering of attributes in SSAS here: : http://www.bp-msbi.com/2011/11/alternate-ordering-of-attributes-in-ssas/

The people requesting this change were eventually convinced that the change was not wise, and I’d like to present those reasons in this post.

Firstly, all of the MDX time intelligence calculations need to be changed. Not a big deal, but we are introducing SSAS to developers who have never used the tool before. What was worse was that the people requesting the change were even considering having the years in descending order, and the months in ascending order. I’m not sure quite how this would have worked, and couldn’t be bothered finding out because the idea was absurd.

The main reason I refused and argued against this change boils down to useability.

When viewing a date dimension for date selection, it is understandable that users would want to see the dates in a descending order. However this is a job for the client tool, and is only relevant when selecting a date. It is nonsensical to view dates in a descending order when joined to data.

Take the following table as an example:

Figure 1

The date dimension is sorted descending. So while the user can quickly see the latest quarter’s results easily enough, understanding the trend is more difficult since English-speaking eyes will naturally move left to right when reading.

Next, Visual Totals are always displayed to the right of the data set, because we read from left to right.

Add a Yearly Total as seen below in Figure 2 and it is now impossible for a user to understand the data with one scan of their eyes – they user needs to find Q1,scan left to right for each quarter, then scan from right to left to get to the next quarter,  then eventually scan back to the right to get the Grand Total !

Figure 2

If we view the same data in a time-series chart such as Figure 3, we might think that sales are in trouble.

Figure 3

At first glance, a user would most likely assume that sales for the year have dipped, when in fact they have increased. Ordering the dates in descending order has made the graph at risk of being misinterpreted.

In addition, date calculations on many tools such as SQL Server Analysis Services, or Tableau Software depend on the date order i.e. these tools need to know that for Year on Year Growth, 2010 comes before 2011. Otherwise we can end up with situations like the following for YTD Sales as shown in Figure 4 below, where the YTD value is decreasing over time.

Figure 4

 

Whereas if the dates were sorted ascending we would get the correct result as seen below in Figure 5:

Figure 5

 

What we did to help the users was create sets of dates, e.g. a rolling 8 weeks, rolling 12 weeks etc. This allowed the users to easily drag the set onto the canvas and find the data they were seeking, since most users are generally only interested what has happened in the most recent two or three months.

 

The people requesting the change did so because they thought it would make it easier for the users. However, the next day I presented my solution to the users in UAT, explained why it did not make sense to change the sort order and the users were fine with that.

 

So in conclusion: leave the sorting to the client tool, and don’t treat users like idiots.

Posted in: Data Modeling, SSAS, SSRS