SSAS Discretization Buckets – Grouping within the dimension

Did you know you can group data through SSAS inherently? Sometimes in business the user doesn’t want to see all the single values, but groups of those values. A customer’s yearly income or age, for example. The business doesn’t necessarily care that someone is 32 years old, but they do care that they are between the 25-34 year old age range. Or someone that makes $55,000 a year is within the range of $50,000-$75,000 a year, and households with that income are some of their best customers. One common answer to this problem is to create an additional column in your dimension and handle the grouping in your ETL, but let’s say you either can’t modify the ETL or would just prefer not to. Additionally, that’s a lot of extra work for something that’s already built in. There is a property at the attribute level within dimensions in Analysis Services that allow you to group your attribute members at run time. It’s called “Discretization”, and it’s very easy to set up.

 

First, click on the attribute you want to group. In this case, we’re grouping “Yearly Income” from the DimCustomers table in the AdventureWorks cube. In the properties window, look for 2 properties called DiscretizationBucketCount and DiscretizationMethod:

 

image

 

 

First enter the number of buckets you want to group your data into. For this example, I just selected 3 buckets with Equal Areas as the discretization method:

 

image

 

There are 2 other Methods as well, they are Automatic and Clusters.

    1. Equal Areas are when Analysis Services takes the members and tries to divide them into groups that contain equal numbers of members.
    2. Clusters are when Analysis Services takes the members, samples the data, and runs an Expectation Maximization Algorithm to determine the number of groups it needs. This is more expensive processing wise than Equal Areas is.
    3. Automatic is when Analysis Services decides whether it should use Clusters or Equal Areas. AS uses the created measure group and decides which discretization method is a better fit for the data.

 

That’s it. Takes 5 minutes to set up, and you get some nice grouping to see your data with.

Advertisements

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