Professional SQL Server™ Analysis Services 2005 with MDX

8.3. Using Properties to Customize Dimensions

Analysis Services provides several properties for use with hierarchies and dimensions. If every property were explained with illustrations and examples, this chapter would be quite large, so in this section you learn about the properties that are likely to be used in fulfillment of most common business requirements.

8.3.1. Ordering Dimension Members

In Chapter 5 you learned to order dimension members of a hierarchy based on key or named columns specified for the attribute. Based on that, Analysis Services sorted the members in the hierarchy and you were able to see the order while browsing the dimension. In certain business scenarios, you might have a need to sort the members based on a specific value or based on some other condition. Analysis Services 2005 provides properties to sort members of a hierarchy in a variety of ways other than the key or named columns.

Analysis Services 2005 provides you an option to sort members of a hierarchy based upon another attribute that is related or included as member property for the primary attribute you want to sort. For example, if you have an Employees hierarchy and if you have the age of the employees defined as a related attribute, you can set the appropriate properties to achieve a sorting of employees based on age. You need to set the property OrderByAttribute to age and then set the property OrderBy to either AttributeKey or AttributeName depending on your requirements, and then deploy ...