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 4

By William Pearson

Let's say that we have a need to compare Information Systems (IS) Cost between the Stores located in California. We also want to view this information specifically for 1997 (the default year). Let's walk through the process of doing the appropriate additional drills to meet our needs.

11.  Right-click the Total Expense box.

12.  Select Drill Down --> Account --> Level 4 across the cascading menus that appear.

The resulting view appears in Illustration 13.

Click for larger image

Illustration 13: Drilled to Level Four

It is at Level 4 of the Account dimension hierarchy that we can distinguish the break out of IS Cost, along with the other components of what is known as Total Expense at the Level 3 rollup. We have attained our goal of "narrowing our beam" to focus upon IS Cost.

Now we need to drill the other related dimensions to the appropriate levels to meet the business need we have proposed. ProClarity makes this drill down, even in other dimensions, easy to accomplish, while allowing us to "maintain our bead" on IS Cost from the Account dimension perspective.

13.  Right-click on the Information Systems box.

14.  Select Drill Down, once again, from the context menu that appears.

We note that, while Account no longer appears as an option for selection in the first cascading context menu that emerges (we are already at the bottom of the Account dimension hierarchy for the Budget cube structure), we can select the Category, Store, or Time dimensions from our current position.

We will select Store next (Category is somewhat irrelevant, as only one Category, Current Year's Actuals, exists in the Budget cube structure for 1997.

15.  Select Store --> Store State in the cascading menu pair that appears.

The Store States appear. They will be in compact formation, if the window is significantly less than maximized. Make sure the window is maximized to gain a complete perspective of how ProClarity is visually representing the decompositions as we request them going forward.

The full perspective is presented in Illustration 14.

Illustration 14: Maximized View of Decomposition

We note that all states appear. We will narrow the presentation to focus upon the California stores, to meet the requirement we outlined earlier.

16.  Right-click the newly appearing CA box in the bottom row of the diagram.

17.  Select Drill Down --> Store --> Store City from the respective cascading context menus that, in turn, appear.

We see the five California stores appear, as shown in Illustration 15.

Illustration 15: The California Stores Appear

We have met the objective of narrowing our focus, within the context of IS Cost, to the California stores. Because the Time (already in year 1997, by default) and Category (defaulted as the Current Year's Actual category, because it is the only category that existed in 1997) dimensions are intersected to meet our stated goal, we can now perform analysis on the California stores with regard to IS Cost. The Decomposition Tree view provides an excellent presentation of the precise drill-down path we have taken, and clearly highlights the significance of each box / member of the chain, to make the allocation of "shares" of the IS Cost component of Total Expense intuitive to the reader. As we noted earlier, more information can be obtained with a simple mouse rollover of any box that the analyst feels a need to examine more closely.

Let's save our work at this stage.

18.  Select File --> Save Book As ... from the main menu.

We receive a warning message box, informing us that books need to contain at least a page. The Warning message box appears in Illustration 16.

Illustration 16: The Warning Received upon our First Save Attempt

19.  Click OK.

The Add to Briefing Book dialog appears, offering a default name. I chose to rename the book to a shorter title, as shown in Illustration 17.

Illustration 17: The Add to Briefing Book Dialog

20.  Click OK, after renaming to taste.

21.  Navigate to an appropriate storage location when the Save Briefing Book As ... dialog appears next.

22.  Give the Briefing Book a name (I named mine the same as the page above).

23.  Click Save to save the .bbk file.

The Save Briefing Book As ... dialog closes, and we return to the page where we left it before the save procedure.

At this stage, we will look at some of the visualization options that abound in ProClarity.

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