Sorting Descending in Analysis Services for a Date Dimension: Don't Do It!

Why To Not Sort Date Dimension Descending

Now that I am wiser, my recommendation is to leave your date dimension in ascending order.  Within a day of implementing this technique to sort descending, we found a couple of time-oriented issues within the development environment this had been deployed to.  Here are some reasons not to sort your date dimension descending:

• Unintended consequences on time calculations. 
• Effect on named sets (although the order could certainly be modified).
• Appearance on charts & reports.  Although users would like to see drop-downs list in descending order, they probably don't always want to see their data displayed that way. 

Live and learn.  Thanks for reading.

- Melissa  10/3/2011

-----------------------------------

The Original Blog Entry:

Overview:  This entry describes how I fulfilled a customer requirement to present the Date attributes & hierarchies in descending order, rather than ascending.  This technique utilizes individual sort fields for each attribute.  Each sort field contains the same content as the key, but is multiplied by * -1 to reverse the order.

Level:  201 (this assumes some SSAS knowledge, as all SSAS steps are not discussed in detail).

Analysis Services Order By Options

Following is a screen shot of how my customer wanted to see their Calendar Date Hierarchy displayed:

     

The good news?  Analysis Services attributes have an OrderBy property, which can sort by the Key or Name field of the attribute, or, alternatively, the Key or Name field of a related attribute.

The bad news?  This OrderBy property works in ascending order only.  There is not a descending option, so we need to provide an explicit field to SSAS.

Steps to Deliver Descending Sort Functionality

The steps I used include the following:

1. Relational DB:  Alter the Date dimension in the data warehouse structure to add the integer sort fields.
2. Relational DB:  Alter the stored procedure which builds the Date dimension so that it populates the new sort fields.
3. SSAS:  Refresh the DSV to detect the new relational fields.
4. SSAS:  Add the new sort fields to the Date dimension (hidden to end users).
5. SSAS:  Update the attribute relationships in the Date dimension.
6. SSAS:  Update the OrderBy property for each attribute.
7. SSAS:  Deploy and test.

The rest of this entry elaborates on some of these steps.

Sort Fields in the Data Warehouse Date Dimension

In order to be able to sort every attribute (and every hierarchy) descending, we need a sort field that goes with every attribute.

Using the screen shot below, let’s focus on the “Quarter of Year” attribute.  It contains:

• Calendar_quarter_year_nr:  Key property in SSAS
• Calendar_quarter_year_cd:  Name property in SSAS
• Calendar_quarter_year_sort:  OrderBy property in SSAS

Note the sort field is nothing more than a reversal of the number (_nr) field.   Sample from Dim_Date:

    

Stored Procedure which Builds the Date Dimension

In order to populate Dim_Date, we use a stored procedure that uses a variety of date functions & variables.  The stored proc is is where *-1 logic is used to populate each sort field.  We end up with the same contents as each Number (_nr) field, but the sort field is negative (reversed). 

Sample from the Build_Dim_Date stored proc:

     

Attribute Relationships in SSAS

After you have refreshed the SSAS DSV for the new Dim_Date sort fields, and after you have added each sort field (hidden) to the SSAS Date dimension, you need to update the attribute relationships.  The attribute relationships must in place before the OrderBy property can be updated.

Sample attribute relationships:

    

As you can see in the above screen shot, having a sort field for each individual attribute expands the attribute relationships pane considerably.  However, the relationships are necessary to associate each individual sort field to the field that’s being displayed.  Only related fields (or the field itself) can be used in the OrderBy property.

OrderBy Property in SSAS

The last thing to do is update the OrderBy property for each attribute.  The following screen shot shows our “Quarter of Year” attribute.

    

Note that the OrderBy is done on the AttributeKey – this means “don’t use the key of my Calendar_quarter_year_nr field; instead, use the key of this related field.”  In my case, the way the stored proc is written, the integer number fields are all set up to sort nicely so we we can use Key – that’s not always true.  In many other dimensions, sorting by the key won’t work at all & you need to use Name instead.

A Few Final Words

I typically try to implement as many fields in the underlying data warehouse structure as possible; however, if you only have one or two fields to be sorted descending, another viable alternative is to create a named calculation in the SSAS DSV.

The beauty of this technique is that it’s just so darn simple and it’s quick to implement.  The * -1 technique worked for me with the Date dimension because I had integer fields for each of my Number (_nr) fields.