4.3 automatically group attribute members

When browsing multi-dimensional data sets, the dimension of members of another attribute hierarchy is usually determined based on the members of one attribute hierarchy. For example, you can group customer sales by city, purchased product, or gender. However, for certain types of attributes, it is useful to automatically create Attribute member groups by Microsoft SQL Server 2005 Analysis Services (SSAs) based on the member DISTRIBUTION IN THE Attribute Hierarchy. For example, you can ask analysis services to create an annual income value group for the customer. When this operation is performed, the user who browses the Attribute Hierarchy will see the group name and value, rather than the member itself. This limits the number of levels displayed to the user, which is more helpful for analysis.

DiscretizationmethodAttribute to determine whether Analysis Services executes the group and the type of the group to be executed. By default, analysis services does not execute any groups. If Automatic Grouping is enabled, analysis services can automatically determine the optimal grouping method based on the structure of the attribute, or select a group in the list below.AlgorithmTo specify the grouping method:

Region Areas

Analysis Services creates a group range, where all dimension members are evenly distributed between groups.

Clusters

Analysis Services uses the K-means clustering analysis method and Gaussian distribution to perform single-dimensional Clustering Analysis on input values to create groups. This option is only valid for numeric columns.

After specifying a grouping method, you must useDiscretizationbucketcountAttribute specifies the number of groups. For more information, see

In tasks of this topic, different types of grouping methods will be enabled for the following objects: annual income value in the customer dimension; employee sick leave hours in the employee dimension; the number of employees' vacation hours in the "employee" dimension. Then, process and browse the Analysis Services tutorial multi-dimensional dataset to view the situation of each member group. Finally, modify the parameters of the member group to view the effect of the group type change.

Group members of the Attribute Hierarchy in the "customer" dimension
Group members of the Attribute Hierarchy in the "customer" dimension

2. In Solution Explorer, double-click"Dimension"Folder"Customer"To open the dimension designer for the customer dimension.

4. In"Data Source view"Right-clickCustomerTable, and then click"Browse data".

   Note,YearlyincomeThe range of column values. If the member group is not enabled, these values become"Annual income"A member of the attribute hierarchy.

6. Close"Browse the dimcustomer table"Tab.

8. In"Attribute"In the pane, select"Annual income".

10. In the "properties" window, SetDiscretizationmethodAttribute Value changedAutomatic, SetDiscretizationbucketcountAttribute Value changed5.

    The modified"Annual income"Attribute.

Is an Attribute Hierarchy member group in the "employee" dimension.
Is an Attribute Hierarchy member group in the "employee" dimension.

2. Switch to the "employee" dimension designer.

4. In"Data Source view"Right-clickEmployeeTable, and then click"Browse data".

   Note:SickleavehoursColumn andVacationhoursColumn value.

6. Close"Browse dimemployee table"Tab.

8. In"Attribute"In the pane, select"Sick leave time".

10. In the "properties" window, SetDiscretizationmethodAttribute Value changedClusters, SetDiscretizationbucketcountAttribute Value changed5.

12. In"Attribute"In the pane, select"Vacation time".

14. In the "properties" window, SetDiscretizationmethodAttribute Value changedEqual areas, SetDiscretizationbucketcountAttribute Value changed5.

Browse modified attribute hierarchies
Browse modified attribute hierarchies

2. In business intelligence development studio"Generate"Click"Deployment Analysis Services tutorial".

4. After the configuration is completed, switch to the Multidimensional Dataset Designer of the Analysis Services tutorial cube, and click"Browse"Tab"Reconnect".

6. Slave"Data"Delete row fields in the pane"Employee"All levels of the hierarchy, and from"Data"Delete all measurement values in the pane.

8. Set"Internet sales"Add metric values"Data"The data area of the pane.

10. In"Metadata"In the pane, expand"Product"Dimension"Product model series"User hierarchyDrag"Data"Pane"Drag the row field here"Region.

12. Expand"Metadata"In the pane"Customer"Dimension, and then expand"Population Statistics"Show the folder, and then"Annual income"Drag the Attribute Hierarchy"Drag column fields here"Region.

    Note,Yearly incomeAttribute hierarchies are now grouped into six buckets, including a bucket for sale by customers whose annual income is unknown.

14. Delete from column area"Annual income"Attribute Hierarchy, from"Data"Delete in the pane"Internet sales"Measurement value.

16. Set"Distributor sales"Add the measurement value to the data area.

18. In"Metadata"In the pane, expand"Employee", And then expand"Organization", Right-click"Sick leave time"And then click"Add to column area".

    Note that all sales are performed by employees in one of the two groups. (To view the three groups without sales personnel, right-click the data area and then click"Show empty cells"). Note that sales of employees with 32-42 hours of sick leave are much more than those with 20-31 hours of sick leave.

    Shows sales by the number of employees' sick leave hours.

20. Slave"Data"Delete column areas in the pane"Sick leave time"Attribute Hierarchy.

22. Set"Vacation time"Add"Data"The column area of the pane.

    Note: two groups are displayed based on the same regional grouping method. The other three groups are hidden because they do not contain data values.

Modify group attributes and check the changes
Modify group attributes and check the changes

2. Switch to the "employee" dimension designer, and then"Attribute"Select"Vacation time".

4. In the "properties" window, SetDiscretizationbucketcountProperty value changed10.

6. In bi development studio"Generate"Click"Deployment Analysis Services tutorial".

8. After the deployment is completed, switch back to the multidimensional cube designer of the Analysis Services tutorial cube.

10. In"Browser"Click"Reconnect"And then view the effect of changing the grouping method.

    Note: Currently, three groups have"Vacation time"Property members, all of which have product sales values. (The other seven groups contain members without sales data .)