Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jun 16, 2003

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

By William Pearson

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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM