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.
» 