27.
In the Attributes
pane, click-select the newly appearing Commute Distance Sort.
28.
Within the Properties
window for the new Commute Distance Sort attribute, set the AttributeHierarchyEnabled
property (in the Advanced section of the Properties window) to False.
29.
Set the AttributeHierarchyOptimizedState
property (also in the Advanced section of the Properties window) to
NotOptimized.
30.
Set the AttributeHierarchyOrdered
property (in the Misc section of the Properties window) to False.
The
settings we have made will hide the attribute from information consumers, and
will conserve processing resources. These settings are appropriate because the
new attribute will be used only for the ordering of the members of our primary
attribute, Commute Distance. Our modifications appear, within the Properties
window for the Commute Distance Sort attribute, as depicted in Illustration
13.
Illustration
13: Our Property Modifications for the Commute Distance Sort Attribute
31.
Returning
again to the Attributes pane, expand Full Name.
We see immediately that all the attributes in the Customer
dimension that are based upon the Customer table in the ANSYS062_Basic
AS DB data source view (including the new Commute Distance Sort attribute)
are related through this attribute.
32.
Expand the Geography
attribute by clicking the “+” sign to its immediate left.
We see here that all the attributes in the Customer
dimension that are based upon the Geography table in the ANSYS062_Basic
AS DB data source view are related through the Geography
attribute.
The relationships
we have examined within the Full Name and Geography attributes
appear as shown in Illustration 14.
Illustration
14: Attribute Relationships We Have Examined ...
33.
Select the Geography
attribute by clicking upon it.
34.
Within the Properties
window for the Geography attribute, set the AttributeHierarchyVisible
property (in the Advanced section of the Properties window) to False.
35.
Set the AttributeHierarchyOptimizedState
property (also in the Advanced section of the Properties window)
to NotOptimized.
36.
Set the AttributeHierarchyOrdered
property (in the Misc section of the Properties window) to False.
As was
the case earlier for the Commute Distance Sort attribute, this attribute
will not be used for browsing; the settings we have made will hide the
attribute from users and will save processing time. (We must keep in mind,
however, that an attribute hierarchy must be enabled if it has member
properties.) Our modifications appear, within the Properties window
for the Geography attribute, as depicted in Illustration 15.
Illustration
15: Our Property Modifications for the Geography Attribute
37.
In the Attributes
pane, once again, expand the Commute Distance attribute by clicking the
“+” sign to its immediate left.
We note here that no relationship is currently defined
between the Commute Distance attribute and the Commute Distance Sort
attribute. We will now define such a relationship.
38.
Drag the Commute
Distance Sort attribute to the <new attribute relationship>
placeholder that appears underneath the Commute Distance attribute.
We have defined
a relationship between the Commute Distance attribute and the Commute
Distance Sort attribute, as evidenced by the appearance of the latter
within the attribute relationships of the primary attribute, as shown in Illustration
16.
Illustration
16: The New Attribute Relationship Appears ...
39.
Click the new Commute
Distance Sort attribute relationship underneath the Commute
Distance attribute, to select it, as necessary.
The Properties
window for the new attribute relationship becomes visible. Here we can
see that the default value for the RelationshipType property of the Commute
Distance Sort member of the Commute Distance attribute is Flexible.
Because the relationship between the members of the Commute Distance
Sort attribute is not expected to change over time, we will reset the RelationshipType
property to Rigid.
40.
In the
Properties window, change the value of the RelationshipType property
to Rigid, depicted in Illustration 17.
Illustration 17: The
Modified RelationshipType Property ...
We are
now in a position to modify the sort order of the Commute Distance
attribute to meet the stated client business requirement.
40.
In the Attributes
pane, once again, click the Commute Distance attribute to select it.
41.
Within the Properties
window, modify the OrderBy property (within the Advanced
properties group) from its current setting of Key to AttributeKey,
using the dropdown selector that becomes enabled when we click the property.
We emphasize to our client colleagues that we could easily
sort by the name of the secondary attribute at this point; we would
simply choose AttributeName via the dropdown selector, instead of AttributeKey.
42.
Change the
value of the OrderByAttribute property, just underneath the OrderBy
property, to Commute Distance Sort.
The affected settings within the Properties window
for the Commute Distance attribute appear as shown in Illustration 18.
Illustration 18: The
Modified Attribute Properties
We are now ready to process Analysis Services
database ANSYS062_Basic AS DB to enact our structural changes.
43.
Right-click ANSYS062_Basic
AS DB atop the tree within the Solution Explorer.
44.
Select Process
from the context menu that appears, as depicted in Illustration 19.
Illustration
19: Process the Practice Database
45.
Click Yes
on the dialog that appears asking if we would like to save all changes first.
46.
Click Run on
the Process Database - ANSYS062_Basic AS DB dialog that appears
next, as shown in Illustration 20.
Illustration
20: Click Run... to Process the Database
47.
Once the Process
Progress viewer indicates successful completion, click Close to
dismiss the viewer.
48.
Click Close
on the Process Database - ANSYS062_Basic AS DB dialog, as well.
We are now ready to verify the effectiveness of our new sort for the
primary attribute members.
Verification: Browse the
Cube to Ascertain the Effectiveness of the New Attribute Member Sort
Let's take a look at the Commute Distance attribute
hierarchy - specifically at the effects of the new sort we have installed -
from the Cube Browser.
49.
Click the Browser
tab to switch to the Cube Browser within the Dimension Designer
for the Customer dimension.
50.
Click the Reconnect
button within the Browser toolbar, as depicted in Illustration 21.
Illustration
21: Click Reconnect
51.
Select the Commute
Distance attribute hierarchy within the Hierarchy selector atop the Browser,
as necessary.
52.
Expand the All
Customers level of the dimension, as required, to expose the attribute
members.
We note that the Commute Distance attribute
hierarchy members are now sorted based upon increasing commute distance, as
shown in Illustration 22.
Illustration
22: The Newly Sorted Attribute Hierarchy Members
Our client colleagues confirm that the business
requirements have been met through the solution we have demonstrated. We have
established the capability to sort attribute members by a secondary
attribute key to obtain a presentation effect that was not readily supported
by the name or key of the primary attribute.
Conclusion
In this
article we gained hands-on exposure to sorting attribute members by a secondary
attribute key in Analysis Services 2005. We began our examination of
the process involved with a discussion surrounding the general concepts
underpinning attribute relationships, including what they define and
support, as well as the mechanics that determine these relationships.
We next discussed the way in which the Dimension Designer supports our
definition of additional relationships between attributes.
After undertaking preparation steps, including the cloning
and deployment of a basic sample cube for use in our subject matter
practice, we began our hands-on practice session, where we set out to meet a
hypothetical client business requirement; using our sample cube, we leveraged
an attribute relationship to demonstrate support of the sorting of an
existing attribute by a secondary attribute key. Throughout our
session, we discussed other considerations that surround the addition and use
of attribute relationships to support the use of the name or key
of a secondary attribute as the basis for ordering the members of a primary
attribute within our cube.
»
See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.
» 
Comment and Contribute
