Home > SSAS > Attribute Discretisation and Bin Names

Attribute Discretisation and Bin Names


Attribute discretization (grouping or binning) can be used to group a large number of attributes into a single member. For example, we could create a group on the income attribute of the customer in AdventureWorks so that similar ranged incomes are grouped together. This post looks at how to implement attribute discretization and more importantly how to control the name of the attribute bins once they are created.

Creating Bins

Attribute Groups (or bins) can be created on any attribute through setting the DiscretizationBucketCount and DiscretizationMethod properties. Once the attribute is discretised, the detail values are no longer available as members for browsing and the detail data is placed aggregated into bins.

There are three methods available (AUTOMATIC, CLUSTERS and EQUALAREAS) which are discussed here. In this example, we will simply create 4 groups of EQUALAREAS on the [Yearly Income] attribute as shown in the picture below.

After processing, the [Yearly Income] attribute shows four values with the bounds of the group indentified in the name.

Before After Discretization

Controlling Bin Names

The bin names are created based on the bounds of the bins created. However, we may wish to control how the member names are displayed by adding some text so that the names are more user friendly.

This can be achieved by formatting the name the attribute, so we set the name property of the attribute (to ‘Yearly Income’) and specify the format content.The content allows the use of place holders that can retrieve information about the bins created. For example, we can retrieve the value of the bins lowest and highest value (or the next bins lowest value).

Furthermore, the format allows a different construct for the first, last and intermediate bins.

The naming template can specify the format for all members or, alternatively specify a template for each level (first, intermediate, last). For example, the template takes the form

<First definition> [;<Intermediate definition>;<Last definition>]

If the Intermediate and Last templates are not supplied, the first definition is applied to all members. Thus, the template

Between %{Bucket Min} and %{Bucket Max}
will produce the outcome shown below;

Before After Discretization First Template

An example of specifying each template level is;

Less Than %{Next bucket first member};Between %{Bucket Min} and %{Bucket Max}; Greater than %{Previous Bucket Max}

Which produces (the final result)

After Discretization First Template All Template

The allowed values for place holder (variables) are;

Variable Description
%{First bucket member} The member name of the first member to be included in the   current member group.
%{Last bucket member} The member name of the last member to be included in the   current member group.
%{Previous bucket last member} The member name of the last member assigned to the   previous member group.
%{Next bucket first member} The member name of the first member to be assigned to the   next member group.
%{Bucket Min} The minimum value of the members to be assigned to the   current member group.
%{Bucket Max} The maximum value of the members to be assigned to the   current member group.
%{Previous Bucket Max} The maximum value of the members to be assigned to the   previous member group.
%{Next Bucket Min} The minimum value of the members to be assigned to the   next member group.
Other Options
The use of discretised groups can reduce the amount of data that is shown to the user, and by using a naming template, we can have strict control over how these bins are displayed to the user. Further reading on TechNet can be found here.

Alternatively, groups can be created by including an additional attribute in the dimension. For example we could include a calculated member in the table or DSV or change the view to achieve the same effect (provided we knew what we wanted in advance). Another method may involve the use of a lookup table that specifies bands and their applicable values.

Categories: SSAS Tags:
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 228 other followers

%d bloggers like this: