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.
and click, the SQL
Server Business Intelligence Development Studio, as appropriate.
briefly see a splash page that lists the components installed on the PC, and
then Visual Studio .NET 2005 opens at the Start page.
Close the Start
page, if desired.
-> Open from the main menu.
Services Database ... from the cascading menu, as depicted in Illustration
3: Opening the Analysis Services Database ...
to Database dialog appears.
the Connect to existing database radio button is selected, type the Analysis
Server name into the Server input box atop the dialog.
selector just beneath, labeled Database, select ANSYS063_Basic AS DB,
as shown in Illustration
4: Selecting the New Basic Analysis Services Database ...
settings on the dialog at default, click OK.
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 well 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
Within the Solution
Explorer, right-click the Product dimension (expand the Dimensions
folder as necessary).
on the context menu that appears, as depicted in Illustration 5.
5: Opening the Dimension Designer ...
tabs of the Dimension Designer open.
Click the Dimension
Structure tab, if it has not already appeared by default.
Review Unknown Member Properties Settings at the Dimension Level
in the Attributes pane of the Dimension Structure tab.
In the Properties
window, locate the UnknownMember and UnknownMemberName
properties, as shown
in Illustration 6.
6: Locate the Dimension-Level Unknown Member Properties (Composite View)
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.
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.
In the Properties
window, once again, select (Custom) in the ErrorConfiguration
property setting, as
shown in Illustration
8: Select (Custom) in the ErrorConfiguration Property
that a + sign appears to the immediate left of the ErrorConfiguration
property. This indicates enablement of the underlying properties collection.
Expand the ErrorConfiguration
properties collection by clicking the + sign to the immediate left of the ErrorConfiguration
exposed ErrorConfiguration properties collection appears as depicted in Illustration 9.
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.
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.