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.)
Within the Solution
Explorer, right-click the Geography dimension (expand the Dimensions
folder as necessary).
on the context menu that appears, as depicted in Illustration 3.
3: Opening the Dimension via the Dimension Designer ...
tabs of the Dimension Designer open.
Click the Dimension
Structure tab, if we have not already arrived there by default.
member attributes that appear within the Attributes pane of the Dimension Structure tab.
The attributes belonging to the Geography dimension appear as shown in Illustration
4: The Member Attributes, Geography Dimension
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.
Within the Attributes
pane of the Dimension Structure tab, right-click the Geography
on the context menu that appears, as depicted in Illustration 5.
5: Select Properties from the Context Menu ...
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.)
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:
(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.)
Basic properties, appearing underneath the Advanced properties
group, include the following:
group comes next, and includes the following four properties:
the Misc group in the Properties pane lies the Parent-Child
group, which includes the following five properties:
the five Source properties, appearing underneath the Parent-Child
properties group, include the following:
pane for the Geography Key attribute, with Advanced, Basic,
Misc, Parent-Child, and Source properties groups expanded,
appears as shown in Illustration
6: The Properties Pane for the Geography Key Attribute
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.
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).
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.
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.
7: Optional Settings for the AttributeHierarchyOptimizedState Property
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
a user-defined hierarchy, or to reference it within MDX statements.
The DefaultMember property allows us to
MDX expression that defines the default member for the attribute.
Click the ellipses
(...) button to the right of the setting box for the DefaultMember property, shown circled in Illustration 8.
Illustration 8: Click
the Ellipses Button to the Right of the DefaultMember Property
Default Member dialog for the Geography Key appears.
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.
Click the radio
button to the immediate left of the Choose a member to be the default
label in the upper portion of the Set Default Member dialog.
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.
Illustration 9: Expanded
Geography Tree within the Choose a Member to Be the Default Pane ...
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).
Click the Cancel
button in the bottom right corner of the Set Default Member dialog, to dismiss the dialog without making
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.
used to discretize the members is set by the next property we will examine, 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.
selector button (the downward pointing arrow shown circled in Illustration 10) to the right of the setting box for the DiscretizationMethod property.
Illustration 10: Click
the Selector Button to the Right of the DiscretizationMethod Property