Professional SQL Server™ Analysis Services 2005 with MDX

5.4. Sorting Members of a Level

Members of a level are members of the attribute that are defined for that level. For example, members of the level Country in the Geography hierarchy are actually members of the attribute English Country Region Name. The member name that is shown in the dimension browser is the text associated with the Name of the Country. It is not uncommon for the dimension tables to have one column for the descriptive name and one column for the key of that column. You can use the descriptive name column to display the name of the attribute and the key column to sort the members in that attribute. The attributes' properties help you sort members of a level.

Each attribute in a dimension has two properties, KeyColumns and NameColumn. The KeyColumns property is used to specify the column(s) that is used for sorting the members and the NameColumn is used for the descriptive name of the member. By default the Dimension Wizard and the Dimension Designer set the KeyColumns attribute when an attribute is added to the dimension. The NameColumn attribute is empty. If the NameColumn is empty, Analysis Services uses the KeyColumns by default for the descriptive names for client requests.

Figure 5-21 shows these properties for the attribute Country. The data type of the attribute is also shown in the KeyColumns property. Country is of data type Wchar, which means all the members are strings. Therefore, when you view the members in the dimension browser the members are sorted ...