Attribute Member Keys - Pt 1: Introduction and Simple Keys

Join Business Intelligence Architect Bill Pearson as he kicks off an exploration of Attribute Member Keys, a continuation of a body of articles surrounding significant components of the Analysis Services dimensional model. In this article we introduce Attribute Member Keys, focusing upon the simple keys and their properties.

Synopsis: Join Business Intelligence Architect Bill Pearson as he kicks off an exploration of Attribute Member Keys, a continuation of a body of articles surrounding significant components of the Analysis Services dimensional model. In this article we introduce Attribute Member Keys, focusing upon the simple keys and their properties.

About the Series ...

This article is a member of the series Introduction to MSSQL Server Analysis Services. The series is designed to provide hands-on application of the fundamentals of MS SQL Server Analysis Services (“Analysis Services”), with each installment progressively presenting features and techniques designed to meet specific real-world needs. For more information on the series, please see my initial article, Creating Our First Cube. For the software components, samples and tools needed to complete the hands-on portions of this article, see Usage-Based Optimization in Analysis Services 2005, another article within this series.

Introduction

In Dimensional Model Components: Dimensions Parts I and II, we introduced the dimensional model in general, 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, 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 the earlier two 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 will examine user hierarchies, to which we will simply refer as “hierarchies,” in a subsequent article.)

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 the 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 subsequent 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 its business operations. 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. In this article, we will extend our examination of attributes to yet another important component we have touched upon earlier, the attribute member key, with which we will get some hands-on exposure in the practice session below. 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.

In this, Part I of a two-part article, we will gain an introduction to attribute member keys, with hands-on exposure within a sample cube. Our examination will include:

• A discussion of the three attribute usage types that we can define within each containing dimension.
• A focus upon the Key attribute usage type (within both Parts I and II of this article).
• An introduction to the attribute member key and a discussion of its role in meeting the primary objectives of business intelligence.
• A discussion of the role of the attribute member key from a technical perspective, including its purpose within its containing dimension within Analysis Services.
• A discussion of the role of the attribute member key from a technical perspective.
• An introduction to the concepts of simple and composite keys.
• A review of the Properties associated with a simple key, based upon the examination of a representative dimension attribute within our sample UDM.
• A look ahead to Part II of this article, where we explore the Properties associated with a composite key.

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.

Procedure: Examine Key Attribute Properties and Characteristics in Analysis Services 2005

In the practice procedures that follow, we will select and examine a representative key attribute within the sample cube, focusing upon the properties that define and support a representative attribute. We will perform our practice sessions within the SQL Server Business Intelligence Development Studio, from which we will perform our examination of attribute properties within our Analysis Services database, ANSYS065_Basic AS DB.

In Dimensional Model Components: Dimensions Part I and II, and Dimensional Attributes: Introduction and Overview Parts I through V, respectively, we overviewed the properties underpinning Database and Cube dimensions, and then examined the properties supporting dimension attributes. Just as we did in those articles, we will examine the detailed settings for a representative attribute member key here. To access these settings for the attribute member key within a representative dimension, we will need to open that dimension within the Dimension Designer first.

The tabs of the Dimension Designer open.

The attributes belonging to the Geography dimension appear as shown in Illustration 5.

We note that five attributes appear within the Attributes pane. Let's get some exposure to the properties associated with attributes by examining a representative member among the attributes we see here.

Review Key Attribute Properties

As we discovered in Dimensional Attributes: Introduction and Overview Part V, within the Source properties of every attribute lays the KeyColumns property. Let’s examine the property and the underlying KeyColumns collection for the Geography attribute key, which represents a simple key within the sample Analysis Services database, by taking the following steps.

The Properties pane appears for the Geography Key attribute. (The Properties pane likely appeared when we selected the Product dimension within the Dimensions pane, by default, below the Solution Explorer. The design environment can, of course, be customized in many ways to accommodate your local environment and development needs.)

We can, at this stage, see the thirty DimensionAttribute properties for the Geography Key attribute within the Properties pane, arranged into five properties groups, which we examined in detail within the earlier articles we have cited.

The expanded Source properties group of the Properties pane for the Geography attribute key appears as depicted in Illustration 8.

Let's take a look at each of the individual properties (and “subproperties”), as relevant to a simple key, discussing the purpose of the property, and examining possible settings with which we can come into contact. (We will examine these settings for a composite key in Part II.) In most attributes, we find that only the KeyColumns property is relevant, although NameColumn and ValueColumn can certainly offer opportunities for employment, as we see in other articles of this series. We will skip the CustomRollupColumn and CustomRollupPropertiesColumn properties for this reason – both are set to “(none)” in the case of our example, the Geography attribute key.

Source Property: KeyColumns

The value we select for the KeyColumns property specifies a column or columns within the underlying data source. The KeyColumns property specifies the column(s) containing the member key(s).

The DataItem Collection Editor appears, as depicted in Illustration 10.

The DataItem Collection Editor is used throughout the Business Intelligence Development Studio to edit the collection of data items associated with the KeyColumns property of various Analysis Services objects. The Members pane on the left side of the dialog lists the data items contained by the collection. Here, we can add or remove data items to the Members pane, as well as move the items up or down as appropriate to meet our business requirements.

The Properties pane displays a list of properties available for the data item that is selected within the Members pane (left half of the Editor), as depicted in Illustration 12.

As we can easily see, the first of the displayed DimGeography.GeographyKey DataItem properties, Source, expands to make available the TableID and the ColumnID boxes, where we specify the location of the key within the underlying database.

Once we have selected the TableID, we can select from a context-sensitive list of columns via the ColumnID selector immediately underneath the TableID selector, as partially depicted in Illustration 14.

The data type options within Analysis Services 2005 have been expanded over those of previous versions. The DataType property allows us to convert the data types from those applicable to the data within the underlying relational database to different data types that we might require for the corresponding member data within Analysis Services. We are thus afforded yet another element of versatility between these two layers of the integrated business intelligence solution.

The DataSize property allows us to specify (for either binary or text data) a size (in bytes and characters, respectively). The default is 255 characters anytime we do not specify size.

Here we can select a value to dictate the manner in which Analysis Services processes null attribute member data. These values are explained in detail in Table 1.

The Collation property affords us a way to specify the rules we wish to invoke for text data string comparisons. While collation in general has multiple purposes, we often use it to determine whether the members of a given pair of strings are alike or different. Several Sort Orders are also available, with the Designator and Sort Order selections defaulting to server settings.

The Format property purports (via the Books Online and other documentation) to allow us to specify - using Visual Basic (Format function) format types - the conventions used in transforming numeric data to text, if such a transformation is required. The reality is that the only member formatting supported within the Unified Dimension Model (UDM) is the Trimming setting that we discuss below. (We can, of course, employ named calculations or column calculations (at the data source view level) within the cube to achieve our formatting ends, as alternative approaches.

The InvalidXmlCharacters property is applicable in cases where we expect data to be received in the XML format, and where we wish to dictate the handling of such data. The values are explained in Table 2.

The MimeType property allows us to specify the binary data type, where necessary to meet our needs.

The Trimming property allows us to specify the desired treatment of trailing spaces at the beginning / end of a string. As we see in Illustration 20 above, the options are self-explanatory.

Finally, in the case of a simple key, such as the Geography Key upon which we are focusing within our practice session, we have a single entry within the Members pane on the left side of the DataItem Collection Editor. The single row is numbered “0,” as shown in Illustration 21.

We will explore this setting and others for a composite key in Part II of this article.

Conclusion

In this, the first half of a two-part article introducing Attribute Member Keys, we continued 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. We reviewed our initial introduction to the dimensional model and summarized its role in meeting the primary objectives of business intelligence. Next, we provided a brief overview of dimension attributes in general, referencing a subseries of articles, within my Introduction to MSSQL Server Analysis Services series, where we explore the properties underlying them in detail.

We then began our exploration of Attribute Member Keys. First, we 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 develop throughout Parts I and II of this article), discussing its role in meeting our business intelligence needs. We next followed with a discussion of the role of the Key attribute from a technical perspective, including its purpose within a containing dimension within Analysis Services.

We then introduced the concepts of simple and composite keys, narrowing our exploration in this half of the article to the former. We reviewed the Properties associated with a simple key, based upon the examination of a representative dimension attribute, Geography, within our sample UDM. Finally, we looked ahead to Part II, where we explore the Properties associated with a composite key.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.