SQL Server Analysis Service-discretization)

In SQL Server Analysis Service, dimension is a very important concept. Generally, a dimension contains multiple attributes, each of which is an object (such as customer and product). In this way, all possible values are listed for specific attributes of each dimension. In some cases, these attributes have millions of values and do not have any hierarchy, which is very difficult for business personnel to find a specific value, if they can be grouped into structures such as year-quater-month-date, but hierarchy is not used, it will be very simple to search for them. This introduces the concept of grouping. For example, for an annual income in a unit with millions of employees, the wage income of each person varies in a large range, in this way, the yearly income attribute has many values. In reality, we may prefer to limit these statistical values based on a specific interval. The concept of grouping is to classify these attribute values by intervals.

 

A member group is a set of consecutive dimension members generated by the system. In Microsoft SQL Server Analysis Services, attribute members can be divided into several member groups by a process named discretization. The level or member group in the hierarchy, or including members, but not both. When a business user browses a level that contains a member group, the name and unit value of the member group are displayed. Members generated by analysis services to support member groups are called group members, which appear to be the same as common members.

 

By using the discretizationmethod and discretizationbucketcount attributes, Analysis Service can automatically create a group for you. UseDiscretizationmethodAttribute control:

 

& Lt; TD width = "514" valign = "TOP" & gt; & lt;/TD & gt;

discretizationmethod Settings
none	display members, not grouped.
automatic	select the optimal data representation: areareas method or clusters method.
region areas	try to divide the members in the attribute into groups with the same number of members.
clusters	try to maximize the expectation (EM) by sampling the finalized data, initializing a large number of random points, and running several times) clustering Analysis algorithms are iterated to divide Members in attributes into several groups. the advantage of this method is that it is applicable to any distribution curve, but it has a large overhead in terms of processing time.

For example, for adventure works dw2008, when I set the discretizationmethod attribute of yearly income to automatic, the following groups will be created:

But obviously, this makes it impossible for you to control the range and display name as you like (even if you can use name template ). In some cases, if you want to, you can also add a computing column to the database table for control, which makes it easier for you to determine their range and name.

Case when weight is null or weight <0 then 'n'/'

When weight <10 then '0-10Kg'

When weight <20 then '10-20Kg'

Else '2014 or more'

End

Naming Template

When a member group is created, the name of the member group is automatically generated. Unless you specify a naming template, use the default naming template. ByNamecolumnAttributeFormatYou can change the naming method. You can bind columns (for featureNamecolumnAttribute)TranslationsThe specified language in the set to redefine different naming templates. The default naming template is"% {First bucket member}-% {last bucket member }".

 

Variable	Description
% {First bucket member}	The name of the first Member in the current member group.
% {Last bucket member}	The name of the last member in the current member group.
% {Previous bucket last member}	Name of the last member allocated to the previous member group.
% {Next bucket first member}	Name of the first member allocated to the next member group.
% {Bucket min}	The minimum number of members to be allocated to the current member group.
% {Bucket max}	The maximum number of members to be allocated to the current member group.
% {Previous bucket max}	The maximum number of members to be allocated to the previous member group.
% {Next bucket min}	The minimum number of members to be allocated to the next member group.

For example, we set the format option of the namecolumn attribute of yearlyincome to "less than % {next bucket min}; between % {first bucket member} and % {last bucket member }; greater than % {previous bucket max} ", the group name will change. We can use name template to change the name of each group:

 

Update a dimension using the discretization attribute

when processing a dimension, the discretization attribute is only re-discretization in completely updated (processfull. To re-discretization attributes, the dimension must be completely updated. If the dimension table of the discretization attribute has been updated and you use processadd to process the dimension, the discretization attribute is not re-discretization. The name and sub-level of the new storage remain unchanged.