Dimension Attributes: Introduction and Overview, Part I - Page 2

Procedure: Examine Attribute Properties in Analysis Services 2005

In the practice procedures that follow, we will examine the properties that define and support a representative attribute. We will perform our practice sessions within the SQL Server Business Intelligence Development Studio, from which we will perform our examination of attribute properties within our Analysis Services database, ANSYS065_Basic AS DB.

In Dimensional Model Components: Dimensions Parts I and II, we overviewed the properties underpinning Database and Cube dimensions, once we had explored both types in general to gain an understanding of the characteristics of each, together with the differences between the two. In this article, we will overview the properties supporting dimension attributes. To access these settings for attributes within a representative dimension, we will need to open that dimension within the Dimension Designer first. (Because database dimensions, and not cube dimensions, contain attributes, we access properties supporting dimension attributes via the Dimension Designer, and not the Cube Designer.)

The tabs of the Dimension Designer open.

The attributes belonging to the Geography dimension appear as shown in Illustration 4.

We note that five attributes appear within the Attributes pane. Let's get some exposure to the properties associated with attributes by examining a representative member among the attributes we see here.

Review Attribute Properties

Analysis Services exposes many properties that determine how dimensions and dimension attributes function. We will examine the properties for a select attribute within our sample UDM by taking the following steps.

The Properties pane appears for the Geography Key attribute. (The Properties pane likely appeared when we selected the Product dimension within the Dimensions pane, by default, below the Solution Explorer. The design environment can, of course, be customized in many ways to accommodate your local environment and development needs.)

We can, at this stage, see the thirty DimensionAttribute properties for the Geography Key attribute within the Properties pane. The first eleven properties, members of the Advanced properties group, include the following:

• AttributeHierarchyDisplayFolder
• AttributeHierarchyEnabled
• AttributeHierarchyOptimizedState
• AttributeHierarchyVisible
• DefaultMember
• DiscretizationBucketCount
• DiscretizationMethod
• EstimatedCount
• IsAggregatable
• OrderBy
• OrderByAttribute

(We will examine the members of the Advanced properties group in this, the first half of a two-part article. We will examine the remaining properties in Part II.)

The five Basic properties, appearing underneath the Advanced properties group, include the following:

• Description
• ID
• Name
• Type
• Usage

The Misc group comes next, and includes the following four properties:

• AttributeHierarchyOrdered
• GroupingBehavior
• InstanceSelection
• MemberNamesUnique

Beneath the Misc group in the Properties pane lies the Parent-Child group, which includes the following five properties:

• MembersWithData
• MembersWithDataCaption
• NamingTemplate
• RootMemberIf
• UnaryOperatorColumn

Finally, the five Source properties, appearing underneath the Parent-Child properties group, include the following:

• CustomRollupColumn
• CustomRollupPropertiesColumn
• KeyColumns
• NameColumn
• ValueColumn

The Properties pane for the Geography Key attribute, with Advanced, Basic, Misc, Parent-Child, and Source properties groups expanded, appears as shown in Illustration 6.

Let's take a look at each of the individual properties, starting with the Advanced properties group, discussing the purpose of the property, and examining possible settings with which we can come into contact.

Advanced Property: AttributeHierarchyDisplayFolder

The AttributeHierarchyDisplayFolder property serves as a point of identification for the folder in which we intend to display the associated attribute hierarchy to information consumers. (This property is for use by client applications).

Advanced Property: AttributeHierarchyEnabled

The AttributeHierarchyEnabled property, with True or False as setting options, determines whether an attribute hierarchy is enabled for this attribute. If the attribute hierarchy is not enabled, then the attribute cannot be employed within a user-defined hierarchy, nor can the attribute hierarchy be referenced within MDX statements.

Advanced Property: AttributeHierarchyOptimizedState

The AttributeHierarchyOptimizedState property allows us to specify the level of optimization applied to the attribute hierarchy. Two options exist for this setting, as depicted in Illustration 7.

The attribute hierarchy is fully optimized (the top setting within the selector) by default. When our selection is FullyOptimized, Analysis Services is directed to build indexes for the hierarchy to improve query performance. The other selection, NotOptimized, means that these indexes are not built - an appropriate setting in cases where the attribute hierarchy is not used for querying per se, but is used for another purpose, such as the ordering of another attribute hierarchy.

Advanced Property: AttributeHierarchyVisible

AttributeHierarchyVisible controls whether the attribute hierarchy is visible to client applications. The default value is True. If we do not anticipate the need for querying the attribute hierarchy, however, we can change the value of this property to False, and still retain the capability to use the attribute hierarchy within a user-defined hierarchy, or to reference it within MDX statements.

Advanced Property: DefaultMember

The DefaultMember property allows us to specify an MDX expression that defines the default member for the attribute.

The Set Default Member dialog for the Geography Key appears.

Here we can select, via the associated radio buttons atop the dialog, a default member for the attribute, to be used for queries where members from the attribute are not explicitly specified. If we do not to specify a default member for the attribute, then the default member becomes the (All) member (assuming the presence of (All) member), or an arbitrary member if the (All) member is not present.

The pane underneath the radio button we have selected becomes enabled, and the Geography attribute members appear underneath the expanded All Geographies level, as depicted in Illustration 9.

We note the (grayed) appearance of the associated MDX expression for the selected default member in the box at the bottom of the dialog, underneath the radio button selection labeled Enter an MDX expression that specifies the default member. (We might have selected this button and entered an MDX expression manually, instead of building one through our selection within the tree above).

Advanced Property: DiscretizationBucketCount

The DiscretizationBucketCount property allows us to specify the number of buckets in which to discretize (group) members of the attribute. Discretization is the process of putting values of a continuous set of data into “buckets,” so that there are a discrete number of possible states. (Say we have numeric attribute with many continuous values. Presenting the member values grouped into buckets might be more useful to information consumers than displaying the values themselves.) The buckets are, in turn, treated as ordered and discrete values. We can discretize both string and numeric columns.

The method used to discretize the members is set by the next property we will examine, DiscretizationMethod.

Advanced Property: DiscretizationMethod

The DiscretizationMethod property allows us to define the method to be used for discretization. Analysis Services’ UDM supports multiple discretization algorithms which we can select via this property.