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

November 15, 2007

Process the Product Dimension, and Examine the Mechanics behind the Removal of Members without Corresponding Key Values within the Underlying Data

Let's process the Product dimension, and take a look “under the hood” to gain an understanding of the mechanics behind the elimination of Products that do not have values within the respective ProductSubcategoryKey column within the underlying data.

Within the View Details viewer, at the end of the SQL query, we see a WHERE clause, as shown in Illustration 34.

The clause reads as follows:

WHERE 
    (
        ( 
             [dbo_DimProduct].[ProductSubcategoryKey] = 
                  [dbo_DimProductSubcategory].[ProductSubcategoryKey]
        )
    )

As is somewhat obvious, the WHERE clause has been added to the SELECT DISTINCT clause to remove those Product members that have no matching value in the ProductSubcategoryKey column.

And so we see how Product members that are referenced within the fact table, but which have no corresponding key matches in the respective dimension table, are removed as a part of processing. Elimination of these unmatched members from inclusion within the cube allows processing to continue uninterrupted for the members that are otherwise acceptable.

We note the absence of the unlabelled level that we saw earlier (recall that it contained assembly components for the client’s Products). The WHERE clause we have examined within the SELECT DISTINCT statement above explains why the blank level has disappeared: the members of the blank level no longer exist within the cube, as they have been “passed over” by the modified SQL statement.

We note the absence of the unlabelled level, once again, confirming our understanding of the effect of the WHERE clause within the modified SQL statement.

In Part II of this article, we will examine the steps involved in modifying the behavior we have noted thus far, with regard to the Analysis Server’s default management of Unknown Members.

Conclusion

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

In the first half of our article, we gained some exposure to the default management of Unknown Members by Analysis Services. Our examination included 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. We then prepared a sample Analysis Services database with related objects, and began a hands-on practice session. We first reviewed Unknown Member properties settings at the dimension level. Next we created new attributes within the Product dimension, upon which we based a user-defined hierarchy, and upon which to establish, in Part II of this article, Unknown Member management within the supporting properties. Finally, we processed the enhanced Product dimension and examined the mechanics behind the exclusion of members without corresponding key values within the underlying data.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.