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 Jan 20, 2004

Introduction to MSSQL Server 2000 Analysis Services: Reporting Options for Analysis Services Cubes: ProClarity Professional, Part I - Page 3

By William Pearson

Exploration and Analysis: Decomposition - It's a Good Thing

ProClarity provides a host of ways to leverage the strengths of MSAS, and makes it easy for analysts and power users to access and take advantage of MSAS' analytical functionality. The interface is intuitive, and users find it remarkably straightforward, from their introduction to the product, to explore complex data, track key performance indicators and publish, or to otherwise share, their findings with other members of the organization.

Once we have established connectivity to the Budget cube, we can explore the data to analyze overall performance and to gain a deeper understanding of the factors that drive profitability. We will examine some of the key features of ProClarity in this section, and use it to explore and analyze the Budget cube. Let's get started with the following steps, beginning where we left off in the previous section, at the Select how you want to visualize your information dialog:

1.  Click the Decomposition Tree option.

The Welcome dialog for the Decomposition Tree Wizard appears, as depicted in Illustration 6.

Illustration 6: Welcome Dialog for the Decomposition Tree Wizard

2.  Click Next to proceed.

The Decomposition Tree Wizard initializes, and prompts us for specification, in the dialog that appears next, of the measure and dimension upon we wish to perform decomposition.

3.  Select Amount in the Breakdown selector box.

4.  Select Account in the list labeled For items in.

The What do you want to analyze? dialog appears, with our settings, as shown in Illustration 7.

Illustration 7: The What Do You Want to Analyze Dialog - Decomposition Wizard

We are specifying that we wish to decompose the Amount measure (the only measure in the somewhat simple Budget cube) for the Account dimension. In other words, we want to be able to determine the values of the accounts that make up the summary numbers appearing in the FoodMart 2000 financial reporting levels - to "break down" the rollup amounts therein into their constituent, hierarchical account parts.

5.  Click Finish to accept the settings.

The initial view appears, with the top (or All Account) rollup amount displayed in the view shown in Illustration 8.

Illustration 8: Initial View - Decomposition Tree Wizard

6.  Roll the mouse over the label in the All Account box until the information shown in Illustration 9 appears.

Illustration 9: Context-sensitive Rollover Display of the Mouse Pointer

We can readily see the value of Amount displayed ($ 398,755.69) is the total (100%) rollup value. The labels for the box exhibit link-like behavior when we hover the mouse pointer over them.

7.  Click the All Account label to drill to the next lower level.

We see Net Income appear next. The reason for this is that the default year, 1997, contains only P & L / Income Statement account activity in the simple Budget cube. We note that the Level 02 label appears underneath the "All" label in the upper left corner of the pane, reflecting that we are on Level 2 of the hierarchy.

8.  Roll the mouse over the Net Income label until the information shown in Illustration 10 appears.

Illustration 10: Rollover Display with Added Property Information

We see a display very similar to the rollover results for the All level, except we note the appearance of member property information, as well. (For a discussion of the meaning of the Unary Operator: + detail, see our DatabaseJournal articles Custom Cubes: Financial Reporting - Part I and Part II.)

9.  Right-click the Net Income label.

A context menu appears.

10.  Select Drill Down --> Account --> Level 3 across the cascading menus that appear, in turn, as depicted in Illustration 11.

Illustration 11: Drill Down --> Account --> Level 3

The drill down is accomplished, leaving us with the view shown in Illustration 12.

Illustration 12: Level Three Account Components of Net Income

In addition, the Level 3 indicator appears in the upper left corner of the pane. We can also "get our bearings" at anytime, determining our location from the perspective of the hierarchy, by noting the information at the bottom of the dialog (in this instance, it states "Decomposition of Net Income," indicating that the Net Sales and Total Expense boxes roll up to the Net Income level in the Account dimension hierarchy).

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