Attribute Member Keys - Pt II: Composite Keys - Page 2

September 19, 2008

An Introduction to Attribute Member Keys ... from the Perspective of the Composite Key

As we have learned, attributes serve as the foundation for our dimensions and cubes. To review, we discovered in Part I that each attribute, typically based upon a single column (or a named calculation) within the associated, underlying dimension table, falls into one of three possible usage roles. Depending upon the attribute’s Usage property setting, the three usage types consist of the following:

• Regular
  
  An attribute that belongs to neither the Parent nor Key roles, a Regular attribute is used to support our dimensions with additional “adjectives.” That is, the regular attribute allows us to associate additional information with the dimension to support analysis of characteristics we deem important within our respective analytical environments. (We address Regular attributes throughout my Introduction to MSSQL Server Analysis Services series.)
• Parent
  
  A Parent attribute is used to support the recursive, parent-child relationships among the members of a dimension requiring such support. Each dimension is limited to only one attribute of this usage type. (We address parent-child dimensions within other articles of my Introduction to MSSQL Server Analysis Services series.)
• Key
  
  Every dimension contains a single Key attribute. The attribute member key serves as the link that associates its containing dimension to a given measure group. Throughout the Analysis Services documentation, as well as within numerous books and periodicals based upon the subject matter, the attribute key is likened to the primary key within a relational table: a relationship, similar to a join within the relational world (relating two tables), is established between the dimension and measure group(s) through the presence of the attribute key.

An attribute member key for a representative dimension, the Sales Summary Order Details dimension within the AdventureWorks sample cube, appears as shown in Illustration 1.

Our focus within this article will be the attribute member key, just as it was in Part I (albeit this time from the perspective of a composite key, versus a simple key). As we noted there, the attribute member key is critical to the identification of unique attribute members within Analysis Services. The key, as we shall see again, is specified within the KeyColumns setting, within the Source group of a dimension’s Attribute properties. (We overviewed the Source properties in my Database Journal article Dimension Attributes: Introduction and Overview, Part V.)

As we noted in Part I, the members of an attribute in Analysis Services can have one of two types of keys: a simple key or a composite key. In Part I, we considered the characteristics and properties of a simple key. In this, the second half of this article, we will consider the characteristics and properties of a composite key. We learned in Part I that a simple key can be of any data type allowed within an Analysis Services database. It must, we noted, be unique, and is defined by a single value. While the composite key is similar to a simple key in that it must be unique for each attribute member, the composite key differs in that it is defined by a combination of values which can be of varying data types.

Composite keys become necessary in scenarios where a simple key alone is insufficient to identify uniqueness. A commonly cited example would be a City attribute within, say, a Customer dimension (a situation that is found within the Adventure Works sample Analysis Services database) for an organization that serves customers all over the world. Reducing such a scenario to the Adventure Works model, it becomes easy to see why it would be impossible to uniquely identify a City via a simple key based upon the City column of the DimGeography table in the underlying Adventure Works DW data source. The reason that this is true is that many Cities, existing it different states, provinces, and countries, have the same names.

We can address this “absence of uniqueness” by employing a composite key. The composite key is composed of a combination of column values that, together, provide a unique key. As an illustration, the example City attribute that I have cited from within the Adventure Works sample database is composed of the City and StateProvinceCode columns of the DimGeography table, which is shown within the KeyColumns property of the Source properties group for the City dimension attribute as depicted in Illustration 2.

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.