Discover another option for
effectively reporting from MSSQL Server Analysis Services cubes. Author Bill
Pearson continues his "triptych" of practical reporting solution
tutorials with an Introduction to Cognos PowerPlay for Analysis Services.
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
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
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
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.
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
The basics of use and
navigation of the PowerPlay Client (aka User Edition Windows)
and PowerPlay Web components in reporting from Analysis Services OLAP
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
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.
We will assume a basic understanding of
Cognos PowerPlay throughout the article, as well as a proper installation of
the software in general, as we noted in the introduction above. This article
assumes a "default" installation of Cognos PowerPlay (PPES and
PPWIN components), as well as the correct installation of Cognos
Connect. I am assuming, furthermore, that you are configured as a Cognos
user in Access Manager and have the appropriate rights / privileges to
complete the steps in this article, or that you have been allowed temporary
privileges. (I will be acting as the Administrator on my server, with my
user set up in Access Manager, but with little else configured in the
way of sophisticated security.) Finally, the article assumes you have access
to the MSSQL Server / the Analysis Services sample cubes, be it via Windows
Authentication Mode or Mixed Mode (for purposes of this article, I'm using SQL
Server authentication). Keep in mind that the multiple potential security
configurations in our individual environments may result in some differences in
the steps (log in requests, etc.) as we proceed.
to MSSQL Server OLAP Cubes in Configuration Manager
The following steps are critical in
establishing connectivity to cubes that reside on a server with PowerPlay
client; we also must have connectivity established to view cube data from the
web. We begin by taking the following steps to configure PPES to access
the appropriate driver:
Click the Start button.
Select Programs -> Cognos
EP Series 7.
Select Tools from the Submenu
Select Configuration Manager,
as shown in Illustration 1.
Illustration 1: Select
Manager Welcome dialog, Introduction tab appears, as shown in
Illustration 2, unless the feature has been disabled prior to this use.
2: The Configuration Wizard - Welcome Dialog, Introduction Tab
The Configuration Manager Welcome
dialog, Start tab appears, as shown in Illustration 3.
3: The Configuration Wizard - Welcome Dialog, Start Tab
Click the Open the Current
Configuration button on the Start tab (shown circled in Illustration
The Configuration Manager dialog
(my Current Configuration appears as "Untitled") appears, as
shown in Illustration 4.
4: Current Configuration Dialog (Compressed View)
the Configuration Manager dialog did not appear as shown above, simply select File
> Open Current Configuration to reach the same point.
Expand the server (in the left
pane), as shown in (partial view) Illustration 5 (mine appears in the
illustration as MOTHER), by clicking the "+" sign to
its left, if necessary.
5: Expand the Server (Shown as MOTHER) ...
Expand Services within
the hierarchy of the tree.
Expand OLAP Data Access
under Services, as shown in Illustration 6.
6: Expand OLAP Data Access, under Services (Partial View)
Select 3rd Party
(the default) to "1" in the right pane, just to the right of Microsoft
OLAP Service, as depicted in Illustration 7.
7: Enabling MSSQL Server OLAP Cube Selection (Compressed View)
Right-click OLAP Data Access in the left pane.
The context menu shown in Illustration
8: OLAP Data Access Context Menu
Click Apply Selection to
apply the change made in Step 11 above.
A warning message box appears.
A status meter indicates update
progress, and then a message box appears confirming successful application of
Click File ->
Save to save our change within
Configuration Manager. If prompted to assign a name / location for the .ccs
file, do so.
Select File ->
Exit to close Configuration
We have enabled the creation of
connections with MSSQL Server OLAP cubes. Now, let's take the steps to connect
to our Analysis Services cube. First, we'll introduce PowerPlay Connect.
Connect to the
Analysis Services Cube using PowerPlay Connect - Simple Security
PowerPlay Connect is the Cognos mechanism for linking OLAP data from
servers other than their own into Cognos PowerPlay. Cognos offers many
integrated Business Intelligence applications, and will likely remain the major
player in its arena for at least the near-term. As I continually emphasize to
my clients, the future, at least from the perspective of the mainstream, of
what will become "third-party" (that is, non-RDBMS generated) cubes
is rather obvious. Connectivity between Cognos PowerPlay and Analysis Services
cubes provides an excellent opportunity to maintain existing reporting
capabilities, while performing background conversions to Analysis Services OLAP
data sources, as part of a forehanded long-term strategy.
You can begin the connect process by
taking the following steps:
Click the Start button.
Select Programs -> Cognos
EP Series 7.
Select Tools from the Submenu
Select PowerPlay Connect,
as shown in Illustration 9.
Illustration 9: Initialize
PowerPlay Connect initializes, its first dialog appearing as shown
in Illustration 10. Notice that the Database type is defaulted
Illustration 10: PowerPlay
Connect Appears with Default Database Type
Select MS SSOS ODBO
using the Database type dropdown selector.
(MS SSOS stands for Microsoft
SQL Server OLAP Services, referring to the MS SQL Server Version 7 name for
Analysis Services, OLAP Services. ODBO, as most of us are aware,
is an acronym for the Microsoft OLE DB for OLAP standard.)
NOTE: If MS SSOS ODBO does not
appear as an option, you need to return to the previous section to enable the
creation of connections with MSSQL Server Analysis Services cubes.
In the Server box, type
in the name of the PPES server.
NOTE: The server name can be entered as "localhost"
if the PPES server is on the same PC. Mine appears as MOTHER in
the appropriate example screens throughout this article.
We can fill in the next box, Provider
information (Database...) as follows:
Click the ellipses button to
the right of the Provider (Database...) box.
The Choose a Remote Cube dialog
Click the Connections
The Connections dialog appears.
The Add a Connection dialog
Type Warehouse Analysis
Services Cube in the Connection box.
Type the server name
(localhost, again, will work, if appropriate to your environment) in the Server
Select Microsoft SQL Server
OLAP Server in the Server Type selector toward the bottom of the of
the Add a Connection dialog.
The Add a Connection dialog box
appears as shown in Illustration 11.
Click OK to apply the
The Connections dialog appears,
displaying our new Connection name, as depicted in Illustration 12.
Illustration 12: The Connections Dialog Displays the New
The Connections dialog closes,
and we are returned to the Choose a Remote Cube dialog.
Leaving the Connection selector
at the new Warehouse Analysis Services Cube selection, expand the FoodMart
2000 database to display the cube tree underneath.
Select the Warehouse
cube, as shown in Illustration 13, to highlight it.
Illustration 13: The Completed Choose a Remote Cube
Click the Explain
The Explanation message box
appears, and displays the string that will be appearing in the Provider
box of the initial PowerPlay Connect dialog, as we shall see.
Close the Explanation
NOTE: The connection
details, as well as the objects that appear with various selections, will
differ based upon the environment, configurations and past actions taken within
our individual locations. Our intent here is simply to get to the sample FoodMart
2000 database and cubes that accompany a typical installation of MSSQL Server
2000, Standard Edition or above.
Click Open to finalize
selection of the Warehouse cube.
The Choose a Remote Cube dialog
closes, and we return to the initial PowerPlay Connect screen.
into the Description box, located toward the bottom of the dialog.
The completed PowerPlay Connect
dialog should now appear as shown in Illustration 14.
14: The Completed PowerPlay Connect Dialog
From the top toolbar, select File.
Click Save As on the
Name the file MSSQL_Warehouse.mdc,
storing it in the default location (remember, however, this location for
The initial dialog reappears, this time
with the new name indicated.
Click the Test button
(shown in Illustration 15) to ascertain that the connection has been
15: The Test Button on the PowerPlay Connect Toolbar
The Connection Succeeded message box should appear, as
depicted in Illustration 16 below. If this is not the result, we will
need to verify that the settings are correct for our individual environments,
and perhaps consult the relevant documentation for the components concerned.
16: Connection Test Succeeds ...
Click OK to close the
NOTE: Depending upon whether you are set up as an
Administrator in Access Manager, upon whether Cognos Common Logon
Server is running, and upon the presence or absence of other setpoints and
environmental factors, you may be prompted for signons at various points in our
procedures. As I stated before, I am assuming that the access required is
granted, and that you possess the required signon information to continue with
the steps that we undertake throughout the article.
We return again to the initial PowerPlay
Connect (now captioned PowerPlay Connect - MSSQL_Warehouse.mdc) dialog.
Select File --> Exit
to close PowerPlay Connect.
We have created a Pointer file (also known as a "stub"
file), whose suffix / extension is ".mdc" (the same as the
extension for the proprietary PowerCube). The .mdc file contains connection
information to identify the data source. Any non-PowerPlay OLAP data source
requires such a file be in place.
NOTE: For more information on PowerPlay Connect,
see the online documentation that accompanies the installation of PPES
and related components.
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
Let's drill a level lower to reinforce our understanding.
11. Click the Store
folder (now labeled USA) again.
Notice that the flyout selection now represents second level Store
dimension categories (states); Store appears at the top (and we can
easily "drill up" or "zoom" back to the top level by
clicking its name here), with the categories that are beneath the country level
appearing as the next lower-level drill candidates, as shown in Illustration
Illustration 22: Drilldown
Selection for the Store Dimension - Second Level Categories
12. Click OR to
drill to the Oregon Store data.
"OR" now appears as the label on the Store
folder - and the measures that appear in the crosstab adjust accordingly. We
have drilled to the Oregon category, state level of the Store dimension, as shown in Illustration
Illustration 23: Effects
of the Drill to OR Stores
13. Click USA on the Store
categories list (shown in Illustration 24) to zoom / "drill up"
to the USA Store data.
Illustration 24: Zoom
Back to the USA Level
14. Drag the Product
folder and drop it to the right of the Stores (now states) list on the
report, as shown in Illustration 25. (A "ghost" image will
appear when we touch the dragged object to the drop point, shown circled in our
Illustration 25: The
Drop Point for the Product Dimension
The Product dimension becomes nested within the Store
dimension, effectively breaking down the Stores (now summarized
by state) by Product. The report appears as shown in Illustration
Illustration 26: The
Product Dimension Nested within the Store Dimension
15. Click the 3D Chart
button (see Illustration 27) on the toolbar to present the data in a 3D
Illustration 27: The 3D
PowerPlay displays both the totals and drilled-down data in the 3D
chart, as depicted in Illustration 28. Information about any part of
the data selected in the chart appears both in the lower edge of the window and
in a tooltip that appears upon touching the mouse to the data item. Note also
the presence of an MDX-esque address (my term, not Cognos'), partially
representing the cell "location" in terms of intersects in the cube,
in the bottom left corner of the display, when we click on a given object in
the bar chart.
Illustration 28: The
Data Displayed as a 3D Bar Chart
We can also right-click any data item in the chart and select Explain
from the context menu to see the precise details for the item of data
selected. An example Explain dialog appears in Illustration 29.
Illustration 29: Example
Explain Dialog for a Selected Bar on the 3D Chart
16. Close the Explain
17. Return to the crosstab
report display by clicking the Crosstab button (shown in Illustration
Illustration 30: The
Now let's take a brief look at PowerPlay in Reporter mode.
We can easily shift to Reporter mode with the simple click of a button.
18. Click the Switch
between Explorer and Reporter button (see Illustration 31).
Illustration 31: The Switch
between Explorer and Reporter Button
The switch to Reporter is almost unrecognizable,
initially. We can ascertain that we are in the Reporter mode, however,
by looking at the caption at the top of the PowerPlay window, which now
indicates the combined report / cube name as before (mine is PPlay2.ppr
of MSSQL_Warehouse) followed by "(Reporter)." (The
combined name would have carried the suffix "(Explorer),"
While there are numerous differences in the Reporter and Explorer
modes, perhaps the primary one is the flexibility we have within Reporter
to easily modify the report layout, and break somewhat from the rigorous "leveling"
operation of Explorer in drilldown and other operations. Let's look at
an example of this flexibility in deleting a single category from our report.
1. Right-click the WA
category from the row axis at the left of the report.
2. Select Delete
from the context menu that appears.
A submenu appears, offering us the option to delete Category(s)
or Level. This is indicative of the difference to which I refer between
the two modes. Explorer only allows us to delete Level at this
point (the Category(s) selection is grayed out and disabled). This fits
in with what we have discovered, while in Explorer, about its general
operation: Remember that we noted that when we drill down in PowerPlay Explorer,
we replace a parent category with its child categories in the
report - all simultaneously. The Explorer view is always at the "same
level" at any given time, and displaying all the members of that level.
This is desirable in a browse, perhaps, but makes for inflexible reporting.
Hence, we can often rely upon the more versatile properties of Reporter
to meet needs that are more specialized.
Keep in mind that, as many new users of PPWIN soon find
out, while it is simple enough to shift from Explorer to Reporter,
switching back can cause unexpected results. The reason for this is that the
flexibility we have just discovered (with the deletion we performed a perfect
example) can cause problems in Explorer, which wants to display all
members at a given "level" of a browse. My experience has been that
it is often efficient to get the report as close as possible to
complete, before making the switch to Reporter, for this reason, but
many strategies exist, and some are certainly better than others are for
specific reporting scenarios.
3. Click Category(s)
to remove the WA line item from the report.
We note that the report appears as shown in Illustration 32.
Illustration 32: Our
Report - sans the WA Category
While we have removed the WA row entirely, the total (or USA) row has remained the
same, meaning that we have not experienced automatic update; this is another
attribute of the Reporter mode that it is important to remember, despite
its flexibility for reporting purposes.
Say we need a total, at this point, for the report in general, and
that total, not too surprisingly, needs to be accurate. There are a couple of
ways to approach this, but let's go about it in a manner that will afford us
exposure to other attributes of the Reporter mode.
4. Click the USA block of the bottom
row to highlight it.
5. Press the Delete
key (another way to delete a category).
We have removed the total (or USA) row. We will now create a total row
to replace it, through the creation of a calculation that accurately
adds the totals of the two remaining state rows.
6. Highlight both the CA
and OR rows, beginning with the CA row. ([CTRL]-Click is
used to highlight non-adjacent objects, as it is in many Windows applications,
and can be employed here.)
7. From the top menu,
click Calculate -> Add.
The Add dialog appears.
8. Type the word "Totals"
into the Label box.
The Add dialog appears as shown in Illustration 33.
Illustration 33: The
Completed Add Dialog Box
9. Click OK to
close the Add dialog.
The new Totals row appears, as shown in Illustration 34.
Illustration 34: The New
(If the order of the highlighted rows has somehow gone
astray, and the new total appears between the CA and OR rows,
simply drag the new row below the OR row and drop at the bottom of the
report. You can also delete and re-create (within Reporter) to achieve
the same effect.
10. Highlight the Time column
that appears in the report (it carries na throughout, at this
11. Press Delete to
remove the column.
The column disappears from our report. We would not have been
able to perform this action in Explorer mode, as we discussed earlier.
12. Highlight the 1997 and
1998 columns, in that order.
13. Click Calculate -> Subtract from the top menu.
The Subtract dialog box appears.
14. Select the 1998 -
1997 radio button.
15. Type "Change"
into the Label box.
The Subtract dialog box appears as depicted in Illustration
Illustration 35: The
Completed Subtract Dialog
The new Change column appears.
17. Click the 1997
18. [CTRL]-Click the new Change
The idea here is to highlight both columns simultaneously,
as shown in Illustration 36.
Illustration 36: The
Properly Highlighted Column Selection
19. Click Calculate -> Percent from the top menu.
The Percent dialog box appears.
20. Select the Percent
(Change, 1997) radio button.
21. Type "% Change"
into the Label box.
The Percent dialog box appears as shown in Illustration
Illustration 37: The
Completed Percent Dialog
The new % Change column appears, as shown in Illustration
Illustration 38: The %
Change Column Appears
We might have added divers other features to this report, or to
its Explorer forebear, such as exception highlighting, various
formatting schemes, additional calculations, and so forth. However, let's move
on, for now, after saving our new report.
23. Click File -> Save As from the top menu.
24. Save the file as %
Change.ppr in a convenient location.
25. Select File -> Exit to close PowerPlay
We will conclude our brief overview of PowerPlay for Windows
reporting for an Analysis Services cube at this point. Much of the body of
functionality that is available can be reviewed within the online documentation
and other sources, just as it might be for a natively generated PowerCube.
Now, let's take a look at the other main option for PowerPlay
analysis and reporting, PowerPlay Web.
Using PowerPlay Web for Reporting and
PowerPlay Web ("PPWEB"), like PPWIN,
has two presentation modes. The purposes of PPWEB's two modes do not
directly parallel those of the PPWIN's Explorer and Reporter
components. In the case of PPWEB, the mode choices are called PowerPlay
Web Explorer and PowerPlay Web Viewer. The Explorer option
again provides a direct browse of the cube data. PowerPlay Web Viewer,
however, is different from PowerPlay for Windows Reporter, in that the Viewer
acts essentially as a simple viewer for PowerPlay .pdf reports. This
option is often adequate in cases where static reports meet the business
requirements of the organization's information consumers, and it allows for
easy deployment of the reports using a minimal overhead browser - all the real
processing occurs on the server. Access to non-local cubes (be they native
PowerCubes or non-Cognos cubes), as well as reports that do not reside on the
PC from which we are running them, is provided through PPES, as we
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. After a brief introduction to the application,
we will initialize PowerPlay Web and begin exploring our cube.
The platform for deploying our MS
Analysis Services cube to the web is essentially the same as that which we
would use to deploy a proprietary PowerCube generated within the PowerPlay
Transformer application. PowerPlay Enterprise Server acts as the
publication / server mechanism in either case, with little difference between
the two cube types, once the Pointer cube is created.
There are two general ways of accessing
the cube from the PPES platform. One is often referred to in the
documentation, the literature on the Cognos site, and other references, as "thin
client." In the "thin-client" environment, information
consumers use PowerPlay for Windows on their PCs to access the Pointer
file, which, even in this option, is deployed via PPES. "Thin
client" operation very closely resembles standard PPWIN client operation,
only connected to a remote cube, so we won't repeat what we have already
The other option for accessing cubes
(be they native cubes or MS Analysis Services Cubes) using the PPES
platform is via Web access, wherein authorized information consumers
access the cube using a web browser ("zero-footprint" because no part
of the PowerPlay application need be installed on the client PC).
In both of the presentation options
above, the Pointer file for the Analysis Services cube must have been
inserted into PPES. We will focus on the Web option for the remainder
of the lesson Web solutions.
PowerPlay Enterprise Server for PowerPlay Reporting
Before briefly examining the reporting
functionality of PPWEB for an MS Analysis Services cube, let's take a
look at the PPES preparation process.
1. Select Start -> Programs.
2. Select Cognos EP
Series 7 -> Cognos Server Administration.
The Cognos EP Series 7 -> Cognos Server
Administration selection screen appears.
3. Select the OLAP
Cubes and Reports button to initialize PowerPlay Enterprise Server -
PowerPlay Enterprise Server - Administration initializes, with the
appearance of the Connect to Server dialog shown in Illustration 39.
Illustration 39: The
Connect to Server Dialog.
4. Type in the name of
your Server (or localhost) as appropriate. (Mine is defaulted to
my server, MOTHER.)
NOTE: If prompted here, or at any step going
forward, supply a user name and password that is valid for your
5. Click OK.
The PowerPlay Enterprise - Server Administration window
appears. Now we will insert the MS Analysis Services cube for "publication"
Select Insert -> Cube from the top menu, PPES Administration
window, as depicted in Illustration 40.
Illustration 40: The PowerPlay
Enterprise - Server Administration Window
Immediately after we click Insert -> Cube, we see the Cube
Properties dialog appear.
6. Type Warehouse
Analysis Services Cube into the Name box of the dialog.
Note that the name typed here can be essentially anything unique
that makes sense to the Administrator, and not necessarily the file name of the
cube, or any names previously associated with the cube elsewhere.
7. Type useful text into
the Description box of the dialog (I typed Sample Warehouse MS
Analysis Services Cube).
8. Click the ellipses
button (...) to the right of the Cube Source box.
The Select Source dialog appears.
9. Navigate to the
location of the Pointer file MSSQL_Warehouse.mdc, which we
created earlier in the article (mine was saved in the default location, the
Bin folder in the Cognos/cer2 directory, as depicted in Illustration
Illustration 41: The
Select Source Dialog
10. Select the MSSQL_Warehouse.mdc
11. Click OK to
apply the file selection and to close the Select Source dialog.
The completed Cube Properties dialog should appear
substantially like that shown in Illustration 42.
12. Click OK to
accept and apply the settings.
The Cube Properties dialog closes, returning us to the PowerPlay
Enterprise - Server Administration window. We see the newly inserted cube,
sporting the new name we assigned earlier in the Cube Properties dialog,
Warehouse Analysis Services Cube, as depicted in Illustration 43.
Illustration 43: The New
Warehouse Analysis Services Cube Appears
Let's perform a test of our new cube by using the Open with
Browser "preview" capability afforded us by PPES Administration.
The icon for access to this feature is shown in Illustration 44.
Illustration 44: The
Open with Browser Icon
13. Click the Open with
The browser initializes, and we are taken to the cube
presentation, as shown in Illustration 45. (Keep in mind our comments
regarding prompted database / other logons, depending upon the level of
integration of security. An example would be the appearance of a database
login screen at this stage in the simple security scenario. Provide logon
credentials to proceed in cases such as this, if applicable).
Illustration 45: The
Browser Presentation (Compressed View)
test is thus indicated. Now that we have established connectivity to the
Analysis Services Warehouse cube, we will overview PPWEB reporting from
the PPES cube.
via the "zero-footprint" web client that requires only a reasonably
current browser (see the documentation for acceptable versions of standard
browsers) on the client to access and report from our cube. The procedures in
this article are presented using Internet Explorer, but the steps should be
quite similar using Netscape.
Close Internet Explorer
Open Internet Explorer
Type the following URL into the
address field, substituting your server name for MOTHER (my server name) :
Click Go, to the right
of the URL.
The Table of Contents appears as
shown in Illustration 46. We see the new Warehouse Analysis Services
Cube that we have published, along with the sample cubes that are added in
the typical installation of PPES and related applications.
Illustration 46: The
Table of Contents Page in Explorer
Click the Warehouse Analysis
Provide database logon
credentials, then log on, as applicable,
The data from the newly connected Warehouse Analysis Cube
appears once again, as shown in Illustration 47.
Illustration 47: The
Warehouse Cube Opens in the PowerPlay Web Explorer Window
We can now get into analyzing our data from the browser interface.
perform all the typical reporting capabilities that are available to us for a
proprietary cube in PowerPlay Web, in addition to leveraging several extended
capabilities that become available with the Analysis Services cube.
Let's take a quick look at navigation in the Cognos PowerPlay Web
Explorer interface at this point.
7. Click the Store dimension
label in the bottom left of the display.
8. Click Store in
the cascading menu that appears.
The flyout menu appears, as shown, offering drilldown paths and
other functionality, some of which are depicted in Illustration 48.
Illustration 48: Drilldown
and other Selections Appear in the Flyout Menu
9. Click USA -> WA on the flyout to
drill down to the WA children.
The Washington cities appear in the row axis at left, as depicted in Illustration
10. Click the Measures
label in the top left corner of the display to obtain a flyout menu that
presents the available measures list, as shown in Illustration 50.
Illustration 50: The Available
Measures Selections for the Warehouse Cube
11. Select Warehouse
Sales from the measures list on the flyout menu.
12. Click the WA
label at the bottom left of the display (at the bottom of the city row labels.
13. Select Action > Nest > Product, as shown in Illustration
Illustration 51: Nesting
Products within the Stores Dimension (Cities Categories / Rows)
We have nested
the Product dimension within the Stores dimension at the cities
category level, resulting in a display as depicted in Illustration 52.
Illustration 52: The
Newly Nested Display (Partial View)
NOTE: Some of your values may differ from the illustrations,
depending upon differences in source data, etc.
Now let's make a couple of simple additions to the presentation to
make it a bit more useful from a financial analysis standpoint. We will create
a calculated column to display the change in our totals between 1997 and
1998, and another calculated column to provide the percent change, both fairly
common features in financial reporting.
14. Click the column axis
dimension selector (currently Time in the blue "capsule"
selector in the right upper corner or the display).
15. Select Action to
display the flyout menu depicted in Illustration 53 below.
Illustration 53: Click
the Column Axis Dimension Selector ...
16. Click -> Insert Calculation from the flyout menu.
17. Select Subtract
in the Operation selector.
18. Select 1998 in
the top Category selector.
19. Select 1997 in
the second Category selector (appearing under Minus:).
20. Type $ Change into
the Calculation Name field.
The competed Calculation dialog appears as shown in Illustration
Illustration 54: The
Completed Calculation Dialog for $ Change
21. Click OK to add
the new calculated column.
The $ Change column appears.
22. Click the column axis
dimension selector (Time) again.
23. Select Action -> Insert Calculation again.
24. Select Divide
in the Operation selector of the Calculation dialog.
25. Select $ Change in
the top Category selector.
26. Select 1997 in
the second Category selector (appearing under Divided by:).
27. Type % Change into
the Calculation Name field.
The competed Calculation dialog appears as shown in Illustration
Illustration 55: The
Completed Calculation Dialog for % Change
28. Click OK to add
the new calculated column.
The % Change column appears. The display, together with
our two new calculated column additions, appears as partially shown in Illustration
Illustration 56: The New
Calculated Columns Appear in the Display (Partial View)
Now let's make another adjustment for readability: let's hide the Time column, as it provides no compelling information at present.
29. Click the column axis dimension selector (Time) once more.
30. Select Action -> Hide Total from the flyout menu, as shown in Illustration 57 below.
The Total column disappears, while leaving the column axis dimension selector (currently displaying Time) available for manipulation. The display appears as partially shown in Illustration 58.
Illustration 58: The Display, sans Total Column (Partial View)
Let's take a quick look at an example of the graphics capabilities within Cognos PowerPlay Web Explorer. Most of the graphics styles can be activated simply by clicking the appropriate toolbar button.
31. Click the 3-D graphic display button from the bottom toolbar of the Web Explorer display (depicted in Illustration 59).
Illustration 59: The 3-D Graphic Display Button from the Toolbar
The resulting 3-D charts reflect the effects of our nesting Products within the Store Cities. A graphic is presented for each Store, as is partially shown in Illustration 60.
Illustration 60: The 3-D Graphic Displays for Products within Stores (Partial View)
Keep in mind that the graphics are far from static representations; drilldown works here, as well, and can be enacted simply by clicking on the appropriate element of the chart.
Let's return to the crosstab display we occupied previously, only this time let's choose an indented display.
32. Click the Indented Crosstab Display button from the toolbar, to the left of the 3-D graphic button, and as displayed (alongside the regular crosstab display button) in Illustration 61.
Illustration 61: Select the Indented Crosstab Display Button (Circled)
The display now appears as an indented crosstab, as depicted in Illustration 62.
Illustration 62: The Indented Crosstab Display (Partial View)
33. Save the report as desired, by clicking the appropriate Save As button on the lower right toolbar. (Note that saving may result in a prompt from Access Manager, after which we will be walked through the setup of various items in Upfront. Upfront is beyond the scope of this article, but help can be found in the online help or within the documentation that is installed with the "typical" installation of the Cognos BI suite.)
We have examined only a handful of the options available in Cognos PowerPlay Web. Cognos PowerPlay is a powerful tool, and is, by far, the current leader in the business intelligence world in depth and breadth of analysis and reporting scope. The functionality available is both robust and easy to use. The zero-footprint interface we have reviewed is also a highly desirable attribute, in many environments, making both deployment and maintenance of the tool much easier than systems requiring client installations on the user PCs.
We will conclude our brief overview of PowerPlay Web reporting for an Analysis Services cube at this point. Much of the body of functionality that is available can be reviewed within the online documentation and other sources, just as it might be for a natively generated PowerCube.
Next in Our Series ...
In this lesson, Reporting Options for Analysis Services Cubes: Cognos PowerPlay, we explored features that integrate Analysis Services and Cognos PowerPlay to provide a vehicle for client reporting and other business intelligence pursuits. The central focus of the article was a basic overview of the steps involved in a simple (non-integrated security) connection of Cognos PowerPlay to a Microsoft Analysis Services cube, and then a high level overview of the use of PowerPlay for Windows and PowerPlay Web for the performance of analysis and reporting upon the Analysis Services OLAP data source.
In our next lesson, we will return to OLAP cube design and manipulation, and begin a new project: we will design and create a simple cube to analyze traffic data for a sample web site. We will construct tables for this purpose, and gain some exposure to the relational side of the cube life cycle in the process. Finally, we will discuss some of the eccentricities involved in constructing such a cube, and provide practical solutions for surmounting these challenges.
» See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.