SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SSAS – Using Dimension OrderBy AttributeKey

Often when building dimensions there is a need to order you dimension attributes by something other than the NameColumn or KeyColumn properties.  An example of this may be an accounting structure that is preferred to be viewed in a certain order.  The data doesn’t represent that order well so you create a sort column that does it for you.  The sort column can be in a table or just in a SQL Server view if you prefer. 

Once you’ve created this field in the table or view you can utilize it in you dimension design by bringing it in as an attribute.  Because this field is used for nothing more than sorting you can set the properties of the new attribute to AttributeHierarchyVisible to False and AttributeHierarchyEnabled to False.  Next go to the Attribute Relationships tab in the dimension designer and right-click on the attribute you wish to sort.  Select New Attribute Relationship which will bring open the screen showed below.  Set the Related Attribute to the column you want to sort by then click OK.

Create Attribute Relationship

The Attribute Relationships should now look something like this:


The last step is to return to the Dimension Structure tab and select the field you want to sort.  In my case I selected English Product Name and then opened the properties menu by hitting F4.  The two properties you need to change are the OrderBy property, which you will change to AttributeKey, and the OrderByAttribute property, which you will set to the Sort field you created earlier. 


Now after processing when you browse this attribute if will be sorted by the sorting attribute you created.


Devin Knight

Devin is a BI consultant at Pragmatic Works Consulting. Previously, he has tech edited the book Professional Microsoft SQL Server 2008 Integration Services and was an author in the book Knight's 24-Hour Trainer: Microsoft SQL Server 2008 Integration Services. Devin has spoken at past conferences like PASS and at several SQL Saturday events. He is a contributing member to the Business Intelligence Special Interest Group (SIG) for PASS as a leader in the SSIS Focus Group. Making his home in Jacksonville, FL, Devin is a participating member of the local users’ group (JSSUG).


No comments.

Leave a Comment

Please register or log in to leave a comment.