SSAS Dimension Attribute Properties : Part 1

Reposted from Jason Thomas blog with the author's permission.

There could not be any SSAS interview where at least one question is not asked about the dimension, attribute or measure properties. Many at times, I myself have kept on searching the web to find more information about a particular property only to find that the information is pretty scattered and you really have to spend some effort to consolidate all of them. Through this post, I will consolidate all relevant information regarding the attribute properties and try to put it under one roof.

A dimension attribute contains a limited list of key values. Each unique key is associated with a dimension member and these dimension members included in the attribute are called attribute members. In the multidimensional model, the attribute also contains properties that define different characteristics of the attribute members called attribute properties. All attribute members have the same properties. To see all the properties of an attribute, click on a particular attribute and press F4. On doing so, you would be able to see the property panel as shown below

Before you go forward and read, you might want to understand some of the terms that are going to keep coming in the explanations below:-

• Attribute Hierarchy : Attributes and Attribute Hierarchies (MSDN)
• Discrete Attributes and Contiguous Attributes : Quoted from Introduction to Attribute Discretization (William Pearson) - “ Whenever we work with attributes, we can expect to encounter two general types of values:
  • Discrete attributes: Discrete values stand apart distinctly, and have clearly defined logical “boundaries” between themselves. The possible values are naturally discrete for the lion’s share of attributes occurring in the business world.
    Example: The Gender attribute, within the Customer dimension of the Adventure Works sample UDM, is (at least for purposes of the sample cube) considered to have only one of two discrete values, female or male.
  • Contiguous attributes: Contiguous values do not stand apart distinctly, but flow along, as if in a continuous line. Contiguous values, especially within large populations, can have very large numbers of possible values. Information consumers can find it difficult to work effectively and efficiently within such wide ranges of values.
    Example: the Vacation Hours attribute, within the Employee dimension of the Adventure Works sample UDM, can have a wide range of possible values, depending upon how many employees are involved, whether there are limits on how many vacation days they can accumulate, and considerations of this nature. The member values are based directly upon the unique values contained within the VacationHours column of the DimEmployee table (with many of the unique values shared among multiple employees). The sheer number of values might make working with them cumbersome for information consumers, if they are simply made available in their existing state. “

The properties are explained below:-

ADVANCED

1) AttributeHierarchyDisplayFolder : This property is used to group attribute hierarchies into a particular folder. For eg, if you assign this particular property to Stocking for the attributes Class and Color in Product dimension, you would be able to see as shown below when you browse through the dimension

2) AttributeHierarchyEnabled : The values of this property can just be True or False and is used to determine the absence or presence of attribute hierarchies. By default, all the attributes in the dimension would be assigned a value of true which would mean that attribute hierarchies would be enabled by default. From a performance point of view, this property is pretty important which is quite evident from the quote given below from OLAP Design Best Practices for Analysis Services 2005

“ Attributes add to the complexity and storage requirements of a dimension, and the number of attributes in a dimension can significantly affect performance.  This is especially of attributes which have AttributeHierachyEnabled set to True. Although SQL Server 2005 Analysis Services can support many attributes in a dimension, having more attributes than are actually used decreases performance unnecessarily and can make the end-user experience more difficult.

It is usually not necessary to create an attribute for every column in a table. Even though the wizards do this by default in Analysis Services 2005, a better design approach is to start with the attributes you know you'll need, and later add more attributes.  Adding attributes as you discover they are needed is generally better a better practice than adding everything and then removing attributes. “

3) AttributeHierarchyOptimizedState : The values of this property can be FullyOptimized and NotOptimized (beats me why it shouldn’t have been a True or False instead) and is used to enable or disable the optimization of the hierarchy. Turning this property to a NotOptimized state would save resources when the hierarchy is being built during the processing and hence, should be turned off for those attributes which wouldn’t be used frequently for analysis. Turning this off would mean a slowdown in the requests that reference this attribute hierarchy which is the reason why we should just turn this off for less frequently used attributes.

4) AttributeHierarchyVisible :The values of this property can be True or False and is used to set the visibility of the attribute to a client application. If it is set as false, the client application would not be able to determine the presence of this attribute hierarchy (which means it would be invisible when the dimension is browsed through the application) but it would still be able to use the attribute hierarchy in queries. It is a best practice to make this property as False for those attributes which are also used in user defined hierarchies as anyways, you would be able to access that attribute from the user defined hierarchy and you can eliminate redundancy.

5) DefaultMember : This property can be used to specify the default member of the attribute. If no value is specified, then by default the ALL level is used. MDX expressions can also be used to set the default member of the attribute to any attribute member by clicking on the button besides the property which will open up the pop-up window as shown below

6) DiscretizationBucketCount : Discretization is basically the process of grouping contiguous values into sets of discrete values.

Analysis Services supports several variations of attribute discretizations based on different algorithms and to enable it, DiscretizationBucketCount is one of the two properties that you have to set. As the name suggests, this property defines the number of groups that the values will be placed in.

7) DiscretizationMethod : This property basically determines which discretization algorithm should be applied. The values of this property are

• None – Discretization would not be done.
• Automatic – Automatically chooses the best grouping technique among the available values.
• EqualArea – If the distribution of contiguous values are plotted as a curve, then the areas under the curve covered by wach range would be equal.
• Cluster – Uses the K-Means algorithm to find ranges on the input values.

Apart from the above algorithms present in the Dimension Editor, it is also possible to specify a user defined discretization method through Data Definition Language (DDL) by defining the boundaries of every group.

8) EstimatedCount : This property specifies the estimated number of members in the attribute. This is usually populated when aggregations are designed (which could mean that when you change environments, the counts could be of the previous environment unless you count again) or specified by the user. This property is used when aggregations are being built and helps the server to make decisions on how to make the aggregations.

9) IsAggregatable : This property is used to determine whether the data associated with the attribute members can be aggregated. If True, then the system will define an ALL level which will have the aggregated data which will be used as the default member when queries which doesn’t reference a particular member of this attribute are executed. For the same reason, if this property is set to False, then a default member should be specified.

10) OrderBy : This property specifies the method by which the attribute members should be sorted and the options are Name, Key, AttributeName and AttributeKey.

11) OrderByAttribute : This property is used to select the attribute on which the ordering should happen if AttributeKey or AttributeName has been used in the OrderBy property.

BASIC

1) Description : This property specifies the description of the attribute and any free text can be entered.

2) ID : This property specifies the unique identifier of the dimension and is non-editable usually in the dimension editor.

3) Name : This property specifies the name of the attribute.

4) Type : I found the best explanation of this property from an article of William Pearson which is quoted below :-

“ The value of the Type property for an attribute determines the attribute type – and specifies the type of information contained by - that attribute. Within Analysis Services 2005, attribute types help to classify an attribute based upon its business utility or functionality. Many of the available options represent types which are used by client applications to display or support an attribute. However, some attribute types also have specific meaning to Analysis Services. Some attribute types identify attributes that represent time periods in various calendars for time dimensions.

Many attribute types for dimensions or attributes are set via the associated wizard that we use when defining these objects. Attribute types can also be set when we employ wizards, such as the Business Intelligence Wizard, to add functionality to dimensions within Analysis Services. A good example is the application of various attribute types to attributes in a dimension when we use the Business Intelligence Wizard to add Account Intelligence to a given dimension: the wizard applies several attribute types to attributes in the affected dimension, for example, to identify attributes that contain the names, codes, numbers, and structure of accounts within the dimension.

Attribute types in Analysis Services fall in into five categorized groups. These attribute type groups include:

• General: These values are available to all attributes, and exist only to enable classification of attributes for client application purposes.
• Account Dimension: These values identify an attribute that belongs to an account dimension.
• Currency Dimension: These values identify an attribute that belongs to a currency dimension.
• Slowly Changing: These values identify an attribute that belongs to a slowly changing dimension.
• Time Dimension: These values identify an attribute that belongs to a time dimension. “

5) Usage : This property defines whether the attribute is a key attribute, an additional attribute for the dimension or a parent attribute.

(The rest of the properties would be continued in SSAS Dimension Attribute Properties : Part 2)

Jason has been working with Microsoft BI tools since he joined the IT industry in 2006. Since then he has worked across SSAS, SSRS and SSIS for a large number of clients. He is currently working for Mariner and is based out of Charlotte, NC. His personal blog can be found at http://road-blogs.blogspot.com