http://www.sqlservercentral.com/blogs/dknight/2011/04/19/ssas-_1320_-using-dimension-orderby-attributekey/

Printed 2014/08/22 10:12PM

SSAS – Using Dimension OrderBy AttributeKey

2011/04/19

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:

OrderbyAttributeKey2

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. 

OrderbyAttributeKey3

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

 


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.