Jaa

Automatically Grouping Attribute Members

  • Artikkeli

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.

After you specify a grouping method, you must specify the number of groups, by using the DiscretizationBucketCount property. For more information, see Grouping Attribute Members (Discretization)

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.

Grouping Attribute Hierarchy Members in the Customer Dimension

To group attribute hierarchy members in the Customer Dimension

  1. In Solution Explorer, double-click Customer in the Dimensions folder to open Dimension Designer for the Customer dimension.

  2. In the Data Source View pane, right-click the Customer table, and then click Explore Data.

    Notice the range of values for the YearlyIncome column. These values become the members of the Yearly Income attribute hierarchy, unless you enable member grouping.

  3. Close the Explore DimCustomer Table tab.

  4. In the Attributes pane, select Yearly Income.

  5. In the Properties window, change the value for the DiscretizationMethod property to Automatic and change the value for the DiscretizationBucketCount property to 5.

    The following image shows the modified properties for Yearly Income.

    Modified properties for Yearly Income

Grouping Attribute Hierarchy Members in the Employee Dimension

To group attribute hierarchy members in the Employee dimension

  1. Switch to Dimension Designer for the Employee dimension.

  2. In the Data Source View pane, right-click the Employee table, and then click Explore Data.

    Notice the values for the SickLeaveHours column and the VacationHours column.

  3. Close the Explore DimEmployee Table tab.

  4. In the Attributes pane, select Sick Leave Hours.

  5. In the Properties window, change the value for the DiscretizationMethod property to Clusters and change the value for the DiscretizationBucketCount property to 5.

  6. In the Attributes pane, select Vacation Hours.

  7. In the Properties window, change the value for the DiscretizationMethod property to Equal Areas and change the value for the DiscretizationBucketCount property to 5.

Browsing the Modified Attribute Hierarchies

To browse the modified attribute hierarchies

  1. On the Build menu of Business Intelligence Development Studio, click Deploy Analysis Services Tutorial.

  2. When deployment has successfully completed, switch to Cube Designer for the Analysis Services Tutorial cube, and then click Reconnect on the Browser tab.

  3. Remove all the levels of the Employees hierarchy from the row field area of the Data pane and remove all measures from the Data pane.

  4. Add the Internet Sales-Sales Amount measure to the data area of the Data pane.

  5. In the Metadata pane, expand the Product dimension, and then drag the Product Model Lines user hierarchy to the Drop Row Fields Here area of the Data pane.

  6. Expand the Customer dimension in the Metadata pane, expand the Demographic display folder, and then drag the Yearly Income attribute hierarchy to the Drop Column Fields Here area.

    Notice that the members of the Yearly Income attribute hierarchy are now grouped into six buckets, including a bucket for sales to customers whose yearly income is unknown.

  7. Remove the Yearly Income attribute hierarchy from the column area and remove the Internet Sales-Sales Amount measure of the Data pane.

  8. Add the Reseller Sales-Sales Amount measure to the data area.

  9. In the Metadata pane, expand Employee, expand Organization, right-click Sick Leave Hours, and then click Add to Column Area.

    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.

    The following image shows sales dimensioned by employee sick leave hours.

    Sales dimensioned by employee sick leave hours

  10. Remove the Sick Leave Hours attribute hierarchy from the column area of the Data pane.

  11. Add Vacation Hours to the column area of the Data pane.

    Notice that two groups appear, based on the equal areas grouping method. Three other groups are hidden because they contain no data values.

Modifying Grouping Properties and Reviewing the Effect of the Changes

To modify the grouping properties and review the effect of the changes

  1. Switch to Dimension Designer for the Employee dimension, and then select Vacation Hours in the Attributes pane.

  2. In the Properties window, change the value of the DiscretizationBucketCount property to 10.

  3. On the Build menu of BI Development Studio, click Deploy Analysis Services Tutorial.

  4. When deployment has successfully completed, switch back to Cube Designer for the Analysis Services Tutorial cube.

  5. Click Reconnect on the Browser tab, and then view the effect of the change to the grouping method.

    Notice that there are now three groups of members of the Vacation Hours attribute that have sales values for products. (The other 7 groups contain members with no sales data.)

Next Task in Lesson

Hiding and Disabling Attribute Hierarchies

See Also

Other Resources

Grouping Attribute Members (Discretization)

Help and Information

Getting SQL Server 2005 Assistance