Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Mar 13, 2008

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

By William Pearson

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.)

1.  Within the Solution Explorer, right-click the Geography dimension (expand the Dimensions folder as necessary).

2.  Click Open on the context menu that appears, as depicted in Illustration 3.

Illustration 3: Opening the Dimension via the Dimension Designer ...

The tabs of the Dimension Designer open.

3.  Click the Dimension Structure tab, if we have not already arrived there by default.

4.  Examine the 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.

Illustration 4: The Member Attributes, Geography Dimension

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.

1.  Within the Attributes pane of the Dimension Structure tab, right-click the Geography Key attribute.

2.  Click Properties on the context menu that appears, as depicted in Illustration 5.

Illustration 5: Select Properties from the Context Menu ...

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.

Illustration 6: The Properties Pane for the Geography Key Attribute

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.

Illustration 7: Optional Settings for the AttributeHierarchyOptimizedState Property

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.

3.  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

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.

4.  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.

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.

Illustration 9: Expanded Geography Tree within the Choose a Member to Be the Default Pane ...

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).

5.  Click the Cancel button in the bottom right corner of the Set Default Member dialog, to dismiss the dialog without making changes.

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.

6.  Click the 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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM