SQL Server: SSAS – Important Attribute Properties Part 2

In the Part 1 we have checked the attribute properties that control the display and visibility of an attribute, properties related to the attribute column bindings and few other ones. In this post we shall see the properties controlling the sorting and grouping of attributes.

Properties that control sorting

• OrderBy – Defines the ordering of the members in the attribute hierarchy. The default ordering is by Name meaning the ordering is based on the value of the NameColumn property if it is defined. Else the ordering is done by the value of the Key column. The options available are Name, Key, AttributeName and AttributeKey

1. Key – In this case, the key column which can be a single column or more columns in case a composite key is used for sorting the members.
2. Name – When using the Name in the OrderyBy property, Analysis Services will order the attribute members in the alphanumeric order.
3. Secondary Attribute – In some cases, neither the key nor the name attribute provides the desired sort order in which case a secondary attribute can be used to fulfill the requirement. However, in order to sort an attribute by a secondary attribute name or key, the secondary attribute must be related to the primary attribute. It can be a standard or calculated column.

• OrderByAttribute – Identifies the attribute by which to order the members of the attribute hierarchy. Used when an AttributeKey or AttributeName is used in the OrderBy property.

Properties that control grouping

Analysis Services provides a facility to group attribute members by organizing them into groups which simplifies the cube browsing for the end users. The users can first drill down using higher level and go to the lower level. A member group is a system generated collection of successive dimension members which is formed using the process known as discretization. The members generated by Analysis Services to support member groups are called grouping members, and look like ordinary members.

Grouping attributes involve the following three attribute member properties which can be set using the dimension designer in BIDS.

• DiscretizationMethod – This property enables grouping. It controls how the members are grouped using the following values,

1. EqualAreas – This method divide the attribute members into groups so that each group contains approximately equal number of members.
2. Clusters – Uses the clustering algorithm to group members. It uses the training data for sampling. It works on any type of data and usually creates useful group but at the cost of higher processing time.
3. Automatic – Based on the data it selects whether to deploy EqualAreas or Clusters to group members.

• DiscretizationBucketCount – This property is used to define the number of groups to be created. The default is the square root of the number of distinct members. In case the property is set to the default value of 0, Analysis Services determines the number of groups to be created by either sampling or reading the data, depending on the setting of the DiscretizationMethod property.
• Naming Template – Allows creating our own naming templates to be applied to the member groups. By default, the member group names are automatically generated when the member groups are created using the default naming template. To specify a naming template, you must set the Format option for the NameColumn property of an attribute.

After processing the cube, the Size Range attribute is available with the values as follows,

Hope at least you get to know some of the attribute properties that an SSAS developer must know to start with.

Regards

Amit Karkhanis

Like us on FaceBook  |  Follow us on Twitter

Join the fastest growing SQL Server group on FaceBook