Professional Microsoft® SQL Server® Analysis Services 2008 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 most important with respect to optimal design or more likely to be used in fulfillment of 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 provides properties to sort members of a hierarchy in a variety of ways other than by the attribute's key or name columns.

Specifically, you have the option to sort members of a given attribute based upon members of another, related attribute. The related bit is important here. In order for this technique to be used, you must define the ordering attribute as a related attribute (also known as member property) of the attribute to be ordered. For example, if you have an Employees parent-child hierarchy and if you have the age of the employees defined as an attribute, you can set the appropriate properties to the key attribute of the dimension ...