Reporting Options for Analysis Services Cubes: Cognos PowerPlay - Page 5

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.

The Welcome dialog for Cognos PowerPlay appears, as shown in Illustration 17.

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

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.

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.

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.

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.