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