Manage Unknown Members in Analysis Services 2005, Part I - Page 2

Procedure: Establish Properties to Manage Unknown Members in Analysis Services 2005

Best practices for handling the null value scenarios that spawn Unknown Members almost universally dictate the conversion of these values as a part of the extract, transform, and load (ETL) process(es) within the underlying relational data warehouse. Development and other scenarios arise, however, that make the management of the resulting Unknown Members at the Analysis Services level convenient. Examples might include situations where we build cubes based upon data sources other than typical data warehouses, or where we are creating a prototype within, say, the business requirements gathering phase of a larger development effort. In these scenarios, it might be possible – perhaps even expected - to encounter dimension-referencing data records within a fact table (or its conceptual equivalent, from the perspective of Analysis Services) that have no corresponding record within the respective dimension table. Because we expect this to be the case, and because we may be well aware that “all is not yet perfect” in the underlying data source (we would, of course, likely intend to add control features to the database to prevent such scenarios as development evolves), the capability to manage Unknown Members via the related properties in Analysis Services offers us the option to make progress in our development efforts, to some extent, by allowing cube processing to continue and to not be arrested by the errors that would normally shut us down when encountering the mismatches we have described.

In the practice session that follows, we will first examine the properties, which we have introduced in the foregoing sections of the article that support management of Unknown Members. We will then add attributes to a dimension, based upon tables that we add to the underlying data source view, within the sample Analysis Services database / UDM we have prepared. Next, will examine the mechanics behind the default exclusion of unmatched attribute members within dimension processing. In Part II of this article, we will enable and configure the associated properties for the dimension and dimension attributes that we add, discussing our options and the respective results. Finally, we will manage error handling for the member key attribute involved.

We will perform our practice session within the SQL Server Business Intelligence Development Studio, from which we will perform select steps of managing Unknown Members within our new Analysis Services database, ANSYS063_Basic AS DB.

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.

The Connect to Database dialog appears.

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. Here we’ll open the Dimension Designer for the Product dimension, where we will first proceed with a review of Unknown Member property settings as they currently exist for the Product dimension. We will then undertake the steps involved in establishing the management of Unknown Members from the perspective of a couple of new attributes we will add, per the client request, to the Product dimension.

The tabs of the Dimension Designer open.

Review Unknown Member Properties Settings at the Dimension Level

We can see that the UnknownMember for the Product dimension is not enabled. We know this because the property value is set to None (instead of one of the other two options, Visible or Hidden). We also note that no custom name is assigned to the UnknownMemberName property, where we see the default of “Unknown” is specified. The Unknown Member properties for the dimension are depicted in a closer view in Illustration 7.

Let's take a look at the ErrorConfiguration property for the Product dimension, and the options that it offers with regard to default or custom settings.

We note that a “+” sign appears to the immediate left of the ErrorConfiguration property. This indicates enablement of the underlying properties collection.

The exposed ErrorConfiguration properties collection appears as depicted in Illustration 9.

The properties collection that appears reveals the default settings for the ErrorConfiguration property. We note especially that the default properties dictate, primarily, that 1) null keys are converted to the Unknown Member and 2) the processing error that is raised with the conversion is ignored, leaving cube processing uninterrupted.

Having examined the default settings, and having explained them to our client colleagues, we are ready to move to our next area of focus. We will add a couple of new attributes to the Products dimension, in compliance with the request of our client colleagues, to support a new user-defined hierarchy. We will, moreover, take advantage of the opportunity that the addition of these attributes affords us to observe and discuss the default behavior of Analysis Services in its management of Unknown Members. As we have noted, we will further leverage the new attributes and hierarchy as a context for the manual management of Unknown Member properties settings in Part II.