Author: William E. Pearson III

Synopsis: Author Bill Pearson continues the Cognos Business Intelligence Collection with an introduction to using Cognos PowerPlay Web with an Analysis Services cube. In this article, we will set up connectivity with simple security, then overview using PowerPlay Web to report and analyze cube data.

Welcome to the Cognos Business Intelligence Collection. The primary focus of this column is the delivery of approaches and concepts to help you to meet real business needs using the Cognos Business Intelligence Suite. The majority of the articles in the Collection will be hands-on tutorials, centered, for the most part, on Cognos EP Series 7 and later releases (although many of the concepts can be extrapolated to earlier versions with minimal alterations). The mission of each article will be to introduce a business need, and then to present a straightforward approach to meeting the need, using the appropriate Cognos application(s).

This article presents another option for using Cognos PowerPlay with an Analysis Services cube: it focuses on using the PowerPlay Web components to report and analyze cube data. Much as we do in the Using PowerPlay for Windows with an Analysis Services Cube article, we first set up connectivity with simple security, then overview using PowerPlay Web to analyze and present our data, for our own purposes or for the needs other information consumers. Even though the initial establishment of connectivity is largely the same for the PowerPlay for Windows and PowerPlay Web options, both articles detail the full process so as to make each standalone for those wanting to pursue only one option, and to have a single, associated reference document for the choice of options that they have made.

As I mentioned in the sister article for PowerPlay Windows, the driver behind a desire to create the PowerPlay for Analysis Services Cubes documents is a question that I frequently hear from clients and readers alike: “How can I use Cognos PowerPlay to report from my Microsoft Analysis Services cubes?” 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”). As I have said many times before, the motivation is obvious: the ascendancy of the RDBMS-generated cube is, at this stage, both inevitable and irresistible.

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

4. Click the Create a New Report button.

The Choose a Local Cube dialog appears. (If the Choose a Remote Cube dialog appears instead, simply click the Local radio button to change to the … Local 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 this.

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 be the major player in its arena for at least the near-term.  As I continually emphasize to my Cognos 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.

PowerPlay Connect initializes, its first dialog appears.  Notice that the Database type is defaulted to PPDSRemote.

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

13.   Click OK to apply the additions.

The Connections dialog appears, displaying our new Connection name.

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 to highlight it.

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 Description box text serves as surrogate identifier that can become important in an integrated logon strategy, as detailed in my article, Cognos for Microsoft Analysis Services Reporting: Establishing Integrated Signon.  The text can be something else, obviously, as long as you remember what you typed here, in the event that you decide to pursue “seamless signon” later.

The completed PowerPlay Connect dialog should now appear.

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 dialog reappears, this time with the new name indicated.

24.   Click the Test button to ascertain that the connection has been established.

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.

25.   Click OK to close the message box.

NOTE:  Depending upon whether you are set up as an Administrator in Access Manager, whether Cognos Common Logon Server is running, and as well as 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.  (The requirement that the file be in place also drives a requirement for PowerPlay Connect – and a corresponding license for same – to exist in any environment where PowerPlay is installed for reporting with “third-party” cubes.  This is another consideration to keep in mind when choosing BI reporting tools.)

NOTE:  For more information on PowerPlay Connect, see the online documentation that accompanies the installation of PPES and related components.

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 introductory article, Cognos Applications Overview. 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’ll be acting as the Administrator on my server, with my user set up in Access Manager, but 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

If you’ve ever tried to establish connectivity to an Analysis Services cube in PowerPlay Connect, and have seen PPDSRemote as the only option for Database Type (versus seeing the MS SSOS ODBO option that you need to go further), you have come across a somewhat common condition, particularly if the need to access AS cubes has not previously arisen. Although this may not be the case at present, keep in mind the following steps, “against the day” when you will come across the “missing MS SSOS ODBO option” scenario. These steps are critical in establishing connectivity to cubes that reside on a server; we also must have connectivity established to view cube data from the web (we touch on that in the upcoming Cognos for Microsoft Analysis Services Reporting: Using PowerPlay Web with an Analysis Services Cube).

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.

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

5. Click the Start tab.

The Configuration Manager Welcome dialog, Start tab appears.

6. Click the Open the Current Configuration buttonon the Start tab (shown circled in the illustration above).

The Configuration Manager dialog (my Current Configuration appears as “Untitled”) appears.

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 (mine appears in the illustration as MOTHER), by clicking the “+” sign to its left, if necessary.

8. Expand Services within the hierarchy of the tree.

9. Expand OLAP Data Access under Services.

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.

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

The context menu shown here appears.

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.

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” incarnation, PowerPlay for Windows. 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 for various elements is addressed in my upcoming article, Cognos for Microsoft Analysis Services Reporting: Establishing Integrated Signon, which is devoted to that sole focus.

PowerPlay Enterprise Server (“PPES”) acts as the OLAP application server for PowerPlay for Windows (as well as other Cognos components) for cube access. 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 the respective OLAP database servers. PPES also provides for client / user access to PowerPlay reports, which typically originate in a PowerPlay client application, via a report server. Load balancing capability 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. This produces the expected result of making performance more efficient, because of minimal client processing and reduced data traffic between the client(s) and server(s) involved, both as a result of lowered need of the client to obtain such data, and in light of the fact that caching mitigates some of the need for communication.

Synopsis: Author Bill Pearson continues the Cognos Business Intelligence Collection with an introduction to using Cognos PowerPlay with an Analysis Services cube. In this article, we will set up connectivity with simple security, then overview using PowerPlay for Windows to report and analyze cube data.

Welcome to the Cognos Business Intelligence Collection. The primary focus of this column is the delivery of approaches and concepts to help you to meet real business needs using the Cognos Business Intelligence Suite. The majority of the articles in the Collection will be hands-on tutorials, centered, for the most part, on Cognos EP Series 7 and later releases (although many of the concepts can be extrapolated to earlier versions with minimal alterations). The mission of each article will be to introduce a business need, and then present a straightforward approach to meeting the need, using the appropriate Cognos application(s).

In this column, I will focus on a question that I frequently hear from clients and readers alike: “How can I use Cognos PowerPlay to report from my Microsoft Analysis Services cubes?” 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.

Within the Construction and Support class, I like to include components of the Cognos BI Suite that are used to either develop, extend, maintain or support BI solutions. The main components that I will address in this article are DecisionStream and Architect. There are others, of course, but these are commonly encountered in the realm of construction and support.

As many of us are aware, DecisionStream is a multifaceted tool whose main focus is data mart creation and optimization. Like most of its ETL application peers, it gathers data from the various sources and stores of the organization, and provides you with a means of designing and populating meaningful star or snowflake schemas to house your relevant business data, often after transformation has been performed as a part of ETL. DecisionStream also assists you in designing and building dimensional models for reporting and analysis. The tool offers optimization capabilities both during and after construction of your repositories, and assists you in various other ongoing maintenance actions, such as the handling of slowly changing dimensions (SCDs).

A DecisionStream display.

Cognos Architect is a metadata management tool that is well fitted to build an integrated BI solution from the ground up. Constructing your BI infrastructure with this tool means that common definitions can be shared among organizational BI applications, reducing work and providing consistency throughout the foundation of the enterprise’s BI system. Metadata management can be handled from a central location with relative ease, business rules can be shared throughout applications and functions, and the presentation of data can be supported from a common metadata layer, as well. In my opinion, Architect is one of the most underutilized, and least understood, “diamonds” in the Cognos crown. I have witnessed many implementations over recent years where Architect might have had much to offer, but where its “optional” nature (and the failure of the implementers to fit it into the implementation project) has resulted in this valuable tool being left by the wayside.

A combined view of the three Architect metadata layer tabs, which represent, in effect, the steps of design within the tool.

Other Components and Considerations

Keep in mind that other Cognos tools and components / subcomponents exist that, while out of the mainstream, might be just the ticket to meet your specific BI needs. The suite also contains many tools, most of which we have discussed above, that support functions within more than one of the classes I have defined above. This has been an attempt to familiarize you with the basics about the components of the Cognos BI suite, and we will discover together many of the intricate details of the individual members of the family as we share the practical articles that lie ahead.

Centralized security within the Cognos BI Suite is delivered by Access Manager. Access Manager allows single-point management of user classes and profiles for all implemented components of the suite from a central console. Both authorization security and logon (or authentication) security is addressed. Access Manager can be used standalone to manage the information that your organization’s consumers can view, or you can combine your Access Manager configuration with database security and OS security to reach the desired level of user authentication. I have seen many diverse combinations of RDBMS, OS, and / or LDAP security put in place at various clients for authentication over recent years.

A view of Access Manager

Cognos provides a simple solution for managing access to corporate content in a web environment via the upfront component. Upfront was not really designed as a complete web site / portal content management solution (a point I find myself making again and again to clients for whom I implement the Cognos BI Suite). Although it serves quite ably to allow you to flexibly customize, personalize, publish and manage content (whether BI-related or not), within the realm of the corporate intranet and elsewhere, it is limited with regard to sophisticated site component management (HTML and other markup languages, text, hyperlinks, graphics, images and so forth). The provision in its design to allow for the publication of, or linking to / from, non-Cognos documents allows for versatility that can be highly useful, and Upfront often meets the business requirements of many organizations right out of the box.

Cognos Upfront remembers your profile information as an established user, so personal settings can be automatically accessed and put into effect with every visit you make to the portal. Upfront can be easily configured to allow for further personalization, letting consumers choose and publish the information that meets their individual business needs, in the format and combinations that they deem most useful. Sharing of information between authorized parties is easy, as well.

A view of Upfront, within which we can see the delivery of a PowerPlay Web report

The second general class of Cognos BI functionality, the Management and Integration Components, might be broken down into the following general categories:

• Content Management
• Security
• Construction and Support

While there are various component applications and other elements that clearly fill these categories, there is, again, a degree of crossover in many cases. Some components operate within more than one class, and can actually be seen even as applications within the perspective we have examined in the paragraphs above. Keep in mind the classifications I give are, again, somewhat of a “figment of my interpretation;” the intent is simply to provide a basis for organizing the components into meaningful groups.

We will look at each category within the Management and Integration Components class in the following tutorials, and discuss several of the more significant elements that reside within each.