Problem:
How to sort the dimension attribute by something other than the key and name column? How do you set the “OrderBy” property?
Example: You have created an Inventory age buckets 1-50,51-100,101-150 and so if a business user uses this dimension attribute then the sorting won’t be logical. It would be 1-50, 101-150,51-100 – so how to show the buckets in the logical order?
Solution:
1. make sure that the table/view that you are bringing in has the sort key.
Example:
2. Now, switch to SSAS and open your dimension. I am assuming that you’ve already configured your data source views and you are already bringing in these columns in the dimension:
3. Let’s start with hiding Aging Bucket Sort key so that it’s not visible to user. Change the AttributeHierarchyVisible to False
4. Now, switch to Attribute Relationships – Right Click on Aging Bucket and click on New Attribute Relationship. And set the attribute relanship between Aging bucket and Aging Bucket Sort Key
And you should see something like this in your attribute relationship section:
5. Now, one more thing to configure. Go back to dimension structure section. Open the properties section for the Aging Bucket Attribute and change the OrderBy property to AttributeKey. Also, change the orderByAttribute property to Aging Bucket Sort Key (in your case, choose the sort key that you have)
That’s it, after you process the model then you should see the attribute being sorted based on the sort key that you had.
Conclusion:
In this post, you saw how to configure sort/order property of a dimension attribute.