Manage Unknown Members in Analysis Services 2005, Part I

Thursday Nov 15th 2007 by William Pearson
Share:

Manage members for which no underlying data exists to populate dimensional attributes. Business Intelligence Architect Bill Pearson provides hands-on guidance in handling these Unknown Member scenarios within Analysis Services 2005 through setting associated properties.

About the Series ...

This article is a member of the series Introduction to MSSQL Server Analysis Services. The series is designed to provide hands-on application of the fundamentals of MS SQL Server 2005 Analysis Services (“Analysis Services”), with each installment progressively presenting features and techniques designed to meet specific real-world needs. For more information on the series, please see my initial article, Creating Our First Cube. For the software components, samples and tools needed to complete the hands-on portions of this article, see Usage-Based Optimization in Analysis Services 2005, another article within this series.

Introduction

When Analysis Services processes dimensions within a cube, it attempts to match each dimension key in the fact table with a corresponding dimension member in the dimension table to which it is joined. In prototyping and other developmental scenarios, and sometimes after recurring ETL updates and other evolutions, we encounter cases where a dimension key exists within the fact table – with no matching key in the dimension table. Analysis Services employs the concept of an Unknown Member to define such unmatched dimension members. (A simple NULL is not assigned because it is possible for another attribute member to have a key whose value is already set at NULL. For that matter, by default, each attribute within our cubes contains an unknown member by design – even an empty attribute.)

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

In this, Part I of our two-part article, we will gain an introduction and some hands-on exposure to managing Unknown Members within a sample cube. Our examination will include:

  • 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.
  • Preparation of a sample Analysis Services database and its constituent objects, with which to complete a hands-on practice session.
  • A review of Unknown Member properties settings at the dimension level.
  • The creation of new attributes within the Product dimension, upon which to establish Unknown Member management within the supporting properties in Part II of this article.
  • Processing the enhanced Product dimension, and examining the mechanics behind the default, physical removal of members without corresponding key values within the underlying data.
  • A discussion of other considerations that surround our management of Unknown Members.

Manage Unknown Members in Analysis Services 2005

When Analysis Services meets with a null value within the underlying data from which it is attempting to populate a dimension’s attributes, its default reaction is to convert the null to an empty string (for string columns) or to a zero (for numeric columns). Also by default, the Analysis Server ignores the error generated by this condition, allowing processing to continue uninterrupted, removing the attribute member associated with the null through the action of the inner join performed between the tables involved. These default settings are made for us when we use the Dimension and Cube Wizards in constructing our dimensions. The wizards set the appropriate properties under either of two conditions in the underlying data: 1) when the key attribute of the dimension is determined to allow nulls, or 2) when the root attribute of a snowflake dimension is based upon a column that allows nulls.

Three property settings dictate how the Analysis Server handles any such null values it encounters in the underlying data. The first two properties, related to the dimension itself, are UnknownMember and UnknownMemberName. The third property, related to the dimension's key attribute, is the NullProcessing property. The wizards set the defaults, based upon the nullability of the items we mention above, to UnknownMember for the NullProcessing property of the key attribute, Visible for the UnknownMember property, and a simple “Unknown” (which we can easily change, as we shall see, to a name more appropriate for our own environments) for the UnknownMemberName property. While a fourth property, NullKeyCovertedToUnknown, is certainly relevant to our coverage of Unknown Members, its purpose is to direct the Analysis Server in how it handles the error generated when it encounters null-valued attribute members (by default, the property is set to IgnoreError, which, as we noted earlier, directs Analysis Services to remove the offending attribute member entirely, and to continue processing).

Things are different, however, when we define a dimension through means other than the wizards. When we use the Dimension Designer to define a dimension, and then add this dimension to our cube, or when we construct dimensions incrementally, we find that we may need to set some of the properties manually. Because this is the case, we will focus upon a dimension within this context in our practice session; this way, we can concentrate on the properties directly (and a bit more efficiently), rather than walking through the steps of the wizard to define a dimension, and then returning to the individual settings to examine and modify them.

To summarize, we direct the Analysis Server in how to manage these “orphan” attribute members by enabling the UnknownMember property for the dimension, and by specifying a value for the UnknownMemberName property for the dimension (unless the default value of “Unknown” is adequate within the local environment). Other actions we might take surrounding “orphan management,” as we shall see, include setting attribute relationships to link dimension attributes appropriately, and defining custom error handling for the key column used as a basis for the joins between the fact / dimension tables supporting the dimensional structure in general.

We will perform these steps and others within the practice sessions that we undertake in Part I and Part II of this article.

Establishing Properties to Manage Unknown Members in Analysis Services 2005

Let’s assume, for purposes of our practice example, that we have received a request for assistance from representatives of our client, the Adventure Works organization. The team within the IT department whose duties include development and administration of Analysis Services, with whom we have worked in the past to deliver solutions to meet various reporting and analysis needs, inform us that they would like an introduction to the management of Unknown Members within their implementation of Analysis Services. They tell us that, while they generally understand the default operation of the Analysis Server to “filter out” these attribute members when they process their cube, they wish to know more about the mechanism behind this process. 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 communicated.

We listen carefully to the requests of our colleagues, and propose to provide an introduction to Unknown Members in Analysis Services; to provide insight as to the default operation of the Analysis Server in this regard; and to provide hands-on 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. Our client colleagues agree that the proposed approach should meet their immediate requirements. We then begin our introduction and set about the assembly of our example to illustrate both default and manual management of Unknown Members within Analysis Services. Before we get started working within a sample cube clone, we will need to prepare the local environment for the practice session. We will take steps to accomplish this within the section that follows.

Preparation: Create a Sample Basic Database within Which to Perform the Steps of Our Practice Session

Create Sample Primary Database for the Practice Exercise

Before getting started with our practice session, we will need a basic sample Analysis Services database (with cube) with which to work. To quickly create a copy of a basic Analysis Services sample project, from which an Analysis Services database can be quickly deployed, please perform the steps of the following procedure, located in the References section of my articles index:

Make a Copy of a Basic Analysis Services Sample Project within a New Solution

Once you have created the new sample project, you should ascertain connectivity to the underlying relational data source (particularly if you are running “side-by-side” installations of MSSQL Server 2000 and MSSQL Server 2005, but it is important to check even if not). You can do this by performing the steps of the following procedure, also located in the References section of my articles index:

Ascertain Connectivity of the Relational Data Source

Once you have created the new sample project, and ascertained connectivity, deploy it to the Analysis Server by performing the steps of the following procedure, located in the References section of my articles index (substitute object names for your own when following the procedure):

Deploy the Analysis Services Project

Ascertain that the New Practice Database is in Place and Rename It

We are now ready to ascertain that the new Analysis Services database is in place, as well as to rename it to keep it safe from writeover in future exercises within the series. (You can, of course, always delete the practice database if you do not choose to keep it for future reference, etc.) To do this, we will work from within the Microsoft SQL Server Management Studio.

1.  Start Microsoft SQL Server Management Studio.

2.  Select Analysis Services in the Server type selector of the Connect to Server dialog that appears.

3.  Type / select the server name (server name / instance, if appropriate) in the Server name selector.

4.  Supply authentication information, as required in your own environment.

The Connect to Server dialog appears similar to that depicted in Illustration 1.


Illustration 1: Connecting to the Server ...

5.  Click the Connect button to connect with the specified Analysis Server.

The Microsoft SQL Server Management Studio opens.

6.  In the Object Explorer pane (it appears by default on the left side of the Studio), expand the Databases folder (click the “+” sign to its immediate left), appearing underneath the Analysis Server within which we are working.

The Databases folder opens, exposing the detected Analysis Services database(s). Our new Analysis Services database, DBJ_Basic AS DB, should appear among the other databases.

7.  Right-click the DBJ_Basic AS DB database, and select Rename from the context menu that appears next, as shown in Illustration 2.


Illustration 2: Renaming the Sample Database

8.  Rename the Analysis Services database as follows:

ANSYS063_Basic AS DB

Having ensured that the new database is in place, and having renamed it, we are ready to begin the procedural part of our practice session.

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.

Create New Attributes within the Product Dimension upon Which to Establish Unknown Member Management within the Supporting Properties

In the next procedural steps, we will add two new attributes, Product Category and Product Subcategory, to the Product dimension, basing these attributes upon tables that we will add into the data source view that supports our cube. We will then establish Unknown Member properties to meet the expressed business requirements of our client, among other structural adjustments we will perform for this purpose.

1.  Click the Browser tab within the Dimension Designer for the Product dimension.

2.  As necessary, select Product Model Lines within the Hierarchy list atop the Browser tab, as shown in Illustration 10.


Illustration 10: Selecting the Product Model Lines Hierarchy ...

3.  Expand All Products within the Browser by clicking the “+” sign that appears to its immediate left.

We note the appearance of five children of the All Products level of the Product Model Lines hierarchy.

4.  Expand the Components member of the Product Line level.

The children of the Components Product Line level (themselves representing the Model Name level of the hierarchy), including an unlabeled member, appear as depicted in Illustration 11.


Illustration 11: The Model Name Level, Including Unlabeled Member

5.  Expand the unlabeled member, appearing among the Components children (itself a member of the of the Model Name level of the Products Model Lines hierarchy).

The children of the unlabeled member, representing assembly components used to build our clients products, appear as partially shown in Illustration 12.


Illustration 12: Children of the Unlabeled Member (Partial View)

Having reviewed the currently existing Product dimension, Product Model Lines hierarchy, we are ready to add the new Product Category and Product Subcategory attributes requested by our client colleagues. To begin, we will transit to the Data Source View underlying our cube.

6.  Click the Dimension Structure tab within the Dimension Designer for the Product dimension.

7.  Right-click an unoccupied area within the Data Source View pane of the Dimension Structure tab.

8.  Select Edit Data Source View from the context menu that appears, as depicted in Illustration 13.


Illustration 13: Editing the Data Source View ...

The Data Source View Designer opens.

9.  Select Reseller Sales within the Diagram Organizer pane in the upper left corner of the Data Source View Designer tab.

10.  From the main menu, select Data Source View.

11.  Select Add/Remove Tables ... from the menu that appears, as shown in Illustration 14.


Illustration 14: Adding Tables to the Data Source View ...

12.  Within the Add/Remove Tables dialog that opens next, select dbo.DimProduct within the Included objects list on the right side of the dialog.

13.  Click the Add Related Tables button underneath the Included objects list, as depicted in Illustration 15.


Illustration 15: Adding DimProduct-related Tables ...

14.  Leaving the newly added dbo.DimProductSubcategory table selected (it was selected by default), click Add Related Tables again.

Both the dbo.DimProductSubcategory and the dbo.DimProductCategory tables are now added to the Included objects list of the Add/Remove Tables dialog, as shown in Illustration 16.


Illustration 16: The Newly Added Tables in the Included Objects List

15.  Click OK to accept the newly added tables, and to dismiss the Add/Remove Tables dialog.

16.  On the main menu, select Format.

17.  Select Auto Layout from the Format menu.

18.  Click Diagram on the cascading menu that appears next, as depicted in Illustration 17.


Illustration 17: Select Format -> Auto Layout -> Diagram

The tables appear in diagram format, where we can see the joins that are established between the dbo.DimProductSubcategory and dbo.DimProductCategory tables. The newly added tables are also joined, via the Product table, to the ResellerSales table. The diagram appears similar to that shown in Illustration 18.


Illustration 18: The Tables in Diagram Format

19.  Return to Dimension Designer for the Product dimension by clicking the Product tab atop the design environment.

20.  Click the Dimension Structure tab, if necessary.

21.  Right-click an unoccupied point within in the Data Source View pane at the right of the tab.

22.  Click Show All Tables on the context menu that appears, as depicted in Illustration 19.


Illustration 19: Select Show All Tables ...

The tables appear in diagram format, where we can see the joins once again, within another view, as shown in Illustration 20.


Illustration 20: Another Diagram View of the (Relevant) Tables

23.  Within the Data Source View pane, right-click ProductCategoryKey in the DimProductCategory table.

24.  Click New Attribute from Column from the context menu that appears, as depicted in Illustration 21.


Illustration 21: Select New Attribute from Column ...

25.  Right-click the new entry, Product Category Key, that appears within the Attributes pane of the Dimension Structure tab.

26.  Select Rename from the context menu that appears, as shown in Illustration 22.


Illustration 22: Renaming the New Attribute ...

27.  Change the name of the new Product Category Key attribute to Category within the caption that is now enabled.

28.  Within the Properties window for the renamed attribute, click the NameColumn property (located within the Source section at the bottom of the Properties pane).

29.  Using the downward arrow within the property cell, select (new), as depicted in Illustration 23.


Illustration 23: Select (new) ...

30.  Select DimProductCategory within the Source table selector, and EnglishProductCategoryName from within the Source column list, in the Object Binding dialog box that appears next, as shown in Illustration 24.


Illustration 24: Make Object Binding Selections ...

31.  Click OK to save our binding specifications and to dismiss the Object Binding dialog.

32.  Within the Data Source View pane, once again, right-click ProductSubcategoryKey in the DimProductSubcategory table.

33.  Click New Attribute from Column from the context menu that appears, as we did earlier.

34.  Right-click the new entry, Product Subcategory Key, that appears within the Attributes pane of the Dimension Structure tab.

35.  Select Rename from the context menu that appears, as we did before.

36.  Change the name of the new Product Subcategory Key attribute to Subcategory within the enabled caption.

37.  Within the Properties window for the renamed attribute, click the NameColumn property (located within the Source section at the bottom of the Properties pane).

38.  Using the downward arrow within the property cell, select (new), as we did earlier.

39.  Select DimProductSubcategory within the Source table selector, and EnglishProductSubcategoryName from within the Source column list, in the Object Binding dialog box that appears next.

40.  Click OK to save our binding specifications and to dismiss the Object Binding dialog, once again.

The two new attributes appear within the Attributes pane of the Dimension Structure tab, as depicted in Illustration 25.


Illustration 25: The Two New Attributes within the Attributes Pane

We are now ready to create a new hierarchy to house our new attributes as levels.

41.  Drag the Product Name attribute from the Attributes pane into an unoccupied area within the Hierarchies and Levels pane.

A new hierarchy, labeled Hierarchy by default, appears within the Hierarchies and Levels pane, as shown in Illustration 26.


Illustration 26: The New Hierarchy Appears ...

42.  Right-click the Hierarchy label atop the new hierarchy.

43.  Select Rename from the context menu that appears, as depicted in Illustration 27.


Illustration 27: Renaming the New Hierarchy ...

44.  Within the enabled caption, replace the existing Hierarchy label with Product Categories.

45.  Drag the Subcategory attribute onto the new Product Categories hierarchy, aligning and dropping it above the Product Name level within the hierarchy (the cursor will form a red bar at the drop point), as shown in Illustration 28.


Illustration 28: Adding a New Level to the Hierarchy ...

46.  Drag the Category attribute onto the new Product Categories hierarchy, aligning and dropping it above the Category level we added immediately above.

The Product Categories hierarchy, with levels in the desired order, appears within the Hierarchies and Levels pane as depicted in Illustration 29.


Illustration 29: The New Product Categories Hierarchy

47.  Click the top portion of the new Product Categories hierarchy, to select it.

48.  Type the following into the AllMemberName property (the top property setting within the Advanced section of the Properties pane) of the new Product Categories hierarchy:

All Products

The Properties pane for the Product Categories hierarchy appears as shown in Illustration 30.


Illustration 30: The AllMemberName Property within the Properties Pane

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.

1.  From our current position within the Dimension Designer for the Product dimension, select Dimension -> Process from the main menu, as depicted in Illustration 31.


Illustration 31: Processing the Product Dimension ...

2.  Click Yes on the dialog box that appears next, asking if we wish to save all changes before building, deploying and processing the dimension, as shown in Illustration 32.


Illustration 32: Click Yes to Save All Changes

3.  Click Run on the Process Dimension - Product dialog that appears next, as depicted in Illustration 33, to process the Product dimension.


Illustration 33: Click Run to Process the Product Dimension

4.  When processing has successfully completed, expand Processing Dimension 'Product' completed successfully in the Process Progress event viewer.

5.  Expand Processing Dimension Attribute 'Product Name' completed successfully, appearing underneath the entry we expanded in the last step.

6.  Click the SELECT DISTINCT query.

7.  Click the View Details button on the Process Progress event viewer.

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


Illustration 34: The WHERE Clause Removes Keyless Members

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.

8.  Click Close to dismiss the View Details viewer.

9.  Click Close to dismiss the Process Progress viewer.

10.  Click Close to dismiss the Process Dimension-Product dialog.

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.

11.  Click the Browser tab in Dimension Designer for the Product dimension.

12.  Click the Reconnect button, as depicted in Illustration 35.


Illustration 35: Click the Reconnect Button

13.  Select Product Model Lines in the Hierarchy selector atop the Browser tab.

14.  Expand All Products within the hierarchy tree by clicking the “+ sign to its immediate left.

15.  Expand Components within the Product Line level, as we did in our earlier browse, to arrive at the results shown in Illustration 36.


Illustration 36: Product Model Lines Hierarchy, Reflecting the Absence of Keyless Members

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.

16.  Select Product Categories in the Hierarchy selector atop the Browser tab.

17.  Expand All Products within the hierarchy tree, as we did earlier.

18.  Expand Components within the Category level, as we did in our earlier browse, to arrive at the results depicted in Illustration 37.


Illustration 37: Product Categories Hierarchy, Reflecting the Absence of Keyless Members

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.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved