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

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.

Introduction

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.

William Pearson
William Pearson
Bill has been working with computers since before becoming a "big eight" CPA, after which he carried his growing information systems knowledge into management accounting, internal auditing, and various capacities of controllership. Bill entered the world of databases and financial systems when he became a consultant for CODA-Financials, a U.K. - based software company that hired only CPA's as application consultants to implement and maintain its integrated financial database - one of the most conceptually powerful, even in his current assessment, to have emerged. At CODA Bill deployed financial databases and business intelligence systems for many global clients. Working with SQL Server, Oracle, Sybase and Informix, and focusing on MSSQL Server, Bill created Island Technologies Inc. in 1997, and has developed a large and diverse customer base over the years since. Bill's background as a CPA, Internal Auditor and Management Accountant enable him to provide value to clients as a liaison between Accounting / Finance and Information Services. Moreover, as a Certified Information Technology Professional (CITP) - a Certified Public Accountant recognized for his or her unique ability to provide business insight by leveraging knowledge of information relationships and supporting technologies - Bill offers his clients the CPA's perspective and ability to understand the complicated business implications and risks associated with technology. From this perspective, he helps them to effectively manage information while ensuring the data's reliability, security, accessibility and relevance. Bill has implemented enterprise business intelligence systems over the years for many Fortune 500 companies, focusing his practice (since the advent of MSSQL Server 2000) upon the integrated Microsoft business intelligence solution. He leverages his years of experience with other enterprise OLAP and reporting applications (Cognos, Business Objects, Crystal, and others) in regular conversions of these once-dominant applications to the Microsoft BI stack. Bill believes it is easier to teach technical skills to people with non-technical training than vice-versa, and he constantly seeks ways to graft new technology into the Accounting and Finance arenas. Bill was awarded Microsoft SQL Server MVP in 2009. Hobbies include advanced literature studies and occasional lectures, with recent concentration upon the works of William Faulkner, Henry James, Marcel Proust, James Joyce, Honoré de Balzac, and Charles Dickens. Other long-time interests have included the exploration of generative music sourced from database architecture.

Latest Articles