Automatically Grouping Attribute Members

When you browse a cube, you typically dimension the members of one attribute hierarchy by the members of another attribute hierarchy. For example, you might group customer sales by city, by product purchased, or by gender. However, with certain types of attributes, it is useful to have Microsoft SQL Server 2005 Analysis Services (SSAS) automatically create groupings of attribute members based on the distribution of the members within an attribute hierarchy. For example, you can have Analysis Services create groups of yearly income values for customers. When you do this, users who browse the attribute hierarchy will see the names and values of the groups instead of the members themselves. This limits the number of levels that are presented to users, which can be more useful for analysis.

The DiscretizationMethod property determines whether Analysis Services performs grouping, and determines the type of grouping that is performed. By default, Analysis Services does not perform any groupings. When you enable automatic groupings, you can allow Analysis Services to automatically determine the best grouping method based on the structure of the attribute, or you can choose one of the grouping algorithms in the following list to specify the grouping method:

EqualAreas

Analysis Services creates group ranges so that the total population of dimension members is distributed equally across the groups.

Clusters

Analysis Services creates groups by performing single-dimensional clustering on the input values by using the K-Means clustering method with Gaussian distributions. This option is valid only for numeric columns.

In the tasks in this topic, you will enable different types of groupings for the following: the yearly income values in the Customer dimension; the number of employee sick leave hours in the Employees dimension; and the number of employee vacation hours in the Employees dimension. You will then process and browse the Analysis Services Tutorial cube to view the effect of the member groups. Finally, you will modify the member group properties to see the effect of the change in grouping type.

Notice that all sales are made by employees within one of two groups. (If you want to see the three groups that have no sales, right-click the data area and then click Show Empty Cells). Notice also that the employees with 32 - 42 sick leave hours made significantly more sales than employees with 20 - 31 sick leave hours.