Attribute Properties - Group Attribute Members

Article
12/09/2022

Applies to: SQL Server Analysis Services Azure Analysis Services Fabric/Power BI Premium

A member group is a system-generated collection of consecutive dimension members. In Microsoft SQL Server SQL Server Analysis Services, members of an attribute can be grouped into a number of member groups through a process called discretization. A level in a hierarchy contains either member groups or members, but not both. When business users browse a level that contains member groups, they see the names and cell values of the member groups. The members generated by SQL Server Analysis Services to support member groups are called grouping members, and look like ordinary members.

The DiscretizationMethod property on an attribute controls how the members are grouped.

DiscretizationMethod Setting	Description
None	Displays the members.
Automatic	Selects the method that best represents the data: either the EqualAreas method or the Clusters method.
EqualAreas	Tries to divide the members in the attribute into groups that contain an equal number of members.
Clusters	Tries to divide the members in the attribute into groups by sampling the training data, initializing to a number of random points, and running several iterations of the Expectation-Maximization (EM) clustering algorithm. This method is useful because it works on any distribution curve, but is more expensive in terms of processing time.

The DiscretizationNumber property on attributes specifies the number of groups to display. If the property is set to the default value of 0, SQL Server Analysis Services determines the number of groups by either sampling or reading the data, depending on the setting of the DiscretizationMethod property.

The sort order of members in the member groups is controlled by using the OrderBy property of the attribute. Based on this sort order, the members in a member group are ordered consecutively.

A common use for member groups is to drill down from a level with few members to a level with many members. To enable users to drill down between levels, change the DiscretizationMethod property on the attribute for the level that contains numerous members from None to one of the discretization methods described in the previous table. For example, a Client dimension contains a Client Name attribute hierarchy with 500,000 members. You can rename this attribute Client Groups and set the DiscretizationMethod property to Automatic to display member groups on the attribute hierarchy member level.

To drill down to individual clients in each group, you can create another Client Name attribute hierarchy bound to the same table column. Then, create a new user hierarchy based on the two attributes. The top level would be based on the Client Groups attribute and the lower level would be based on the Client Name attribute. The IsAggregatable property would be True on both attributes. The user could then expand the (All) level on the hierarchy to view the group members, and expand the group members to view the leaf members of the hierarchy. To hide either group or client level, you could set the AttributeHierarchyVisible property to False on the corresponding attribute.

Naming Template

Member group names are generated automatically when the member groups are created. Unless you specify a naming template, the default naming template is used. You can change this method of naming by specifying a naming template in the Format option for the NameColumn property of an attribute. Different naming templates can be redefined for every language specified in the Translations collection of the column binding that was used for the NameColumn property of the attribute.

The Format setting uses the following string expression to define the naming template:

<Naming template> ::= <First definition> [;<Intermediate definition>;<Last definition>]

<First definition> ::= <Name expression>

<Intermediate definition> ::= <Name expression>

<Last definition> ::= <Name expression>

The <First definition> parameter applies only to the first or only member group generated by the discretization method. If the optional parameters <Intermediate definition> and <Last definition> are not provided, the <First definition> parameter is used for all measure groups generated for that attribute.

The <Last definition> parameter applies only to the last member group generated by the discretization method.

The <Intermediate bucket name> parameter applies to every member group other than the first or last member group generated by the discretization method. If two or fewer member groups are generated, this parameter is ignored.

The <Bucket name> parameter is a string expression that can incorporate a set of variables to represent member or member group information as part of the name of the member group:

Variable	Description
%{First bucket member}	The member name of the first member to be included in the current member group.
%{Last bucket member}	The member name of the last member to be included in the current member group.
%{Previous bucket last member}	The member name of the last member assigned to the previous member group.
%{Next bucket first member}	The member name of the first member to be assigned to the next member group.
%{Bucket Min}	The minimum value of the members to be assigned to the current member group.
%{Bucket Max}	The maximum value of the members to be assigned to the current member group.
%{Previous Bucket Max}	The maximum value of the members to be assigned to the previous member group.
%{Next Bucket Min}	The minimum value of the members to be assigned to the next member group.

The default naming template is "%{First bucket member} - %{Last bucket member}", to provide compatibility with earlier versions of SQL Server Analysis Services.

Note

To include a semicolon (;) as a literal character in the naming template, prefix it with the percent (%) character.

Example

The following string expression could be used to classify the Yearly Income attribute of the Customer dimension in the Adventure Works DW Multidimensional 2012 sample SQL Server Analysis Services database, where the Yearly Income attribute uses member grouping:

"Less than %{Next Bucket Min};Between %{First bucket member} and %{Last bucket member};Greater than %{Previous Bucket Max}"

Adding New Members to Existing Member Groups

If new members are added to the dimension, they are assigned to the appropriate member groups by comparing the value of the member against the current member group layout.

If a member is inserted between the last member of the previous member group and the first member of the next member group, the new member becomes the last member of the previous member group.

Updating a Dimension with Discretized Attributes

When you process a dimension, a discretized attribute is rediscretized only with a full update (ProcessFull). To rediscretize an attribute, you must perform a full update of the dimension. If the dimension table of a discretized attribute is updated and you process the dimension with an incremental update (ProcessAdd), the discretized attribute is not rediscretized. The names and children of the new buckets remain the same. For more information about processing dimensions, see Processing Analysis Services Objects.

Usage Limitations

You cannot create member groups in the topmost or bottommost level of a hierarchy. However, if you need to do this, you can add a level in such a way that the level in which you want to create member groups is no longer the top or bottom level. You can hide the added level by setting its Visible property to False.
You cannot create member groups in two consecutive levels of a hierarchy.
Member groups are not supported for dimensions that use the ROLAP storage mode.
If the dimension table of a dimension that contains member groups is updated, and the dimension is subsequently fully processed, a new set of member groups is generated. The names and children of the new member groups may be different from the old member groups.