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

Establishing Properties to Manage Unknown Members in Analysis Services 2005

To restate the business requirement underlying our practice example, as we established in Part I, we have received a request for assistance from representatives of our client, the Adventure Works organization (the details of which we describe in the first half of our article). In short, our client colleagues have requested an introduction to the management of Unknown Members within their implementation of Analysis Services, stating that they wish to know more about the mechanism behind this process (they already understand some of the rudiments of default operation by Analysis Services in this regard). 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 recently requested.

After listening carefully to the needs enumerated by our colleagues, we proposed in Part I to provide an introduction to Unknown Members in Analysis Services; to provide insight as to the default operation of the Analysis Server; and to provide practical 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. Once our client colleagues agreed that the proposed approach should meet their immediate requirements, we begin our introduction and set about the assembly of our example to illustrate both default and manual management of Unknown Members within Analysis Services.

Procedure: Continue Working with Properties to Manage Unknown Members in Analysis Services 2005

In the practice session that began in Part I, we first examined the properties that support management of Unknown Members. We then added attributes to a dimension, based upon tables that we added to the underlying data source view, within the sample Analysis Services database / UDM we had prepared. We then examined the mechanics behind the default exclusion of unmatched attribute members within dimension processing.

In this half of the practice session, we will resume where we left off at the end of Part I, enabling and configuring the associated properties for the dimension and dimension attributes that we have added, while 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, as before, from which we will perform select steps of managing Unknown Members within the Analysis Services database we created in Part I, ANSYS063_Basic AS DB.

Here we’ll re-open the Dimension Designer for the Product dimension, where we left off in Part I, and set about modifying the way that Analysis Services manages Unknown Members by default.

Modify Analysis Services’ Default Management of Unknown Members through Associated Property Settings

We will next examine modification of Analysis Services’ default management of Unknown Members through the modification of associated property settings. First, we will enable the UnknownMember property of the Products dimension. We will then set a value for the UnknownMemberName property, to generate a name that is more useful to our client colleagues than a simple “Unknown.” Next, we will set the NullProcessing property for the Subcategory and Model Name attributes to UnknownMember.

Our next steps will surround attribute relationships: We will establish the Category attribute as a related attribute of the Subcategory attribute, and we will then establish the Product Line attribute as a related attribute of the Model Name attribute. Our intent with the steps will be to enforce the use of the specified Unknown Member name value for any Product that has a key resident in the fact table, but which has no matching key within the SubcategoryKey column with which we were working in the earlier section “Create New Attributes within the Product Dimension upon Which to Establish Unknown Member Management within the Supporting Properties.”

The tabs of the Dimension Designer open.

The Properties window for the Product dimension appears, with our modifications, as shown in Illustration 2.

Modifying the UnknownMember property from “None” (which means that the property is disabled for the dimension) to either “Visible” or “Hidden” enables the property. Our input of “Assembly Components” to the UnknownMemberName property assigns our choice of Unknown Member name value to any Product that has a key resident in the fact table, but for which the Analysis Server determines that no matching key exists within the SubcategoryKey column of the associated dimension table. (This effectively creates what I refer to as an “orphan cage.”)

Now, let’s take a look at the relationships that exist between certain Product attributes.

The expanded attributes appear as depicted in Illustration 3.

Within the expanded attributes, we note that Product Line is related to the Model Name attribute (meaning, too, that it is indirectly linked to the Product Name key attribute). We can see that no relationships have been defined for the Subcategory attribute. Moreover, we see that Category attribute is linked to the Product Name attribute directly through the key attribute.

Our next steps will allow us to enforce the use of our newly specified Unknown Member name value for any Product that has a key resident in the fact table, but which has no matching key within the SubCategoryKey column with which we worked earlier. We will establish a relationship between the Category attribute and the Subcategory attribute, and then we will establish the Product Line attribute as a related attribute of the Model Name attribute.

By taking the above steps, we have linked the Category attribute to the rows in the fact table through the Subcategory attribute, which in turn is linked to the rows in the fact table through the Product Name attribute.

As we have discussed in earlier sections, these modifications dictate how Analysis Services handles any nulls it encounters, during processing, for either or both of the Subcategory attribute or the Model Name attribute. Our selection of the UnknownMember option for the NullProcessing property means that the Unknown Member value (whatever we dictate that to be) will be substituted for the key value. Hierarchies based upon these members will thereupon reflect the new grouping for the Unknown Members.