Alternatively Sorting Attribute Members in Analysis Services 2005

About the Series …

This
article is a member of the series Introduction
to MSSQL Server Analysis Services. The
series is designed to provide hands-on application of the fundamentals of MS
SQL Server Analysis Services (“Analysis Services”), with each
installment progressively presenting features and techniques designed to meet
specific real-world needs. For more information on the series, please see my
initial article, Creating Our First Cube. For the software components, samples and tools
needed to complete the hands-on portions of this article, see Usage-Based Optimization in Analysis Services 2005, another article within this
series.

Introduction

While it is quite common to sort
the attribute members of our Analysis Services 2005 cubes based
upon either their name or key values (perhaps even a composite
member key), many of us encounter situations where we need to accomplish
a different sort order – a sort order that is based upon a characteristic lying
outside the attribute members themselves. In these cases, an option we
have is to order such an attribute on the basis of the name or key
of a related secondary attribute.

In this
article we will gain some hands-on exposure to sorting attribute members by
a secondary attribute key in Analysis Services 2005. (As we shall
see, sorting by the secondary attribute name is handled in a virtually
identical manner.) Our examination will include:

• A discussion
  surrounding the general concepts underpinning attribute relationships,
  including what they define and support, as well as the mechanics that determine
  these relationships.
• A discussion
  surrounding the way in which the Dimension Designer supports our
  definition of additional relationships between attributes.
• Cloning and
  deployment of a basic sample cube for use in our subject matter practice.
• A hands-on
  practice session, using our sample cube, where we create, and then leverage, an
  attribute relationship to demonstrate support of sorting of an existing attribute
  by a secondary attribute name or key.
• A discussion
  of 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.

Sort Attribute Members by a Secondary Attribute Name or Key

To
support the ordering of an attribute’s members (I will sometimes refer
to this as the “primary” attribute, to minimize confusion) based upon
the name or key of a secondary attribute, we must select a
secondary attribute that is related to the primary attribute.
An attribute
relationship defines the relationship or dependency between attributes.

Attributes are related to each other
differently for dimensions based upon single relational tables versus dimensions
based upon multiple tables. In the case of a single-table-based dimension,
all attributes are usually related via the key attribute.
In effect, the dimension’s attributes exist to provide information about
the dimension’s members – members that are linked, via the dimension’s key
attribute, to the facts in the fact table for each related measure
group. By contrast, the attributes of multiple-table-based
dimensions are typically linked based upon the join key between the
tables.

The
Dimension Designer affords us an easy means of defining additional relationships
between attributes, among other actions related to attributes
(such as changing default relationships for performance enhancement
purposes, etc.). In so doing, the Dimension Designer supports our
ability to meet specific business requirements for additional information about
members. When creating attribute relationships, we must keep in
mind that the primary attribute must relate to no more than one member
value within the attribute to which we wish to relate it.

Analysis
Services 2005
allows us a further option within our definition of a relationship
between two attributes. Based upon whether the relationship between
members will change over time, we can define the relationship as rigid
or flexible, as we shall see in our practice section. When we define a
relationship as rigid, attribute aggregations are not
recalculated every time the dimension is incrementally processed.
If the relationship between members does change, however, the dimension must
be fully processed.

Establishing the Capability to Sort Attribute Members by a Secondary Attribute Name or Key

Let’s consider an example whereby
we can obtain hands-on practice with establishing the capability to sort attribute members by a secondary
attribute key. (As we have already mentioned, sorting by the secondary
attribute name is handled in a virtually identical manner.) We will first
create a secondary attribute, and then relate this new attribute
to a primary attribute – an attribute that we have identified as
being a candidate for sorting by the secondary
attribute key. We will then align
the settings of the primary attribute to support the desired sort
presentation of its members.

Let’s
assume, for purposes of our practice example, that we have received a request
for assistance from representatives of our client, the Adventure Works organization.
Analysts within the VP – Marketing group, with whom we have worked in the past
to deliver solutions to meet various reporting and analysis needs, inform us
that they have received a request to add a new sort to the Customer
dimension of the existing cube structure. At the heart of the request lies a
specific attribute hierarchy, Commute Distance, which, due to an
oversight at design time, is not sorting its members, the respective distances,
from “least to most.” As is typically the case within the context of our
consulting events, the group assures us that, once they grasp the concepts to
meet the immediate need, they will want to expand the capability to fix similar,
less-than-optimal design characteristics in other areas of cube structure.

For the
time being, our colleagues want simply to remedy the deficiency, and to make
the Commute Distance attribute hierarchy members sort
intuitively, in ascending fashion. After we initially examine the cube
structure, we find a means of meeting the business requirement: we suggest
using another column that already exists in the underlying Adventure Works
DW star schema as the basis of the sort that the client representatives
tell us that they need. We offer to show our colleagues how to establish a
capability to sort attribute members by a secondary attribute name
or key, whereby we will leverage the data in the column we have
identified to deliver just the sort they need to meet their new requirement.
(We explain that our example will consist of showing how to sort upon the secondary
attribute key, but we emphasize that sorting by the name of the secondary
attribute would only involve a different selection within a single
property, within an essentially identical procedure.)

Our client
colleagues agree that the proposed approach should meet their sorting requirement.
We then set about the assembly of our example to illustrate how we will support
the sort capability we have described. Before we get started with making these enhancements to a
sample cube clone, we will need to prepare our local environment for the
practice session. We will take steps to accomplish this within the section
that follows.

Preparation: Create a Sample
Basic Database within Which to Perform the Steps of Our Practice Session

Create Sample Primary Database
for the Practice Exercise

Before getting started with our
practice session, we will need a basic sample Analysis Services database
(with cube) with which to work. To quickly create a copy of a basic Analysis
Services sample project, from which an Analysis Services database
can be quickly deployed, please perform the steps of the following procedure,
located in the References section of my articles index:

Once you have created the new
sample project, you should ascertain connectivity to the underlying
relational data source (particularly if you are running
“side-by-side” installations of MSSQL Server 2000 and MSSQL
Server 2005, but it is important to check even if not). You can do this by performing
the steps of the following procedure, also located in the References
section of my articles index:

Once you have created the new
sample project, and ascertained connectivity, deploy it to the Analysis
Server by performing the steps of the
following procedure, located in the References section of my articles
index (substitute object names for your own when following the procedure):

Ascertain that the New Practice
Database is in Place and Rename It

We are now ready to ascertain that
the new Analysis Services database is in place, as well as to rename it
to keep it safe from writeover in future exercises within the series. (You can,
of course, always delete the practice database if you do not choose to keep it
for future reference, etc.) To do this, we will work from within the Microsoft
SQL Server Management Studio.

The Connect to Server
dialog appears similar to that depicted in Illustration 1.

The Microsoft SQL
Server Management Studio opens.

The Databases folder
opens, exposing the detected Analysis Services database(s). Our new Analysis
Services database, DBJ_Basic AS DB, should appear among the other
databases.

ANSYS062_Basic AS DB

Having ensured that the
new database is in place, and having renamed it, we are ready to begin the
procedural part of our practice session.