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 Nov 15, 2007

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

By William Pearson

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.

1.  Click Start.

2.  Navigate to, and click, the SQL Server Business Intelligence Development Studio, as appropriate.

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.

3.  Close the Start page, if desired.

4.  Select File -> Open from the main menu.

5.  Click Analysis Services Database ... from the cascading menu, as depicted in Illustration 3.

Illustration 3: Opening the Analysis Services Database ...

The Connect to Database dialog appears.

6.  Ensuring that the Connect to existing database radio button is selected, type the Analysis Server name into the Server input box atop the dialog.

7.  Using the selector just beneath, labeled Database, select ANSYS063_Basic AS DB, as shown in Illustration 4.

Illustration 4: Selecting the New Basic Analysis Services Database ...

8.  Leaving other settings on the dialog at default, click OK.

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.

9.  Within the Solution Explorer, right-click the Product dimension (expand the Dimensions folder as necessary).

10.  Click Open on the context menu that appears, as depicted in Illustration 5.

Illustration 5: Opening the Dimension Designer ...

The tabs of the Dimension Designer open.

11.  Click the Dimension Structure tab, if it has not already appeared by default.

Review Unknown Member Properties Settings at the Dimension Level

1.  Select Product in the Attributes pane of the Dimension Structure tab.

2.  In the Properties window, locate the UnknownMember and UnknownMemberName properties, as shown in Illustration 6.

Illustration 6: Locate the Dimension-Level Unknown Member Properties (Composite View)

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.

Illustration 7: The Unknown Member Properties for the Product Dimension (Close-up)

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.

12.  In the Properties window, once again, select (Custom) in the ErrorConfiguration property setting, as shown in Illustration 8.

Illustration 8: Select (Custom) in the ErrorConfiguration Property

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

13.  Expand the ErrorConfiguration properties collection by clicking the “+” sign to the immediate left of the ErrorConfiguration property.

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

Illustration 9: The ErrorConfiguration Properties Collection (Defaults)

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.

14.  Leave the ErrorConfiguration properties settings at default, and collapse the collection by clicking the “-” sign to the immediate left of the ErrorConfiguration property.

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.

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