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 Sep 12, 2005

Introduction to MSSQL Server Analysis Services: Reporting Options for Analysis Services Cubes: MS Excel 2003 and More ... - Page 4

By William Pearson

Viewing Member Details

The capability to "explode" the hierarchical levels of our dimensions to member children enhances data analysis by allowing business users not only to view the reports to which they have become accustomed (for example, a trending of monthly organizational expenses), but also to drill down to the details supporting any summary value. This allows the analyst to see the details that make up the value upon which he / she drills down. The beauty of multidimensional analysis becomes clear, with this interactive ability to find root causes for changes in activity over time (among myriad other values). Information consumers can also return to higher levels within hierarchies to view summary information.

The PivotTable report / cube combination provides the ability to view various levels of activity for members as a group, as well as to analyze the details of summary values on an individual member basis, as we will see. We can explore the drill-down capabilities of the PivotTable report by performing the following actions:

11.  Within the Warehouse column of the existing report, double-click the cell containing USA.

The report drills down to the Warehouse State Province, the immediate children of the Warehouse Country, as shown in Illustration 21.

Click for larger image

Illustration 21: Drilling Down to the Children of the USA Warehouse Country

12.  Double click the Washington (WA) Warehouse State Province to drill to its children.

The children of the Washington Warehouse State Province appear, as depicted in Illustration 22.

Illustration 22: Children of the Washington Warehouse State Province

We can always reverse a drill-down action ("drill up," as it were) by double-clicking the original drill-down object. We will short-circuit the process and zoom up to the original Warehouse Country level, where we began drilling earlier.

13.  Double-click the USA Warehouse Country once again.

The resulting dataset should resemble that depicted in Illustration 23.

Illustration 23: Zoom Up to the USA Warehouse Country

14.  Double-click USA again, to expose the three Warehouse State Provinces once more.

15.  Click (once, to highlight) the Warehouse State Province level heading (above the CA member, and to the right of the Warehouse Country column.

16.  Click the Show Detail button on the PivotTable toolbar (another means of drilling down to the immediate children), as shown in Illustration 24.

Illustration 24: Another Means of Drilling Down - All Members of a Level Simultaneously

The children of all members of the State Province level appear, as depicted in Illustration 25.

Illustration 25: Children of the Entire State Province Level

Next, let's assume that we want to hide the "USA" column of the report (logical enough, if all our stores are located in the United States - the "USA" level is somewhat redundant, and takes up useful real estate.)

17.  Right-click the Warehouse Country level heading.

18.  Click Hide Levels in the context menu that appears, as shown in Illustration 26.

Illustration 26: The Context Menu - Hide Levels Option

This leaves us with a view that is more compact, similar to that depicted in Illustration 27.

Illustration 27: The PivotTable Report, sans the USA Warehouse Country Column

There are many other options for browsing our cubes, as well as with formatting the views we generate. It pays to invest some time experimenting with the plethora of available choices, and determining the combination of settings needed to get information to the targeted consumers in a fashion that will be most useful to them. Next, we will delve a bit further into the concepts of adding multidimensionality to our reports, and demonstrate a straightforward approach to leveraging even more of the power of our OLAP cube to deliver analysis-focused data.

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