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

Tuesday Feb 17th 2004 by William Pearson
Share:

Discover yet another option for effectively reporting from MSSQL Server Analysis Services cubes.  Author  and MSAS Consultant Bill Pearson revisits practical reporting solutions with an Introduction to ProClarity for Analysis Services.

About the Series ...

This is the twentieth article of the series, Introduction to MSSQL Server 2000 Analysis Services.  As I stated in the first article, Creating Our First Cube, the primary focus of this series is an introduction to the practical creation and manipulation of multidimensional OLAP cubes. The series is designed to provide hands-on application of the fundamentals of MS SQL Server 2000 Analysis Services, with each installment progressively adding features and techniques designed to meet specific real - world needs.  For more information on the series, as well as the hardware / software requirements to prepare for the exercises we will undertake, please see my initial article, Creating Our First Cube.

Note:  Service Pack 3 updates are assumed for MSSQL Server 2000, MSSQL Server 2000 Analysis Services, and the related Books Online and Samples. Images are from a Windows 2003 Server environment, but the steps performed in the articles, together with the views that result, will be quite similar within any environment that supports MSSQL Server 2000 and MSSQL Server 2000 Analysis Services ("MSAS").

Along with MSSQL Server 2000 and MSAS, which we use in all articles of the series, additional application considerations apply for this tutorial, because it introduces a third-party reporting solution, ProClarity Professional ("ProClarity").  For those joining the series at this point because of a desire to work with MSAS and its components from a ProClarity perspective, it is assumed that MSAS is accessible to / installed on the PC, with the appropriate access rights to the sample cubes (which are provided in a typical installation of Analysis Services). 

We will use ProClarity (Platform 5 or higher), and various components that come along with a typical installation of ProClarity, to demonstrate the setup of, and some approaches for, using this business intelligence tool for creating and deploying reports, based upon an MSAS cube as a data source.  ProClarity provides the capability for establishing connections to our cubes, among other functions that we will explore.  Because we are targeting existing or evaluation users of ProClarity, we will assume installation of the application involved, and focus upon the specific setup required to enable reporting from MSAS cubes.  If this is the first time ProClarity is being accessed on your machine, or if ProClarity is not already installed / configured, you will need to consult the ProClarity documentation for installation instructions. 

Introduction

In Part I of this article, we returned to the objectives of an earlier subseries, Reporting Options for Analysis Services CubesAs we stated in that set of articles, our focus was to respond to a constant request from readers: to explore options beyond the Analysis Manager / Sample Application interfaces for analyzing and reporting data in MSAS cubes.  I began an examination of another such option, ProClarity, based upon a suggestion I received from a reader, and upon my own favorable experiences with this outstanding tool in recent months.

After a brief introduction to the application, together with an overview of establishing connectivity with an MSAS cube, we examined some of the options offered by ProClarity for analyzing our MSAS OLAP data.  We began to perform practice examples of browsing and analyzing our data from within the application, examining the layout and navigation of the analysis interface that ProClarity offers as we practiced its use.

In this article, we will continue our exploration of ProClarity in the same hands-on manner, with a focus on exposing more of the rich analysis and reporting capabilities of the application.

Reporting Options for MSAS Cubes:  ProClarity

First, we will return to the point at which we left our "tour" at the end of Part I, and finish discussing some features from the rudimentary perspective of the Budget cube.  Next, we will move our examination to a cube with a bit more complexity, and take advantage of the opportunities that this avails to experience additional features of the application.

The topics within this article will include:

  • An introduction to several additional features that are available within ProClarity to provide for analyzing, and creating enterprise reporting components from, data in MSAS cubes;
  • Various use and navigation tips for the ProClarity interface in analyzing, and reporting from, MSAS cubes.
  • Practice examples of uses for the ProClarity functionality that we expose.

Analyzing MSAS Cubes:  Additional ProClarity Features

As we stated in Part I, ProClarity Professional forms part of a toolset that enables analysts and other power users to create, manage and publish:

  • Reports
  • KPIs
  • Queries
  • Datasets
  • Logic

The destination options that are available are numerous, as the ProClarity Analytics Suite anticipates many of the requirements that appear in today's enterprise business intelligence implementations. As we have already noted, ProClarity includes ad-hoc analytic capabilities, robust query and calculation options and powerful visualization functionality that affords flexible navigation to the business user. In addition to its capacity for insulating the practitioner from much of the complication that underlies the analysis process, ProClarity provides another powerful incentive to potential customers - it promises substantial savings over the cost of one the large, proprietary solutions that have dominated the business intelligence market sector for years.  ProClarity's architecture  allows us to build robust, powerful OLAP reports and business intelligence applications based directly on the MSAS cubes, and allows the flexibility to select relational and other components singly and independently.  This affords an organization the opportunity to assemble a custom solution tailored ideally to its own industry and reporting environment, using a "best of class" selection approach for the applications it needs.

A Return to Our Review of the Basics

Let's open the Briefing Book where we left off in Part I, and return to the view with which we closed the article.

1.  Click the Start button.

2.  Select Programs --> ProClarity.

3.  Select Professional from the Submenu that appears.

The Welcome dialog appears.

4.  Select the Local Briefing Book radio button.

5.  Click OK.

The Open Briefing Book ... dialog appears.

6.  Navigate to, and select, the Briefing Book file in which we saved our work at the conclusion of Part I, (I named mine ANSYS19-01.bbk), as depicted in Illustration 1.


Illustration 1:  Select the Briefing Book Saved in Part I

7.  Click Open.

The Briefing Book opens, displaying the page we saved in Part I.

8.  Select View --> Business Charts --> Bar Chart from the main menu.

The bar chart appears, with the title bar indicating that we are viewing Amount for Information Systems, 1997 (Store City).

9.  Select View --> Grid --> Bottom from the main menu.

10.  The combined view, with the bar chart atop the grid, appears as shown in Illustration 2.


Illustration 2:  View Combining Bar Chart and Grid

Notice the tabs in the pane to the left of the combined view (ensure that the Dimension button in the toolbar is depressed).  In this pane, we can select any given tab, titled with the dimension or measure that it represents, to perform various operations on them.  Much can be accomplished among these objects with the menus available in context menus that appear with a right click on the respective object.

We also see the physical assignment in the view of the dimensions and measures, with regard to Rows, Columns and Background.

11.  Click the Measures tab to bring its contents forward.

12.  Right-click the solitary measure, Amount.

The context menu that appears, displayed in Illustration 3, is fairly typical.


Illustration 3:  Context Menu for Measure / Dimension Object

13.  Click New Measure on the context menu.

The New Measure Wizard - Step 1 of 2 dialog appears, as shown in Illustration 4.


Illustration 4:  The New Measure Wizard - Step 1 of 2 Dialog



While there are numerous commonly desirable options here, let's create a quick custom measure, just to get a feel for the process.  For purposes of this example, let's say we want to add a measure to present a projected across-the-board 6.50 percent increase in the Information Systems expense amount.



14.  Select the Custom Formula radio button.


15.  Click Next.


The New Measure Wizard - Step 2 of 2 dialog appears.


16.  Type Projected 1998 into the Name box.


17.  Type the following simple formula into the Formula box (or use the tools below it to insert the syntax):


[Measures].[Amount] * 1.065

The New Measure Wizard - Step 2 of 2 dialog appears, as depicted in Illustration 5.


Click for larger image

Illustration 5:  The New Measure Wizard - Step 2 of 2 Dialog

18.  Click Finish.

The newly added measure, Projected 1998 appears, under the My Items folder on the Measures tab, as shown in Illustration 6.


Illustration 6:  The Custom Measure Appears (Circled in Red)

19.  Click the Projected 1998 measure to highlight / select it.

20.  Click the Execute button (circled in Illustration 7) in the main toolbar.

The combined bar chart / grid changes to reflect the new measure, as depicted in Illustration 7.


Illustration 7:  The Custom Measure in the Display After Executing

Before we leave this example, let's do a quick row / column swap.

21.  In the Columns area of the lower Dimension Tool pane, click the Measures member to select it.

22.  Drag Measures over to the Rows area, dropping it under Store.

23.  Select the Store member.

24.  Drag Store to the Columns area, once inhabited by Measures.

The lower section of the Dimension Tool pane now appears as shown in Illustration 8.


Illustration 8:  Swapped Rows and Column Members - Preparation

We now need to execute again, to refresh the display.

25.  Click the Execute button once again.

The display changes to reflect the swapped rows and columns, as depicted in Illustration 9.


Illustration 9:  Swapped Rows and Columns After Executing

Swapping rows / columns might not improve the presentation in this case, but can often be a valuable tool for analysis / reporting purposes.

26.  Drag the Measures and Store members back to their original positions.

27.  Click Execute to refresh the display, once more.

28.  Select File --> Save Book to resave the Briefing Book.

We will examine further common navigation features, together with more advanced attributes, in our next example.

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



We can drill down the hierarchy further in this manner, or we can perform drill ups, cross-dimensional drills and other actions.  We can accomplish this by using a right-click (with the resulting context menu), the Navigation item in the main menu, and other options, for a data item upon which we wish to gain further insight. 



14.  Right-click the USA field in the display.



The context menu appears as shown in Illustration 15.




Illustration 15:  Context Menu Options



Relevant options that are available in the context menu are summarized in Table 1.



     
   

Drill Down

Displays next lower hierarchy level / more information about member

Drill Up

Displays next higher hierarchy level / less information about member

Expand

Provides more detail about member / leaves other members at their current hierarchy levels

Collapse

Displays member only / hides detail associated with it

Isolate

Displays only selected member(s)

Eliminate

Removes selected member(s) from data display


Table 1:  Context Menu Option Summary

15.  Select Isolate --> Warehouses - USA from the context menu.

USA is isolated as the only row in the display.

16.  Click USA once to drill down to the three states that make it up.

The drilldown occurs.

17.  Right-click the CA data field.

18.  Via the context menu and cascading menu, select Drill Down --> Select from List.

The Drill down selection list appears.  At this point, we are offered the capability to drill down across dimensions, a highly useful capability that is not as readily available in other business intelligence tools on the market today.

19.  Select / highlight only the [Product].All Products

The Drill Down selection list, with our choice highlighted appears as shown in Illustration 16.


Illustration 16:  Drill Down Selection List - With Our Choice Highlighted

20.  Click OK.

The display adjusts to reflect our new drill down, and appears as depicted in Illustration 17.


Illustration 17:  New Data Display, Secondary Drill Down Result in Place

Notice, once more, the Warehouse dimension tab contents.  We are seeing Children of USA, displayed in the Warehouse dimension hierarchy as the selected members for the display.

We can continue to drill further, return to prior displays by drilling up, or perform other actions that we reviewed in Table 1.  (Again, these are also available by selecting Navigate in the main menu). For now, we will move on to explore additional areas.

21.  Select Book --> Add to Briefing Book from the main menu.

The Add to Briefing Book dialog appears.

22.  Name the page something that makes sense.  (I called mine ANSYS19-02.)

23.  Click OK to save.

We will now draw our article to a close with an examination of an advanced visualization feature of ProClarity, Perspective analysis.

Advanced Visualization: Perspective Analysis

Resembling a scatter chart, but designed to provide more detailed and richer information, a Perspective view in ProClarity allows us to perform analysis centered on the relationship between two measures, across a set of dimensional members.  Perspective views can provide useful insight into data we are exploring, in scenarios where we are analyzing large volumes of data, undertaking quadrant analysis, or anytime we wish to map multiple measures across a dimension.

As we have already saved our work in the previous section, we can "refresh" the view quickly to "start with a new slate," in examining a Perspective visualization in ProClarity.

1.  Click the Reset button (shown in Illustration 18) on the main toolbar, just below the main menu.

Click for larger image

Illustration 18:  The Reset Button on the Main Toolbar

We receive a message box, confirming our wish to reset both view options and dimensions, as shown in Illustration 19.

Click for larger image

Illustration 19:  The Confirm Message Box

2.  Click Yes to continue.

The view resets to a single bar, a graphical representation of the top (or All) rollup amount, whose nature is similar to that of the value that initially greeted us in Part I (with, of course, that view being in the context of the Decomposition Wizard), when we first established connectivity to the Budget cube.

3.  Select View --> Advanced Analysis Tools --> Perspective from the main menu, as depicted in Illustration 20.


Illustration 20:  Select View --> Advanced Analysis Tools --> Perspective

4.  The Welcome dialog for the Perspective Wizard appears, as shown in Illustration 21.


Illustration 21:  The Welcome Dialog - Perspective Wizard

5.  Click Next.

The Perspective Wizard - Step 1 of 2 dialog appears, as depicted in Illustration 22.


Illustration 22:  The Perspective Wizard - Step 1 of 2 Dialog

It is here that we select the dimensions that we wish to analyze within the context of the current Perspective.

6.  Click the Warehouse dimension to highlight it.

7.  Click the Change Selection ... button on the Step 1 of 2 dialog.

The Change Selections dialog appears.

8.  Expand the All Warehouses level by clicking the "+" sign to its immediate left.

9.  Expand the USA level by clicking the "+" sign to its immediate left.

10.  Click+Shift to select the three states located under the expanded USA simultaneously.

The Change Selections dialog now appears as shown in Illustration 23.


Illustration 23:  The Change Selections Dialog

11.  Click OK to accept changes.

We are returned to the Perspective Wizard - Step 1 of 2 dialog, where we see confirmation of the Warehouse states' selection.

12.  Click the Product dimension to highlight it.

13.  Click the Change Selection ... button on the Step 1 of 2 dialog.

The Change Selections dialog appears.

14.  Right-click the All Products level atop the Product dimension hierarchy.

15.  Point to the Select Descendants item on the context menu that appears.

16.  Click Product Family on the context menu that cascades, to select
the item, as depicted in llustration 24.

Click for larger image

Illustration 24:  Select Descendants --> Select Product Family from Cascading Menus

The Change Selections dialog now appears as shown in Illustration 25.

Click for larger image

Illustration 25:  The Change Selections Dialog

17.  Click OK to accept changes and return to the Perspective Wizard.

The defaulted year of 1997 will suffice for our immediate purposes with regard to the Time dimension, as will defaults for the other dimensions.

18.  Click Next.

We arrive at the Perspective Wizard, Step 2 of 2 dialog.

19.  Click the selector button to the right of the Y-axis Measure box.

The Measures dropdown selector appears.

20.  Select Warehouse Profit from the selector.

The selector appears as depicted in Illustration 26.


Illustration 26:  Choose Warehouse Profit in the Selector

21.  Click OK to accept changes and return to the Perspective Wizard.

22.  Click the selector button to the right of the X-axis Measure box.

The Measures dropdown selector appears.

23.  Select Warehouse Cost from the selector.

The selector appears as depicted in Illustration 27.


Illustration 27:  Choose Warehouse Cost in the Selector

24.  Click OK to accept changes and return to the Perspective Wizard.

The Perspective Wizard, Step 2 of 2 dialog appears as depicted in Illustration 28.


Illustration 28:  The Perspective Wizard, Step 2 of 2, with Selections

25.  Click Finish.

The data view display appears as shown in Illustration 29.


Illustration 29:  The Initial Data View

26.  Click OK to accept changes and return to the Perspective Wizard.

27.  Right-click anywhere on the white graph area of the data view.

28.  Select Data Point Attributes from the context menu that appears.

The context menu, with our selection circled, appears in Illustration 30.


Illustration 30:  Select Data Point Attributes

The Data Point Attributes selection dialog appears.

29.  Click the Warehouse tab to bring it to the front, as necessary.

30.  Double-click All Warehouses on the tab.

31.  Double-click USA, which appears along with the two other countries under the expanded All Warehouses.

The three states in which we have warehouses now appear.

32.  Click-highlight CA.

33.  Select any shade of Red in the Color selector.

34.  Select "* Star" in the Shape selector.

35.  Click Add.

CA appears in the Rules list, with a red star to its left.

36.  Click-highlight OR.

37.  Select any shade of Blue in the Color selector.

38.  Select "* Star" in the Shape selector.

39.  Click Add.

OR appears in the Rules list, with a blue star to its left.

40.  Click-highlight WA.

41.  Select any shade of Green in the Color selector.

42.  Select "* Star" in the Shape selector.

43.  Click Add

WA appears in the Rules list, with a green star to its left. 

The completed Data Point Attributes selection dialog appears as depicted in Illustration 31.


Illustration 31:  Data Point Attributes

44.  Click OK.

The view appears, as presented in Illustration 32, showing that, although Warehouse Profit and Cost amounts varied among the states for the three product families, profit margins / ratios, at least at this level of rollup, were not dramatically different (note that the points plotted approach a straight line).

Consistent with the behavior of the application in other areas, we can see the amounts that underlie each coordinate by simply hovering the pointer over it. The values that make it up appear, as shown for one example in Illustration 32.


Illustration 32:  Perspective - Final Data View

The uniformity we see in our results is largely due to the fact that this is a sample database; we might also attempt the same exercise at lower levels in the Product dimension hierarchy to isolate differences that are more meaningful. Were there significant outliers, we might investigate the details further through other analytical approaches.  Whatever the case, the ProClarity Perspective view is an excellent tool for uncovering patterns for our various business purposes.

45.  Select Book --> Add to Briefing Book from the main menu.

The Add to Briefing Book dialog appears.

46.  Name the page something meaningful.  (I called mine ANSYS19-03.)

47.  Click OK to save.

48.  Select File --> Save Book.

49.  Select File --> Exit when ready to close ProClarity.

We have examined only a handful of the options available to analysts in ProClarity.  Much of the available body of functionality, including the numerous ways to save, publish and otherwise deploy the results of analysis and other report components, can be reviewed within the online documentation. This would be an obvious next step for anyone interested in evaluating or learning the application further.

ProClarity is a rising star in the business intelligence market, particularly because MSAS is gaining acceptance as a low-cost source of robust OLAP data sources.  ProClarity and other solutions that capitalize on the MSAS cube will benefit significantly from the fact that, as the RDBMS-level MSAS cube rapidly gains market share, organizations will seek to choose more pragmatic solutions that let MSAS handle cube builds, while focusing solely on OLAP analysis and reporting. 

The days of acceptance of the tiresome overhead and financial punishment of a proprietary cube machine, among the other peripheral structures that the previously dominant business intelligence solution providers peddle in their enterprise business intelligence solutions, can no longer be gladly suffered by those organizations "in the know."  For the OLAP reporting portion of a larger, custom-fit enterprise analysis and reporting solution, ProClarity is an excellent example of a robust, easy-to-use application that should be evaluated early in the selection process.

Summary ...

In this, the second half of a two-part article, we explored ProClarity Professional in a return to an earlier subseries, Reporting Options for Analysis Services CubesOur focus in those articles, as well as this, was to respond to a recurring request from readers to explore options for analyzing and reporting data in MSAS cubes. 

After a brief introduction to ProClarity, together with an overview of establishing connectivity between ProClarity and an MSAS cube, we examined some of the options offered by the application for analyzing our OLAP data.  We performed practice examples of browsing and analysis from within the application, continually examining the layout and navigation of the ProClarity interface as we practiced its use.

In this two-article set, we provided a hands-on introduction to numerous useful features that are available within ProClarity to provide for analyzing, and creating enterprise reporting components from, data in MSAS cubes.  We worked through practice examples of ProClarity's functionality, discussing several key advantages that the tool offers.  Finally, throughout our exploration of the application, we exposed various use and navigation tips for the ProClarity interface in analyzing, and reporting from, MSAS cubes.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved