Attribute Discretization: Customize Grouping Names

This article
continues my exploration of attribute discretization, a capability in Analysis
Services that allows us to group members of an attribute into a number of member
groups. Our concentration here will be to go beyond the individual
examinations of the methods themselves that we undertook in other articles, and
to get some exposure to custom naming of the groups created by the discretization
process we choose. The practice we undertake to this end will, once again,
surround a representative dimension attribute within our sample UDM.

Introduction

This article continues the overview of Attribute Discretization
in Analysis Services we began in Introduction
to Attribute Discretization, and continued in Attribute
Discretization: Using the Automatic Method, Attribute
Discretization: Using the “Equal Areas” Method and Attribute
Discretization: Using the Clusters Method. Both this article and its Discretization-related
predecessors extend the examination of the dimensional model begun in Dimensional
Model Components: Dimensions Parts I and II, and
continued
through Dimensional Attributes: Introduction and
Overview Parts I through V, as well as our focus upon the properties underlying attributes,
extending our overview into attribute member Keys, Names, Values and Relationships
within several subsequent articles.

In Introduction
to Attribute Discretization, Attribute Discretization: Using the
Automatic Method, Attribute Discretization: Using the “Equal Areas” Method and
Attribute Discretization: Using the Clusters Method, I summarized preceding articles within the current
subseries, consisting of a general introduction to the dimensional model. I
noted the wide acceptance of the dimensional model as the preferred structure
for presenting quantitative and other organizational data to information
consumers. The articles of the series then undertook an examination of dimensions,
and, subsequently, dimension attributes, the analytical “perspectives” and
structures upon which the dimensional model relies in meeting the primary
objectives of business intelligence, including its capacity to support:

• the
  presentation of relevant and accurate information representing business
  operations and events;
• the rapid and
  accurate return of query results;
• “slice and
  dice” query creation and modification;
• an environment
  wherein information consumers can pose questions quickly and easily, and obtain
  rapid results datasets.

With Introduction to Attribute
Discretization, we introduced a capability in Analysis Services – to
which we refer as attribute discretization – that allows us to group members of
an attribute into a number of member groups. We discussed design and other
considerations involved in the discretization of attributes, and touched upon
best practices surrounding the use of this capability.
In Attribute Discretization: Using the
Automatic Method, we introduced the first of multiple pre-defined discretization
methods supported within the Analysis Services UDM. We discussed the options
that are available, focusing upon the employment of the Automatic discretization
method within the sample cube, to meet the business requirements of a
hypothetical client. We then began our practice session with an inspection of the contiguous members of a
select attribute hierarchy, noting the absence of grouping and discussing shortcomings
of this default arrangement. Next, we enabled the Automatic discretization
method within the dimension attribute Properties pane, and then reprocessed the
sample cube with which we were working to enact the new Automatic
discretization of the select attribute members. Finally, we performed further
inspections of the members of the attribute hierarchy involved in the request
for assistance by our hypothetical client, noting the new, more intuitive
grouping established by the newly enacted Automatic discretization method.

Next, in Attribute
Discretization: Using the Equal Areas Method, we introduced the
second of the pre-defined discretization methods supported within the Analysis
Services UDM. We discussed the options that are available with this particular
approach, as we did in the article previous for the Automatic method, focusing
upon the employment of the Equal Areas discretization method, again within the
sample cube, to meet the business requirements of a hypothetical client. We
then began our practice session with an inspection, via the browser in the Dimension
Designer, of the contiguous members of another select attribute hierarchy,
noting the absence of grouping and discussing shortcomings of this default
arrangement. Next, we enabled the Equal Areas discretization method within the
dimension attribute Properties pane, and again reprocessed the sample cube with
which we were working to enact the new Equal Areas discretization of the select
attribute members. Finally, we performed additional inspections, via the Dimension
Designer and Cube Designer browsers, of the members of the attribute hierarchy involved
in the request for assistance by our hypothetical client, noting the new, more
intuitive grouping established by the newly enacted Equal Areas discretization method.

In last month’s article, Attribute
Discretization: Using the Clusters Method, we introduced the third
of the pre-defined discretization methods supported within the Analysis
Services UDM. We again discussed the options that are available with this
particular approach, as we did in the previous articles for the Automatic and Equal
Areas methods, focusing this time upon the employment of the Clusters
discretization method, to meet the business requirements of a hypothetical
client within the sample cube. Our practice session again followed with an
inspection of the “pre-discretized”
contiguous members of another select attribute hierarchy, where we again noted
the absence of grouping and discussed shortcomings of this default
arrangement. We then enabled the Clusters discretization method within the
dimension attribute Properties pane, and after reprocessing the sample cube
with which we were working, we performed another inspection, again via the Dimension
Designer and Cube Designer browsers, of the members of the affected attribute
hierarchy, noting the more intuitive grouping established by the newly enacted Clusters
discretization method.

In this article, we will gain some hands-on exposure to going
beyond the First Group Member – Last Group Member default that Analysis
Services uses in creating group labels within the various discretization
methods, and enacting the generation of custom labels for our groups. Our examination will include:

• A brief review
  (for those joining our “discretization subseries” for the first time) of attribute
  discretization in Analysis Services, potential benefits that accrue from discretization
  in our UDMs, and how the process can help us to meet the primary objectives of
  business intelligence.
• Examination,
  via the browser in the Dimension Designer, of the pre-existing members of a
  select attribute hierarchy, noting the absence of grouping and discussing
  shortcomings of this default arrangement.
• A discussion
  of customized discretization group labeling within Analysis Services.
• Enablement of
  the Automatic discretization method within the dimension attribute Properties
  pane.
• Reprocessing
  the cube to enact the new Automatic discretization of the select attribute
  members.
• Another
  examination, via the browsers in both the Dimension Designer and the Cube
  Designer, of the members of our attribute hierarchy selection, noting the new,
  more intuitive grouping established by the newly enacted Automatic
  discretization method, together with the default labels provided by Analysis
  Services.
• Modification
  of a copy of the naming template supplied with Analysis Services
• Reprocessing
  the cube to enact the modified naming template we have supplied, within the
  context of Automatic discretization of the selected attribute members.
• A final
  examination, via the browsers in both the Dimension Designer and the Cube
  Designer, of the members of a select attribute hierarchy, noting the new, more
  intuitive grouping, together with more user-friendly labels, established by the
  newly enacted Automatic discretization method.

Attribute Discretization with Custom Group Names

In this article, we will perform attribute discretization
once again, but in this practice session, we will extend hands-on exposure with
discretization to include the addition of custom group labels.

For those first joining my subset of articles surrounding discretization,
let’s do a brief overview of Analysis Services discretization in general. (Those
who have “been along for the ride” can, of course, skip directly to the
sections below.) As we learned in Introduction to
Attribute Discretization, whenever we work with attributes, we can
expect to encounter two general types of values, discrete and contiguous. Discrete
values stand apart distinctly, and have clearly defined logical “boundaries”
between themselves. Citing the Gender attribute, within the Customer dimension
of the Adventure Works sample UDM, wherein the attribute is considered to have
only one of two discrete values,
female or male, we noted that possible values are naturally discrete for the
lion’s share of attributes occurring in the business world.

In contrast to discrete values, we noted that contiguous
values do not stand apart distinctly, but flow along, as if in a continuous
line. Moreover, we discussed the fact that contiguous values, especially within
large populations, can have very large numbers of possible values, and that
information consumers can find it difficult to work effectively and efficiently
within such wide ranges of values. As an example, we cited the Vacation Hours
attribute, within the Employee dimension of the Adventure Works sample UDM, an
attribute which could have a wide range of possible values, depending upon how
many employees are involved, whether there are limits on how many vacation days
they can accumulate, and considerations of this nature. (The member values are
based directly upon the values contained within the VacationHours column of the
DimEmployee table – with many of the values shared among multiple employees.)
The sheer number of values might make working with them cumbersome for
information consumers, if they are simply made available in their existing
state.

As we discussed in Introduction
to Attribute Discretization, discretization can help us to make it
easier for information consumers to work with large numbers of possible attribute
member values. As discretization creates a manageable number of groups of attribute
values that are clearly separated by boundaries, we can thereby group contiguous
values into sets of discrete values, via a system-generated collection
of consecutive dimension members known as member groups.

We also discussed, in Introduction
to Attribute Discretization, that once the discretization process groups the attribute
members into the member groups, the member groups are then housed within a
level within the dimensional hierarchy. (A given level within a dimensional
hierarchy can contain either members or member groups, but not both.) When information
consumers browse a level that contains member groups, they see the names and
cell values of the member groups. The members generated by Analysis Services to
support member groups are called grouping members, and they look like ordinary
members.

Analysis Services affords us flexibility in methods of attribute
discretization, based upon algorithms of varying complexity. The different
methods of discretization all have the same function – to group contiguous
values into sets of discrete values. They simply manage grouping via different
approaches. Beyond the Dimension Designer, Analysis Services also supports
user-defined discretization, via data definition language (DDL), should the
“out – of – the – box” approaches not meet the business needs of our local
environments. Moreover, we can alternatively implement custom discretization
via the underlying data warehouse, using views at the relational level; named
calculations in the data source view; calculated members in Analysis Services;
or via other approaches.

Analysis Services supports four DiscretizationMethod
property settings, which include three pre-defined discretization methods:

• None (default):
  Analysis Services performs no grouping, and simply displays the attribute
  members.
• Automatic:
  Analysis Services selects the method that best represents the data: either the EqualAreas
  method or the Clusters method.
• EqualAreas:
  Analysis Services attempts to divide the attribute members into groups that
  contain an equal number of members.
• Clusters:
  Analysis Services attempts to divide the members of the attribute into groups
  that contain an equal number of members. (Per the Books Online, this
  method is useful because it works on any distribution curve, but is more
  expensive in terms of processing time.)

The DiscretizationMethod property determines whether Analysis
Services is to create groupings, and then determines the type of grouping that
is performed. As we learned in Introduction to
Attribute Discretization, Analysis Services
does not perform any groupings by default (the default setting for the DiscretizationMethod
property is “None”). When we enable Automatic grouping, as we shall see in the
practice session that follows, we direct Analysis Services to automatically
determine the best grouping method based upon the structure of the attribute
for which we are performing discretization. We then process the affected
dimension / cube and Analysis Services creates group ranges, and then
distributes the total population of attribute members appropriately across
those groups. As we shall see, once we specify a grouping method, we next
specify the number of groups, by using the DiscretizationBucketCount property
(its default value is zero).

As we
have noted throughout my MSSQL Server Analysis
Services column, as well as throughout
my other Database Journal series’, one of the most important objectives
in building a high performance Analysis Services solution is an efficient and
effective dimension design. The identification of opportunities where we can
effectively use attribute discretization, and the effective design, placement
and labeling of the member groups that we generate thereby, can mean the
provision of a much more consumer-friendly interface for our clients and
employers. We will
gain hands – on exposure to the Automatic method of attribute discretization in the practice
session that follows, primarily as a means of supporting the focus of our
article, the custom naming of groups, versus the simple acceptance of the
default names provided by Analysis Services, anytime we discretize attributes. We will first enable Automatic
discretization, and then process the cube, which we will examine for default
naming of the attribute groups created. We will then focus upon the setup for customized
group labeling, reprocess the cube, and then examine the changes in the naming
of our attribute groups.

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

In Dimensional Model 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 the earlier articles of this subseries)) by taking
the following steps within the SQL Server Business Intelligence Development
Studio.

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.

The Connect
to Database dialog appears.

SQL
Server Business Intelligence Development Studio briefly reads the database from
the Analysis Server, and then we see the Solution Explorer populated with the constituent
database objects. Having overviewed attribute discretization in this and the
previous article, we will now get some hands-on exposure to the use of Automatic
attribute discretization for the members of a representative dimension attribute
within our practice UDM. Our objective will be to then examine the default
labeling of the newly created attribute groups, before enabling the custom
labeling of the same attribute groups through the use of the naming template
supplied by Analysis Services, reprocessing the cube, and re-examining the
attribute groups for the custom naming we have enacted.