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

Tuesday Jan 20th 2004 by William Pearson

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

About the Series ...

This is the nineteenth 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, manipulation and use of multidimensional OLAP cubes. The series is designed to provide hands-on application of the fundamentals of MS SQL Server 2000 Analysis Services ("MSAS"), 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 MSAS.

Along with MSSQL Server 2000 and MSAS, which are employed 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 MSAS).

We will use ProClarity (Platform 5 or higher), and various components that come along with its typical / default installation, to demonstrate the setup of, and some approaches for, using this business intelligence tool for performing analysis and creating components of 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, please consult the ProClarity documentation for installation instructions.


While the majority of the articles of our series to date have focused upon the design, creation and manipulation of cubes within MSAS, I began in Article Ten to discuss analysis and reporting options for cubes. I undertook this because, as most of us are aware, until the release of MSSQL 2000 Reporting Services (stay tuned for a series of articles on this amazing new paradigm in enterprise reporting!), Microsoft does not provide a graphical tool within MSAS to analyze or report upon the data in an OLAP cube. In addition, I offered tutorials on using a couple of external options in response to the expressed need of several readers for alternatives in this regard - alternatives beyond the mere browse capabilities within Analysis Services.

We therefore explored some of the options offered by the Excel PivotTable Report (Article 10), the Microsoft Office PivotTable List (Article 11), and Cognos PowerPlay (Article 12) for report building with MSAS cubes. Within each of these articles we presented an introduction to using the respective tool to retrieve and display information from our cubes, first discussing the steps needed to define our data source, and to establish a connection to the cube. Next, for each option, we exposed the functionality involved, the physical layout and general navigation. Moreover, we then explored the use of each tool in sample browsing / reporting scenarios with MSAS cube data. Finally, we discussed various presentation and formatting considerations for the respective reporting options, when it was useful.

Reporting Options for MSAS Cubes: ProClarity

In this two-part article, we will return briefly to our exploration of what has continued to be a popular topic (and which, as I mentioned in Article Ten, results in several e-mails each week in my inbox), Reporting Options for Analysis Services Cubes. As I stated before, our focus is to explore options beyond the Analysis Manager / Sample Application interfaces for obtaining reports from, or browsing MSAS cubes within a range of similar business intelligence capabilities. My intent continues to be, therefore, to review the process of establishing connectivity and enabling reporting capabilities for each of the options, coupled with a high-level overview of the functionality that is available. It is beyond the scope of the articles to compare the product features themselves, to any significant extent. As I stated from the outset of the Reporting Options articles, I will consider providing a similar examination of other analysis and reporting applications, as time and circumstances permit, based upon useful suggestions I receive from readers. (This article is itself the result of such a suggestion.)

After a brief introduction to the application, together with an overview of the process of connecting it to an MSAS cube, we will examine some of the options offered by ProClarity for report building. As we did with the MS Office and Cognos reporting options in the previous Reporting Options articles, we will present an introduction to using ProClarity to retrieve and display information from our cubes, after exposing the steps required for establishment of a connection to the cube. Next, we will briefly examine the layout and navigation of the reporting interface that ProClarity offers, and examine illustrations of browsing and reporting our cube data from within the application.

The topics within this article will include:

  • The establishment of connectivity and other preparatory steps to enable analysis and reporting via the ProClarity application;
  • An introduction to several features and options that are available within ProClarity for browsing MSAS cubes, and creating components of robust and flexible reports;
  • The basics of use and navigation of the ProClarity interface in analyzing and reporting from MSAS cubes.

Introduction to ProClarity

In exploring the use of ProClarity with MSAS, we will primarily examine using the application to perform analysis from the perspective of ProClarity Professional, a part of the ProClarity Analytics Suite, which contains both Windows desktop and thin browser client components. The examples and images I present are taken from an installation in a simple, standalone server environment. We will be looking at the Windows desktop component, and focusing more on analysis than reporting, per se, as the same principles apply in general; Analysis typically involves more interactive scenarios, and the flexibility that ProClarity affords in publishing allows the practitioner to generate and deploy reports in many common formats and environments.

ProClarity is typically used by advanced analysts and other power users to perform analysis of cube data, and to create, manage and publish reports, KPIs, queries, sets and logic to numerous end destinations that are anticipated in the suite. Included are ad-hoc analytic capabilities, powerful visualization and flexible presentation functionality, and other robust query and calculation options. One of several strengths of the application, as we shall see, is that it removes unnecessary complication from the analysis process, making decision support more intuitive and faster (the aim of many of the solutions in the Enterprise Business Intelligence applications class), and available in a "self-serve" environment. The end desire is a unified, integrated analysis and reporting capability that is reliable, flexible, easy to use and rapidly delivered.

ProClarity comprises a powerful enterprise analysis and reporting option that promises substantial savings over the cost of any of the large, proprietary solutions such as Cognos, Business Objects and others whose five- to six-figure price tags include both relational and OLAP elements, as well as cube-building apparati. While these proprietary solutions are compatible with most popular RDBMS', it has only been in recent versions that the "Big Sisters" began to make their once dedicated cube reporting components work with other OLAP servers, including MSAS. The motivation is obvious, as the ascendancy of the RDBMS-generated cube is, at this late stage, both inevitable and irresistible.

A tool like ProClarity allows us to perform in-depth analysis and build robust, powerful OLAP reports and business intelligence applications, based directly on MSAS cubes. This eliminates the peripheral components that add so much to the cost of the predecessor enterprise reporting applications - and allows the flexibility to select relational and other analysis, reporting and consolidation components singly and independently, thereby affording the construction of a custom solution tailored ideally to the industry and reporting environment.

Article Scope

In exploring the use of ProClarity with MSAS cubes, we soon become aware that there are multiple approaches to reaching our ends. In this article, we will examine using the ProClarity desktop incarnation, to report from select sample cubes that accompany a typical installation of MSAS.

We will expose the steps of setting up connectivity without many of the complications of real world implementation, where the integration of security and a raft of other components, all of which are beyond the scope of this article, would require careful consideration. Our intent here is simply to offer another option for reporting from MSAS Cubes, as we have stated in each article of our Reporting Options for Analysis Services Cubes sub-series.

Using ProClarity with an MSAS Cube

We will assume a basic understanding of ProClarity throughout the article, as well as a proper "default" installation of the software in general, as we noted in the introduction above. The article assumes you have access to the MSSQL Server / the MSAS sample cubes, be it via Windows Authentication Mode or Mixed Mode (for purposes of this article, I'm using Windows Authentication). Keep in mind that the multiple potential security configurations in our individual environments may result in some differences in the steps as we proceed.

Initialize ProClarity and Establish Connectivity

The following steps allow us to establish connectivity to the Budget sample cube in the relatively simple scenario we have established for this lesson.

1.  Click the Start button.

2.  Select Programs --> ProClarity.

3.  Select Professional from the Submenu that appears, as shown in Illustration 1.

Illustration 1: Select ProClarity --> Professional

The Welcome dialog appears.

4.  Select the Cube for browsing radio button, as shown in Illustration 2.

Illustration 2: The Welcome Dialog

5.  Click OK.

The Connect ... dialog appears.

6.  Select the appropriate server in the Server selector box.

Use the Find Server search button, if necessary. In a single server configuration, the correct server will likely be the default. Mine is MOTHER1, as shown in Illustration 3.

Illustration 3: Select the Server that Houses the Sample Cubes

We also have the option of selecting a local cube as the data source within this dialog. The Advanced tab extends the dialog to allow for input of a Username and Password, should security requirements dictate the use of authentication of this sort.

7.  Click OK.

The Open Cube dialog appears, as shown in Illustration 4.

Illustration 4: The Open Cube Dialog

The Open Cube dialog lists the MSAS cubes existing on the server. The Connect ... button that appears on the dialog affords us another chance to change servers, as required, to connect to the appropriate cube to meet our needs. We note, in the lower left corner of the dialog, that connection is maintained via Microsoft OLE DB Provider for OLAP Services 8.0.

8.  Select the Budget cube by highlighting it.

I chose the Budget for Part I of our lesson because it has a very straightforward design and a single measure. This means minimal distraction with the structure of the cube, to allow us to focus on ProClarity and its functionality in our first exercises.

9.  Click OK.

We have the option of selecting the desired visualization with which we browse our cube in the next dialog that appears, as shown in Illustration 5.

Illustration 5: Select How You Want to Visualize Your Information Dialog

We will proceed from this point in the following section.

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

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.

Exploration and Analysis: Charting Options

The unique decomposition perspective of our data that we can obtain using ProClarity Professional is further embellished by the visualization options that the application offers. Let's take a look at some of the simpler charting options based upon our work so far.

1.  Select View --> Business Charts from the main menu.

We note that several options are available for simple charting, as shown in Illustration 18, which, depending upon the dimensions and measures we have selected, may offer several useful views of our data.

Click for larger image

Illustration 18: Business Chart Options that Appear under the View Menu Item

2.  Select Bar Chart from the submenu shown above.

The bar chart appears, sorted, highest to lowest IS Cost, with the Alameda store clearly leading the rest, as depicted in Illustration 19.

Illustration 19: The Bar Chart for IS Cost - California Stores

NOTE: By changing the physical dimensions of the overall application window, we can make the graphics fit a wide range of physical dimensions, just as I have compacted my view above. Axis intervals shown on the chart automatically adjust with re-sizing, to give the graphic a consistent appearance within the context of the information that it is presenting.

Another decided strength that is apparent in the view presented is an indication of just what data we are viewing: The title bar above the bar graph, for example, displays measure, dimension, and level information; sort criteria is made clear underneath the graphic, and a legend appears to the right to link the colors to the members that they represent. Touching the pointer to a given member (Los Angeles in the example shown in Illustration 19) results in the contextual display of even more information specific to the member (in this case, value and name), putting a "number with the graphic" should an information consumer wish to easily see the exact amount of the measure.

We can corroborate, as well as embellish further, our view of the information by juxtaposing the same data, in grid form, below the newly created graphic.

3.  Select View --> Grid --> Bottom to add the grid view below the bar chart.

The composite view now appears as shown in Illustration 20. Because some readers relate more to graphic representations of the data, others to numbers, and some to a combination of both, this representation is often effective in getting a message across to a group of information consumers.

Illustration 20: Composite View - Bar Chart and Grid (Notes Icon Circled)

ProClarity offers flexibility in constructing similar views with different representations, of course, and the perfect combinations can be assembled with a simple swap out of the desired components.

Another compelling feature, at least from the standpoints of convenience and practicality, is the capability to append notes to our graphic composition. The Notes icon in the upper right corner of the title bar (circled in Illustration 20) can be used as an "on / off" switch for displaying a notes area that attaches to the presentation. This can come in quite handy in adding text to comment upon the data, for presenting assumptions surrounding certain elements, and a host of other possible uses. (This sort of accommodation would be a welcome addition to many of the enterprise reporting packages on the market today.)

We will conclude Part I at this stage, returning in the second half of the article to examine many more of the features that make analysis of our data a breeze in ProClarity. But first, let's go a step further in saving our view. Say we work long and hard at putting together the prefect composite for a group of information consumers, and that we want the view we have so assiduously composed to be immediately available after cube refreshes, reflecting the new data in a consistent display that is easily summoned by the viewer on a recurring basis.

4.  Select My Views from the main menu.

5.  Click Add to My Views from the dropdown menu.

The Add to My Views dialog appears.

6.  Name the view IS Cost - California Focus, as shown in Illustration 21.

Illustration 21: Adding the View to My Views

The new view appears to the left of the interface, in the Internet Explorer-esque My Views pane. The title bar we discussed earlier, above the graphic we have created, also changes to reflect the new view name. These changes are circled in Illustration 22, which depicts a full view of the ProClarity interface at this point in our practice set.

Illustration 22: The Interface at this Stage

7.  Select File --> Save Book from the main menu.

8.  Exit the application as desired.

We will return to our examination of ProClarity in Part II of this article, where we will delve further into the application and see more examples of ways that it can add value in the analysis and presentation of the data in our MSAS cubes.

Summary ...

In this article, we returned to our exploration of an earlier subseries, Reporting Options for Analysis Services Cubes. As 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 obtaining reports from, or browsing, MSAS cubes within a range of similar business intelligence capabilities. This article presented the first of two parts, and focused on ProClarity Professional, based upon a suggestion I received from a noted practitioner in the Business Intelligence arena, and upon my own very favorable experiences with this outstanding tool in recent months.

After a brief introduction to the application, together with an overview of the process of connecting it to an MSAS cube, we examined some of the options offered by ProClarity for analyzing our OLAP data. Once we established connectivity, we performed 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.

We will continue our exploration of ProClarity in Part II, where we will uncover more of the rich analysis and reporting capabilities of the application.

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

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

Mobile Site | Full Site