Attribute Relationships: Settings and Properties

January 22, 2009

This article continues the overview of Attribute
Relationships in Analysis Services begun in Introduction
to Attribute Relationships in MSSQL Server Analysis Services. Both this article and its
predecessor extend the examination of the dimensional model that
we began in Dimensional Model Components: Dimensions Parts Iand
II. After
taking up various additional components of the dimensional
model in subsequent articles, we performed hands-on exploration of
the general
characteristics and purposes of attributes in Dimensional Attributes:Introduction and Overview Parts Ithrough V. We then fixed our focus upon
the properties underlying attributes, based upon the examination
of a representative attribute within our sample cube., extending our
overview into attribute member Keys, Names and Values. This
article continues the focus upon attribute relationships, which define
the possible associations between attributes, including a discussion
surrounding why these relationships are important, and how they define
the properties of association that a given attribute has with other attributes.
Our concentration here will be the performance of a detailed examination of the
properties underlying attribute relationships, along with a
review of the respective settings associated with each property, based
upon a representative dimension attribute within our sample UDM.

Note: For more
information about my Introduction to MSSQL Server Analysis Services column in general,
see the section entitled About
the MSSQL Server Analysis
Services Series that follows the
conclusion of this article.

Introduction

In Introduction to
Attribute Relationships in MSSQL Server Analysis Services, I
summarized the articles preceding it within the current subseries surrounding a
general introduction to the dimensional model. I noted the wide
acceptance of the dimensional model as the preferred structure for
presenting quantitative and other organizational data to information consumers.
The articles of the series then undertook an 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:

the presentation
of relevant and accurate information representing business operations and
events;

We
extended our examination of dimensions into several detailed articles.
These articles are comprised of Dimensional Model Components: Dimensions Parts Iand
II, wherein we emphasized that dimensions,
which represent the perspectives of a business or other operation, and
reflect the intuitive ways that information consumers need to query and view
data, form the foundation of the dimensional model. We noted that 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.)

We
next introduced dimension attributes within the subseries, and conducted
an extensive overview of their nature, properties, and detailed settings in Dimensional Attributes: Introduction and Overview Parts
Ithrough V. We noted that attributes
help us to define with specificity what dimensions cannot define by
themselves. Moreover,we learned that attributes are collected
within a database dimension, where we can access them tohelp us to
specify the coordinates required to define cube space.

Throughout
the current subseries, I have emphasized that dimensions and dimensionattributes should support the way that management and information
consumers of a given organization describe the events and results of the
business operations of the entity. 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 Ithrough V, we fixed our focus upon the properties
underlying them, based upon the examination of representative attributes 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 inpractice
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. 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 undertook
a discussion of the nature and uses of the attribute Key from a
technical perspective, including its purpose within a containing dimension
in Analysis Services.

In Attribute Member Keys
 Pt I: Introduction and Simple Keys and Attribute Member
Keys  Pt II: Composite Keys, we explored the concepts of simple and composite
keys, narrowing our examination in Part I
to the former, where we reviewed the Properties associated with a simple
key, based upon the examination of a representative dimensionattribute
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 within our sample UDM.

In Attribute Member
Names, we examined the attribute member Name
property, which we had briefly introduced in Dimensional
Attributes: Introduction and Overview Part V. We examined the
details of the attribute member Name property, and shed some light
on how attribute member Name might most appropriately be used
without degrading system performance or creating other unexpected or
undesirable results. Finally, we examined the sister attribute
member Value property (which we introduced along with attribute member Name
inDimensional Attributes: Introduction
and Overview Part V) in Attribute
Member Values in Analysis Services. As we did in our overview of attribute
member Name, we examined the details of Value. Our concentration
was also similarly upon its appropriate use in providing support for the
selection and delivery of enterprise data in a more focused and
consumer-friendly manner, without the unwanted effects of system performance
degradation, and other unexpected or undesirable results, that can accompany
the uninformed use of the property.

Finally, in our last article, Introduction
to Attribute Relationships in MSSQL Server Analysis Services,
we introduced another part of the conceptual model, Attribute Relationships.
In this overview, we discussed several best practices and design, among other,
considerations involved in the use of attribute relationships. Our
focus was upon the general exploitation of attribute relationships in
providing support for the selection and delivery of enterprise data.

We will continue our exploration of attribute
relationships in this article, where we will examine attribute
relationships in a more detailed manner, similar to the way we treated the
subject matter in previous articles within this subseries. We will concentrate
in detail upon the properties and settings that underlie them.

Our examination will include:

A review of
the nature of the attribute relationship,and its possible roles
in helping to meet the primary objectives of business intelligence, based upon
and extending the discussion we initiated in Introduction to Attribute
Relationships in MSSQL Server Analysis Services;

A detailed
examination of the properties underlying attribute relationships,
along with a review of the respective settings associated with each property,
based upon the attributes of a representative dimension within
our sample UDM;

Hands-on
practice in creating, modifying and deleting attribute relationships for
several attributes within a representative dimension of our
sample UDM;

A look forward
to the article that follows within our series, where we will continue our
detailed examination of the properties underlying attribute relationships,
along with a review of the respective settings associated with each property,
based upon the attributes of additional representative dimensions within
our sample UDM.

Attribute Relationships

As we
have learned, attributes serve as the foundation for our dimensions
and cubes. Moreover, in Analysis Services 2005, attributes
within a dimension are always related either directly or indirectly to
the key attribute. Assuming the definition of a dimension based upon
a star schema, where all dimension attributes are derived from
the same relational table, an attribute relationship is automatically defined
between the key attribute and each non-key attribute of the dimension.
Alternatively, if we assume the definition of a dimension based upon a snowflake
schema, where dimension attributes are derived from multiple related
tables, Analysis Services automatically defines an attribute
relationship in the following manner:

Between the key
attribute and each non-key attribute associated with columns in the
main dimension table;

Between the key
attribute and the attribute associated with the foreign key in
the secondary table that links the underlying dimension tables;

Between the attribute
associated with the foreign key in the secondary table and each non-key
attribute associated with columns from the secondary table.

As we
noted in Introduction to Attribute
Relationships in MSSQL Server Analysis Services, there are a
number of reasons to change the assigned default attribute relationships.
For example, we might want to define a natural hierarchy, a custom
sort order, or dimension granularity based on a non-key attribute
(we focus upon these activities in other articles of this series). We might
also want to performance tune the default relationships to optimize processing
in general.

Relationships representing natural
hierarchies are enforced by creating an attribute relationship
between the attribute for a level and the attribute for
the level below it. For Analysis Services, this specifies a
natural relationship and potential aggregation. In the Customer
dimension of the sample Adventure Works UDM, a natural hierarchy exists
for the Country, State-Province, City, Postal Code,
and Customer attributes. The natural hierarchy for {Country, State-Province,
City, Postal Code, Customer} has been established through the addition of the
following attribute relationships:

The Country
attribute as an attribute relationship to the State-Province
attribute;

The State-Province
attribute as an attribute relationship to the City attribute;

The City
attribute as an attribute relationship to the Postal Code
attribute.

We will see construct
relationships within the UDM as part of the practice session that follows.

As we have noted in other articles
of this series, we can also create a user-defined hierarchy that does not
represent a natural hierarchy in the data (this is called an ad
hoc or reporting hierarchy), for purposes of navigating
data in the cube. For example, we could create a user-defined hierarchy
based on Customer {Education, Gender}. Information consumers of the data
would see no difference in how the two hierarchies behave, although the natural
hierarchy benefits from aggregating and indexing structures 
invisible to the consumer  that account for the natural relationships
in the source data.

The attribute relationship,
as we have learned, defines the possible associations that exist between
attributes within a given dimension. In doing so, the attribute
relationship affects virtually all functions of Analysis Services.
The attribute relationship defines the properties of association
that exist (including whether another attribute can be accessed via the
given attribute) between a given attribute and another attribute.
(A given attribute is treated as a member property of the
current attribute when it can be accessed via the current attribute
 hence the relatively common reference to a related attribute as a member
property in much of the documentation and other literature.)

We will gain hands - on exposure to attribute relationship properties and settingsin
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

In
Dimensional Model
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 the earlier articles of
this subseries)) by taking the following steps within the SQL Server
Business Intelligence Development Studio.

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.

1.
Click Start.

2.
Navigate to,
and click, the SQL
Server Business Intelligence Development Studio, as appropriate.

We
briefly see a splash page that lists the components installed on the PC, and
then Visual Studio .NET 2005 opens at the Start page.

6.
Ensuring that
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).

7.
Using the
selector just beneath, labeled Database, select ANSYS065_Basic AS DB,
as depicted in Illustration
2.

Illustration 2: Selecting the Basic Analysis Services Database ...

8.
Leaving other
settings on the dialog at default, click OK.

SQL
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 now get some hands-on exposure to attribute
relationships for attributes of a representative dimension within
our practice UDM.