Procedure: Continue to Define Attribute Relationships and Examine Attribute Relationship Property Settings in
Analysis Services 2005
In the practice procedures that follow,
we will select and examine select attributes of multiple representative dimensions
within the sample cube, and then focus upon the attribute relationship property
settings that define and support the selected 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 from within our Analysis Services database, ANSYS065_Basic
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 attribute.
Lets get further hands-on exposure with
defining attribute relationships for the attributes in the natural
user hierarchies that exist within our basic UDM. Within our practice
session we will work within the following (natural) hierarchies:
Sales Territory hierarchy (Sales Territory
Lines hierarchy (Product
Fiscal Time and Calendar Time
hierarchies (Time dimension);
Geography hierarchy (Geography
Define Attribute Relationships for Attributes in the Sales Territory Hierarchy
will continue our practice, begun in Attribute Relationships: Settings and Properties, with attribute relationships
within the Sales Territory hierarchy in the steps that follow.
Within the Solution
Explorer, right-click the Sales Territory
dimension (expand the Dimensions folder as necessary).
on the context menu that appears.
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 Sales Territory dimension appear as shown in
3: The Member Attributes, Sales Territory Dimension
note that three attributes appear within the Attributes pane. We
will get further 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, three levels in the Sales Territory user hierarchy. This hierarchy
currently exists as a drill down path for information consumers, and appears as
depicted in Illustration
4: Hierarchies and Levels Pane, Sales Territory Dimension
In the Attributes
pane, expand the Sales Territory Country attribute and the Sales Territory Region attribute.
Once these attributes are expanded, we see two attribute
relationships established within the Sales Territory Region attribute and no attribute relationships established
within the Sales Territory Country attribute, as shown in Illustration 5.
5: Original Attribute Relationships Established in the Sales Territory
Drag the Sales
Territory Group attribute relationship from the Sales Territory Region
attribute to the <new attribute relationship> placeholder for the Sales
Territory Country attribute.
Once we have made our modification, the attribute
relationships established appear as depicted in Illustration 6.
Illustration 6: Attribute
Relationships after Our Modification
Sales Territory Group is now related to Sales
Territory Country, while Sales Territory Country remains related to
Sales Territory Region. The RelationshipType property for each of
these relationships should be set to Flexible (their defaults) because
the groupings of regions within a country might change over time and because
the groupings of countries into groups might change over time, as well.
With the Sales
Territory Group attribute relationship highlighted, we can observe that, in
the Properties window (which appears for the highlighted Sales
Territory Group attribute, by default in the right bottom corner of
the design environment), the RelationshipType property for this
attribute is set to Flexible. This is appropriate because the relationship
between a Sales Territory Country and a Sales Territory Group may
change over time.
property, as we noted in Attribute Relationships: Settings and Properties,
defines rules for the modification of the key value of the members of
the related, dependent attribute (in our example, the Sales Territory
Country is the current attribute, whereas the Sales Territory
Group is the related attribute). When we define an attribute
relationship, we use the RelationshipType property to specify that
the relationship is one of two types: Rigid or Flexible.
In Attribute Relationships:
Settings and Properties, we discussed the fact that, 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 and Attribute
Relationships: Settings and Properties.
We note that the RelationshipType
property setting 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 Sales Territory Group is dependent upon
the Sales Territory Country attribute with a Flexible
relationship, since the territory group can
change anytime countries are moved among territory groups within the
While we will not make
modifications here, we also see that the Cardinality setting for the Sales
Territory Group 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). As we noted in Attribute Relationships: Settings and Properties,
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 Sales Territory Group 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:
False: The related attribute is not
visible to the information consumer, and therefore cannot be used as a member
property of the current attribute member;
True: The related attribute is
visible to, and can be accessed by, the information consumer as a member
property of the current attribute member.
For purposes of our
immediate example, we will leave the Visible property as its current
setting of True.
Next, we will move to another
dimensional hierarchy, where we will define further attribute