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 ...
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.
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).
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
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.
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.
topics within this article will include:
establishment of connectivity and other preparatory steps to enable analysis
and reporting via the ProClarity application;
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
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.
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.
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
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
4. Select the Cube for browsing radio
button, as shown in Illustration 2.
Illustration 2: The
The Connect ...
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: 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.
The Open Cube
dialog appears, as shown in Illustration 4.
Illustration 4: 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
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
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
proceed from this point in the following section.
Exploration and Analysis: Decomposition - It's a Good Thing
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.
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
1. Click the Decomposition Tree
dialog for the Decomposition Tree Wizard appears, as depicted in Illustration
Illustration 6: Welcome
Dialog for the Decomposition Tree Wizard
2. Click Next to proceed.
Tree Wizard initializes, and prompts us for specification, in the dialog
that appears next, of the measure and dimension upon we wish to perform
3. Select Amount in the Breakdown
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
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
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
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
Cubes: Financial Reporting - Part I and Part
9. Right-click the Net Income
A context menu
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
11. Right-click the Total Expense
12. Select Drill Down --> Account --> Level 4 across the cascading menus that
The resulting view
appears in Illustration 13.
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.
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
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
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
Illustration 15: The California Stores Appear
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
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
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
20. Click OK, after renaming to
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
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.
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.
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.