Alternatively Sorting Attribute Members in Analysis Services 2005 - Page 2
September 10, 2007
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.
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.
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.
The tabs of the Cube Designer open. Next, well 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.
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.
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.
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.
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.
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.
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.