Business Intelligence Architect Bill Pearson continues his exploration of Attribute Member Keys in another member of a group of articles surrounding significant components of the Analysis Services dimensional model. In this article we resume our examination of Attribute Member Keys, focusing upon composite keys and their properties.
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, 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 will examine user hierarchies, to which we will
simply refer as hierarchies, in a subsequent article.)
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 subsequent
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 fixed our focus upon the properties underlying
them, based upon the examination of a representative attribute within
our sample cube. In this article, we will continue our extended examination of
attributes to yet another important component we have touched upon earlier,
the attribute member key, with which we will continue to get some
hands-on exposure in the practice session below. 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.
In Part I of this article, we introduced Attribute Member Keys, 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. We reviewed our initial introduction to
the dimensional model and summarized its role in meeting the primary
objectives of business intelligence. Next, we provided a brief overview of dimension
attributes in general, referencing a subseries of articles, within my Introduction to MSSQL Server Analysis Services series, where we explore the properties
underlying them in detail.
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 develop throughout Parts I
and II of this article), discussing its role in meeting our
business intelligence needs. We next followed with a discussion of the role of
the Key attribute from a technical perspective, including its purpose
within a containing dimension within Analysis Services.
We then introduced the concepts of simple and composite
keys, narrowing our exploration in Part I
to the former. We
reviewed the Properties associated with a simple key, based upon
the examination of a representative dimension attribute, Geography,
within our sample UDM. Finally, we looked ahead to this, the second
half of the article, where we will explore the Properties associated
with a composite key, and gain hands-on exposure within a sample cube.
Our examination will include:
A review of
the three attribute usage types that we can define within each
A review of
the nature of the attribute member key and its role in meeting the
primary objectives of business intelligence.
A review of
the role of the attribute member key from a technical perspective,
including its purpose within its containing dimension within Analysis
of the differences between simple and composite keys, and an
explanation as to why composite keys are sometimes required to uniquely
identify attribute members.
A review of the
Properties associated with a composite key, based upon the
examination of a representative dimension attribute within our
An Introduction to Attribute Member Keys ... from the Perspective of the Composite Key
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 attributes Usage property
setting, the three usage types consist of the following:
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.)
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
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
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 dimensions Attribute properties. (We
overviewed the Source properties in my Database Journal article Dimension
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.
Preparation: Locate and Open the Sample Basic UDM Created Earlier
In Dimensional Model
Components: Dimensions Part I, we created a sample basic UDM 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 Studio.
NOTE: Please access the UDM
which we prepared in Dimensional Model Components: Dimensions Part I before proceeding with this
article. If you have not completed the preparation to which I refer in the
previous article, or if you cannot locate / access the Analysis Services
database with which we worked there, 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 3.
Illustration 3: Opening
the Analysis Services Database ...
to Database dialog appears.
the Connect to existing database radio button is selected, type the Analysis
Server name into the Server input box atop the dialog.
selector just beneath, labeled Database, select ANSYS065_Basic AS DB,
as depicted in Illustration
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 properties for an example attribute member key, from
within our sample UDM.
Procedure: Examine Key Attribute Properties and Characteristics in Analysis Services 2005 (Composite Key)
In the practice procedures that
follow, we will select and examine a representative key attribute within
the sample cube, focusing upon the properties that define and support such
an attribute. We will perform our practice sessions within the SQL
Server Business Intelligence Development Studio, from which we will perform
our examination of attribute properties within our Analysis Services
database, ANSYS065_Basic AS DB.
In Dimensional Model
Components: Dimensions Part I and II, and Dimensional Attributes: Introduction and Overview Parts I through V, respectively, we overviewed the properties underpinning
Database and Cube dimensions, and then examined the properties
supporting dimension attributes. Moreover, in Part I of this article, we focused upon
those properties for a simple attribute key. Just as we did in those
articles, we will examine the detailed settings for a representative attribute member key here, concentrating on those settings within the
context of a composite key. To access these settings for the attribute member key within a representative dimension, we will
need to open that dimension within the Dimension Designer first.
Within the Solution
Explorer, right-click the Time dimension (expand the Dimensions
folder as necessary).
on the context menu that appears, as shown in Illustration 5.
Illustration 5: Opening the Dimension via the Dimension Designer ...
tabs of the Dimension Designer open.
Click the Dimension
Structure tab, if we have not already arrived there by default.
member attributes that appear within the Attributes pane of the Dimension Structure tab.
The attributes belonging to the Time dimension appear as depicted in
Illustration 6: The Member Attributes, Time Dimension
note that eight attributes appear within the Attributes pane.
Let's get some exposure to the properties associated with attribute keys (in particular, a composite
key) by examining a representative member among the attributes
we see here.
Review Key Attribute Properties
Attributes: Introduction and Overview Part V, and as a part of our more
detailed exploration in Attribute
Member Keys Pt I: Introduction and Simple Keys, we discovered
that, within the
of every attribute lays the KeyColumns property. Lets examine the
property and the underlying KeyColumns collection for the Calendar Quarter attribute
which represents a composite key within the sample Analysis Services database, by taking the
Within the Attributes
pane of the Dimension Structure tab, right-click the Calendar
Quarter Key attribute.
on the context menu that appears, as shown in Illustration 7.
Illustration 7: Select Properties from the Context Menu ...
pane appears for the
(The Properties pane likely appeared when we selected the Calendar Quarter attribute
the Attributes pane, by default, below the Solution Explorer.
The design environment can, of course, be customized in many ways to accommodate
your local environment and development needs.)
Expand the Source
group, at the bottom of the Properties pane, by clicking the +
sign that appears to the immediate left of its label, if necessary, as depicted
in Illustration 8.
Illustration 8: Expand the Source Group in the Properties Pane
The expanded Source properties group of the Properties
pane for the Calendar Quarter attribute key appears as shown in Illustration 9.
Illustration 9: The Source Properties for the Calendar Quarte Attribute Key
take a look at each of the individual properties (and subproperties), as
relevant to a composite key, discussing the purpose of the property,
and examining possible settings with which we can come into contact. (As we
have noted, we examine these settings for a simple key in Part I.) In most attributes, we
find that only the KeyColumns property is relevant, from the perspective
of simple or composite keys, although NameColumn and ValueColumn
can certainly offer opportunities for employment, as we see in other articles
of this series. We will skip the CustomRollupColumn and CustomRollupPropertiesColumn
properties for this reason both are set to (none) in the case of our
example, the Calendar
Quarter attribute key.
Source Property: KeyColumns
The value we select for the KeyColumns property
specifies a column or
columns within the underlying data source. The KeyColumns property specifies the column(s)
containing the member key(s).
Click the box
to the immediate right of the KeyColumns label, just beneath the expanded CustomRollupPropertiesColumn label, within the expanded Source properties
group of the Properties pane.
note that the setting box currently contains (Collection), instead of
a Table.Column name (such as that which we see in the NameColumn
setting just below the KeyColumns setting we currently occupy) an
indicator that this is a composite key.
ellipses (... ) button that appears on the right edge of the KeyColumns property box, as depicted in Illustration 10.
Illustration 10: Click the Ellipses ( ... ) Button to the Right of the KeyColumns Property
The DataItem Collection Editor appears, as shown in Illustration 11.
Illustration 11: The DataItem Collection Editor Appears
we noted in Part I, the DataItem Collection Editor
is used throughout
Intelligence Development Studio to edit the collection of data items associated with the KeyColumns
property of various Analysis Services objects. The Members pane
on the left side of the dialog lists the data items contained by the
collection. Here, we can add or remove data items to the Members pane,
as well as move the items up or down as appropriate to meet our business
notice that two members appear within the Members pane, on the left side
of the DataItem
(as opposed to the single member that appears for a simple key). Because both members have similar settings in the Source properties
group, we will only examine those of the 0
member, DimTime.CalendarYear, understanding that the settings for the 1
member, DimTime.CalendarQuarter (and other members, were they required
to create a unique key, as discussed earlier), would be need
to be set appropriately to specify its origin in the underlying data, etc.
the 0 member (DimTime.CalendarYear) is selected
within the Members pane, expand the Misc group in the Properties
pane (right half of the Editor) by clicking the + sign that
appears to the immediate left of the Misc label, as depicted in Illustration 12.
Illustration 12: Expand the Misc Group in the Properties Pane
Expand the Source
properties group in the Properties pane, atop the list that appears
under the newly expanded Misc group, by clicking the + sign
that appears to the immediate left of the Source label.
The Properties pane displays a list of properties available
for the data item that is selected within the Members pane (left half of the Editor), as shown in Illustration 13.
Illustration 13: The Expanded Misc Properties Appear
we can see, the first of the displayed DimTime.CalendarYear DataItem
properties, Source, expands to make available the TableID and the
ColumnID boxes, where we specify the location of the key within
the underlying database.
Click the box
to the immediate right of the TableID label, just beneath the expanded Source
group label, to enable the downward-pointing selector button.
downward arrow selector button, to expose the tables for
selection, as partially depicted in Illustration 14.
Illustration 14: Source - TableID Property Value Selection Options (Partial View)
we have selected the TableID, we can select from a context-sensitive
list of columns via the ColumnID selector immediately underneath the TableID
selector, as partially shown in Illustration 15.
Illustration 15: Source - ColumnID Property Value Selection Options (Partial View)
Leaving both Source
subproperties at their previously established settings, click the box to the
immediate right of the DataType label, just beneath the expanded Source
ColumnID property, once again to enable the downward-pointing selector
downward arrow selector button, to expose the types for
selection, as partially depicted in Illustration 16.
Illustration 16: DataType Property Value Selection Options (Partial View)
in Part I that the data type options within Analysis
Services 2005 have been expanded over those of previous versions. The DataType
property allows us to convert the data types from those applicable to
the data within the underlying relational database to different data types that
we might require for the corresponding member data within Analysis Services.
We are thus afforded yet another element of versatility between these two
layers of the integrated business intelligence solution.
Leaving the DataType property at its previously established
setting, click the DataSize label, just beneath the DataType property label, simply to rest it
DataSize property allows us to specify (for either binary or text data) a size
(in bytes and characters, respectively). The setting we see in our example is
4. (The default is 255 characters anytime we do
not specify size.)
Leaving the DataSize property at its previously established
setting, click the box to the immediate right of the NullProcessing
label, just beneath the DataSize property, once again to enable the
downward-pointing selector button.
selector button, to expose the five options for NullProcessing
selection, as shown in Illustration 17.
Illustration 17: NullProcessing Selection Options
we can select a value to dictate the manner in which Analysis Services
processes null attribute member data. These values are explained in
detail in Table 1.
Table 1: Options for NullProcessing Rule Selection
preserves the null
NOTE: This selection dictates the expenditure of additional
resources in the storage and processing of null data.
Server displays an error message, because the null value is
Server associates the null value with an unknown member
(which dictates that the value is to be treated in accordance with
established unknown member rules).
converts the null value to a blank (when the data type is a string)
or to a zero (when the data type is other than a string).
Server selects the value based upon its determination of context.
Leaving the NullProcessing property at its previously established
setting, click the box to the immediate right of the Collation label,
just beneath the NullProcessing property,
this time to enable the ellipses (...) button to its right.
ellipses (...) button, to expose the Define Collation dialog, which
appears as depicted in Illustration 18.
p>Illustration 18: The DefineCollation Dialog
property affords us a way to specify the rules we wish to invoke for text data
string comparisons. While collation in general has multiple purposes, it
is often used to support the determination of whether the members of a given
pair of strings are alike or different. Several Sort Orders are also
available, with the Designator and Sort Order selections
defaulting to server settings.
downward arrow selector button to the right of the box labeled Collation
designator, to expose the collations available for selection, as partially shown
in Illustration 19.
Illustration 19: Available Collation Options (Partial View)
settings Collation Designator dialog at their previously established settings, click the Cancel
button to dismiss the dialog.
Click the Format
label, just beneath the Collation property label, simply to rest it
noted in Part I that
the Format property purports (via the Books Online and other documentation)
to allow us to specify - using Visual Basic (Format function) format
types - the conventions used in transforming numeric data to text, if such a
transformation is required. The reality is that the only member formatting
supported within the Unified Dimension Model (UDM) is the Trimming
setting that we discuss below. (We can, of course, employ named calculations
or column calculations (at the data source view level) within the cube
to achieve our formatting ends, as alternative approaches.
Leaving the Format property blank, click the box to the
immediate right of the InvalidXmlCharacters label, just beneath the Format property, once again to enable the
downward-pointing selector button.
downward arrow selector button, to expose the three selection options
for InvalidXmlCharacters, as depicted in Illustration 20.
Illustration 20: Selection Options for InvalidXmlCharacters
InvalidXmlCharacters property is applicable in cases
where we expect data to be received in the XML format, and where we wish to
dictate the handling of such data. The values are explained in Table 2.
Table 2: Options for InvalidXmlCharacters Selection
does not change) invalid characters.
characters with a question mark (?)
Leaving the InvalidXmlCharacters property at its previously established
setting, click the MimeType label, just beneath the InvalidXmlCharacters property label, simply to rest it
The MimeType property allows us to specify the
binary data type, where necessary to meet our needs.
Leaving the MimeType property blank, click the box to the immediate
right of the Trimming label, just beneath the MimeType property, as before, to enable the
downward-pointing selector button.
selector button, to expose the four options for Trimming selection, as shown
in Illustration 21.
Illustration 21: Trimming Property Value Selection Options
The Trimming property allows us to specify the
desired treatment of trailing spaces at the beginning / end of a string. As we see in Illustration 21 above, the options are self-explanatory.
Click the OK
button to dismiss the DataItem Collection Editor.
NOTE: Please consider saving the
project we have created to this point for use in subsequent related articles of
this subseries, so as to avoid the need to repeat the preparation process we
have undertaken initially, to provide a practice environment.
-> Save All to save our work, up to this
point, within the originally chosen location, where it can be easily accessed
for our activities within subsequent articles of this subseries.
-> Exit to leave the design environment,
when ready, and to close the Business Intelligence Development Studio.
the second half of a two-part article introducing Attribute Member Keys,
we continued 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. We
reviewed our initial introduction to the dimensional model and
summarized its role in meeting the primary objectives of business
intelligence. Next, we provided a brief overview of dimension attributes
in general, referencing a subseries of articles, within my Introduction to MSSQL Server Analysis Services
series, where we explore the properties underlying dimension
attributes in detail.
We continued our exploration of Attribute Member Keys.
First, we re-examined the three Attribute usage types (which we
initially discussed in Attribute Member Keys Pt I:
Introduction and Simple Keys) that we can define within a containing dimension. We then
narrowed our focus to the Key attribute usage type (a focus that we
developed throughout Parts
I and II of this article), discussing its
role in meeting our business intelligence needs. We next followed with a discussion
of the role of the Key attribute from a technical perspective, including
its purpose within a containing dimension within Analysis Services.
We then reviewed the concepts of simple and composite
keys, narrowing our exploration in this half of the article to the latter.
We discussed differences between the two key types, and why composite keys
are sometimes required to uniquely identify attribute members. Finally,
we reviewed the Properties associated with a composite key, based
upon the examination of a representative dimension attribute, Time,
within our sample UDM.
See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.