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

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.

No comments:
Post a Comment