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:
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:
There are 2 other Methods as well, they are Automatic and Clusters.
- Equal Areas are when Analysis Services takes the members and tries to divide them into groups that contain equal numbers of members.
- 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.
- 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.