Professional Microsoft® SQL Server® Analysis Services 2008 with MDX

5.4. Sorting Members of a Level

Members of a level are the members of the attribute that defines that level. For example, the members of the level Country in the Geography hierarchy are actually the members of the attribute English Country Region Name. The member name that is shown in the Dimension Designer Browser is the text associated with the Name of the Country. It is not uncommon for dimension tables to have one column for the descriptive name and one column that is the key column of the table. 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 columns that are 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. They do not set the NameColumn property. If the NameColumn property is empty, Analysis Services will return the KeyColumns value for the descriptive names in response to client requests.

Figure 5-34 shows these properties for the attribute English Country Region Name (Country Level in the Geography multilevel hierarchy). The data type of the attribute is also shown in the KeyColumns property. Country is of data type WChar, ...