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
.

1. 
Start Microsoft
SQL Server Management Studio
.

2. 
Select Analysis
Services
in the Server type selector of the Connect to Server
dialog that appears.

3. 
Type / select
the server name (server name / instance, if appropriate) in the Server name
selector.

4. 
Supply
authentication information, as required in your own environment.

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



Illustration 1:
Connecting to the Server …

5. 
Click the Connect
button to connect with the specified Analysis Server.

The Microsoft SQL
Server Management Studio
opens.

6. 
In the Object
Explorer
pane (it appears by default on the left side of the Studio),
expand the Databases folder (click the “+” sign to its immediate left),
appearing underneath the Analysis Server within which we are working.

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.

7. 
Right-click
the DBJ_Basic AS DB database, and select Rename from the context
menu that appears next, as shown in Illustration 2.



Illustration 2:
Renaming the Sample Database

8. 
Rename the Analysis
Services
database as follows:

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.

William Pearson
William Pearson
Bill has been working with computers since before becoming a "big eight" CPA, after which he carried his growing information systems knowledge into management accounting, internal auditing, and various capacities of controllership. Bill entered the world of databases and financial systems when he became a consultant for CODA-Financials, a U.K. - based software company that hired only CPA's as application consultants to implement and maintain its integrated financial database - one of the most conceptually powerful, even in his current assessment, to have emerged. At CODA Bill deployed financial databases and business intelligence systems for many global clients. Working with SQL Server, Oracle, Sybase and Informix, and focusing on MSSQL Server, Bill created Island Technologies Inc. in 1997, and has developed a large and diverse customer base over the years since. Bill's background as a CPA, Internal Auditor and Management Accountant enable him to provide value to clients as a liaison between Accounting / Finance and Information Services. Moreover, as a Certified Information Technology Professional (CITP) - a Certified Public Accountant recognized for his or her unique ability to provide business insight by leveraging knowledge of information relationships and supporting technologies - Bill offers his clients the CPA's perspective and ability to understand the complicated business implications and risks associated with technology. From this perspective, he helps them to effectively manage information while ensuring the data's reliability, security, accessibility and relevance. Bill has implemented enterprise business intelligence systems over the years for many Fortune 500 companies, focusing his practice (since the advent of MSSQL Server 2000) upon the integrated Microsoft business intelligence solution. He leverages his years of experience with other enterprise OLAP and reporting applications (Cognos, Business Objects, Crystal, and others) in regular conversions of these once-dominant applications to the Microsoft BI stack. Bill believes it is easier to teach technical skills to people with non-technical training than vice-versa, and he constantly seeks ways to graft new technology into the Accounting and Finance arenas. Bill was awarded Microsoft SQL Server MVP in 2009. Hobbies include advanced literature studies and occasional lectures, with recent concentration upon the works of William Faulkner, Henry James, Marcel Proust, James Joyce, Honoré de Balzac, and Charles Dickens. Other long-time interests have included the exploration of generative music sourced from database architecture.

Latest Articles