Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Dec 23, 2008

Introduction to Attribute Relationships in MSSQL Server Analysis Services

By William Pearson

This article introduces Attribute Relationships in Analysis Services, and extends the examination of the dimensional model that we began in Dimensional Model Components: Dimensions Parts I and II. Here, we will focus upon the purpose of attribute relationships, which define the possible associations between attributes. Moreover, we will discuss why these relationships are important, and how they allow us to define the properties of association that a given attribute has with other attributes.

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.


In Dimensional Model Components: Dimensions Parts I and II, we undertook a general introduction to the dimensional model, noting its wide acceptance as the preferred structure for presenting quantitative and other organizational data to information consumers. We then began our 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 learned, in Dimensional Model Components: Dimensions Parts I and II, that dimensions form the foundation of the dimensional model. They represent the perspectives of a business or other operation, and reflect the intuitive ways that information consumers need to query and view data. We noted that we might consider dimensions as nouns that take part in, are acted upon by, or are otherwise associated with, the verbs (or actions / transactions undertaken by the business) that are represented by the facts or measures contained within our business intelligence systems.

We discovered in earlier articles that, within the Analysis Services model, database dimensions underlie all other dimensions, whose added properties distinguish them from the database dimensions they reference, within the model. Each dimension within our model contains one or more hierarchies. As 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. For purposes of this article, the term “attribute” means the same thing as “attribute hierarchy”. (We examine user hierarchies, to which we will simply refer as “hierarchies,” in other articles specifically devoted to the topic.)

To extend the metaphor we used earlier in describing dimensions as nouns and measures as verbs, we might consider attributes as somewhat similar to adjectives. That is, attributes help us to define with specificity what dimensions cannot define by themselves. Dimensions alone are like lines in geometry: they don't define “area” within multidimensional space, nor do they themselves even define the hierarchies that they contain. A database dimension is a collection of related objects called attributes, which we use to specify the coordinates required to define cube space.

Within the table underlying a given dimension (assuming a more-or-less typical star schema database) are individual rows supporting each of the members of the associated dimension. Each row contains the set of attributes that identify, describe, and otherwise define and classify the member upon whose row they reside. For instance, a member of the Patient dimension, within an Analysis Services implementation for a healthcare provider, might contain information such as patient name, patient ID, gender, age group, race, and other attributes. Some of these attributes might relate to each other hierarchically, and, as we shall see in other articles of this subseries (as well as within other of my articles), multiple hierarchies of this sort are common in real-world dimensions.

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.

As a part of our exploration of attribute member Keys, 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, Geography, 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 a representative dimension attribute, Date, also 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 this article, we will examine another part of the conceptual model, Attribute Relationships. In this introduction, we will discuss several best practices and design, and other, considerations involved in their use. Our focus will be upon the general exploitation of attribute relationships in providing support for the selection and delivery of enterprise data. (In our next article, we will examine attribute relationships in a manner similar to previous articles within this subseries, concentrating in detail upon the properties that underlie them.)

Our examination will include:

  • A review of the nature of an attribute relationship, its purpose within its containing dimension within Analysis Services, and its role in helping to meet the primary objectives of business intelligence.
  • A general overview of attribute relationships, and a discussion of their default configurations, and how we can improve upon those configurations to enhance overall processing performance.
  • A discussion of other best practices and general considerations surrounding attribute relationships.
  • A look forward to the article that follows within our series, where we will perform 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.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM