About the Series …
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
In Dimensional Model Components: Dimensions Parts I and
II, we introduced the dimensional
model in general, noting its wide acceptance as the preferred structure for
presenting quantitative and other organizational data to information
consumers. We then began our examination of dimensions, the analytical “perspectives”
upon which the dimensional model relies in meeting the primary
objectives of business intelligence, including its capacity to support:
of relevant and accurate information representing business operations and
the rapid and
accurate return of query results;
dice” query creation and modification;
wherein information consumers can pose questions quickly and easily, and achieve
rapid results datasets.
learned, in Dimensional Model Components:
Dimensions Parts I and II, that dimensions form the
foundation of the dimensional model. They represent the perspectives
of a business or other operation, and reflect the intuitive ways that
information consumers need to query and view data. We noted that we might
consider dimensions as nouns that take part in, are acted upon
by, or are otherwise associated with, the verbs (or actions /
transactions undertaken by the business) that are represented by the facts or
measures contained within our business intelligence systems.
discovered in earlier articles that, within the Analysis Services model,
database dimensions underlie all other dimensions, whose added
properties distinguish them from the database dimensions they reference,
within the model. Each dimension within our model contains one or more hierarchies.
As we learn in other articles of this series, two types of hierarchies
exist within Analysis Services: attribute hierarchies and user
(sometimes called “multi-level”) hierarchies. For purposes of
this article, the term “attribute” means the same thing as “attribute
hierarchy”. (We examine user hierarchies, to which we will simply
refer as “hierarchies,” in other articles specifically devoted to that topic.)
the metaphor we used earlier in describing dimensions as nouns
and measures as verbs, we might consider attributes as
somewhat similar to adjectives. That is, attributes help us to
define with specificity what dimensions cannot define by themselves. Dimensions
alone are like lines in geometry: they don’t define “area” within
multidimensional space, nor do they themselves even define the hierarchies
that they contain. A database dimension is a collection of related
objects called attributes, which we use to specify the coordinates
required to define cube space.
the table underlying a given dimension (assuming a more-or-less typical
star schema database) are individual rows supporting each of the members
of the associated dimension. Each row contains the set of attributes
that identify, describe, and otherwise define and classify the member
upon whose row they reside. For instance, a member of the Patient
dimension, within the Analysis Services implementation for a healthcare
provider, might contain information such as patient name, patient ID, gender,
age group, race, and other attributes. Some of these attributes
might relate to each other hierarchically, and, as we shall see in other
articles of this subseries (as well as within other of my articles), multiple hierarchies
of this sort are common in real-world dimensions.
Dimensions and dimension attributes
should support the way that management and information consumers of a given
organization describe the events and results of its business operations.
Because we maintain dimension and related attribute information
within the database underlying our Analysis Services implementation, we
can support business intelligence for our clients and employers even when these
details are not captured within the system where transaction processing takes
place. Within the analysis and reporting capabilities we supply in this
manner, dimensions and attributes are useful for aggregation,
filtering, labeling, and other purposes.
Having covered the general characteristics and purposes of attributes
in Dimensional Attributes: Introduction and Overview Parts I through V, we then fixed our focus upon the properties
underlying them, based upon the examination of a representative attribute within
our sample cube. We then continued our extended examination of attributes
to yet another important component we had touched upon earlier, the attribute
member key, with which we gained some hands-on exposure in practice
sessions that followed our coverage of the concepts. In Attribute
Member Keys – Pt I: Introduction and Simple Keys and Attribute
Member Keys – Pt II: Composite Keys, we introduced Attribute Member Keys in detail, continuing our
recent group of articles focusing upon dimensional model components,
with an objective of discussing the associated concepts, and of providing
hands-on exposure to the properties supporting them.
As a part of our exploration of attribute member Keys,
we first discussed the three attribute usage types that we can
define within a containing dimension. We then narrowed our focus to the Key
attribute usage type (a focus that we developed, as we have noted,
throughout Attribute Member Keys – Pt I:
Introduction and Simple Keys and Attribute Member Keys – Pt II: Composite Keys), discussing its role in meeting
our business intelligence needs. We next followed with a discussion of the nature
and uses of the attribute Key from a technical perspective, including
its purpose within a containing dimension within Analysis Services.
In Attribute Member Keys – Pt I: Introduction and Simple
Keys and Attribute Member Keys – Pt II: Composite Keys, we introduced the concepts of simple
and composite keys, narrowing our exploration in
Part I to the former, where we reviewed the Properties
associated with a simple key, based upon the examination of a
representative dimension attribute, Geography, within our
sample UDM. In Part II, we revisited the differences
between simple and composite keys, and explained in more detail
why composite keys are sometimes required to uniquely identify attribute
members. We then reviewed the properties associated with a composite
key, based upon the examination of a representative dimension attribute,
Date, also within our sample UDM.
In this article, we will examine the attribute member Name
property, which we briefly touched upon in Dimensional
Attributes: Introduction and Overview
Part V. We will examine the details of the attribute member
Name, and shed some light on how they can most appropriately be used
without degrading system performance or creating other unexpected or
Our examination will include:
A review of
the nature of the attribute member Name property, and its possible
roles in helping to meet the primary objectives of business intelligence.
A review of
the nature and uses of the attribute member Name from a technical
perspective, including its purpose within its containing dimension within
A discussion surrounding
some of the differences between attribute Name and Key properties.
differences between Analysis Services 2000 and Analysis Services 2005
regarding the use of expressions within our Name column references;
A review of
the settings associated with the Name property, based upon the
examination of a representative dimension attribute within our
Attribute Member Names
have learned, attributes serve as the foundation for our dimensions
and cubes. To review, we discovered in Attribute
Member Keys – Pt I: Introduction and Simple Keys that each
attribute, typically based upon a single column (or a named calculation)
within the associated, underlying dimension table, falls into one of three
possible usage roles, Regular, Parent, and Key. We
then focused upon the
attribute member Key
our subject from the perspective of both a simple key and a composite
key). As we noted there, the attribute member Key is
critical to the identification of unique attribute members within Analysis
Services. The Key, we learned, is specified within the KeyColumns
setting, within the Source group of a dimension’s Attribute properties.
(We overviewed the Source properties in my Database Journal
article Dimension Attributes: Introduction and
Overview, Part V.)
attribute members are assigned a Key (be it simple or composite) to
uniquely identify them, members can be assigned a Name. A descriptive
name is often more consumer – friendly, and not necessarily a mere luxury:
even if a simple name derived from the Key is sufficiently
understandable to the organization’s information consumers, we would still need
to employ the NameColumn property where a composite key (see Attribute Member
Keys – Pt II: Composite Keys) is involved; otherwise Analysis Services cannot
determine the appropriate Name to assign.
We do not
have to assign a Name. If we do not, Analysis Services
assigns as the Name the underlying attribute Key column. Such
arrangement might be perfectly adequate for, say, consumers who recognize part
or serial numbers, or other designations, and do not need “English” names, but
in most cases, a Name comes in handy for both analysis and reporting.
Moreover, and, even in cases where everyone doesn’t need it, it can certainly
be suppressed (as in a report), etc., except for scenarios within which benefit
is obtained from its presence. (I have written reports where the consumer could
make the choice at runtime to hide or display the Name, Key or
combination of both, or even to select Name, Key or combination
of both to populate the associated parameter picklist each time the
report is executed.
NOTE: I introduce and examine the intrinsic NAME_VALUE and MEMBER_VALUE properties
(which are derived from the NameColumn and ValueColumn property
settings that we examine within this article), from the perspective of their
use within MDX queries, in my articles Intrinsic Member Properties: The
MEMBER_NAME Property and Intrinsic Member Properties:
The MEMBER_VALUE Property. Both articles are members of my MDX Essentials series at Database Journal.
We will gain hands-on exposure to attribute member Name in
the practice session that follows. Before we get started working within a sample cube clone,
we will need to prepare the local environment for the practice session. We
will take steps to accomplish this within the section that follows.
Preparation: Locate and Open the Sample Basic UDM Created Earlier
Components: Dimensions Part I, we created a sample basic Analysis Services database within
which to perform the steps of the practice sessions we set out to undertake in
the various articles of this subseries. Once we had ascertained that the new
practice database appeared to be in place, and once we had renamed it to ANSYS065_Basic
AS DB, we began our examination of dimension properties. We continued
with our examination of attributes within the same practice environment,
which we will now access (as we did within Dimensional Model
Components: Dimensions Part I and Dimensional Attributes: Introduction and Overview Parts I through V) by taking the
following steps within the SQL Server Business Intelligence Development
NOTE: Please access the Analysis
Services database which we prepared in Dimensional Model Components: Dimensions Part I (and have used in subsequent
articles) before proceeding with this article. If you have not completed the
preparation to which I refer, or if you cannot locate / access the Analysis
Services database with which we worked in the referenced previous articles,
please consider taking the preparation steps provided in Dimensional Model Components: Dimensions Part I before continuing, and prospectively
saving the objects with which you work, so as to avoid the need to repeat the
preparation process we have already undertaken for subsequent related articles
within this subseries.
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 shown in Illustration 1.
Illustration 1: Opening the Analysis Services Database …
to Database dialog appears.
the Connect to existing database radio button atop the dialog is
selected, type the Analysis Server name into the Server input box
(also near the top of the dialog).
selector just beneath, labeled Database, select ANSYS065_Basic AS DB,
as depicted in Illustration
Illustration 2: Selecting the 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. Having overviewed the properties of dimension
attributes in previous articles, we will continue to get some hands-on
exposure to the Name property for an example dimension attribute
member, from within our practice UDM.