Using PowerPlay for Windows for
Reporting and Analysis with an Analysis Services Cube
The PowerPlay Windows Client, known as PowerPlay for Windows ("PPWIN")
in much of the recent literature, provides an option for reporting from
OLAP cubes. Client reporting / exploring of cube data is done over the network
via PPES. The PPWIN approach is often promoted as being for "Power
Users," although I have certainly seen a mix of complexity in actual uses
over the time I have implemented PowerPlay. True, a more powerful ad hoc
capability is indeed possible with PPWIN, together with several more
elaborate reporting features, but the PPWEB reporting approach, which we
overview later in the article, certainly provides a respectable
cadre of attributes that make it a considerably versatile and potent reporting
PPWIN allows for two "modes" of
reporting, referred to as "Reporter" and "Explorer."
Explorer is a good choice for (as its name implies) exploring, or
browsing, the information in the cube. Explorer provides a good "overall
cube" view that allows us to examine cube categories, dimensions,
measures, and other components, particularly in a "whole" view of the
cube as a data source. Moreover, the Explorer mode allows us to easily present
data as a "percentage of a whole," for example, as a percentage of
the row, column, total and so forth.
Reporter works best when we have a specific
requirement for information that we wish to present. Reporter allows us
to perform calculations, and provides other "flexibilities" not found
in the largely browser-esque Explorer.
An associated option exists for an Excel
interface (PowerPlay for Excel is the name given this option), as well,
which is implemented in Excel as an add-in, if the worksheet look and
feel is desirable. If this option is selected, no further "client"
application is required on the user's PC; many of the familiar PowerPlay client
components appear within the enhanced Excel interface, although there are some
limitations in choosing the Excel option over the pure PPWIN client.
Let's look at using PowerPlay to
report from our Analysis Services cube. We'll examine a few of the capabilities
within PowerPlay for viewing cube data, and get a feel for the functionality PowerPlay
can provide us in reporting for Analysis Services data sources. We have
established connectivity to the Warehouse cube via Cognos Connect
in the steps above. Next, we will initialize PowerPlay and begin
exploring our cube.
Click the Start button.
Select Programs -> Cognos
EP Series 7.
Select Cognos PowerPlay
from the submenu that appears.
The Welcome dialog for Cognos
PowerPlay appears, as shown in Illustration 17.
Illustration 17: Cognos
PowerPlay Welcome Dialog
Click the Create a New
The Choose a Local Cube dialog
appears, as shown in Illustration 18. (If the Choose a Remote Cube
dialog appears instead, simply click the Local radio button to change to the ...
Illustration 18: The
Choose a Local Cube Dialog
Click to select the
MSSQL_Warehouse pointer .mdc file we created earlier.
PowerPlay opens and displays a default Explorer view
of the MSSQL_Warehouse cube, made possible by its connection to the
Analysis Services Server via the Pointer cube file we created in an earlier
section. The crosstab report we see should resemble that shown in Illustration
Illustration 19: The PowerPlay
Explorer Default Report View - MSSQL_Warehouse Cube
We can perform the same general
PowerPlay functionality in working with the MSSQL_Warehouse cube that we
can perform with a proprietary PowerCube. We can also access local cubes
(.cub files) that are saved directly from the MS PivotTable Service from
PowerPlay. Further, we can also perform many functions that are not
available within the standard PowerCube scenario. Among these are Analysis
Services' extended capabilities in the areas of multiple hierarchies, member
properties, and virtual cubes and dimensions. We can exploit differences
that exist in Analysis Services' calculated members capabilities, as
Click the View menu item
in the top toolbar.
Uncheck the Dimension Viewer option (to free up a
bit more real estate on the screen), if appropriate.
Click the Store Folder
in the Dimension Line (the line of labeled folder icons just atop
the crosstab area.)
We are presented with a flyout
selection of first level Store dimension categories (countries),
from which we can select to drill down, as shown in Illustration 20.
Illustration 20: Drilldown
Selection for the Store Dimension
10. Click USA to drill to the USA
The data in the crosstab now reflects the drilled down
presentation. Notice that "USA" now appears as the label on the Store
dimension folder - where we saw Store before. This also demonstrates
handily something about how PowerPlay's Explorer mode works: When we
drill down in PowerPlay Explorer, we replace a parent category with
its child categories in the report - all simultaneously. In this
example, we can see that, by selecting USA (country level) for drilldown, the child
categories (state level) are displayed. (Drilling down on a nested level,
the creation of which we will introduce later, preserves the number of levels,
until we reach the bottom of the hierarchy.)
The display appears as shown in Illustration
Illustration 21: Drilldown
Results - USA Stores