Attribute Discretization: Customize Grouping Names

Join BI Architect Bill Pearson as he continues to explore attribute discretization, as a part of an extended examination of the dimensional model lying at the heart of the integrated Microsoft Business Intelligence solution.

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.

Procedure: Employ the Automatic Discretization Method for Attribute Members in Analysis Services

When we browse a cube, we typically dimension the members of one attribute hierarchy by the members of another attribute hierarchy. For example, we might group customer sales by product purchased, by customer geography (state, province or country), or by customer gender. However, with certain types of attributes, it is useful to have Analysis Services automatically create groupings of attribute members, particularly when large numbers of contiguous values are involved, based upon the distribution of the members within an attribute hierarchy.

As an example, let’s assume that we have been approached by representatives of our hypothetical client, the Adventure Works organization. These representatives, members of the Human Resources department of the business, tell us that one of the existing attributes of the Employee dimension, Vacation Hours, does not serve them well in browses and reports, as the many different values are simply listed: the members are simply derived from the unique Vacation Hours values in the VacationHours column of the DimEmployee table that underlies the Analysis Services layer of the business intelligence solution. (The developer of the cube, having returned to his home country, is no longer available to assist the HR department in modifying the Vacation Hours presentation into a more useful format.)

We listen to the description of the problem, and then suggest grouping the Vacation Hours information via the pre-defined Automatic discretization method offered within Analysis Services. We suggest this as a starter approach, so that our client colleagues can see how grouping the values in this manner might make the data more meaningful in browsing / reporting. We tell them that, once we do this, information consumers who browse the Vacation Hours attribute hierarchy will see the names / values of the groups instead of the members themselves. This limits the number of levels that are presented to users, which can be less confusing, and more useful for analysis. (We inform the client representatives, too, that other methods of discretization can be leveraged if the Automatic method proves less than ideal – we are simply suggesting the Automatic method as a good starting point to illustrate an approach to meeting the end objective – an approach that we can easily “tweak” once our colleagues understand the general concept of discretization, and can then make intelligent choices with regard to specific nuances that they might deem desirable.

Browse the Existing, Ungrouped Members within the Attribute Hierarchy

We will begin our practice with the Automatic discretization method within the Vacation Hours attribute hierarchy of the Employee dimension.

The tabs of the Dimension Designer open. Let’s look at the current state of the attribute under consideration, Vacation Hours.

The attributes belonging to the Vacation Hours attribute hierarchy appear as partially shown in Illustration 5.

We note that many contiguous values appear. It is easy to see how grouping these values into logical “buckets” might make the data easier for information consumers to analyze.

Add Automatic Discretization within the Attribute Hierarchy

We will enact the Automatic discretization method from the Dimension Structure tab of the Employee dimension.

The Properties window for the attribute appears (by default in the bottom right corner of the design environment), as depicted in Illustration 6.

The DiscretizationMethod property specifies the method used to group the members of the owner attribute. Once we tell Analysis Services the method of discretization, we can tell it how many “buckets” to create for purposes of grouping the attribute members.

The DiscretizationBucketCount property specifies the number of buckets in which to discretize members of the attribute to which the property belongs. The default setting is “0”. When we leave the DiscretizationBucketCount property at default, Analysis Services generates the number of groups independently, after sampling the underlying data involved.

The affected portion of the Properties window for the Vacation Hours attribute hierarchy appears as depicted (modifications surrounded by the red box) in Illustration 8.

We are now ready to process the Analysis Services database, and then to examine the results of our handiwork with the browsers, as before.

Process the Analysis Services Database to Update Structure for Our Discretization Settings

We will process the Analysis Services database within which we have been working, and then browse the Vacation Hours attribute members to confirm that we have provided preliminary answers to the client’s stated requirements.

Information updates on the server, and then the Process Database dialog appears, as shown in Illustration 11.

The Process Progress viewer appears, and generates periodic status updates for various processing events. When processing is complete, we see a Process succeeded message appear in the Status bar in the lower part of the viewer, as depicted in Illustration 12.

We are now ready to return to the Dimension Designer browser to examine the results of our handiwork.

Browse the Newly Discretized Attribute with the Dimension Browser

The browser details update, and (assuming the All Employees level remains expanded in the browser), we see ten groups appear, as depicted in Illustration 14.

We see the groups appear, as expected. Ten groups have been created, based upon the algorithm selected by the Automatic discretization method.

Browse the Newly Discretized Attribute with the Cube Browser

Let’s go one step further and examine the results of our selection of the Automatic discretization method from another practical perspective, that of the cube browser. This will give us an appreciation for the improvements seen by the information consumers in querying / analyzing from the affected data.

The tabs of the Cube Designer open, and we arrive, by default, at the Cube Structure tab.

We see all ten Vacation Hours buckets appear in the rows of the browser pane.

All ten Vacation Hours buckets continue to appear in the rows of the browser pane – with “+” sign “expand” buttons appearing to the immediate left of the bucket labels.

The two Vacation Hours buckets expand, revealing lists of the employee members whose total Vacation Hours place them within the respective buckets in which they appear, as partially depicted in Illustration 18.

In what is but one example of how we can use the Vacation Hours buckets, we can see lists of employees that have Vacation Hours on the books for each of the ten buckets we have created via Automatic discretization. We tell our client colleagues that they might use this arrangement to do far more than present lists of the members of the various strata. They might also flesh out the browser with other dimension members (such as Calendar Years, etc.), drop in various measures (such as Reseller Sales and the like), and perform analysis (as another simple example) upon employee balances of “unused vacation” when viewed within the perspective of the sales figures attributed to those employees.

Procedure: Employ the Naming Template to Support Custom Attribute Group Naming for Attribute Members in Analysis Services

The client representatives with whom we are working state that they are pleased with the overall results, but then they make one further request: they tell us that they would prefer that each of the “buckets” we have created to group the Vacation Hours be assigned a more explicit label. They tell us that they would rather that the label be “beefed up” to include more explanatory verbiage – in accordance the following example:

Instead of:

“0 - 8”

they would rather see:

“8 & Under”

and, for all members of the “intermediate ranges,” instead of, say,

“9 - 19”

They would rather see:

“Between 9 & 19 hours (incl)”.

Moreover, instead of

“90 - 99”

they would rather see:

“90 & Over”

We inform our client colleagues that Analysis Services generates member group names automatically when it creates the groups, based upon a default naming template, which is used unless we specify another naming template within the Format setting for the NameColumn property of an attribute, as we shall see. A separate naming template can be defined for each language specified in the Translations collection of the respective column underlying the NameColumn property of the attribute.

The Format setting uses the following string expression to define the naming template:

<Naming template> ::= <First definition> [;<Intermediate definition>;<Last definition>]

<First definition> ::= <Name expression>

<Intermediate defintion> ::= <Name expression>

<Last definition> ::= <Name expression>

The <First definition> parameter applies only to the first (or only, if there is only one) member group generated by the discretization method. If the optional parameters <Intermediate definition> and <Last definition> are not provided, the <First definition> parameter is used for all member groups generated for that attribute.

The <Last definition> parameter applies only to the last member group generated by the discretization method. The <Intermediate bucket name> parameter applies to every member group other than the first or last member group generated by the discretization method. (If two or fewer member groups are generated, this parameter is ignored.) The <Bucket name> parameter is a string expression that can incorporate a set of variables to represent member or member group information as part of the name of the member group, as presented in Table 1 below:

Use the Naming Template to Support Custom Attribute Group Naming

Let’s put what we have learned to work to help our client colleagues reach their objectives in providing more descriptive names for Employee Vacation Hours.

We note that the NameColumn setting is empty, leaving us to conclude that the member names are being derived from the KeyColumns setting.

The DataItem Collection Editor appears.

%{Last Bucket member} & Under; Between %{First Bucket member} & %{Last Bucket member} (incl); %{First Bucket member} & Over

An example of the intended output of each of the three delimited sections in our syntax above is presented in Table 2 below.

Our syntax partially appears within the Format property box of the DataItem Collection Editor as shown (modifications surrounded by the red box) in Illustration 21.

We are now ready to process the Analysis Services database, and then to examine the results of our handiwork with the browsers, as before.

Process the Analysis Services Database to Update for Our Custom Attribute Group Names

We will process the Analysis Services database within which we have been working, as we did earlier, and then browse the Vacation Hours attribute members, once again to confirm that we have adequately provided support for the custom naming requirements that our client colleagues have requested.

Information updates on the server, and then the Process Database dialog appears.

The Process Progress viewer appears, and generates periodic status updates for various processing events, as we noted earlier. When processing is complete, we see a Process succeeded message appear in the Status bar in the lower part of the viewer, once again.

We are now ready to return to the Dimension Designer browser to examine the results of our handiwork.

Browse the Newly Named Attribute Groups with the Dimension Browser

The browser details update, and (assuming the All Employees level remains expanded in the browser), we see ten groups appear, as depicted in Illustration 22.

We see the groups appear, as expected. Moreover, we note that the ten groups created by Analysis Services via the Automatic Discretization method, display the names in the format requested by the client representatives with which we have been working.

Browse the Newly Named Attribute Groups with the Cube Browser

Let’s go one step further and examine the results of use of the attributes group naming template from another practical perspective, that of the cube browser. As we noted in the similar browse of the earlier section, this will give us an appreciation for the improvements seen by the information consumers in querying / analyzing from the affected data.

We see all ten Vacation Hours buckets, with new custom labeling, appear in the rows of the browser pane.

All ten Vacation Hours buckets continue to appear in the rows of the browser pane – with “+” sign “expand” buttons appearing to the immediate left of the bucket labels.

The two Vacation Hours buckets expand, revealing lists of the employee members whose total Vacation Hours place them within the respective buckets in which they appear, as partially shown in Illustration 23.

We again emphasize to our client colleagues that they might use this arrangement to do far more than present lists of the members of the various strata. We also note that the naming template can be modified to meet similar, or even somewhat different, custom naming needs for the attribute groups they create via discretization, and encourage them to experiment further with the procedures we have examined together.

Having demonstrated the potential effects that we can achieve using the naming template, in conjunction with Automatic discretization (but certainly available to the other discretization methods, as well), we turn the development environment over to the client representatives with which we have worked. Our colleagues express satisfaction with our efforts, and state that they grasp the concepts adequately to leverage the naming template anytime they apply any of the discretization methods offered within Analysis Services to other attributes within their cubes.

Conclusion

In this article, we continued our exploration of discretization in Analysis Services, this time with the objective of introducing, and gaining some hands-on exposure to employing, custom naming for the attribute groups created within the various discretization methods. Our focus was to go beyond the First Group Member – Last Group Member default that Analysis Services uses in creating group labels during discretization, and enacting the generation of custom labels for our groups.

We first discussed the discretization options that are available (referring to individual articles covering each within this Database Journal subseries), before choosing to work with Automatic discretization in the sample cube, to serve as a basis for meeting the custom naming requirements of a hypothetical client. Our examination included a brief, general review 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. We performed an overview of the multiple pre-defined discretization processes supported within the Analysis Services UDM. We then began our practice session with an inspection, via the browser in the Dimension Designer, 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. We 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, 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 (and the default naming of the attribute groups) established by the most recent use of the Automatic discretization method.

Once we had a discretized the attribute members and examined the default naming of the resulting groups, we discussed the naming template supplied with Analysis Services, describing its parts and their uses. We next modified the template to meet the naming requirements requested by our hypothetical client, and placed the syntax within the Format property of the affected attribute. We then reprocessed the cube to enact the new naming template we had supplied, within the context of Automatic discretization of the selected attribute members. Finally, we performed 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.

About the MSSQL Server Analysis Services 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 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.

» See All Articles by Columnist William E. Pearson, III