Reporting Options for Analysis Services Cubes: Cognos PowerPlay

About the Series …

This is the twelfth
article of the series, Introduction to MSSQL Server 2000 Analysis
. 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 ("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

Along with MSSQL
Server 2000
and MSSQL Server 2000 Analysis Services, of which we
have made repeated use in the previous articles of the series, additional application
considerations apply for this tutorial because it introduces a third-party
reporting solution, Cognos PowerPlay. For those joining the series at
this point because of a desire to work with Analysis Services and its
components from a Cognos perspective, it is assumed that Analysis Services is
accessible to / installed on the PC, with the appropriate access rights to the
sample cubes (which are provided in a Typical installation of
Analysis Services). In addition, I will be performing all steps on a Windows
2000 Server, although the steps will be quite similar with later Windows operating

Through, and together
with, PowerPlay Enterprise Server (EP Series 7.0 or higher), we will
use PowerPlay for Windows, PowerPlay Web and various components of the Cognos
suite that underlie these applications to demonstrate the setup of, and
some approaches for, using PowerPlay to create and deploy reports based upon an
MSSQL Analysis Services OLAP cube as a data source. PowerPlay provides the
capability for establishing connections to our cubes, among other functions.
Because we are targeting users of PowerPlay, we will assume minimal installation
of the products involved, exposing the specific setup required to enable
reporting from Analysis Services cubes. If this is the first time Cognos
PowerPlay is being accessed on the machine, we may need to consult the Cognos
PowerPlay EP 7.0 (and above) online documentation for installation


While the majority of
our series to date has focused upon the design and creation of cubes within
Analysis Services (see Articles One through Nine of the Introduction to MSSQL Server 2000 Analysis
series), we began in Article
to discuss reporting
options for our cubes. This comes in response to the expressed need of several
readers for options in this regard – options beyond the mere browse
capabilities within Analysis Services.

In our last two lessons, Articles Ten and Eleven, we explored some of
the options offered by Microsoft Office – specifically the Excel PivotTable
and Office PivotTable List, respectively – for report
building with Analysis Services cubes.

As we did with the Excel PivotTable Report in the first of our Reporting
articles (Article Ten), we presented an introduction to using
FrontPage to retrieve and display information from our cubes in Article Eleven, first discussing the
steps needed to define our data source, and to establish a connection to the
cube. For each of the two options, we exposed the functionality involved, the
physical layout, and general navigation; we then explored the use of each in sample
browsing / reporting scenarios with Analysis Services cube data, exposing
differences in architecture when appropriate. Finally, we discussed various
presentation and formatting considerations for the two options.

Reporting Options
for Analysis Services Cubes: Cognos PowerPlay

In this lesson, we will continue our
exploration of what has become a popular topic, a topic which, as I mentioned
in Article Ten, generates many e-mails each week in my inbox, Reporting
Options for Analysis Services Cubes
As I stated in the first of the Reporting
articles, our focus is to explore options for obtaining reports
from Analysis Services cubes within a range of similar business
intelligence capabilities. My intent is therefore to review the process
of establishing connectivity and enabling reporting capabilities for each of
the options, and not to compare the product features themselves, to any
significant extent.
As I stated from the outset of the Reporting
articles, I will consider providing a similar examination of
other reporting products at a later time, based upon any suggestions I receive
from readers, if this turns out to be useful.

After a brief introduction to the
application, we will examine some of the options offered by Cognos PowerPlay
for report building with Analysis Services cubes. As we did with the MS Office
reporting options in the previous two Reporting Options articles,
we will present an introduction to using Cognos PowerPlay to retrieve and
display information from our cubes. We will discuss in order the steps needed
to set the application up specifically for accessing a non-Cognos cube, then expose
the steps required for the definition our data source, and for the
establishment of a connection to the cube. Next, we will briefly examine the
layout and navigation of the two reporting approaches that PowerPlay offers,
and examine illustrations of browsing and reporting our cube data from each.

The topics within this
article will include:

  • An introduction to the features that are available within Cognos
    PowerPlay EP Series 7.0
    and later, and options that provide for creating
    robust and flexible reports;

  • Setup of the Cognos PowerPlay components to provide
    two distinct reporting options, PowerPlay Web and PowerPlay Client
    (AKA User Edition Windows) reporting;

  • The establishment of
    connectivity and other preparatory steps to enable reporting via the two

  • The basics of use and
    navigation of the PowerPlay Client (aka User Edition Windows)
    and PowerPlay Web components in reporting from Analysis Services OLAP

Introduction to
Cognos PowerPlay

In exploring the use of Cognos
PowerPlay with Analysis Services cubes, we will be considering two primary
approaches. In this article, we will examine using Cognos PowerPlay to report from
the client platform, as well as over the web. As the two approaches share
several preparatory steps, we will perform the common setup procedures, and
then we will expose the specific setup of each.

virtually anyone using PowerPlay is aware, in a "typical"
implementation, the application forms the reporting component of an end-to-end
OLAP solution, whose proprietary OLAP data sources, "PowerCubes," are
created via another component called Transformer. Cognos began in
recent versions to make PowerPlay work with other OLAP servers, including MSSQL
Server Analysis Services
("Analysis Services"). The motivation
is obvious, as the ascendancy of the RDBMS-generated cube is, at this stage,
both inevitable and irresistible.

Introduction and Scope

In exploring the use of Cognos PowerPlay with
Analysis Services cubes, we soon become aware that there are multiple
approaches to reaching our ends. In this article, we will examine using Cognos
PowerPlay’s "client" and web incarnations, PowerPlay for Windows and
PowerPlay Web, respectively.

We will explore the components involved first,
and then we will expose the steps of setting up connectivity with simple
(that is, security that is non-integrated between Access
, the operating system, MSSQL Server / Analysis Services, etc.).
The integration of security is involved and beyond the scope of this article.
Our intent here is simply to offer another option for reporting from Analysis
Services Cubes, as we have stated in each article of the sub-series.

PowerPlay Enterprise Server ("PPES")
can act as the OLAP application server for PowerPlay for Windows and PowerPlay
, providing cube access to users. With PPES we can access not
only Cognos’ proprietary PowerCubes and our Analysis Services cubes, but
numerous other "third-party" cubes, such as IBM DB2 OLAP, SAP BW, and
Hyperion Essbase, through their respective OLAP database servers. PPES
also provides for user access to PowerPlay reports, which typically
originate in one of the PowerPlay client applications, via a report server.
Load balancing is an important attribute in the highly rated PPES, which
is designed to be multi-server and multi-process capable; with proper
configuration, it can easily support multiple concurrent requests from both Web
and Windows clients, which can be accessing reports and / or cubes.

As one might expect in any well-constructed
client / server architecture, the server performs many of the heavy processing
functions. Performance becomes more efficient because of minimal client
processing and reduced data traffic between the client and server involved in
any given operating relationship. The dual benefits are realized as a result of
the lowered need of the client to obtain data, and because caching mitigates a
portion of the need for communication.

We will examine the setup of the components together, as most of
the steps of establishing connectivity to the Analysis Services cube are held
in common between the different reporting options. Once we have established
connectivity and laid the groundwork for PowerPlay reporting in general, we
will discuss and contrast the two main options we have for analysis and
reporting, PowerPlay for Windows ("PPWIN") and PowerPlay
("PPWEB"). As I stated earlier, for the purposes of this article,
we will assume that we wish to establish simple security, meaning that
you may be prompted for logon input at more than one juncture while processing.

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