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
» 