Saturday, October 1, 2011

Sort Dimension Members in DESCending order

Sometimes we need to display Date dimension members in descending order. For example, Client ask to show Calendar Year in DESC order.
But Analysis Services doesn't provide a way to sort dimension members in Descending order. SSAS provides only Ascending order.
However, we can achieve this sort functionality by using a simple trick. I will take an example to display Calendar Year member in DESC order. Below are the steps to achieve our goal:

Step1: Go to Data Source View (DSV) and right click on Date dimension in DSV diagram. Click on New Named Calculation... to create a new member.

Step2: Now enter CalendarYearSort in Column name and 0-CalendarYear in Expression textbox. This additional column will be used to sort calendar year in descending order. Click OK to proceed. Here I considered that CalendarYear is existing column in the dimension.



Step3: Go to Dimension Structure of Date dimension and drag and drop new member "CalendarYearSort" in attributes list.

Step4: Click on Attributes Relationship tab. Right click on Calendar Year member and select New Attribute Relationship... It will open Create Attribute Relationship wizard.

 

Step5: In Create Attribute Relationship, select Related Attribute as Calendar Year Sort and Relationship type as Rigid. Click OK to proceed.

Step6: Again go back to Dimension Structure tab and click on Calendar Year attribute. Go to properties and set following things as shown below:
OrderBy: AttributeKey and OrderByAttribute: CalendarYearSort

Step7: Now Calendar Year is set in decending order. Just save and process the cube.

Note: Before processing the cube, you can set AttributeHierarchyVisible = FALSE because we should not show this attribute in cube and AttributeHierarchyEnabled = False to reduce cube space and increase the performance.

3 comments:

  1. Shouldn't we be setting the AttributeHierarchyEnabled also to False, as we are not going to use this attribute for any of the other calculations? Doing so will help reducing the cube space and hence the performance would be better. Also the processing time would be lesser as you dont need to build the data structures for that attribute hierarchy.

    ReplyDelete
  2. @road-blogs: That make sense... I appreciate this.

    ReplyDelete
  3. I am encountering sorting issue with Measure..
    Any solution for this problem?

    thanks

    ReplyDelete

Here are few FREE resources you may find helpful.