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 attributes Usage property setting, the three usage types consist of the following:
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 dimensions 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.