Attribute Member Keys - Pt 1: Introduction and Simple Keys - Page 2

An Introduction to Attribute Member Keys

As we have learned, attributes serve as the foundation for our dimensions and cubes. Typically based upon a single column, or named calculation, within the associated, underlying dimension table, each attribute 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.

  The attribute member key for a representative dimension, the Geography dimension within the AdventureWorks sample cube, appears as shown in Illustration 1.


Illustration 1: A Representative Key Attribute ...

Our focus within this article will be the attribute member key. The attribute member key is critical to the identification of unique attribute members within Analysis Services. The key, as we shall see, 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.)

The members of an attribute in Analysis Services can have one of two types of keys: a simple key or a composite key. In this, the first half of this article, we will consider the characteristics and properties of a simple key. In Part II, we will consider the characteristics and properties of a composite key. A simple key can be of any data type allowed within an Analysis Services database. It must, of course, be unique, and is defined by a single value.

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 UDM 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 Dimensional Model Components: Dimensions Part I and Dimensional Attributes: Introduction and Overview Parts I through V) by taking the following steps within the SQL Server Business Intelligence Development Studio,.

NOTE: Please access the UDM which we prepared in Dimensional Model Components: Dimensions Part I before proceeding with this article. If you have not completed the preparation to which I refer in the previous article, or if you cannot locate / access the Analysis Services database with which we worked there, 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 depicted in Illustration 1.


Illustration 2: Opening the Analysis Services Database ...

The Connect to Database dialog appears.

6.  Ensuring that the Connect to existing database radio button is selected, type the Analysis Server name into the Server input box atop the dialog.

7.  Using the selector just beneath, labeled Database, select ANSYS065_Basic AS DB, as shown in Illustration 3.


Illustration 3: Selecting the New 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 properties for an example attribute member key, from within our sample UDM.