This article concludes our overview of Attribute Relationships within an extended examination of the dimensional model of the integrated Microsoft Business Intelligence solution.
This article continues the overview of Attribute
Relationships in Analysis Services, which we
began in Introduction
to Attribute Relationships in MSSQL Server Analysis Services, and continued in Attribute
Relationships: Settings and Properties. Both this article
and its predecessor extend the examination of the dimensional model that
we began in Dimensional Model Components: Dimensions Parts I and
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 I through 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 to continue the detailed examination of the properties
underlying attribute relationships that we began in Attribute Relationships: Settings and Properties,
along with a review of the respective settings associated with each property,
based upon attributes of additional representative dimensions 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;
-
the rapid and
accurate return of query results;
-
slice and
dice query creation and modification;
-
an environment
wherein information consumers can pose questions quickly and easily, and
achieve rapid results datasets.
We
extended our examination of dimensions into several detailed articles.
These articles are comprised of Dimensional Model Components: Dimensions Parts I and
II, noting 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. (We noted that 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
I through V. We noted that attributes
help us to define with specificity what dimensions cannot define by
themselves. Moreover, attributes are collected within a database
dimension, where we can access them to help us to specify the
coordinates required to define cube space.
Throughout
the current subseries, I have emphasized that dimensions and dimension
attributes 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 I through V, we fixed our focus upon the properties
underlying them, based upon the examination of a representative attribute 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 in practice
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
followed with a discussion of the nature and uses of the attribute Key from
a technical perspective, including its purpose within a containing dimension
within Analysis Services.
In Attribute Member Keys
Pt I: Introduction and Simple Keys and Attribute Member
Keys Pt II: Composite Keys, we introduced the concepts of simple and composite
keys, narrowing our exploration in Part I
to the former, where we reviewed the Properties associated with a simple
key, based upon the examination of a representative dimension attribute
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 another
representative dimension attribute, Date 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, and shed some light on how
they 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 in Dimensional
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.
In 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, and 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. Finally, we
continued our exploration of attribute relationships in our previous
article, Attribute Relationships: Settings
and Properties, where we examined attribute relationships
in a more detailed manner, similar to previous articles within this subseries.
We performed a
detailed examination of the properties underlying attribute
relationships, along with a review of the respective settings associated
with each property. As a part of our exposure to the attribute
relationships within a representative dimension of our sample UDM,
we obtained hands-on practice in creating, modifying and deleting attribute
relationships within several dimension attributes.
We will continue our exploration of attribute
relationships in this article, where we will examine the attribute
relationships of additional representative dimensions in a detailed
manner, similar to the procedures we undertook in Attribute Relationships: Settings and Properties.
We will again concentrate in detail upon the properties and settings
that underlie these relationships.
Our continuing 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 continued
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 and modifying attribute relationships within the attributes
of several additional dimensions within our sample UDM.
Attribute Relationships: More Properties and Settings
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, you 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. For example, as we saw in Attribute Relationships: Settings and Properties,
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} was 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 establish additional
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 (which 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.)
We will gain hands - on exposure to attribute relationship properties and settings in
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.
3.
Close the Start
page, if desired.
4.
Select File
-> Open from the main menu.
5.
Click Analysis
Services Database ... from the cascading menu, as shown in Illustration 1.
Illustration 1: Opening the Analysis Services Database ...
The Connect
to Database dialog appears.
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 continue to get some hands-on
exposure to attribute relationships this time for attribute members
of multiple representative dimensions within our practice UDM.
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
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
Attribute Member
Keys Pt I: Introduction and Simple Keys, and in Attribute Member Keys Pt II:
Composite Keys
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
attribute relationships
here.
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
dimension);
-
Product Model
Lines hierarchy (Product
dimension);
-
Fiscal Time and Calendar Time
hierarchies (Time dimension);
-
Geography hierarchy (Geography
dimension).
Define Attribute Relationships for Attributes in the Sales Territory Hierarchy
We
will continue our practice, begun in Attribute Relationships: Settings and Properties, with attribute relationships
within the Sales Territory hierarchy in the steps that follow.
1.
Within the Solution
Explorer, right-click the Sales Territory
dimension (expand the Dimensions folder as necessary).
2.
Click Open
on the context menu that appears.
The
tabs of the Dimension Designer open.
3.
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
Illustration 3.

Illustration
3: The Member Attributes, Sales Territory Dimension
We
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.
Illustration
4: Hierarchies and Levels Pane, Sales Territory Dimension
4.
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.
Illustration
5: Original Attribute Relationships Established in the Sales Territory
Dimension
5.
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.
The RelationshipType
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
organization.
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:
-
One (One
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
one relationship.
-
Many (One
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
one relationships.
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
relationships.
Define Attribute Relationships for Attributes in the Product Model Lines Hierarchy
We
will next continue our practice with attribute relationships within the Product
Model Lines hierarchy of the Product dimension.
1.
Within the Solution
Explorer, right-click the Product dimension.
2.
Click Open
on the context menu that appears, once again.
The
tabs of the Dimension Designer open.
3.
Click the Dimension
Structure tab, if we have not already arrived there by default.
The attributes belonging to the Product dimension appear as shown in
Illustration 7.
Illustration
7: The Member Attributes, Product Dimension
We
note that eighteen attributes appear within the Attributes pane.
We will gain 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 Product
Model Lines user hierarchy. This hierarchy currently exists, again, simply
as a drill down path for information consumers, and appears as depicted in Illustration 8.
Illustration
8: Hierarchies and Levels Pane, Customer Dimension
4.
In the Attributes
pane, expand the Model Name attribute and the Product Name
attribute.
Once these attributes are expanded, we see seventeen attribute
relationships established within the Product Name attribute and
no attribute relationships established within the Model Name attribute,
as shown in
Illustration 9.
Illustration
9: Original Attribute Relationships Established in the Product Dimension
5.
Drag the Product
Line attribute relationship from the Product Name attribute
to the <new attribute relationship> placeholder for the Model
Name attribute.
Once we have made our modification, the established attribute
relationships appear as depicted in Illustration 10.
Illustration 10:
Attribute Relationships after Our Modification
Product Line is now related to Model Name.
The RelationshipType property for this relationship should be set to Flexible
(the default) because the relationship between a product line and a model
name might change over time.
Next, we will move to the
Fiscal Time hierarchy, where we will define further attribute
relationships.
Define Attribute Relationships for Attributes in the Fiscal Time Hierarchy
We
will next continue our practice with attribute relationships within the Fiscal
Time hierarchy of the Time dimension.
1.
Within the Solution
Explorer, right-click the Time dimension.
2.
Click Open
on the context menu that appears, once again.
The
tabs of the Dimension Designer open.
3.
Click the Dimension
Structure tab, if we have not already arrived there by default.
The attributes belonging to the Time dimension appear as shown in
Illustration 11.
Illustration
11: The Member Attributes, Product Dimension
We
note that eight attributes appear within the Attributes pane. We
will gain 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, two hierarchies,
named Calendar Time and Fiscal Time, as depicted in Illustration 12.
Illustration
12: Hierarchies and Levels Pane, Time Dimension
We will be concerned with the Fiscal Time hierarchy in
this section (we focus on the Calendar Time hierarchy in our next
section). There are five levels in the Fiscal Time user hierarchy. This
hierarchy currently exists, once again, as a drill down path for information
consumers.
4.
In the Attributes
pane, expand the following attributes:
-
Date
-
Fiscal
Quarter
-
Fiscal
Semester
-
EnglishMonthName
Once these attributes are expanded, we see seven attribute
relationships established within the Date
attribute and no attribute relationships established within the Fiscal
Quarter, Fiscal Semester, and Month Name attributes, as shown in Illustration 13.
Illustration
13: Original Attribute Relationships Established in the Time Dimension
Fiscal Time Hierarchy
5.
Drag the Fiscal
Quarter attribute relationship from the Date attribute
to the <new attribute relationship> placeholder for the Month
Name attribute.
6.
Within the Properties
window, set the value for the RelationshipType property for the
relocated Fiscal Quarter attribute relationship to Rigid,
as depicted in Illustration 14.
Illustration 14: Set the
RelationshipType for the Relationship to Rigid ...
7.
Drag the Fiscal
Semester attribute relationship from the Date attribute
to the <new attribute relationship> placeholder for the Fiscal
Quarter attribute.
8.
Within the Properties
window, set the value for the RelationshipType property for the
relocated Fiscal Semester attribute relationship to Rigid.
9.
Drag the Fiscal
Year attribute relationship from the Date attribute to
the <new attribute relationship> placeholder for the Fiscal
Semester attribute.
10.
Within the Properties
window, set the value for the RelationshipType property for the
relocated Fiscal Year attribute relationship to Rigid.
Once we have made our modifications, the attribute
relationships established appear as shown in Illustration 15.
Illustration 15:
Attribute Relationships after Our Modifications
Fiscal Quarter is now related to Month Name,
Fiscal Semester is now related to Fiscal Quarter, and Fiscal
Year is now related to Fiscal Semester. The Rigid RelationshipType
property is appropriate for these attribute relationships, because they
will not change over time.
Next, we will move to the
Calendar Time hierarchy, where we will define a similar set of attribute
relationships.
Define Attribute Relationships for Attributes in the Calendar Time Hierarchy
We
will next work with attribute relationships within the Calendar Time hierarchy
of the Time dimension. Because we are already within the Time
dimension, we can get directly to work. There are five levels in the Calendar
Time user hierarchy, as depicted, once again in Illustration 16.

Illustration
16: Hierarchies and Levels Pane, Time Dimension
1.
Collapse the attributes
we expanded in the last section (those relating to the Fiscal Time hierarchy)
2.
In the Attributes
pane, expand the following attributes:
-
Calendar
Quarter
-
Calendar
Semester
-
Date
-
Month Name
Once these attributes are expanded, we see four attribute
relationships remaining within the Date attribute no attribute
relationships established within the Calendar Quarter and Calendar
Semester attributes, and one attribute relationships established
within the Month Name attribute, as shown in Illustration 17.
Illustration
17: Attribute Relationships Established in the Time Dimension Calendar
Hierarchy
3.
Drag the Calendar
Quarter attribute relationship from the Date attribute
to the <new attribute relationship> placeholder for the Month
Name attribute.
4.
Within the Properties
window, set the value for the RelationshipType property for the
relocated Calendar Quarter attribute relationship to Rigid.
5.
Drag the Calendar
Semester attribute relationship from the Date attribute
to the <new attribute relationship> placeholder for the Calendar
Quarter attribute.
6.
Within the Properties
window, set the value for the RelationshipType property for the
relocated Calendar Semester attribute relationship to Rigid.
7.
Drag the Calendar
Year attribute relationship from the Date attribute to
the <new attribute relationship> placeholder for the Calendar
Semester attribute.
8.
Within the Properties
window, set the value for the RelationshipType property for the
relocated Calendar Year attribute relationship to Rigid.
Once we have made our modifications, the attribute
relationships established appear as depicted in Illustration 18.
Illustration 18:
Attribute Relationships after Our Modifications
Calendar Quarter is now related to Month Name,
Calendar Semester is now related to Calendar Quarter, and Calendar
Year is now related to Calendar Semester. The Rigid RelationshipType
property is appropriate for these attribute relationships, because, once
again, they will not change over time.
Finally, we will move to
the Geography hierarchy, where we will a final set of attribute
relationships.
Define Attribute Relationships for Attributes in the Geography Hierarchy
We
will conclude our practice with attribute relationships within the Geography
hierarchy of the Geography dimension.
1.
Within the Solution
Explorer, right-click the Geography dimension.
2.
Click Open
on the context menu that appears, once again.
The
tabs of the Dimension Designer open.
3.
Click the Dimension
Structure tab, if we have not already arrived there by default.
The attributes belonging to the Geography dimension appear as shown in
Illustration 19.
Illustration
19: The Member Attributes, Geography Dimension
We
note that five attributes appear within the Attributes pane. We
will gain 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, four levels in the Geography
user hierarchy. This hierarchy exists, like the others we have examined in
this practice session, as a drill down path for information consumers, and
appears as depicted in
Illustration 20.
Illustration
20: Hierarchies and Levels Pane, Geography Dimension
4.
In the Attributes
pane, expand the following attributes:
-
City
-
Geography
Key
-
Postal Code
-
State-Province
Once these attributes are expanded, we see four attribute
relationships currently established within the Geography Key attribute,
with no attribute relationships established within the City, Postal
Code and State Province attributes, as shown in Illustration 21.
Illustration
21: Attribute Relationships Established in the Geography Dimension
5.
Drag the City
attribute relationship from the Geography Key attribute to
the <new attribute relationship> placeholder for the Postal
Code attribute.
Because postal codes
within a city have been known to change over time, the appropriate value for
the RelationshipType property for the City attribute
relationship is Flexible, which is the default setting that we can
see in the associated Properties window.
6.
Drag the State-Province
attribute relationship from the Geography Key attribute to
the <new attribute relationship> placeholder for the City attribute.
7.
Within the Properties
window, set the value for the RelationshipType property for the
relocated State-Province attribute relationship to Rigid.
The Rigid RelationshipType
property is appropriate for the State-Province attribute relationship
because the relationship between a given city and the state within which it is
physically located will not change at least not in a foreseeable way.
8.
Drag the Country-Region
attribute relationship from the Geography Key attribute
to the <new attribute relationship> placeholder for the State-Province
attribute.
9.
Within the Properties
window, set the value for the RelationshipType property for the
relocated Country-Region attribute relationship to Rigid.
The Rigid RelationshipType
property is appropriate for the Country-Region attribute relationship
because the relationship between a given state and the country within which it
is physically located will not change again, at least not in a foreseeable
way.
Once we have made our modifications, the attribute
relationships established within the Geography dimension appear
as depicted in
Illustration 22.
Illustration 22:
Attribute Relationships after Our Modifications
Postal Code remains related to Geography
Key, City is now related to Postal Code, State-Province
is now related to City, and Country-Region is now related to State-Province.
We have established the RelationshipType property as Rigid for
all except Postal Code and City, among these attribute
relationships, as well.
This will conclude our
work with attribute relationships. We encounter these important
relationships within other articles of my Introduction to MSSQL Server Analysis
Services column, where we make both typical
and special settings to meet specific business needs within the context of the
focus of each article.
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.
10.
Select File
-> 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.
11.
Click Yes when
prompted, via the Visual Studio message box that appears next, to reprocess
the affected cube objects.
12.
Click Run
on the Process Object(s) dialog box that appears next, as appropriate.
Processing
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.
13.
Click Close
to dismiss the Process Progress viewer.
14.
Click Close
to dismiss the Process Object(s) dialog box.
15.
Select File
-> Exit to leave the design environment,
when ready, and to close the Business Intelligence Development Studio.
Conclusion
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 and the practice session we began in Attribute Relationships: Settings and Properties,
through a continued detailed examination of attribute relationships. Our
concentration upon these details was enhanced by our continuing hands-on
practice session, where we once again 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,
and continued in Attribute Relationships:
Settings and Properties. We performed more detailed examination
of the properties underlying attribute relationships, along with
a review of the respective settings associated with each property, based
upon additional representative dimension attributes within our sample UDM,
as a part of our practice session. Throughout our practice procedures we
obtained hands-on exposure to creating and modifying attribute relationships
within several representative dimensions within our sample UDM.
About the MSSQL Server Analysis Services Series
This
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
series.
»
See All Articles by Columnist William E. Pearson, III