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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Sep 10, 2007

Alternatively Sorting Attribute Members in Analysis Services 2005 - Page 3

By William Pearson

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.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




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


















Thanks for your registration, follow us on our social networks to keep up-to-date