Manage Unknown Members in Analysis Services 2005, Part I

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 2005 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

When Analysis Services processes dimensions within a cube, it attempts to match each dimension key in the fact table with a corresponding dimension member in the dimension table to which it is joined. In prototyping and other developmental scenarios, and sometimes after recurring ETL updates and other evolutions, we encounter cases where a dimension key exists within the fact table – with no matching key in the dimension table. Analysis Services employs the concept of an Unknown Member to define such unmatched dimension members. (A simple NULL is not assigned because it is possible for another attribute member to have a key whose value is already set at NULL. For that matter, by default, each attribute within our cubes contains an unknown member by design – even an empty attribute.)

In this two-part article, we will consider the management of the Unknown Member within Analysis Services. The Unknown Member settings offer us capabilities, similar to those found in once dominant enterprise BI applications such as Cognos PowerPlay / Transformer, for handling scenarios involving unmatched dimension keys such as we have described. The capabilities afforded by the Unknown Member options allow us to override the processing failure that would occur in these cases of mismatch, as well is to assign a name other than “Unknown” to the Unknown Member within each dimension, to control visibility of the Unknown Member, and more.

In this, Part I of our two-part article, we will gain an introduction and some hands-on exposure to managing Unknown Members within a sample cube. Our examination will include:

• A discussion surrounding the general concepts and properties underpinning Unknown Members, including what they define and support, as well as the mechanics behind their management.
• Preparation of a sample Analysis Services database and its constituent objects, with which to complete a hands-on practice session.
• A review of Unknown Member properties settings at the dimension level.
• The creation of new attributes within the Product dimension, upon which to establish Unknown Member management within the supporting properties in Part II of this article.
• Processing the enhanced Product dimension, and examining the mechanics behind the default, physical removal of members without corresponding key values within the underlying data.
• A discussion of other considerations that surround our management of Unknown Members.

Manage Unknown Members in Analysis Services 2005

When Analysis Services meets with a null value within the underlying data from which it is attempting to populate a dimension’s attributes, its default reaction is to convert the null to an empty string (for string columns) or to a zero (for numeric columns). Also by default, the Analysis Server ignores the error generated by this condition, allowing processing to continue uninterrupted, removing the attribute member associated with the null through the action of the inner join performed between the tables involved. These default settings are made for us when we use the Dimension and Cube Wizards in constructing our dimensions. The wizards set the appropriate properties under either of two conditions in the underlying data: 1) when the key attribute of the dimension is determined to allow nulls, or 2) when the root attribute of a snowflake dimension is based upon a column that allows nulls.

Three property settings dictate how the Analysis Server handles any such null values it encounters in the underlying data. The first two properties, related to the dimension itself, are UnknownMember and UnknownMemberName. The third property, related to the dimension's key attribute, is the NullProcessing property. The wizards set the defaults, based upon the nullability of the items we mention above, to UnknownMember for the NullProcessing property of the key attribute, Visible for the UnknownMember property, and a simple “Unknown” (which we can easily change, as we shall see, to a name more appropriate for our own environments) for the UnknownMemberName property. While a fourth property, NullKeyCovertedToUnknown, is certainly relevant to our coverage of Unknown Members, its purpose is to direct the Analysis Server in how it handles the error generated when it encounters null-valued attribute members (by default, the property is set to IgnoreError, which, as we noted earlier, directs Analysis Services to remove the offending attribute member entirely, and to continue processing).

Things are different, however, when we define a dimension through means other than the wizards. When we use the Dimension Designer to define a dimension, and then add this dimension to our cube, or when we construct dimensions incrementally, we find that we may need to set some of the properties manually. Because this is the case, we will focus upon a dimension within this context in our practice session; this way, we can concentrate on the properties directly (and a bit more efficiently), rather than walking through the steps of the wizard to define a dimension, and then returning to the individual settings to examine and modify them.

To summarize, we direct the Analysis Server in how to manage these “orphan” attribute members by enabling the UnknownMember property for the dimension, and by specifying a value for the UnknownMemberName property for the dimension (unless the default value of “Unknown” is adequate within the local environment). Other actions we might take surrounding “orphan management,” as we shall see, include setting attribute relationships to link dimension attributes appropriately, and defining custom error handling for the key column used as a basis for the joins between the fact / dimension tables supporting the dimensional structure in general.

We will perform these steps and others within the practice sessions that we undertake in Part I and Part II of this article.

Establishing Properties to Manage Unknown Members in Analysis Services 2005

Let’s assume, for purposes of our practice example, that we have received a request for assistance from representatives of our client, the Adventure Works organization. The team within the IT department whose duties include development and administration of Analysis Services, with whom we have worked in the past to deliver solutions to meet various reporting and analysis needs, inform us that they would like an introduction to the management of Unknown Members within their implementation of Analysis Services. They tell us that, while they generally understand the default operation of the Analysis Server to “filter out” these attribute members when they process their cube, they wish to know more about the mechanism behind this process. Moreover, they feel that they need some hands-on guidance in adding a couple of new attributes to their Product dimension, upon which they intend to base a new user-defined hierarchy that they have determined they need to support new reporting and analysis requirements that have been communicated.

We listen carefully to the requests of our colleagues, and propose to provide an introduction to Unknown Members in Analysis Services; to provide insight as to the default operation of the Analysis Server in this regard; and to provide hands-on guidance in manually managing Unknown Member property settings, via the respective settings that we establish among the new attribute members and hierarchical structure we help them to create and configure. Our client colleagues agree that the proposed approach should meet their immediate requirements. We then begin our introduction and set about the assembly of our example to illustrate both default and manual management of Unknown Members within Analysis Services. 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: Create a Sample Basic Database within Which to Perform the Steps of Our Practice Session

Create Sample Primary Database for the Practice Exercise

Before getting started with our practice session, we will need a basic sample Analysis Services database (with cube) with which to work. To quickly create a copy of a basic Analysis Services sample project, from which an Analysis Services database can be quickly deployed, please perform the steps of the following procedure, located in the References section of my articles index:

Once you have created the new sample project, you should ascertain connectivity to the underlying relational data source (particularly if you are running “side-by-side” installations of MSSQL Server 2000 and MSSQL Server 2005, but it is important to check even if not). You can do this by performing the steps of the following procedure, also located in the References section of my articles index:

Once you have created the new sample project, and ascertained connectivity, deploy it to the Analysis Server by performing the steps of the following procedure, located in the References section of my articles index (substitute object names for your own when following the procedure):

Ascertain that the New Practice Database is in Place and Rename It

We are now ready to ascertain that the new Analysis Services database is in place, as well as to rename it to keep it safe from writeover in future exercises within the series. (You can, of course, always delete the practice database if you do not choose to keep it for future reference, etc.) To do this, we will work from within the Microsoft SQL Server Management Studio.

The Connect to Server dialog appears similar to that depicted in Illustration 1.

The Microsoft SQL Server Management Studio opens.

The Databases folder opens, exposing the detected Analysis Services database(s). Our new Analysis Services database, DBJ_Basic AS DB, should appear among the other databases.

ANSYS063_Basic AS DB

Having ensured that the new database is in place, and having renamed it, we are ready to begin the procedural part of our practice session.