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 ...
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
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
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.
In Part I of this article, we returned to the objectives 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 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
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.
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
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
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.
Click the Start
from the Submenu that appears.
Select the Local
Briefing Book radio button.
The Open Briefing
Book ... dialog appears.
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.
Select the Briefing Book Saved in Part I
The Briefing Book
opens, displaying the page we saved in Part I.
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).
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.
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
The New Measure
Wizard - Step 1 of 2 dialog appears, as shown in Illustration
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
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.
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
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
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.
Select File --> Open Cube.
Select the FoodMart
2000 Warehouse cube.
The Open Cube dialog
appears as shown in Illustration 10.
The Select how you
want to visualize your information dialog appears.
Select the Grid option.
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.
Select the Warehouse
dimension from within the Background box, at the bottom of the Dimension
Drag the Warehouse
dimension to drop it above the Product dimension in the Rows
Click the Measures
tab, atop the Dimension Tool.
Select (by click-highlighting) the Warehouse Cost measure on the
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
The arrangement of the
Dimension Tool is depicted in Illustration 11.
Illustration 11: Arrangement of the
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
The display drills
down (and auto refreshes) to reflect the child warehouse cities, as depicted in
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
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
The context menu appears
as shown in Illustration 15.
Illustration 15: Context Menu
Relevant options that
are available in the context menu are summarized in Table 1.
Table 1: Context Menu Option Summary
Displays next lower hierarchy level /
more information about member
Displays next higher hierarchy level
/ less information about member
Provides more detail about member / leaves
other members at their current hierarchy levels
Displays member only / hides detail
associated with it
Displays only selected member(s)
Removes selected member(s) from data
15. Select Isolate --> Warehouses - USA from the context menu.
USA is isolated as the only row in
16. Click USA once to drill down to the three
states that make it up.
The drilldown occurs.
17. Right-click the CA data
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
19. Select / highlight only the [Product].All
The Drill Down
selection list, with our choice highlighted appears as shown in Illustration
Illustration 16: Drill Down Selection List - With Our Choice Highlighted
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
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.
Click the Reset
button (shown in Illustration 18) on the main toolbar, just below the
We receive a message
box, confirming our wish to reset both view options and dimensions, as shown in
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.
Select View --> Advanced Analysis Tools --> Perspective from the main menu, as depicted
in Illustration 20.
Illustration 20: Select View --> Advanced Analysis
Tools --> Perspective
dialog for the Perspective Wizard appears, as shown in Illustration
Illustration 21: The Welcome Dialog
- Perspective Wizard
Wizard - Step 1 of 2 dialog appears, as
depicted in Illustration 22.
Illustration 22: The Perspective
Wizard - Step 1 of 2 Dialog
is here that we select the dimensions that we wish to analyze within the
context of the current Perspective.
Click the Warehouse
dimension to highlight it.
Click the Change
Selection ... button on the Step 1 of 2 dialog.
Selections dialog appears.
Expand the All
Warehouses level by clicking the "+" sign to its immediate
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.
Selections dialog now appears as shown in Illustration 23.
Illustration 23: The Change
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.
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.
Product Family on the context menu
that cascades, to select
the item, as depicted in llustration 24.
Selections dialog now appears as shown in Illustration 25.
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.
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.
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.
dropdown selector appears.
23. Select Warehouse Cost from
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
Illustration 28: The Perspective
Wizard, Step 2 of 2, with Selections
The data view display
appears as shown in Illustration 29.
Illustration 29: The Initial Data
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
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.
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 Cubes. Our 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.