Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Dec 14, 2007

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

By William Pearson

Browse the Product Dimension to Verify the Modified Management of Unknown Members via Property Settings

Let’s process the modified dimension, and then leverage the convenience of the built-in Browser to verify the effects of our handiwork.

1.  Right-click the Product dimension within the Solution Explorer.

2.  Select Process ... from the context menu that appears, as depicted in Illustration 7.

Illustration 7: Process the Product Dimension ...

3.  Click Yes when asked if we wish to “... save all changes first.”

4.  When processing has successfully completed, click the Browser tab in Dimension Designer for the Product dimension.

5.  Click the Reconnect button, once again.

6.  Select Product Categories in the Hierarchy list.

7.  Expand All Products.

Assembly Components appears as a new member of the Category level, as shown in Illustration 8.

Illustration 8: Assembly Components Appears as a Member of the Category Level ...

8.  Expand the Assembly Components member of the Category level.

9.  Expand the Assembly Components member that appears underneath the Assembly Components member just expanded (itself a member of the Subcategory level).

We next see all the Assembly Components appearing at the Product Name level, as partially depicted in Illustration 9.

Illustration 9: Assembly Components Appears at the Product Name Level – Product Category Hierarchy (Partial View)

10.  Returning to the Hierarchy list, select Product Model Lines.

11.  Expand All Products.

12.  Expand the Assembly Components member of the Product Line level.

13.  Expand the Assembly Components member of the Model Name level.

We now see the Assembly Components appearing at the Product Name level, as shown in Illustration 10.

Illustration 10: Assembly Components Appears at the Product Name Level – Product Model Lines Hierarchy (Partial View)

Having demonstrated the effectiveness of our settings in managing Unknown Members, and having received confirmation from our client colleagues that their immediate business requirements have been met through the solution we have demonstrated, we conclude our two-part practice session. We have established the capability to manage Unknown Members through settings modifications for a dimension within our client’s cube.

14.  Close the Business Intelligence Development Studio when ready.


In this 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, first reviewing 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 we intended to establish, in the second half of the article, Unknown Member management capabilities within the supporting properties.

After processing the enhanced Product dimension and examining the mechanics behind the exclusion of members without corresponding key values within the underlying data, we moved to this, Part II, of our article. In this half, we continued to gain an introduction and hands-on exposure to managing Unknown Members, resuming our procedures with the sample cube that we created in Part I. Our continuing examination included further coverage, where appropriate, of the general concepts and properties (including what they define and support, and how we can manage them) underpinning Unknown Members. We established Unknown Member management within the associated supporting properties for the Product dimension attributes that we added to the sample cube, at the request of our client colleagues, in the first half of our article. We then processed the enhanced Product dimension, and used the Browser within the Dimension Designer to verify the effectiveness of the results of our work. Finally, throughout the article we discussed other considerations that surround our management of Unknown Members.

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

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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM