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:
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 !
If we view the same data in a time-series chart such as Figure 3, we might think that sales are in trouble.
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.
Whereas if the dates were sorted ascending we would get the correct result as seen below in Figure 5: