Sorting Attribute Members Based on a Secondary Attribute

In Lesson 3, you learned about sorting attribute members based on either their name or key value. You also learned about the use of a composite member key to affect attribute members and sort order. For more information, see Modifying the Time Dimension. However, sometimes you may have to order attribute members based on a secondary attribute to achieve the desired sort order, for example if neither the name of the attribute nor the key of the attribute provide the sort order that you want. In order to sort an attribute by a secondary attribute name or key, you must use a secondary attribute that is related to the attribute.

Attribute relationships define the relationships or dependencies between attributes. In a dimension that is based on a single relational table, all attributes are typically related to each other through the key attribute. This is because all the attributes for a dimension provide information about the members linked by the key attribute of the dimension to the facts in the fact table for each related measure group. In a dimension that is based on multiple tables, attributes are typically linked based on the join key between the tables.

However, users may also be interested in additional information about members at a particular level in a hierarchy. Dimension Designer lets you define additional relationships between attributes or change the default relationships to increase performance. The main constraint when you create an attribute relationship is to make sure that the attribute referred to has no more than one value for any member in the attribute to which it is related. When you define a relationship between two attributes, you can define the relationship as rigid or flexible, based on whether the relationships between members will change over time. For example, an employee may move to a different sales region, but a city will not move to a different state. If a relationship is defined as rigid, attribute aggregations are not recalculated every time the dimension is incrementally processed. However, if the relationship between members does change, the dimension must be fully processed. For more information, see Attribute Relationships, Defining and Configuring an Attribute Relationship, Configuring Attribute Relationship Properties, and Specifying Attribute Relationships Between Attributes in a User-Defined Hierarchy.

In the tasks in this topic, you will define a new attribute in the Time dimension based on an existing column in the underlying dimension table. You will use this new attribute to sort calendar month members chronologically instead of alphabetically. You will also define a new attribute in the Customer dimension based on the named calculation that you will use to sort the Commute Distance attribute members. In the tasks in the next topic, you will learn to use attribute relationships to increase query performance.

Defining an Attribute Relationship and Sort Order in the Time Dimension

To define an attribute relationship and sort order in the Time dimension

1. Open Dimension Designer for the Time dimension, and then review the OrderBy property for the Month Name attribute in the Properties window.

   Notice that the Month Name attribute members are ordered by their key values.

2. Switch to the Browser tab, verify that Calendar Time is selected in the Hierarchy list, and then expand the levels in the user-defined hierarchy to review the sort order for the calendar months.

   Notice that the members of the attribute hierarchy are sorted based on the ASCII values of their member keys, which are month and year. In this case, sorting by the attribute name or key does not sort calendar months chronologically. To solve this, you will sort the members of the attribute hierarchy based on a new attribute, the MonthNumberOfYear attribute. You will create this attribute based on a column that conveniently exists in the DimTime dimension table.

3. Switch to the Dimension Structure tab for the Time dimension, right-click MonthNumberOfYear in the Data Source View pane, and then click New Attribute from Column.

4. In the Attributes pane, select Month Number Of Year, and then set the AttributeHierarchyEnabled property to False in the Properties window, set the AttributeHierarchyOptimizedState property to NotOptimized, and set the AttributeHierarchyOrdered property to False.

   These settings will hide the attribute from users and will save processing time, and should be set this way because this new attribute will be used only for ordering the members of another attribute.

1. Expand Date in the Attributes pane.
   Notice that all the attributes in the Time dimension are related directly to the Date attribute, which is the member key that relates the dimension members to the facts in the related measure groups.
2. Expand Month Name.
   Notice that there is no relationship defined between the Month Name attribute and the Month Number Of Year attribute.
3. Drag the Month Number Of Year attribute to the <new attribute relationship> tag under the Month Name attribute.
   While you could have defined a additional relationship, moving the existing relationship will result in more efficient processing performance and reduced redundancy. You have now defined a relationship between the Month Number Of Year attribute and the Month Name attribute. In the Properties window, notice that the default value for the RelationshipType property for the Month Number Of Year attribute relationship is Flexible.
4. In the Properties window, change the value of the RelationshipType property to Rigid.
   The relationships between the members of the Month Name attribute and the Month Number Of Year attribute will not change over time. As a result, Analysis Services will not drop aggregations for this relationship during incremental processing. If a change does occur, a processing error will occur during incremental processing and you will need to perform a full process of the dimension. You are now ready to set the sort order for the members of Month Name.
   The following image shows the RelationshipType property set to Rigid for the Month Number Of Year attribute relationship of the Month Name attribute.
   
5. Select Month Name in the Attributes pane, and then change the value of the OrderBy property in the Properties window to AttributeKey and change the value of the OrderByAttribute property to Month Number Of Year.
   The following image shows these property changes in the Properties window.
   
6. On the Build menu, click Deploy Analysis Services Tutorial.
7. When deployment has successfully completed, switch to the Browser tab for the Time dimension, click Reconnect, and then browse the Calendar Time and Fiscal Time user hierarchies to verify that months now sort in chronological order.
   Notice that the months are now sorted in chronological order, as shown in the following image.
   

Defining Attribute Relationships and Sort Order in the Customer Dimension

To define the attribute relationships and sort order in the Customer dimension

1. Switch to the Dimension Structure tab in Dimension Designer for the Customer dimension.

2. In the Attributes pane, select the Commute Distance attribute, and then review the OrderBy property in the Properties window.

3. Switch to the Browser tab, and then browse the members of the Commute Distance attribute hierarchy.

   Notice that the members of this attribute hierarchy are sorted based on the ASCII values of the member key. In this case, sorting by the attribute name or key does not sort the commute distances from least to most. In this task, you sort the members of the attribute hierarchy based on the CommuteDistanceSort named calculation that ascribes the appropriate sort number to each distinct value in the column. This named calculation has already been added to the Customer table in the Adventure Works DW data source view to save time; you can switch to this data source view to view the SQL script that is used in this named calculation. For more information, see Defining Named Calculations in a Data Source View (Analysis Services).

   The following image shows the members of the Commute Distance attribute hierarchy, sorted by the ASCII values of the member key.

   

4. Switch to the Dimension Structure tab in Dimension Designer for the Customer dimension, right-click CommuteDistanceSort in the Customer table in the Data Source View pane, and then click New Attribute from Column.

5. In the Attributes pane, select Commute Distance Sort, and then set the AttributeHierarchyEnabled property for this attribute to False in the Properties window, set the AttributeHierarchyOptimizedState property to NotOptimized, and set the AttributeHierarchyOrdered property to False.

   These settings will hide the attribute from users and will save processing resources, and should be set this way because the new attribute will be used only for the ordering of members of another attribute.

6. In the Attributes pane, expand Full Name.

   Notice that all the attributes in the Customer dimension that are derived from the Customer table in the Analysis Services Tutorial data source view are related through this attribute.

7. Expand Geography.

   Notice that all the attributes in the Customer dimension that are derived from the Geography table in the Analysis Services Tutorial data source view are related through the Geography attribute.

8. Select Geography, and then set its AttributeHierarchyVisible property to False in the Properties window, set its AttributeHierarchyOptimized property to NotOptimized, and set its AttributeHierarchyOrdered property to False.

   Because this attribute will not be used for browsing, these settings will hide the attribute from users and will save processing time. An attribute hierarchy must be enabled if it has member properties.

9. In the Attributes pane, expand Commute Distance.

   Notice that there is no relationship defined between this attribute and the Commute Distance Sort attribute.

10. Drag the Commute Distance Sort attribute to the <new attribute relationship> tag under the Commute Distance attribute.

    You have now defined a relationship between the Commute Distance attribute and the Commute Distance Sort attribute. In the Properties window, notice that the default value for the RelationshipType property of the Commute Distance Sort member of the Commute Distance attribute is Flexible.

11. In the Properties window, change the value of the RelationshipType property to Rigid.

    The relationship between the members of the Commute Distance attribute and the Commute Distance Sort attribute will not change over time. You are now ready to set the sort order for the Commute Distance attribute.

12. In the Attributes pane, select Commute Distance, and then change the value of the OrderBy property in the Properties window to AttributeKey, and change the value of the OrderByAttribute property to Commute Distance Sort.

13. On the Build menu, click Deploy Analysis Services Tutorial.

14. When deployment has successfully completed, switch to the Browser tab of Dimension Designer for the Customer dimension, click Reconnect, and then browse the Commute Distance attribute hierarchy.

    Notice that the attribute hierarchy members are now sorted in a logical order based on increasing distance, as shown in the following image.

    

Next Task in Lesson

Specifying Attribute Relationships Between Attributes in a User-Defined Hierarchy