About the Series …
This is the twelfth
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 ("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
systems.
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
BI 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
instructions.
Introduction
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
Services series), we began in Article
Ten 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
Report 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
Options 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
Options 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
Options 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
options; -
The basics of use and
navigation of the PowerPlay Client (aka User Edition Windows)
and PowerPlay Web components in reporting from Analysis Services OLAP
cubes.
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.
As
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
security (that is, security that is non-integrated between Access
Manager, 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
Web, 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
Web ("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.
