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 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
In Dimensional Model Components: Dimensions Parts I and
II, we introduced the dimensional
model in general, noting its wide acceptance as the preferred structure for
presenting quantitative and other organizational data to information
consumers. We then began our examination of dimensions, the analytical perspectives
upon which the dimensional model relies in meeting the primary
objectives of business intelligence, including its capacity to support:
of relevant and accurate information representing business operations and
the rapid and
accurate return of query results;
dice query creation and modification;
wherein information consumers can pose questions quickly and easily, and achieve
rapid results datasets.
learned, in Dimensional Model Components:
Dimensions Parts I and II, that dimensions form the
foundation of the dimensional model. They represent the perspectives
of a business or other operation, and reflect the intuitive ways that
information consumers need to query and view data. We noted that we might
consider dimensions as nouns that take part in, are acted upon
by, or are otherwise associated with, the verbs (or actions /
transactions undertaken by the business) that are represented by the facts or
measures contained within our business intelligence systems.
discovered in earlier articles that, within the Analysis Services model,
database dimensions underlie all other dimensions, whose added
properties distinguish them from the database dimensions they reference,
within the model. Each dimension within our model contains one or more hierarchies.
As we learn in other articles of this series, two types of hierarchies
exist within Analysis Services: attribute hierarchies and user
(sometimes called multi-level) hierarchies. For purposes of
this article, the term attribute means the same thing as attribute
hierarchy. (We examine user hierarchies, to which we will simply
refer as hierarchies, in other articles specifically devoted to that topic.)
the metaphor we used earlier in describing dimensions as nouns
and measures as verbs, we might consider attributes as
somewhat similar to adjectives. That is, attributes help us to
define with specificity what dimensions cannot define by themselves. Dimensions
alone are like lines in geometry: they don't define area within
multidimensional space, nor do they themselves even define the hierarchies
that they contain. A database dimension is a collection of related
objects called attributes, which we use to specify the coordinates
required to define cube space.
the table underlying a given dimension (assuming a more-or-less typical
star schema database) are individual rows supporting each of the members
of the associated dimension. Each row contains the set of attributes
that identify, describe, and otherwise define and classify the member
upon whose row they reside. For instance, a member of the Patient
dimension, within the Analysis Services implementation for a healthcare
provider, might contain information such as patient name, patient ID, gender,
age group, race, and other attributes. Some of these attributes
might relate to each other hierarchically, and, as we shall see in other
articles of this subseries (as well as within other of my articles), multiple hierarchies
of this sort are common in real-world dimensions.
Dimensions and dimension attributes
should support the way that management and information consumers of a given
organization describe the events and results of its business operations.
Because we maintain dimension and related attribute information
within the database underlying our Analysis Services implementation, we
can support business intelligence for our clients and employers even when these
details are not captured within the system where transaction processing takes
place. Within the analysis and reporting capabilities we supply in this
manner, dimensions and attributes are useful for aggregation,
filtering, labeling, and other purposes.
Having covered the general characteristics and purposes of attributes
in Dimensional Attributes: Introduction and Overview Parts I through V, we then fixed our focus upon the properties
underlying them, based upon the examination of a representative attribute within
our sample cube. We then continued our extended examination of attributes
to yet another important component we had touched upon earlier, the attribute
member key, with which we gained some hands-on exposure in practice
sessions that followed our coverage of the concepts. In Attribute
Member Keys Pt I: Introduction and Simple Keys and Attribute
Member Keys Pt II: Composite Keys, we introduced Attribute Member Keys in detail, continuing our
recent group of articles focusing upon dimensional model components,
with an objective of discussing the associated concepts, and of providing
hands-on exposure to the properties supporting them.
As a part of our exploration of attribute member Keys,
we first discussed the three attribute usage types that we can
define within a containing dimension. We then narrowed our focus to the Key
attribute usage type (a focus that we developed, as we have noted,
throughout Attribute Member Keys Pt I:
Introduction and Simple Keys and Attribute Member Keys Pt II: Composite Keys), discussing its role in meeting
our business intelligence needs. We next followed with a discussion of the nature
and uses of the attribute Key from a technical perspective, including
its purpose within a containing dimension within Analysis Services.
In Attribute Member Keys Pt I: Introduction and Simple
Keys and Attribute Member Keys Pt II: Composite Keys, we introduced the concepts of simple
and composite keys, narrowing our exploration in
Part I to the former, where we reviewed the Properties
associated with a simple key, based upon the examination of a
representative dimension attribute, Geography, within our
sample UDM. In Part II, we revisited the differences
between simple and composite keys, and explained in more detail
why composite keys are sometimes required to uniquely identify attribute
members. We then reviewed the properties associated with a composite
key, based upon the examination of a representative dimension attribute,
Date, also within our sample UDM.
In this article, we will examine the attribute member Name
property, which we briefly touched upon in Dimensional
Attributes: Introduction and Overview
Part V. We will examine the details of the attribute member
Name, and shed some light on how they can most appropriately be used
without degrading system performance or creating other unexpected or
Our examination will include:
A review of
the nature of the attribute member Name property, and its possible
roles in helping to meet the primary objectives of business intelligence.
A review of
the nature and uses of the attribute member Name from a technical
perspective, including its purpose within its containing dimension within
A discussion surrounding
some of the differences between attribute Name and Key properties.
differences between Analysis Services 2000 and Analysis Services 2005
regarding the use of expressions within our Name column references;
A review of
the settings associated with the Name property, based upon the
examination of a representative dimension attribute within our
Attribute Member Names
have learned, attributes serve as the foundation for our dimensions
and cubes. To review, we discovered in Attribute
Member Keys Pt I: Introduction and Simple Keys that each
attribute, typically based upon a single column (or a named calculation)
within the associated, underlying dimension table, falls into one of three
possible usage roles, Regular, Parent, and Key. We
then focused upon the
attribute member Key
our subject from the perspective of both a simple key and a composite
key). As we noted there, the attribute member Key is
critical to the identification of unique attribute members within Analysis
Services. The Key, we learned, is specified within the KeyColumns
setting, within the Source group of a dimensions Attribute properties.
(We overviewed the Source properties in my Database Journal
article Dimension Attributes: Introduction and
Overview, Part V.)
attribute members are assigned a Key (be it simple or composite) to
uniquely identify them, members can be assigned a Name. A descriptive
name is often more consumer - friendly, and not necessarily a mere luxury:
even if a simple name derived from the Key is sufficiently
understandable to the organization's information consumers, we would still need
to employ the NameColumn property where a composite key (see Attribute Member
Keys Pt II: Composite Keys) is involved; otherwise Analysis Services cannot
determine the appropriate Name to assign.
We do not
have to assign a Name. If we do not, Analysis Services
assigns as the Name the underlying attribute Key column. Such
arrangement might be perfectly adequate for, say, consumers who recognize part
or serial numbers, or other designations, and do not need English names, but
in most cases, a Name comes in handy for both analysis and reporting.
Moreover, and, even in cases where everyone doesn't need it, it can certainly
be suppressed (as in a report), etc., except for scenarios within which benefit
is obtained from its presence. (I have written reports where the consumer could
make the choice at runtime to hide or display the Name, Key or
combination of both, or even to select Name, Key or combination
of both to populate the associated parameter picklist each time the
report is executed.
NOTE: I introduce and examine the intrinsic NAME_VALUE and MEMBER_VALUE properties
(which are derived from the NameColumn and ValueColumn property
settings that we examine within this article), from the perspective of their
use within MDX queries, in my articles Intrinsic Member Properties: The
MEMBER_NAME Property and Intrinsic Member Properties:
The MEMBER_VALUE Property. Both articles are members of my MDX Essentials series at Database Journal.
We will gain hands-on exposure to attribute member Name in
the practice session that follows. 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: Locate and Open the Sample Basic UDM Created Earlier
Components: Dimensions Part I, we created a sample basic Analysis Services database within
which to perform the steps of the practice sessions we set out to undertake in
the various articles of this subseries. Once we had ascertained that the new
practice database appeared to be in place, and once we had renamed it to ANSYS065_Basic
AS DB, we began our examination of dimension properties. We continued
with our examination of attributes within the same practice environment,
which we will now access (as we did within Dimensional Model
Components: Dimensions Part I and Dimensional Attributes: Introduction and Overview Parts I through V) by taking the
following steps within the SQL Server Business Intelligence Development
NOTE: Please access the Analysis
Services database which we prepared in Dimensional Model Components: Dimensions Part I (and have used in subsequent
articles) before proceeding with this article. If you have not completed the
preparation to which I refer, or if you cannot locate / access the Analysis
Services database with which we worked in the referenced previous articles,
please consider taking the preparation steps provided in Dimensional Model Components: Dimensions Part I before continuing, and prospectively
saving the objects with which you work, so as to avoid the need to repeat the
preparation process we have already undertaken for subsequent related articles
within this subseries.
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 shown in Illustration 1.
Illustration 1: Opening the Analysis Services Database ...
to Database dialog appears.
the Connect to existing database radio button atop the dialog is
selected, type the Analysis Server name into the Server input box
(also near the top of the dialog).
selector just beneath, labeled Database, select ANSYS065_Basic AS DB,
as depicted in Illustration
Illustration 2: Selecting the 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. Having overviewed the properties of dimension
attributes in previous articles, we will continue to get some hands-on
exposure to the Name property for an example dimension attribute
member, from within our practice UDM.
Procedure: Examine Attribute Name Property Settings in Analysis Services 2005
In the practice procedures that
follow, we will select and examine a representative dimension attribute
within the sample cube, and then focus upon the Name property settings
that define and support the selected attribute. We will perform our practice
sessions within the SQL Server Business Intelligence Development Studio,
from which we will examine the dimension attribute Name property within
our Analysis Services database, ANSYS065_Basic AS DB.
In Dimensional Model Components:
Dimensions Part I and II, and Dimensional Attributes: Introduction and Overview Parts I through V, respectively, we overviewed the properties
underpinning Database and Cube dimensions, and then examined the properties
supporting dimension attributes. In Attribute Member Keys Pt I:
Introduction and Simple Keys, and
in Attribute Member Keys Pt II:
Composite Keys we focused upon those properties
for a simple attribute key and a composite attribute key,
respectively. Just as we did in those articles, we will examine the detailed
settings for a representative attribute member here, concentrating on those settings within the context of the
member Name. To access these settings for the attribute member within a representative dimension,
we will need to open that dimension within the Dimension Designer
Within the Solution
Explorer, right-click the Time dimension (expand the Dimensions
folder as necessary).
on the context menu that appears, as shown in Illustration 3.
Illustration 3: Opening the Dimension via the Dimension Designer ...
tabs of the Dimension Designer open.
Click the Dimension
Structure tab, if we have not already arrived there by default.
The attributes belonging to the Time dimension appear as depicted in
Illustration 4: The Member Attributes, Time Dimension
note that eight attributes appear within the Attributes pane.
Let's get some exposure to the Name property settings associated with attribute members
by examining a representative member among the attributes we see
Review Attribute Name Property Settings
In Dimensional Attributes: Introduction and Overview Part V, and as a part of our more
detailed exploration in Attribute
Member Keys Pt I: Introduction and Simple Keys, and in Attribute
Member Keys Pt II: Composite Keys
we discovered that, within the Source properties of every attribute member lays the Name property. Lets examine the
property and the underlying NameColumn settings for the Month Name attribute, which is supported
by a composite
within the sample Analysis Services database, by taking the
Within the Attributes
pane of the Dimension Structure tab, right-click the Month
on the context menu that appears, as shown in Illustration 5.
Illustration 5: Select Properties from the Context Menu ...
pane appears for the Month Name attribute. (The Properties pane
likely appeared when we selected the Month Name attribute within the Attributes pane,
by default, below the Solution Explorer. It also may have been hidden,
set up to float within the Studio, or to be anchored elsewhere.) The
design environment can, of course, be customized in many ways to accommodate
your local environment and development needs.)
Expand the Source
group, at the bottom of the Properties pane, by clicking the +
sign that appears to the immediate left of its label, if necessary, as depicted
in Illustration 6.
Illustration 6: Expand the Source Group in the Properties Pane
The expanded Source properties group of the Properties
pane for the Month Name attribute appears as shown in Illustration 7.
Illustration 7: The Source Properties for the Month Name Attribute
take a look at the Name property (and its subproperties), as relevant
to the Month Name attribute, discussing the purpose of the property,
and examining possible settings with which we can come into contact.
Source Property: NameColumn
Much like the KeyColumns property, which we
examined in Attribute Member
Keys Pt I: Introduction and Simple Keys, and Attribute Member
Keys Pt II: Composite Keys, the value we select for the NameColumn property
specifies a column or
columns within the underlying data source. The NameColumn property specifies the column(s)
containing the attribute member Name.
Click the box
to the immediate right of the NameColumn label, just beneath the KeyColumns label, within the expanded Source properties group of the Properties
note that the setting box currently contains DimTime.MonthName,
indicating that the setting is pointed to a column within the dimension table
that was likely designed for the purpose of populating the attribute Name.
Click the selector
(the downward pointing arrow) button that appears on the right edge
of the NameColumn
as depicted in Illustration 8.
Illustration 8: Click the Selector Button, then Select (new) ...
The Object Binding dialog appears, with highlighted Column binding
defaulted to the top column in the Source column list, TimeKey as shown in Illustration 9.
Illustration 9: The Object Binding Dialog Appears
we have noted in other articles of this series, the Object Binding dialog is used throughout the Business
Intelligence Development Studio to edit / add the column binding of
data items associated with properties of various Analysis Services
objects. The Object Binding dialog is typically made available
for single column selection options, where selection is made by simply clicking
the appropriate column within the Source column list.
Click the Cancel
button on the dialog to dismiss it without making changes to the column
Expand the Source
properties group in the Properties pane, atop the list that appears
under the NameColumn group that we expanded above, by clicking the +
sign that appears to the immediate left of the Source label.
The TableID and ColumnID settings appear, as depicted in Illustration 10.
Illustration 10: The Expanded Source Properties Appear
we can see, the first of the displayed DimTime.CalendarYear DataItem
properties, Source, expands to make available the TableID and the
ColumnID boxes, where we can also specify the location of the Name
within the underlying database. If we click on the Source label, or on
the box to its right, an ellipses (...) button becomes enabled. This affords
us another access point to the Object Binding dialog we saw earlier,
where we can, once again, select the Table and Column that we
Click the box
to the immediate right of the TableID label, just beneath the expanded Source
group label, to enable the downward-pointing selector button.
downward arrow selector button, to expose the tables for
selection, as partially shown in Illustration 11.
Illustration 11: Source - TableID Property Value Selection Options (Partial View)
we have selected the TableID, we can select from a context-sensitive
list of columns via the ColumnID selector, immediately underneath the TableID
selector, as partially depicted in Illustration 12.
Illustration 12: Source - ColumnID Property Value Selection Options (Partial View)
Leaving both Source
subproperties at their previously established settings, click the box to the
immediate right of the DataType label, just beneath the expanded Source
ColumnID property, once again to enable the downward-pointing selector
downward arrow selector button, to expose the types for
selection, as partially shown in Illustration 13.
Illustration 13: DataType Property Value Selection Options (Partial View)
We have mentioned
in several other articles of this series that the data type options
within Analysis Services 2005 have been expanded over those of previous
versions. The DataType property allows us to convert the data types
from those applicable to the data within the underlying relational database to
different data types that we might require for the corresponding member data
within Analysis Services. Unlike the data types we observed for the attribute
Key in Attribute Member Keys Pt I:
Introduction and Simple Keys, and
Member Keys Pt II: Composite Keys,
however, only a text data type (or a type that can be converted to text)
can be assigned for an attribute Name. We are therefore still afforded a
degree of versatility between these two layers of the integrated business
Leaving the DataType property at its previously established
setting, click the DataSize label, just beneath the DataType property label, simply to rest it
DataSize property allows us to specify (for either binary or text data) a size
(in bytes and characters, respectively). The setting we see in our example is
15. (The default is 255 characters anytime we do
not specify size.)
dictate we keep the following considerations in mind as we specify attribute member
We should keep
attribute member Names as short as is practical: the longer the Names,
the more resources required for retrieval and storage (therefore system
performance can be negatively impacted (the loading of long Names into
memory can cause significant degradation with regard to system speed, and can
be costly with regard to disk space).
We should avoid
using special characters and spaces in attribute member Names. These
characters and spaces can make the use of member Names cumbersome with
regard to resources, ordering and so forth.
Leaving the DataSize property at its previously established
setting, click the box to the immediate right of the NullProcessing
label, just beneath the DataSize property, once again to enable the associated
downward-pointing selector button.
selector button, to expose the five options for NullProcessing
selection, as depicted in Illustration 14.
Illustration 14: NullProcessing Selection Options
we can select a value to dictate the manner in which Analysis Services
processes null attribute member data. These values are explained in
detail in Table 1.
Table 1: Options for
NullProcessing Rule Selection
preserves the null
NOTE: This selection dictates the expenditure of additional
resources in the storage and processing of null data.
Server displays an error message, because the null value is
Server associates the null value with an unknown member
(which dictates that the value is to be treated in accordance with
established unknown member rules).
converts the null value to a blank (when the data type is a string)
or to a zero (when the data type is other than a string).
Server selects the value based upon its determination of context.
Leaving the NullProcessing property at its previously established
setting, click the box to the immediate right of the Collation label,
just beneath the NullProcessing property,
this time to enable the ellipses (...) button to its right.
ellipses (...) button, to expose the Define Collation dialog, which
appears as shown in Illustration 15.
Illustration 15: The Define Collation Dialog
property affords us a way to specify the rules we wish to invoke for text data
string comparisons. While collation in general has multiple purposes, it
is often used to support the determination of whether the members of a given
pair of strings are alike or different. Several Sort Orders are also
available, with the Designator and Sort Order selections
defaulting to server settings (in effect here, where the Collation
property appears empty within the Properties pane.
can have an impact upon the uniqueness of attribute member Names,
the setting becomes particularly significant when we are specifying Names.
Examples given in various books and other documentation surrounding this consideration
often cite the fact that treatment of capitalized and non-capitalized letters
(some Collation settings ignore capitalization entirely, whereas others
treat capitalized letters, etc., as different characters than the same,
non-capitalized letters). Collation also impacts sorting, so when
information consumers dictate, say, in a report they create in Reporting
Services that they wish to retrieve a group of attribute members in order
of their respective Names (versus in order of the attribute member Key),
results might not match expectations if the Collation property setting
is not taken into consideration .
NOTE: We always have the option of using another attribute
entirely as a basis for our sort orders, if Key or Name is
somehow unsatisfactory. For more information on this versatile option, see my
Sorting Attribute Members in Analysis Services 2005.
downward arrow selector button to the right of the box labeled Collation
designator, to expose the collations available for selection, as partially depicted
in Illustration 16.
Illustration 16: Available Collation Options (Partial View)
settings in the Collation Designator dialog at their previously established values, click the Cancel button
to dismiss the dialog.
Click the Format
label, just beneath the Collation property label, simply to rest it
noted in other articles of this series that the Format property purports
(via the Books Online and other documentation) to allow us to specify -
using Visual Basic (Format function) format types - the conventions used
in transforming numeric data to text, if such a transformation is required. The
reality is that the only member formatting supported within the Unified
Dimension Model (UDM) is the Trimming setting that we discuss
below. (We can, of course, employ named calculations or column
calculations (at the data source view level) within the cube to
achieve our formatting ends, as alternative approaches.
Leaving the Format property blank, click the box to the
immediate right of the InvalidXmlCharacters label, just beneath the Format property, once again to enable the
downward-pointing selector button.
downward arrow selector button, to expose the three selection options
for InvalidXmlCharacters, as shown in Illustration 17.
Illustration 17: Selection Options for InvalidXmlCharacters
InvalidXmlCharacters property is applicable in cases
where we expect data to be received in the XML format, and where we wish to
dictate the handling of such data, if, and when, invalid characters appear.
The values are explained in Table 2.
Table 2: Options for InvalidXmlCharacters Selection
does not change) invalid characters.
characters with a question mark (?)
Leaving the InvalidXmlCharacters property at its previously established
setting, click the MimeType label, just beneath the InvalidXmlCharacters property label, simply to rest it
The MimeType property allows us to specify the
binary data type, where necessary to meet our needs.
Leaving the MimeType property blank, click the box to the immediate
right of the Trimming label, just beneath the MimeType property, as before, to enable the
downward-pointing selector button.
selector button, to expose the four options for Trimming selection, as depicted
in Illustration 18.
Illustration 28: Trimming Property Value Selection Options
The Trimming property allows us to specify the
desired treatment of trailing spaces at the beginning / end of a string. As we see in Illustration 18 above, the options are self-explanatory.
Leave the Trimming
setting as it currently exists.
NOTE: Please consider saving the
project we have created to this point for use in subsequent related articles of
this subseries, so as to avoid the need to repeat the preparation process we
have undertaken initially, to provide a practice environment.
-> Save All to save our work, up to this
point, within the originally chosen location, where it can be easily accessed
for our activities within subsequent articles of this subseries.
-> Exit to leave the design environment,
when ready, and to close the Business Intelligence Development Studio.
In this article, we examined the attribute member Name
property, which we briefly touched upon in Dimensional
Attributes: Introduction and Overview
Part V. We examined the details of this property, and shed some
light on how it can most appropriately be used without degrading system
performance or creating other unexpected or undesirable results.
We focused, throughout our examination of attribute member
Name, on the general nature of the property, and its possible roles in
helping to meet the primary objectives of business intelligence. We reviewed
its roles from a technical perspective, including its purpose within its containing
dimension within Analysis Services.
In gaining hands-on exposure to attribute member Names,
we discussed, at appropriate points, some of the differences between attribute Name
and Key properties. We also mentioned significant differences between Analysis
Services 2000 and Analysis Services 2005, particularly regarding
the use of expressions within our Name column references. Finally, we
performed a detailed review of the settings associated with the attribute
member Name property, based upon the examination of a representative dimension
attribute within our sample UDM.
See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.