Procedure: Define Attribute Relationships and Examine Attribute Relationship Property Settings in Analysis
In the practice procedures that
follow, we will select and examine a representative dimension within the
sample cube, and then focus upon the attribute relationship property
settings that reference select dimension attributes. We will perform
our practice sessions within the SQL Server Business Intelligence
Development Studio, from which we will examine the attribute
relationship property settings 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. In
Keys Pt I: Introduction and Simple Keys, and in Attribute Member Keys Pt II:
we focused upon those properties for a simple attribute key and a composite
attribute key, respectively. Just as we did for the respective subject
matter objects in those articles, we will examine the detailed settings for representative
As I noted earlier, we can
organize attribute hierarchies into levels within user
hierarchies to provide navigation paths for users in a cube. A user
hierarchy can represent a natural hierarchy, such as city, state,
and country, (as we shall see in our practice session) or can simply
represent a navigation path that fits a local business scenario, such as employee
name, title, and department name. Moreover, as we also
mentioned earlier, to the information consumer navigating a hierarchy,
these two types of user hierarchies are identical.
As a part of my
discussion in Introduction to Attribute Relationships in MSSQL Server
Analysis Services, I stated that, with a natural hierarchy, if we
define attribute relationships between the attributes that make
up the levels, Analysis Services can use an aggregation
from one attribute to obtain the results from a related attribute.
If there are no defined relationships between attributes, Analysis
Services will aggregate all non-key attributes from the key
Lets get some hands-on exposure with defining
attribute relationships for the attributes in a natural user
hierarchy that exists within our basic UDM. Within our practice session we
will work within the Customer Geography (natural) hierarchy of the Customer
Define Attribute Relationships for Attributes in the Customer Geography Hierarchy
will begin our practice with attribute relationships within the Customer
Geography hierarchy of the Customer dimension.
Within the Solution
Explorer, right-click the Customer dimension (expand the Dimensions
folder as necessary).
on the context menu that appears, as shown in Illustration 3.
Illustration 3: 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.
The attributes belonging to the Customer dimension appear as depicted in
Illustration 4: The Member Attributes, Customer Dimension
note that twenty attributes appear within the Attributes pane. We
will get some exposure to attribute relationships, by adding / examining representative relationships
among the attributes we see here.
We can also see, within the Hierarchies and Levels pane, four levels in the Customer
Geography user hierarchy. This hierarchy currently exists simply as a drill
down path for information consumers, and appears as shown in Illustration 5.
Illustration 5: Hierarchies and Levels Pane, Customer Dimension
In the Attributes
pane, expand the Geography attribute by clicking the + sign to
its immediate left.
We note that four member properties link the non-key
attributes from the Geography table to the key attribute from
the Geography table, as depicted in Illustration 6.
Illustration 6: Member Properties Linking Non-Key Attributes to Key Attribute
In the Attributes
pane, expand the Full Name attribute.
We see that the Geography
attribute is related to the Full Name attribute. We also note that
the Postal Code attribute is indirectly related to the Full
Name attribute through the Geography attribute, because Postal
Code is linked to the Geography attribute and the Geography attribute
is linked to the Full Name attribute. These relationships are
circled in Illustration 7.
Illustration 7: Initial Indirect Relationship between the Geography and Full Name Attributes
Drag the Postal
Code attribute from the Geography attribute to the <new
attribute relationship> placeholder for the Full Name attribute,
as shown in Illustration 8.
Illustration 8: Making the Postal Code Attribute Directly Related to the Full Name Attribute
The Postal Code attribute
is now directly related to the Full Name attribute.
In the Properties
window (which appears for the highlighted Postal Code attribute, by
default in the right bottom corner of the design environment), we can observe that
the RelationshipType property for this attribute is set to Flexible.
This is appropriate because the relationship between a customer and a
postal code may change over time.
property defines rules for the modification of the key value of the
members of the related, dependent attribute (in our example, the Full
Name attribute is the current attribute, whereas the Postal Code
attribute is the related attribute). When we define an attribute
relationship, we use the RelationshipType property to specify that
the relationship is one of the following types:
Rigid: The key values of the
related attribute and the current attribute have a fixed association, and
cannot change without a full reprocessing of the dimension.
Flexible: The key of the related, dependent
attribute, and therefore the entire member of the dependent attribute,
can be changed anytime. In our example above, the Postal Code attribute
is dependent upon the Full Name (Customer) attribute with
a Flexible relationship, since the postal code can change anytime a
customer moves to another location.
If we define a
relationship as Rigid, Analysis Services retains aggregations
when the dimension is updated. If a relationship that is defined as Rigid
actually changes, Analysis Services generates an error during processing
unless the dimension is fully processed. Specifying the appropriate relationships
and relationship properties increases query and processing performance,
as we noted in Introduction
to Attribute Relationships in MSSQL Server Analysis Services.
We noted that the RelationshipType
property in our example is set to Flexible: The key of the
related, dependent attribute, and therefore the entire member of the
dependent attribute, can be changed anytime. In our example above, the Postal
Code attribute is dependent upon the Full Name (Customer) attribute
with a Flexible relationship, since the postal code can change anytime a
While we will not make
modifications here, we also see that the Cardinality setting for the Postal
Code attribute relationship is set to Many. Cardinality
defines the nature of the relationship of the key of related attributes
(and their members) when those members are used as member properties of
the current attribute (and its members). The Cardinality setting
can have one of two possible values:
to One): One,
and only one, member of the current attribute is associated with each
member of the related attribute. For example, if we were associating
the full names of customers with a social security, or other unique
identifying code (this attribute does not exist in the example UDM
I am only using it as an illustration here), we would have a one to
to Many): A
given member of a related attribute can be associated with multiple members of
the current attribute. Needless to say, one to many
relationships occur far more often in Analysis Services than one to
Finally, we can see that
the Visible setting for the Postal Code attribute relationship is
set to True. The Visible setting specifies whether the
related attribute is accessible, as a member property of the current member, to
the information consumer. The Visible setting
can have either of two possible values:
related attribute is not visible to the information consumer, and
therefore cannot be used as a member property of the current member.
related attribute is visible to, and can be accessed by, the information
consumer as a member property of the current member.
For purposes of our
immediate example, we will leave the Visible property as its current
setting of True. The Properties window for the Postal Code
attribute relationship appears as depicted in Illustration 9.
Illustration 9: Properties Window for the Postal Code Attribute Relationship
In the Attributes
pane, expand the Postal Code attribute.
The City attribute
is currently related to the Postal Code attribute through the Geography
attribute, rather than being directly related. We will next directly
relate the City attribute to the Postal Code attribute.
Drag the City
attribute relationship from the Geography attribute to the <new
attribute relationship> placeholder for the Postal Code attribute.
The City attribute is
now directly related to the Postal Code attribute. In the Properties window,
we note, as we did for the Postal Code attribute earlier, that the RelationshipType
property for this attribute is set to Flexible. This is
appropriate because the relationship between a city and a postal code may
change over time.
In the Attributes
pane, expand City.
State-Province attribute is currently (indirectly) related to the City
attribute through the Full Name and Geography attributes,
hence we do not presently see the State-Province attribute under City.
10. Drag the State-Province Name
attribute relationship from the Geography attribute to the
<new attribute relationship> placeholder for the City
The value of the RelationshipType
property for the State-Province attribute relationship should be set to Rigid
because the relationship between a city and a state does not change over time.
11. With the newly placed State Province Name attribute highlighted,
change the value of the RelationshipType property for State-Province
attribute relationship, from its default setting of Flexible, to Rigid,
in the Properties window, as shown in Illustration 10.
Illustration 10: Modify the RelationshipType Property to Rigid
12. In the Attributes pane,
expand the State-Province attribute.
13. Drag the Country-Region attribute
relationship from the Geography attribute to the <new
attribute relationship> placeholder for the State-Province
The value of the RelationshipType
property of the Country Region attribute relationship should
be set to Rigid because the relationship between a state-province and a
country-region does not change over time.
14. With the newly placed Country
Region attribute highlighted, change the value of the RelationshipType
property for Country Region attribute relationship, from
its default setting of Flexible to Rigid in the Properties
Because we have, in the
foregoing steps, moved all the attribute relationships from the Geography
attribute to other attributes, instead of creating new attribute
relationships for each of those attributes, there is no reason for
retaining the now-empty Geography attribute.
As we discussed in Introduction to Attribute Relationships in MSSQL
Server Analysis Services, we generally assist in aggregation design
and improve query performance by defining the most direct relationships
within our models.
15. In the Attributes pane, right-click
the Geography attribute.
16. Select Delete from the
context menu that appears, as depicted in Illustration 11.
Illustration 11: Delete the Now-empty Geography Attribute
This will conclude the
first half of our work with attribute relationships. We will continue
our practice with these important relationships within the next article of this
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 other articles of this subseries.
Click Yes when
prompted, via the Visual Studio message box that appears next, to reprocess
the affected cube objects.
on the Process Object(s) dialog box that appears next, as shown in Illustration
Illustration 12: Click Run to Process Affected Objects ...
begins, and we see completion of the various steps via the Process Progress viewer.
We see a Process Succeeded message in the Status bar at the
bottom of the viewer, once processing is complete, as depicted in Illustration
Illustration 13: Process Succeeded
to dismiss the Process Progress viewer.
to dismiss the Process Object(s) dialog box.
-> Exit to leave the design environment,
when ready, and to close the Business Intelligence Development Studio.
In this article, we continued our exploration of attribute
relationships, stating that our objective would be to complement the
introduction we undertook in Introduction to Attribute Relationships in MSSQL Server
Analysis Services, through a more detailed examination of attribute
relationships. Our concentration upon these details was enhanced by a
hands-on practice session, where we gained exposure to the properties
and settings that underlie attribute relationships.
Our examination included a review of the nature of the attribute
relationship in Analysis Services, 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.
We performed 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, as a part of our practice session. (We stated
that we would gain further hands-on practice, working with attribute
relationships within additional dimensions, in the article that follows
this one.) Throughout our practice procedures we obtained hands-on exposure to
creating, modifying and deleting attribute relationships within a select
dimension attribute within our sample UDM.
About the MSSQL Server Analysis Services 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
See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.