About the Series ...
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
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
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:
surrounding the general concepts and properties underpinning Unknown Members,
including what they define and support, as well as the mechanics behind their
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.
of new attributes within the Product dimension, upon which to establish Unknown
Member management within the supporting properties in Part II of
enhanced Product dimension, and examining the mechanics behind the default,
physical removal of members without corresponding key values within the
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 dimensions
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
Establishing Properties to Manage Unknown Members in Analysis Services 2005
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.
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
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:
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):
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.
SQL Server Management Studio.
Services in the Server type selector of the Connect to Server
dialog that appears.
Type / select
the server name (server name / instance, if appropriate) in the Server name
authentication information, as required in your own environment.
The Connect to Server
dialog appears similar to that depicted in Illustration 1.
Connecting to the Server ...
Click the Connect
button to connect with the specified Analysis Server.
The Microsoft SQL
Server Management Studio opens.
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
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
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.