Alternatively Sorting Attribute Members in Analysis Services 2005

Monday Sep 10th 2007 by William Pearson
Share:

Go beyond sorting attribute members based upon their own name or key value. Business Intelligence Architect Bill Pearson provides hands-on guidance in sorting attribute members by the name or key of a secondary attribute in Analysis Services 2005.

About the Series ...

This article is a member of the series Introduction to MSSQL Server Analysis Services. The series is designed to provide hands-on application of the fundamentals of MS SQL Server Analysis Services (“Analysis Services”), with each installment progressively presenting features and techniques designed to meet specific real-world needs. For more information on the series, please see my initial article, Creating Our First Cube. For the software components, samples and tools needed to complete the hands-on portions of this article, see Usage-Based Optimization in Analysis Services 2005, another article within this series.

Introduction

While it is quite common to sort the attribute members of our Analysis Services 2005 cubes based upon either their name or key values (perhaps even a composite member key), many of us encounter situations where we need to accomplish a different sort order – a sort order that is based upon a characteristic lying outside the attribute members themselves. In these cases, an option we have is to order such an attribute on the basis of the name or key of a related secondary attribute.

In this article we will gain some hands-on exposure to sorting attribute members by a secondary attribute key in Analysis Services 2005. (As we shall see, sorting by the secondary attribute name is handled in a virtually identical manner.) Our examination will include:

  • A discussion surrounding the general concepts underpinning attribute relationships, including what they define and support, as well as the mechanics that determine these relationships.
  • A discussion surrounding the way in which the Dimension Designer supports our definition of additional relationships between attributes.
  • Cloning and deployment of a basic sample cube for use in our subject matter practice.
  • A hands-on practice session, using our sample cube, where we create, and then leverage, an attribute relationship to demonstrate support of sorting of an existing attribute by a secondary attribute name or key.
  • A discussion of 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.

Sort Attribute Members by a Secondary Attribute Name or Key

To support the ordering of an attribute’s members (I will sometimes refer to this as the “primary” attribute, to minimize confusion) based upon the name or key of a secondary attribute, we must select a secondary attribute that is related to the primary attribute. An attribute relationship defines the relationship or dependency between attributes.

Attributes are related to each other differently for dimensions based upon single relational tables versus dimensions based upon multiple tables. In the case of a single-table-based dimension, all attributes are usually related via the key attribute. In effect, the dimension’s attributes exist to provide information about the dimension’s members - members that are linked, via the dimension’s key attribute, to the facts in the fact table for each related measure group. By contrast, the attributes of multiple-table-based dimensions are typically linked based upon the join key between the tables.

The Dimension Designer affords us an easy means of defining additional relationships between attributes, among other actions related to attributes (such as changing default relationships for performance enhancement purposes, etc.). In so doing, the Dimension Designer supports our ability to meet specific business requirements for additional information about members. When creating attribute relationships, we must keep in mind that the primary attribute must relate to no more than one member value within the attribute to which we wish to relate it.

Analysis Services 2005 allows us a further option within our definition of a relationship between two attributes. Based upon whether the relationship between members will change over time, we can define the relationship as rigid or flexible, as we shall see in our practice section. When we define a relationship as rigid, attribute aggregations are not recalculated every time the dimension is incrementally processed. If the relationship between members does change, however, the dimension must be fully processed.

Establishing the Capability to Sort Attribute Members by a Secondary Attribute Name or Key

Let’s consider an example whereby we can obtain hands-on practice with establishing the capability to sort attribute members by a secondary attribute key. (As we have already mentioned, sorting by the secondary attribute name is handled in a virtually identical manner.) We will first create a secondary attribute, and then relate this new attribute to a primary attribute an attribute that we have identified as being a candidate for sorting by the secondary attribute key. We will then align the settings of the primary attribute to support the desired sort presentation of its members.

Let’s assume, for purposes of our practice example, that we have received a request for assistance from representatives of our client, the Adventure Works organization. Analysts within the VP - Marketing group, with whom we have worked in the past to deliver solutions to meet various reporting and analysis needs, inform us that they have received a request to add a new sort to the Customer dimension of the existing cube structure. At the heart of the request lies a specific attribute hierarchy, Commute Distance, which, due to an oversight at design time, is not sorting its members, the respective distances, from “least to most.” As is typically the case within the context of our consulting events, the group assures us that, once they grasp the concepts to meet the immediate need, they will want to expand the capability to fix similar, less-than-optimal design characteristics in other areas of cube structure.

For the time being, our colleagues want simply to remedy the deficiency, and to make the Commute Distance attribute hierarchy members sort intuitively, in ascending fashion. After we initially examine the cube structure, we find a means of meeting the business requirement: we suggest using another column that already exists in the underlying Adventure Works DW star schema as the basis of the sort that the client representatives tell us that they need. We offer to show our colleagues how to establish a capability to sort attribute members by a secondary attribute name or key, whereby we will leverage the data in the column we have identified to deliver just the sort they need to meet their new requirement. (We explain that our example will consist of showing how to sort upon the secondary attribute key, but we emphasize that sorting by the name of the secondary attribute would only involve a different selection within a single property, within an essentially identical procedure.)

Our client colleagues agree that the proposed approach should meet their sorting requirement. We then set about the assembly of our example to illustrate how we will support the sort capability we have described. Before we get started with making these enhancements to a sample cube clone, we will need to prepare our local environment for the practice session. We will take steps to accomplish this within the section that follows.

Preparation: Create a Sample Basic Database within Which to Perform the Steps of Our Practice Session

Create Sample Primary Database for the Practice Exercise

Before getting started with our practice session, we will need a basic sample Analysis Services database (with cube) with which to work. To quickly create a copy of a basic Analysis Services sample project, from which an Analysis Services database can be quickly deployed, please perform the steps of the following procedure, located in the References section of my articles index:

Make a Copy of a Basic Analysis Services Sample Project within a New Solution

Once you have created the new sample project, you should ascertain connectivity to the underlying relational data source (particularly if you are running “side-by-side” installations of MSSQL Server 2000 and MSSQL Server 2005, but it is important to check even if not). You can do this by performing the steps of the following procedure, also located in the References section of my articles index:

Ascertain Connectivity of the Relational Data Source

Once you have created the new sample project, and ascertained connectivity, deploy it to the Analysis Server by performing the steps of the following procedure, located in the References section of my articles index (substitute object names for your own when following the procedure):

Deploy the Analysis Services Project

Ascertain that the New Practice Database is in Place and Rename It

We are now ready to ascertain that the new Analysis Services database is in place, as well as to rename it to keep it safe from writeover in future exercises within the series. (You can, of course, always delete the practice database if you do not choose to keep it for future reference, etc.) To do this, we will work from within the Microsoft SQL Server Management Studio.

1.  Start Microsoft SQL Server Management Studio.

2.  Select Analysis Services in the Server type selector of the Connect to Server dialog that appears.

3.  Type / select the server name (server name / instance, if appropriate) in the Server name selector.

4.  Supply authentication information, as required in your own environment.

The Connect to Server dialog appears similar to that depicted in Illustration 1.


Illustration 1: Connecting to the Server ...

5.  Click the Connect button to connect with the specified Analysis Server.

The Microsoft SQL Server Management Studio opens.

6.  In the Object Explorer pane (it appears by default on the left side of the Studio), expand the Databases folder (click the “+” sign to its immediate left), appearing underneath the Analysis Server within which we are working.

The Databases folder opens, exposing the detected Analysis Services database(s). Our new Analysis Services database, DBJ_Basic AS DB, should appear among the other databases.

7.  Right-click the DBJ_Basic AS DB database, and select Rename from the context menu that appears next, as shown in Illustration 2.


Illustration 2: Renaming the Sample Database

8.  Rename the Analysis Services database as follows:

ANSYS062_Basic AS DB

Having ensured that the new database is in place, and having renamed it, we are ready to begin the procedural part of our practice session.

Procedure: Establish the Capability to Sort Attribute Members by a Secondary Attribute Key

Specific business requirements sometimes dictate the need, as we have noted, to sort attribute members of our Analysis Services 2005 cubes based upon something besides their name or key values. We will get some hands-on practice ordering an attribute on the basis of the key of a related secondary attribute within the steps that follow. We will perform our practice session within the SQL Server Business Intelligence Development Studio, from which we will perform the steps of adding the secondary-attribute-based sort in our new Analysis Services database, ANSYS062_Basic AS DB.

1.  Click Start.

2.  Navigate to, and click, the SQL Server Business Intelligence Development Studio, as appropriate.

We briefly see a splash page that lists the components installed on the PC, and then Visual Studio .NET 2005 opens at the Start page.

3.  Close the Start page, if desired.

4.  Select File -> Open from the main menu.

5.  Click Analysis Services Database ... from the cascading menu, as depicted in Illustration 3.


Illustration 3: Opening the Analysis Services Database ...

The Connect to Database dialog appears.

6.  Ensuring that the Connect to existing database radio button is selected, type the Analysis Server name into the Server input box atop the dialog.

7.  Using the selector just beneath, labeled Database, select ANSYS062_Basic AS DB, as shown in Illustration 4.


Illustration 4: Selecting the New Basic Analysis Services Database ...

8.  Leaving other settings on the dialog at default, click OK.

SQL Server Business Intelligence Development Studio briefly reads the database from the Analysis Server, and then we see the Solution Explorer populated with the database objects. We will be working largely within the Dimension Designer, but will also need to access elements within the cube structure, as well, so we will open both Cube and Dimension Designers at this point.

9.  Within the Solution Explorer, right-click the Basic cube (expand the Cubes folder as necessary).

10.  Click Open on the context menu that appears, as depicted in Illustration 5.


Illustration 5: Opening the Cube Designer ...

The tabs of the Cube Designer open. Next, we’ll open the Dimension Designer for the dimension within which we will be working , where we will proceed with the steps involved in bringing about the sort that our client colleagues have requested.

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

12.  Click Open on the context menu that appears, as shown in Illustration 6.


Illustration 6: Opening the Dimension Designer ...

The tabs of the Dimension Designer open.

13.  Click the Dimension Structure tab, if it has not already appeared by default.

14.  Within the Attributes pane, click the Commute Distance attribute to select it, and to cause the Properties window for the attribute to appear.

The Attributes pane, with our selection of the Commute Distance attribute, together with the associated Properties window (partial view), appears as depicted in Illustration 7.


Illustration 7: Commute Distance Attribute and Properties Window (Composite View)

We note particularly the setting of the OrderBy property within the Properties window: The members of the Commute Distance attribute hierarchy are being sorted based upon the member key, an ASCII value. This, we tell the client representatives, is why the sorts that we are currently obtaining are not simply “ascending values,” as we shall next observe by examining the current sort in the Browser.

15.  Click the Browser tab.

16.  Using the Hierarchy selector atop the Browser tab, select the Commute Distance attribute hierarchy, as shown in Illustration 8.


Illustration 8: Select the Commute Distance Attribute Hierarchy in the Browser ...

The All level of the Commute Distance attribute hierarchy appears in the Browser.

17.  Expand the All Customers level in the Browser by clicking the “+” sign to its immediate left.

The members of the Commute Distance attribute hierarchy appear next, as depicted in Illustration 9.


Illustration 9: Commute Distance Attribute Hierarchy Members Appear, Sorted by Key

We note that the Commute Distance members within the attribute hierarchy are not sorted “from least to most.” We explain to our client colleagues that this is due to the fact that the member key, which consists of ASCII-based values, is being used as the basis of the sort in the OrderBy property, which we saw earlier.

We inform our colleagues that a means of adjusting the sort to meet their current requirements is only a few steps away in Analysis Services 2005. We can find the basis for our conclusion by examining a named calculation that is already in place within the star schema underlying the Analysis Services database – and a resulting column which provides what we need to support the more intuitive, ascending sort that the client representatives seek.

18.  Click the Basic tab to expose the Cube Designer, which we opened along with the Dimension Designer earlier.

19.  If it has not already appeared by default, click the Cube Structure tab within the Cube Designer to expose the Data Source View pane.

20.  Within the Data Source View pane, right-click the Customer dimension table.

21.  Click Explore Data on the context menu that appears, as shown in Illustration 10.


Illustration 10: Exploring Customer Dim Table Data ...

Sample data loads, and populates the Table tab of the opening viewer.

22.  Scroll to the right within the data on the Table tab, until the CommuteDist column appears, as depicted in Illustration 11.


Illustration 11: Named Calculation Column to Support Ascending Sort ...

We explain to the client team that the CommuteDist named calculation assigns a numeric sort number to each distinct member value within the Commute Distance attribute hierarchy. As we can see, this column will support the ascending sort that the new business requirements specify. Our next steps will surround making modifications within the Dimension Designer to base sorting among the members of the Commute Distance attribute hierarchy upon this column, instead of the member key.

23.  Click the Customer tab to return to the Dimension Designer for the Customer dimension, which we opened along with the Dimension Designer earlier.

24.  Click the Dimension Structure tab within the Dimension Designer.

25.  Right-click the CommuteDistanceSort named calculation in the Customer table in the Data Source View pane.

26.  Click New Attribute from Column, as shown in Illustration 12.


Illustration 12: Click New Attribute from Column ...

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.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved