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:

Make a Copy of a Basic Analysis Services Sample Project within a New Solution

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.

1.  Start Microsoft SQL Server Management Studio.

2.  Select Analysis Services in the Server type selector of the Connect to Server dialog that appears.

3.  Type / select the server name (server name / instance, if appropriate) in the Server name selector.

4.  Supply authentication information, as required in your own environment.

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


Illustration 1: Connecting to the Server …

5.  Click the Connect button to connect with the specified Analysis Server.

The Microsoft SQL Server Management Studio opens.

6.  In the Object Explorer pane (it appears by default on the left side of the Studio), expand the Databases folder (click the “+” sign to its immediate left), appearing underneath the Analysis Server within which we are working.

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.

7.  Right-click the DBJ_Basic AS DB database, and select Rename from the context menu that appears next, as shown in Illustration 2.


Illustration 2: Renaming the Sample Database

8.  Rename the Analysis Services database as follows:

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.

William Pearson
William Pearson
Bill has been working with computers since before becoming a "big eight" CPA, after which he carried his growing information systems knowledge into management accounting, internal auditing, and various capacities of controllership. Bill entered the world of databases and financial systems when he became a consultant for CODA-Financials, a U.K. - based software company that hired only CPA's as application consultants to implement and maintain its integrated financial database - one of the most conceptually powerful, even in his current assessment, to have emerged. At CODA Bill deployed financial databases and business intelligence systems for many global clients. Working with SQL Server, Oracle, Sybase and Informix, and focusing on MSSQL Server, Bill created Island Technologies Inc. in 1997, and has developed a large and diverse customer base over the years since. Bill's background as a CPA, Internal Auditor and Management Accountant enable him to provide value to clients as a liaison between Accounting / Finance and Information Services. Moreover, as a Certified Information Technology Professional (CITP) - a Certified Public Accountant recognized for his or her unique ability to provide business insight by leveraging knowledge of information relationships and supporting technologies - Bill offers his clients the CPA's perspective and ability to understand the complicated business implications and risks associated with technology. From this perspective, he helps them to effectively manage information while ensuring the data's reliability, security, accessibility and relevance. Bill has implemented enterprise business intelligence systems over the years for many Fortune 500 companies, focusing his practice (since the advent of MSSQL Server 2000) upon the integrated Microsoft business intelligence solution. He leverages his years of experience with other enterprise OLAP and reporting applications (Cognos, Business Objects, Crystal, and others) in regular conversions of these once-dominant applications to the Microsoft BI stack. Bill believes it is easier to teach technical skills to people with non-technical training than vice-versa, and he constantly seeks ways to graft new technology into the Accounting and Finance arenas. Bill was awarded Microsoft SQL Server MVP in 2009. Hobbies include advanced literature studies and occasional lectures, with recent concentration upon the works of William Faulkner, Henry James, Marcel Proust, James Joyce, Honoré de Balzac, and Charles Dickens. Other long-time interests have included the exploration of generative music sourced from database architecture.

Latest Articles