Alternatively Sorting Attribute Members in Analysis Services 2005 - Page 3

September 10, 2007

27.  In the Attributes pane, click-select the newly appearing Commute Distance Sort.

28.  Within the Properties window for the new Commute Distance Sort attribute, set the AttributeHierarchyEnabled property (in the Advanced section of the Properties window) to False.

29.  Set the AttributeHierarchyOptimizedState property (also in the Advanced section of the Properties window) to NotOptimized.

30.  Set the AttributeHierarchyOrdered property (in the Misc section of the Properties window) to False.

The settings we have made will hide the attribute from information consumers, and will conserve processing resources. These settings are appropriate because the new attribute will be used only for the ordering of the members of our primary attribute, Commute Distance. Our modifications appear, within the Properties window for the Commute Distance Sort attribute, as depicted in Illustration 13.


Illustration 13: Our Property Modifications for the Commute Distance Sort Attribute

31.  Returning again to the Attributes pane, expand Full Name.

We see immediately that all the attributes in the Customer dimension that are based upon the Customer table in the ANSYS062_Basic AS DB data source view (including the new Commute Distance Sort attribute) are related through this attribute.

32.  Expand the Geography attribute by clicking the “+” sign to its immediate left.

We see here that all the attributes in the Customer dimension that are based upon the Geography table in the ANSYS062_Basic AS DB data source view are related through the Geography attribute.

The relationships we have examined within the Full Name and Geography attributes appear as shown in Illustration 14.


Illustration 14: Attribute Relationships We Have Examined ...

33.  Select the Geography attribute by clicking upon it.

34.  Within the Properties window for the Geography attribute, set the AttributeHierarchyVisible property (in the Advanced section of the Properties window) to False.

35.  Set the AttributeHierarchyOptimizedState property (also in the Advanced section of the Properties window) to NotOptimized.

36.  Set the AttributeHierarchyOrdered property (in the Misc section of the Properties window) to False.

As was the case earlier for the Commute Distance Sort attribute, this attribute will not be used for browsing; the settings we have made will hide the attribute from users and will save processing time. (We must keep in mind, however, that an attribute hierarchy must be enabled if it has member properties.) Our modifications appear, within the Properties window for the Geography attribute, as depicted in Illustration 15.


Illustration 15: Our Property Modifications for the Geography Attribute

37.  In the Attributes pane, once again, expand the Commute Distance attribute by clicking the “+” sign to its immediate left.

We note here that no relationship is currently defined between the Commute Distance attribute and the Commute Distance Sort attribute. We will now define such a relationship.

38.  Drag the Commute Distance Sort attribute to the <new attribute relationship> placeholder that appears underneath the Commute Distance attribute.

We have defined a relationship between the Commute Distance attribute and the Commute Distance Sort attribute, as evidenced by the appearance of the latter within the attribute relationships of the primary attribute, as shown in Illustration 16.


Illustration 16: The New Attribute Relationship Appears ...

39.  Click the new Commute Distance Sort attribute relationship underneath the Commute Distance attribute, to select it, as necessary.

The Properties window for the new attribute relationship becomes visible. Here we can see that the default value for the RelationshipType property of the Commute Distance Sort member of the Commute Distance attribute is Flexible. Because the relationship between the members of the Commute Distance Sort attribute is not expected to change over time, we will reset the RelationshipType property to Rigid.

40.  In the Properties window, change the value of the RelationshipType property to Rigid, depicted in Illustration 17.


Illustration 17: The Modified RelationshipType Property ...

We are now in a position to modify the sort order of the Commute Distance attribute to meet the stated client business requirement.

40.  In the Attributes pane, once again, click the Commute Distance attribute to select it.

41.  Within the Properties window, modify the OrderBy property (within the Advanced properties group) from its current setting of Key to AttributeKey, using the dropdown selector that becomes enabled when we click the property.

We emphasize to our client colleagues that we could easily sort by the name of the secondary attribute at this point; we would simply choose AttributeName via the dropdown selector, instead of AttributeKey.

42.  Change the value of the OrderByAttribute property, just underneath the OrderBy property, to Commute Distance Sort.

The affected settings within the Properties window for the Commute Distance attribute appear as shown in Illustration 18.


Illustration 18: The Modified Attribute Properties

We are now ready to process Analysis Services database ANSYS062_Basic AS DB to enact our structural changes.

43.  Right-click ANSYS062_Basic AS DB atop the tree within the Solution Explorer.

44.  Select Process from the context menu that appears, as depicted in Illustration 19.


Illustration 19: Process the Practice Database

45.  Click Yes on the dialog that appears asking if we would like to save all changes first.

46.  Click Run on the Process Database - ANSYS062_Basic AS DB dialog that appears next, as shown in Illustration 20.


Illustration 20: Click Run... to Process the Database

47.  Once the Process Progress viewer indicates successful completion, click Close to dismiss the viewer.

48.  Click Close on the Process Database - ANSYS062_Basic AS DB dialog, as well.

We are now ready to verify the effectiveness of our new sort for the primary attribute members.

Verification: Browse the Cube to Ascertain the Effectiveness of the New Attribute Member Sort

Let's take a look at the Commute Distance attribute hierarchy - specifically at the effects of the new sort we have installed - from the Cube Browser.

49.  Click the Browser tab to switch to the Cube Browser within the Dimension Designer for the Customer dimension.

50.  Click the Reconnect button within the Browser toolbar, as depicted in Illustration 21.


Illustration 21: Click Reconnect

51.  Select the Commute Distance attribute hierarchy within the Hierarchy selector atop the Browser, as necessary.

52.  Expand the All Customers level of the dimension, as required, to expose the attribute members.

We note that the Commute Distance attribute hierarchy members are now sorted based upon increasing commute distance, as shown in Illustration 22.


Illustration 22: The Newly Sorted Attribute Hierarchy Members

Our client colleagues confirm that the business requirements have been met through the solution we have demonstrated. We have established the capability to sort attribute members by a secondary attribute key to obtain a presentation effect that was not readily supported by the name or key of the primary attribute.

Conclusion

In this article we gained hands-on exposure to sorting attribute members by a secondary attribute key in Analysis Services 2005. We began our examination of the process involved with a discussion surrounding the general concepts underpinning attribute relationships, including what they define and support, as well as the mechanics that determine these relationships. We next discussed the way in which the Dimension Designer supports our definition of additional relationships between attributes.

After undertaking preparation steps, including the cloning and deployment of a basic sample cube for use in our subject matter practice, we began our hands-on practice session, where we set out to meet a hypothetical client business requirement; using our sample cube, we leveraged an attribute relationship to demonstrate support of the sorting of an existing attribute by a secondary attribute key. Throughout our session, we discussed other considerations that surround the addition and use of attribute relationships to support the use of the name or key of a secondary attribute as the basis for ordering the members of a primary attribute within our cube.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

Introduction to MSSQL Server Analysis Services Series
Introduction to Security in Analysis Services
Cube Storage: Planning Partitions from a SQL Server Management Studio Perspective
Cube Storage: Planning Partitions (Business Intelligence Development Studio Perspective)
Cube Storage: Introduction to Partitions
Introduction to Cube Storage
Attribute Discretization: Customize Grouping Names
Attribute Discretization: Using the "Clusters" Method
Attribute Discretization: Using the "Equal Areas" Method
Attribute Discretization: Using the Automatic Method
Introduction to Attribute Discretization
More Exposure to Settings and Properties in Analysis Services Attribute Relationships
Attribute Relationships: Settings and Properties
Introduction to Attribute Relationships in MSSQL Server Analysis Services
Attribute Member Values in Analysis Services
MSSQL Analysis Services - Attribute Member Names
Attribute Member Keys - Pt II: Composite Keys
Attribute Member Keys - Pt 1: Introduction and Simple Keys
Dimension Attributes: Introduction and Overview, Part V
Dimension Attributes: Introduction and Overview, Part IV
Dimension Attributes: Introduction and Overview, Part III
Dimension Attributes: Introduction and Overview, Part II
Dimension Attributes: Introduction and Overview, Part I
Dimensional Model Components: Dimensions Part II
Dimensional Model Components: Dimensions Part I
Manage Unknown Members in Analysis Services 2005, Part II
Manage Unknown Members in Analysis Services 2005, Part I
Alternatively Sorting Attribute Members in Analysis Services 2005
Introduction to Linked Objects in Analysis Services 2005
Distinct Counts in Analysis Services 2005
Positing the Intelligence: Conditional Formatting in the Analysis Services Layer
Administration and Optimization: SQL Server Profiler for Analysis Services Queries
Mastering Enterprise BI: Time Intelligence Pt. II
Mastering Enterprise BI: Time Intelligence Pt. I
Design and Documentation: Introducing the Visio 2007 PivotDiagram
Actions in Analysis Services 2005: The URL Action
Actions in Analysis Services 2005: The Drillthrough Action
Mastering Enterprise BI: Introducing Actions in Analysis Services 2005
Mastering Enterprise BI: Introduction to Translations
Mastering Enterprise BI: Introduction to Perspectives
Introduction to the Analysis Services 2005 Query Log
Mastering Enterprise BI: Working with Measure Groups
Mastering Enterprise BI: Introduction to Key Performance Indicators
Mastering Enterprise BI: Extend the Data Source with Named Calculations, Pt. II
Mastering Enterprise BI: Extend the Data Source with Named Calculations, Pt. I
Process Analysis Services Objects with Integration Services
Usage-Based Optimization in Analysis Services 2005
Introduction to MSSQL Server Analysis Services: Named Sets Revisited
Introduction to MSSQL Server Analysis Services: Migrating an Analysis Services 2000 Database to Analysis Services 2005
Introduction to MSSQL Server Analysis Services: Introducing Data Source Views
Introduction to MSSQL Server Analysis Services: Reporting Options for Analysis Services Cubes: MS Excel 2003 and More ...
Introduction to MSSQL Server Analysis Services: Mastering Enterprise BI: Create Aging "Buckets" in a Cube
Introduction to MSSQL Server Analysis Services: Mastering Enterprise BI: Relative Time Periods in an Analysis Services Cube, Part II
Introduction to MSSQL Server Analysis Services: Mastering Enterprise BI: Relative Time Periods in an Analysis Services Cube
Introduction to MSSQL Server Analysis Services: Process Analysis Services Cubes with DTS
Introduction to MSSQL Server Analysis Services: Presentation Nuances: CrossTab View - Same Dimension
Introduction to MSSQL Server Analysis Services: Point-and-Click Cube Schema Simplification
Introduction to MSSQL Server 2000 Analysis Services: Manage Distinct Count with a Virtual Cube
Introduction to MSSQL Server 2000 Analysis Services: Distinct Count Basics: Two Perspectives
Introduction to MSSQL Server 2000 Analysis Services: Semi-Additive Measures and Periodic Balances
Introduction to MSSQL Server 2000 Analysis Services: Performing Incremental Cube Updates - An Introduction
Introduction to MSSQL Server 2000 Analysis Services: Partitioning a Cube in Analysis Services - An Introduction
Introduction to MSSQL Server 2000 Analysis Services: Basic Storage Design
Introduction to MSSQL Server 2000 Analysis Services: Derived Measures vs. Calculated Measures
Introduction to MSSQL Server 2000 Analysis Services: Creating a Dynamic Default Member
Introduction to MSSQL Server 2000 Analysis Services: Another Approach to Local Cube Design and Creation
Introduction to MSSQL Server 2000 Analysis Services: Introduction to Local Cubes
Introduction to MSSQL Server 2000 Analysis Services: Actions in Virtual Cubes
Introduction to MSSQL Server 2000 Analysis Services: Putting Actions to Work in Regular Cubes
Introduction to MSSQL Server 2000 Analysis Services: Reporting Options for Analysis Services Cubes: ProClarity Part II
Introduction to MSSQL Server 2000 Analysis Services: Reporting Options for Analysis Services Cubes: ProClarity Professional, Part I
Introduction to MSSQL Server 2000 Analysis Services: Using Calculated Cells in Analysis Services , Part II
Introduction to MSSQL Server 2000 Analysis Services: Using Calculated Cells in Analysis Services, Part I
Introduction to MSSQL Server 2000 Analysis Services: MSAS Administration and Optimization: Toward More Sophisticated Analysis
Introduction to MSSQL Server 2000 Analysis Services: MSAS Administration and Optimization: Simple Cube Usage Analysis
Introduction to MSSQL Server 2000 Analysis Services: Build a Web Site Traffic Analysis Cube: Part II
Build a Web Site Traffic Analysis Cube: Part I
Reporting Options for Analysis Services Cubes: Cognos PowerPlay
Reporting Options for Analysis Services Cubes: MS FrontPage 2002
Reporting Options for Analysis Services Cubes: MS Excel 2002
Introduction to MSSQL Server 2000 Analysis Services: Drilling Through to Details: From Two Perspectives
Introduction to MSSQL Server 2000 Analysis Services: Custom Cubes: Financial Reporting - Part II
Introduction to MSSQL Server 2000 Analysis Services Custom Cubes: Financial Reporting (Part I)
Introduction to SQL Server 2000 Analysis Services: Exploring Virtual Cubes
Introduction to SQL Server 2000 Analysis Services: Working with the Cube Editor
Introduction to SQL Server 2000 Analysis Services: Parent-Child Dimensions
Introduction to SQL Server 2000 Analysis Services: Handling Time Dimensions
Introduction to SQL Server 2000 Analysis Services: Working with Dimensions
Introduction to SQL Server 2000 Analysis Services: Creating Our First Cube








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers