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 by the names. The Dim Geography dimension table has the column Country Region Code. You can define the sort order of the countries based on the Country Region Code instead of their names by changing the KeyColumns and NameColumn properties appropriately. The following exercise demonstrates exactly how you can change the order of the countries based on the order of Country Region Code (AU, CA, DE, FR, GB, and US) instead of the country names:

image from book
Figure 5-21

  1. Click on English Country Region Name in the Attributes pane, then in the properties pane, click the NameColumn property value drop-down and select New. This opens an Object Binding dialog showing all the columns in the Dim Geography table. Select the column English Country Region Name and click OK.

  2. Click the KeyColumns property value (the three dots button). This action launches the DataItem Collector dialog. Delete the column EnglishCountryRegionName from the collection and then click the Add button. A new item New Binding (WChar) appears in the list box Members. Under the Misc section in the dialog select the Source property and click on the button. The Object Binding dialog is now launched. Change the binding type from Generate Column to Column Binding and select CountryRegionCode from the Source column list. The DataItem Collector dialog should look like Figure 5-22. Click the OK button.

    image from book
    Figure 5-22

  3. Click the Advanced Properties for the attribute EnglishCountryRegionName. Change the value of property OrderBy from Name to Key. This instructs the server to order this attribute using the Key attribute (CountryRegionCode), which you specified in step 2.

  4. Deploy the project to the Analysis Services instance. Deploying the project to Analysis Services instance results in sending the new changes defined in steps 1 through 3 followed by processing the dimension. Once you are in the Browser tab click on the Reconnect option to retrieve the latest dimension data.

In the dimension browser select the Geography hierarchy. The order of the countries has now changed based on the order of Country Region Code (AU, CA, DE, FR, GB, and US) instead of the country names you viewed in Figure 5-19. The new order of countries is shown in Figure 5-23.

image from book
Figure 5-23

Professional SQL Server Analysis Services 2005 with MDX
Professional SQL Server Analysis Services 2005 with MDX (Programmer to Programmer)
ISBN: 0764579185
EAN: 2147483647
Year: 2004
Pages: 176 © 2008-2017.
If you may any questions please contact us: