Manage Unknown Members in Analysis Services 2005, Part II - Page 2
December 14, 2007
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.
NOTE: Our steps in creating a sample basic database within which to perform the steps of our practice sessions are detailed in Part I. Before you can proceed with the practical steps below, you will need to accomplish the steps outlined in creating and enhancing the sample cube in Part I.
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.
1. Reopen SQL Server Business Intelligence Development Studio, as appropriate.
2. Close the Start page, if desired.
3. Ensure that the ANSYS063_Basic AS DB database with which we worked in Part I is open, and appears within the Solution Explorer.
Note: For the steps involved in completing the above, see Part I.
Here well 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.
1. Within the Solution Explorer, right-click the Product dimension (expand the Dimensions folder as necessary).
2. Click Open on the context menu that appears, as depicted in Illustration 1.
The tabs of the Dimension Designer open.
3. Click the Dimension Structure tab, if it has not already appeared by default.
4. Ensure that Product, atop the Attribute pane, is selected to cause the dimensions Properties window to appear (by default in the lower right corner of the design environment).
5. Within the Properties window for the Product dimension, change the UnknownMember property value (within the Advanced properties section of the window) to Visible.
6. Change the value for the UnknownMemberName property (just beneath the UnknownMember property value) to Assembly Components.
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, lets take a look at the relationships that exist between certain Product attributes.
7. Expand the following attributes within the Attributes pane of the Dimension Structure tab for the Product dimension.
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.
8. Drag the Category attribute relationship from the Product Name attribute to the Subcategory attribute, shown in Illustration 4.
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.
9. In the Attributes pane, click / select Subcategory.
10. Click the ellipsis button (...) in the KeyColumns property cell (located within the Source section) in the Properties window, as depicted in Illustration 5.
11. In the DataItem Collection Editor dialog that next appears, change the NullProcessing property to UnknownMember, as shown in Illustration 6.
12. Click OK to accept our modification and to dismiss the DataItem Collection Editor dialog.
13. Click / select Model Name within the Attributes pane.
14. Click the ellipsis button (...) in the KeyColumns property cell in the Properties window, as we did for the Subcategory attribute earlier.
15. In the DataItem Collection Editor dialog that next appears, change the NullProcessing property to UnknownMember, once again.
16. Click OK to accept our modification, once again, and to dismiss the DataItem Collection Editor dialog.
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.