Procedure: Establish the
Capability to Sort Attribute Members by a Secondary Attribute Key
Specific business requirements sometimes
dictate the need, as we have noted, to sort attribute members of our Analysis
Services 2005 cubes based upon something besides their name or key
values. We will get some hands-on practice ordering an attribute on
the basis of the key of a related secondary attribute within the
steps that follow. We will perform our practice session within the SQL
Server Business Intelligence Development Studio, from which we will perform
the steps of adding the secondary-attribute-based sort in our new Analysis
Services database, ANSYS062_Basic AS DB.
and click, the SQL
Server Business Intelligence Development Studio, as appropriate.
briefly see a splash page that lists the components installed on the PC, and
then Visual Studio .NET 2005 opens at the Start page.
Close the Start
page, if desired.
-> Open from the main menu.
Services Database ... from the cascading menu, as depicted in Illustration
3: Opening the Analysis Services Database ...
to Database dialog appears.
the Connect to existing database radio button is selected, type the Analysis
Server name into the Server input box atop the dialog.
selector just beneath, labeled Database, select ANSYS062_Basic AS DB,
as shown in Illustration
4: Selecting the New Basic Analysis Services Database ...
settings on the dialog at default, click OK.
Server Business Intelligence Development Studio briefly reads the database from
the Analysis Server, and then we see the Solution Explorer populated
with the database objects. We will be working largely within the Dimension
Designer, but will also need to access elements within the cube structure,
as well, so we will open both Cube and Dimension Designers at
Within the Solution
Explorer, right-click the Basic cube (expand the Cubes folder
on the context menu that appears, as depicted in Illustration 5.
5: Opening the Cube Designer ...
tabs of the Cube Designer open. Next, well open the Dimension
Designer for the dimension within which we will be working , where we will
proceed with the steps involved in bringing about the sort that our client
colleagues have requested.
Within the Solution
Explorer, right-click the Customer dimension (expand the Dimensions
folder as necessary).
on the context menu that appears, as shown in Illustration 6.
6: Opening the Dimension Designer ...
tabs of the Dimension Designer open.
Click the Dimension
Structure tab, if it has not already appeared by default.
Within the Attributes
pane, click the Commute Distance attribute to select it, and to cause
the Properties window for the attribute to appear.
pane, with our selection of the Commute Distance attribute, together
with the associated Properties window (partial view), appears as
depicted in Illustration 7.
7: Commute Distance Attribute and Properties Window (Composite View)
particularly the setting of the OrderBy property within the Properties
window: The members of the Commute Distance attribute hierarchy are
being sorted based upon the member key, an ASCII value. This, we tell
the client representatives, is why the sorts that we are currently obtaining
are not simply ascending values, as we shall next observe by examining the current
sort in the Browser.
Click the Browser
Using the Hierarchy
selector atop the Browser tab, select the Commute Distance
attribute hierarchy, as shown
in Illustration 8.
8: Select the Commute Distance Attribute Hierarchy in the Browser ...
level of the Commute Distance attribute hierarchy appears in the Browser.
Expand the All
Customers level in the Browser by clicking the + sign to
its immediate left.
members of the Commute Distance attribute hierarchy appear next, as
depicted in Illustration 9.
9: Commute Distance Attribute Hierarchy Members Appear, Sorted by Key
We note that
the Commute Distance members within the attribute hierarchy are not
sorted from least to most. We explain to our client colleagues that this is
due to the fact that the member key, which consists of ASCII-based
values, is being used as the basis of the sort in the OrderBy property,
which we saw earlier.
our colleagues that a means of adjusting the sort to meet their current
requirements is only a few steps away in Analysis Services 2005. We can
find the basis for our conclusion by examining a named calculation that
is already in place within the star schema underlying the Analysis Services
database and a resulting column which provides what we need to support the
more intuitive, ascending sort that the client representatives seek.
Click the Basic
tab to expose the Cube Designer, which we opened along with the Dimension
If it has not
already appeared by default, click the Cube Structure tab within the Cube
Designer to expose the Data Source View pane.
Within the Data
Source View pane, right-click the Customer dimension table.
Data on the context menu that appears, as shown in Illustration 10.
10: Exploring Customer Dim Table Data ...
data loads, and populates the Table tab of the opening viewer.
Scroll to the
right within the data on the Table tab, until the CommuteDist
column appears, as depicted in Illustration 11.
11: Named Calculation Column to Support Ascending Sort ...
to the client team that the CommuteDist named calculation assigns a
numeric sort number to each distinct member value within the Commute Distance
attribute hierarchy. As we can see, this column will support the ascending
sort that the new business requirements specify. Our next steps will surround
making modifications within the Dimension Designer to base sorting among
the members of the Commute Distance attribute hierarchy upon this
column, instead of the member key.
Click the Customer
tab to return to the Dimension Designer for the Customer dimension,
which we opened along with the Dimension Designer earlier.
Click the Dimension
Structure tab within the Dimension Designer.
CommuteDistanceSort named calculation in the Customer table in the Data
Source View pane.
Click New Attribute from
Column, as shown
in Illustration 12.
12: Click New Attribute from Column ...