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

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

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Feb 17, 2004

Introduction to MSSQL Server 2000 Analysis Services: Reporting Options for Analysis Services Cubes: ProClarity Part II - Page 4

By William Pearson

More Exploration and Further Features in ProClarity Professional

Let's open another cube, Warehouse, so we can explore further options that might be clearer with a richer, slightly more sophisticated data set.

1.  Select File --> Open Cube.

2.  Select the FoodMart 2000 Warehouse cube.

The Open Cube dialog appears as shown in Illustration 10.

Click for larger image

Illustration 10:  The Open Cube Dialog with Selection Highlighted

3.  Click OK.

The Select how you want to visualize your information dialog appears.

4.  Select the Grid option.

The grid appears, once again, at fully rolled up summary level.  This level, as we can see by looking through the dimension and measures tabs (click the Dimensions button in the toolbar if this does not appear already), consists of the topmost All level for each dimension, except for Time, whose default is year 1997.  The measures default is Store Invoice.

5.  Select the Warehouse dimension from within the Background box, at the bottom of the Dimension Tool.

6.  Drag the Warehouse dimension to drop it above the Product dimension in the Rows box.

7.  Click the Measures tab, atop the Dimension Tool.

8.  Select (by click-highlighting) the Warehouse Cost measure on the Warehouse tab.

9.  Click the Warehouse Tab to bring it in front of the other tabs.

This will allow us to keep an eye on the Warehouse dimension hierarchy over the next few steps.  The All Warehouses level appears atop the tab in the present view.

The arrangement of the Dimension Tool is depicted in Illustration 11.

Illustration 11:  Arrangement of the Dimension Tool

10.  Click the Execute button in the toolbar to refresh our changes.

We see a single row, combining the Warehouse and Product dimensions, in a display that appears as shown in Illustration 12.

Illustration 12:  The New "Combination Row" with Rolled Up Total

11.  Expand the All Warehouses level in the Warehouse dimension hierarchy (shown circled in red in Illustration 13), on the Warehouse dimension tab, by clicking the "+" sign to its left.

Illustration 13:  Expand the Warehouse Dimension (Circled in Red)

The child warehouse countries (the next level in the Warehouse dimension hierarchy) appear broken out in the expanded view, as we see in Illustration 13 above.

12.  Hover the mouse pointer over the All Warehouses portion of the top row of the data display.

The pointer turns into a small drill icon, indicating that drilling is enabled for the item over which it is hovering.

13.  Click the All Warehouses field once.

The display drills down (and auto refreshes) to reflect the child warehouse cities, as depicted in Illustration 14

Illustration 14:  Drill Down Exposes the Next Underlying Level

In addition, we note that the members listed in the Warehouse dimension tab, previously the individual child warehouse countries we saw in Illustration 13, are now replaced by a single item, Children of All Warehouses.  (This is a manifestation of the manner in which the multidimensional expressions language, otherwise known as MDX, handles the drill down in ProClarity.  For more on MDX, and the .Children function in particular, see "MDX Member Functions: The 'Family' Functions"  in my MDX Essentials series at Database Journal.)

MS SQL Archives

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