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.
1.
Click Start.
2.
Navigate to,
and click, the SQL
Server Business Intelligence Development Studio, as appropriate.
We
briefly see a splash page that lists the components installed on the PC, and
then Visual Studio .NET 2005 opens at the Start page.
3.
Close the Start
page, if desired.
4.
Select File
-> Open from the main menu.
5.
Click Analysis
Services Database ... from the cascading menu, as depicted in Illustration
3.
Illustration
3: Opening the Analysis Services Database ...
The Connect
to Database dialog appears.
6.
Ensuring that
the Connect to existing database radio button is selected, type the Analysis
Server name into the Server input box atop the dialog.
7.
Using the
selector just beneath, labeled Database, select ANSYS062_Basic AS DB,
as shown in Illustration
4.
Illustration
4: Selecting the New Basic Analysis Services Database ...
8.
Leaving other
settings on the dialog at default, click OK.
SQL
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
this point.
9.
Within the Solution
Explorer, right-click the Basic cube (expand the Cubes folder
as necessary).
10.
Click Open
on the context menu that appears, as depicted in Illustration 5.
Illustration
5: Opening the Cube Designer ...
The
tabs of the Cube Designer open. Next, we’ll 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.
11.
Within the Solution
Explorer, right-click the Customer dimension (expand the Dimensions
folder as necessary).
12.
Click Open
on the context menu that appears, as shown in Illustration 6.
Illustration
6: Opening the Dimension Designer ...
The
tabs of the Dimension Designer open.
13.
Click the Dimension
Structure tab, if it has not already appeared by default.
14.
Within the Attributes
pane, click the Commute Distance attribute to select it, and to cause
the Properties window for the attribute to appear.
The Attributes
pane, with our selection of the Commute Distance attribute, together
with the associated Properties window (partial view), appears as
depicted in Illustration 7.
Illustration
7: Commute Distance Attribute and Properties Window (Composite View)
We note
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.
15.
Click the Browser
tab.
16.
Using the Hierarchy
selector atop the Browser tab, select the Commute Distance
attribute hierarchy, as shown
in Illustration 8.
Illustration
8: Select the Commute Distance Attribute Hierarchy in the Browser ...
The All
level of the Commute Distance attribute hierarchy appears in the Browser.
17.
Expand the All
Customers level in the Browser by clicking the “+” sign to
its immediate left.
The
members of the Commute Distance attribute hierarchy appear next, as
depicted in Illustration 9.
Illustration
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.
We inform
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.
18.
Click the Basic
tab to expose the Cube Designer, which we opened along with the Dimension
Designer earlier.
19.
If it has not
already appeared by default, click the Cube Structure tab within the Cube
Designer to expose the Data Source View pane.
20.
Within the Data
Source View pane, right-click the Customer dimension table.
21.
Click Explore
Data on the context menu that appears, as shown in Illustration 10.
Illustration
10: Exploring Customer Dim Table Data ...
Sample
data loads, and populates the Table tab of the opening viewer.
22.
Scroll to the
right within the data on the Table tab, until the CommuteDist
column appears, as depicted in Illustration 11.
Illustration
11: Named Calculation Column to Support Ascending Sort ...
We explain
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.
23.
Click the Customer
tab to return to the Dimension Designer for the Customer dimension,
which we opened along with the Dimension Designer earlier.
24.
Click the Dimension
Structure tab within the Dimension Designer.
25.
Right-click the
CommuteDistanceSort named calculation in the Customer table in the Data
Source View pane.
26.
Click New Attribute from
Column, as shown
in Illustration 12.
Illustration
12: Click New Attribute from Column ...
» 