Reporting Options for Analysis Services Cubes: Cognos PowerPlay

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.

Introduction

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.