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

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

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Sep 10, 2007

Alternatively Sorting Attribute Members in Analysis Services 2005 - Page 2

By William Pearson

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

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