Reporting Options for Analysis Services Cubes: Cognos PowerPlay

Monday Jun 16th 2003 by William Pearson

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 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.


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.

Establishing Connectivity

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.

Enable Connections 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:

1.      Click the Start button.

2.      Select Programs -> Cognos EP Series 7.

3.      Select Tools from the Submenu that appears.

4.      Select Configuration Manager, as shown in Illustration 1.

Illustration 1: Select Configuration Manager

The Configuration Manager Welcome dialog, Introduction tab appears, as shown in Illustration 2, unless the feature has been disabled prior to this use.

Illustration 2: The Configuration Wizard - Welcome Dialog, Introduction Tab

5.      Click the Start tab.

The Configuration Manager Welcome dialog, Start tab appears, as shown in Illustration 3.

Illustration 3: The Configuration Wizard - Welcome Dialog, Start Tab

6.      Click the Open the Current Configuration button on the Start tab (shown circled in Illustration 3 above).

The Configuration Manager dialog (my Current Configuration appears as "Untitled") appears, as shown in Illustration 4.

Illustration 4: Current Configuration Dialog (Compressed View)

Note: If the Configuration Manager dialog did not appear as shown above, simply select File > Open Current Configuration to reach the same point.

7.      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.

Illustration 5: Expand the Server (Shown as MOTHER) ...

8.      Expand Services within the hierarchy of the tree.

9.      Expand OLAP Data Access under Services, as shown in Illustration 6.

Illustration 6: Expand OLAP Data Access, under Services (Partial View)

10.  Select 3rd Party OLAP Server.

11.  Change "0" (the default) to "1" in the right pane, just to the right of Microsoft OLAP Service, as depicted in Illustration 7.

Illustration 7: Enabling MSSQL Server OLAP Cube Selection (Compressed View)

12.  Right-click OLAP Data Access in the left pane.

The context menu shown in Illustration 8 appears.

Illustration 8: OLAP Data Access Context Menu

13.  Click Apply Selection to apply the change made in Step 11 above.

A warning message box appears.

14.  Click Yes.

A status meter indicates update progress, and then a message box appears confirming successful application of all properties.

15.  Click File -> Save to save our change within Configuration Manager. If prompted to assign a name / location for the .ccs file, do so.

16.  Select File -> Exit to close Configuration Manager.

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:

1.      Click the Start button.

2.      Select Programs -> Cognos EP Series 7.

3.      Select Tools from the Submenu that appears.

4.      Select PowerPlay Connect, as shown in Illustration 9.

Illustration 9: Initialize PowerPlay Connect

PowerPlay Connect initializes, its first dialog appearing as shown in Illustration 10. Notice that the Database type is defaulted to PPDSRemote.

Illustration 10: PowerPlay Connect Appears with Default Database Type

5.      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.

6.      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:

7.      Click the ellipses button to the right of the Provider (Database...) box.

The Choose a Remote Cube dialog appears.

8.      Click the Connections button.

The Connections dialog appears.

9.      Click the Add button.

The Add a Connection dialog appears.

10.  Type Warehouse Analysis Services Cube in the Connection box.

11.  Type the server name (localhost, again, will work, if appropriate to your environment) in the Server box.

12.  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 for larger image

Illustration 11: The Completed Add a Connection Dialog

13.  Click OK to apply the additions.

The Connections dialog appears, displaying our new Connection name, as depicted in Illustration 12.

Illustration 12: The Connections Dialog Displays the New Connection

14.  Click OK.

The Connections dialog closes, and we are returned to the Choose a Remote Cube dialog.

15.  Leaving the Connection selector at the new Warehouse Analysis Services Cube selection, expand the FoodMart 2000 database to display the cube tree underneath.

16.  Select the Warehouse cube, as shown in Illustration 13, to highlight it.

Illustration 13: The Completed Choose a Remote Cube Dialog

17.  Click the Explain button.

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.

18.  Close the Explanation message box.

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.

19.  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.

20.  Type MSSQL_Warehouse into the Description box, located toward the bottom of the dialog.

The completed PowerPlay Connect dialog should now appear as shown in Illustration 14.

Illustration 14: The Completed PowerPlay Connect Dialog

21.  From the top toolbar, select File.

22.  Click Save As on the menu.

23.  Name the file MSSQL_Warehouse.mdc, storing it in the default location (remember, however, this location for later).

The initial dialog reappears, this time with the new name indicated.

24.  Click the Test button (shown in Illustration 15) to ascertain that the connection has been established.

Illustration 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.

Illustration 16: Connection Test Succeeds ...

25.  Click OK to close the message box.

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.

26.  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 option.

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.

1.      Click the Start button.

2.      Select Programs -> Cognos EP Series 7.

3.      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

4.      Click the Create a New Report button.

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 ... Local dialog).

Illustration 18: The Choose a Local Cube Dialog

5.      Click to select the MSSQL_Warehouse pointer .mdc file we created earlier.

6.      Click Open.

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 19.

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 well.

7.      Click the View menu item in the top toolbar.

8.      Uncheck the Dimension Viewer option (to free up a bit more real estate on the screen), if appropriate.

9.      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 Store data.

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 21.

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 22.

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 23.

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.)

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 26.

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 Bar Chart.

Illustration 27: The 3D Chart Button

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 dialog.

17.  Return to the crosstab report display by clicking the Crosstab button (shown in Illustration 30).

Illustration 30: The Crosstab Button

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)," previously.)

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 Totals Row

(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 point).

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 35.

Illustration 35: The Completed Subtract Dialog

16.  Click OK.

The new Change column appears.

17.  Click the 1997 column.

18.  [CTRL]-Click the new Change column.

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 37.

Illustration 37: The Completed Percent Dialog

22.  Click OK.

The new % Change column appears, as shown in Illustration 38.

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 for Windows.

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 Analysis

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 discussed above.

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 covered earlier.

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.

Preparing 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 - Administration.

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 environment.

5.      Click OK.

The PowerPlay Enterprise - Server Administration window appears. Now we will insert the MS Analysis Services cube for "publication" via PPES.

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 41.

Illustration 41: The Select Source Dialog

10.  Select the MSSQL_Warehouse.mdc file.

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.

click for larger image
Illustration 42: The Completed Cube Properties Dialog

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 Browser icon.

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)

A successful 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.

Reporting 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.

1.      Close Internet Explorer completely.

2.      Open Internet Explorer again.

3.      Type the following URL into the address field, substituting your server name for MOTHER (my server name) :


4.      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

5.      Click the Warehouse Analysis Services Cube.

6.      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. We can 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 49.

click for larger image

Illustration 49: Drilled Down to the Washington Cities

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 51.

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.

The Calculation dialog appears.

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 54.

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 55.

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 56.

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.

click for larger image

Illustration 57: Hiding the Time Column

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.

Mobile Site | Full Site