SSAS TUTORIAL: Sorting Attribute Members Based on a Secondary Attribute

In this task, you will define a new attribute in the Date 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 Date Dimension

1.       Open Dimension Designer for the Date dimension, and then review the OrderByproperty 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 Date 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, theMonthNumberOfYear attribute. You will create this attribute based on a column that conveniently exists in the Date dimension table.

3.       Switch to the Dimension Structure tab for the Date dimension, right-clickMonthNumberOfYear 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 theAttributeHierarchyEnabled property to False in the Properties window, set theAttributeHierarchyOptimizedState property to NotOptimized, and set theAttributeHierarchyOrdered property to False.

These settings will hide the attribute from users and will improve processing time. This attribute will not be used for browsing. It will only be used for ordering the members of another attribute.

5.       Click the Attribute Relationships tab.

Notice that all the attributes in the Date dimension are related directly to the Dateattribute, which is the member key that relates the dimension members to the facts in the related measure groups. There is no relationship defined between the Month Name attribute and the Month Number Of Year attribute.

6.       In the diagram, right-click the Month Name attribute and then select New Attribute Relationship.

7.       In the Create Attribute Relationship dialog box, the Source Attribute is Month Name. Set the Related Attribute to Month Number Of Year.

8.       In the Relationship type list, set the relationship type to Rigid.

The relationships between the members of the Month Name attribute and theMonth 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.

9.       Click OK.

10.    Click the Dimension Structure tab.

11.    Select Month Name in the Attributes pane, and then change the value of theOrderBy property in the Properties window to AttributeKey and change the value of the OrderByAttribute property to Month Number Of Year.

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

13.    When deployment has successfully completed, switch to the Browser tab for the Date dimension, click Reconnect, and then browse the Calendar Date and Fiscal Date 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

1.       Switch to the Browser tab in Dimension Designer for the Customer dimension, 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. To save time, this named calculation has already been added to the Customer table in the Adventure Works DW data source view. You can switch to this data source view to view the SQL script that is used in this named calculation.

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

2.       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.

3.       In the Attributes pane, select Commute Distance Sort, and then set theAttributeHierarchyEnabled 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 improve processing time. This attribute will not be used for browsing. It will only be used for ordering the members of another attribute.

4.       Select Geography, and then set its AttributeHierarchyVisible property to False in the Properties window, set its AttributeHierarchyOptimizedState property toNotOptimized, and set its AttributeHierarchyOrdered property to False.

These settings will hide the attribute from users and will improve processing time. This attribute will not be used for browsing. It will be only be used for ordering the members of another attribute. Because Geography has member properties, itsAttributeHierarchyEnabled property must be set to True. Therefore, to hide the attribute, you set the AttributeHierarchyVisible property to False.

5.       Click the Attribute Relationships tab.

6.       In the attributes list, right-click the Commute Distance attribute and then select New Attribute Relationship.

7.       In the Create Attribute Relationship dialog box, the Source Attribute is Commute Distance. Set the Related Attribute to Commute Distance Sort.

8.       In the Relationship type list, set the relationship type to Rigid.

The relationship between the members of the Commute Distance attribute and theCommute Distance Sort attribute will not change over time.

9.       Click OK.

You are now ready to set the sort order for the Commute Distance attribute.

10.    Click the Dimension Structure tab.

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

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

13.    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.