An Introduction to Attribute Member Keys ... from the Perspective of the Composite Key
As we
have learned, attributes serve as the foundation for our dimensions
and cubes. To review, we discovered in Part I 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. Depending upon the attribute’s Usage property
setting, the three usage types consist of the following:
-
Regular
An attribute that belongs to neither the Parent nor
Key roles, a Regular attribute is used to support our dimensions
with additional “adjectives.” That is, the regular attribute allows us
to associate additional information with the dimension to support analysis of
characteristics we deem important within our respective analytical
environments. (We address Regular attributes throughout my Introduction
to MSSQL Server Analysis Services series.)
-
Parent
A Parent attribute is used to support the
recursive, parent-child relationships among the members of a dimension
requiring such support. Each dimension is limited to only one attribute
of this usage type. (We address parent-child dimensions within other
articles of my Introduction to MSSQL Server Analysis Services
series.)
-
Key
Every dimension contains a single Key attribute.
The attribute member key serves as the link that associates its containing
dimension to a given measure group. Throughout the Analysis
Services documentation, as well as within numerous books and periodicals
based upon the subject matter, the attribute key is likened to the
primary key within a relational table: a relationship, similar to a join within
the relational world (relating two tables), is established between the dimension
and measure group(s) through the presence of the attribute key.
An attribute member key for a
representative dimension, the Sales Summary Order Details dimension within the AdventureWorks sample cube, appears as
shown in Illustration
1.
Illustration 1: A
Representative Key Attribute ...
Our focus within this article will be the attribute member key, just as it was in Part I (albeit this time from the
perspective of a composite key, versus a simple key). As we noted there, the attribute member key is critical to the identification
of unique attribute members within Analysis Services. The key, as
we shall see again, 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.)
As we noted in Part I, the members of an attribute in Analysis
Services can have one of two types of keys: a simple key or a composite
key. In Part I, we considered the characteristics
and properties of a simple key. In this, the second half of this article,
we will consider the characteristics and properties of a composite key.
We learned in Part I
that a simple key can be of any data type allowed within an Analysis
Services database. It must, we noted, be unique, and is defined by a
single value. While the composite key is similar to a simple key in
that it must be unique for each attribute member, the composite key
differs in that it is defined by a combination of values which can be of
varying data types.
Composite keys become necessary in scenarios where a simple key
alone is insufficient to identify uniqueness. A commonly cited example would
be a City attribute within, say, a Customer dimension (a
situation that is found within the Adventure Works sample Analysis
Services database) for an organization that serves customers all over the
world. Reducing such a scenario to the Adventure Works model, it
becomes easy to see why it would be impossible to uniquely identify a City
via a simple key based upon the City column of the DimGeography
table in the underlying Adventure Works DW data source. The reason that
this is true is that many Cities, existing it different states,
provinces, and countries, have the same names.
We can address this “absence of uniqueness” by employing a
composite key. The composite key is composed of a combination of column
values that, together, provide a unique key. As an illustration, the
example City attribute that I have cited from within the Adventure
Works sample database is composed of the City and StateProvinceCode
columns of the DimGeography table, which is shown within the KeyColumns
property of the Source properties group for the City
dimension attribute as depicted in Illustration 2.
Illustration 2: A
Representative Composite Key within the Adventure Works Database
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.
» 