Generate Cognos reports using InfoSphere Warehouse Cubes

A step-by-step guide

In this article, you'll learn how to generate IBM® Cognos® professional reports using InfoSphere™ Warehouse data models. Cognos is SOA-based, light-weight, and user-friendly, and no programming skills are required to create reports. This article serves as a start for users who may have used other tools in the past and now want to generate professional Cognos reports using InfoSphere Warehouse cubes.

Rayudu VYSS (rayudu.vecha@in.ibm.com), System Software Engineer, IBM

Rayudu VYSS photoRayudu VYSS joined the India Software Lab in 2006 and since then he has been a part of the Platform Technology Center team. He has worked on information technology products including DB2 Enterprise Server Edition, DB2 InfoSphere Warehouse, and Cognos.



Sridhar Munireddy (smunired@in.ibm.com), Staff Software Engineer, IBM

Sridhar Munireddy photoSridhar Munireddy has worked in the India Software Lab under Information Management for the past three years. He has worked with DB2, focussing on replication and federation technology, and then moved on to proactive services for DB2. He has hands-on experience with various reporting techonlogies such as Jasper reports, BIRT, Alphablox, Crystal Reports, and IBM Cognos.



18 June 2009

Also available in Portuguese

Introduction

This article presents step-by-step instructions for setting up Cognos, importing a data model from InfoSphere Warehouse, and creating and running reports in Cognos. It's written for users who have some knowledge of creating the Infospere Warehouse cubes and basic knowledge of Cognos.

In our examples, Cognos 8.4 is the reporting server and Infosphere Warehouse 9.5 is the database. For our example configuration, Infosphere Warehouse 9.5 is installed on a UNIX® machine, and the Cognos server and BI model is on a Windows machine. However you could also have Infosphere Warehouse 9.5 installed on the same Windows machine where Cognos server is installed, or you could have InfoSphere Warehouse on a separate Windows machine.

Software requirements

The following software is required for the example configuration described in this article:

  • Cognos BI Server 8.4
  • Cognos BI Model 8.4
  • IBM InfoSphere Warehouse Server 9.5
  • IBM HTTP Server 6.1

Steps to set up Cognos with InfoSphere Warehouse

Here are the steps required to set up Cognos with InfoSphere Warehouse for our scenario:

  1. Install Cognos server and Cognos BI model
  2. Install the DB2 client if the database is on a remote DB2 server
  3. Create and configure the DB2 database
  4. Configure the IBM HTTP server as Web server for Cognos
  5. Configure the content store in the Cognos configuration tool
  6. Test the content store connection and start the Cognos service
  7. Export the cube model in XML format from InfoSphere Warehouse
  8. Create the model in the framework manager by importing the cube.
  9. Create the Cognos package and publish it
  10. Author the report using Report Studio

Next we'll look at each step in detail.

Step 1: Install Cognos server and Cognos BI model

Follow your normal documentation for installing Cognos 8.4 Server and the Cognos BI model. For our purposes, there are two key points to be aware of while installing.

  • Make sure to disable or uncheck the Content Store Database" option. Otherwise a Derby database will be installed by default.
  • When you install the BI model, be sure to give a location other than the Cognos server path. The default install path for Cognos on Windows is c:\program files\cognos\c8.

Step 2: Install the DB2 client if the database is on a remote DB2 server

If the DB2 server is not installed locally on the Cognos machine, you will need to have the DB2 client installed. Make sure that you install a client version that is compatible with the server version. For example, if the DB2 server is version 9.5, then you need DB2 9.5 client.

Step 3: Create and configure a DB2 database

Next, create a database on the DB2 server to be the content store for Cognos. While creating the DB2 database there are certain parameters to be considered, which are explained in detail in the following steps. Cognos will create default tables and other report content information in this database. Preferably, this database should be dedicated to storing Cognos-related tables and report content.

Use the following commands to create and configure the database:

Listing 1. Creating and configuring the content store database
db2start
db2 create db COGNOS using codeset UTF-8 TERRITORY US
db2 connect to COGNOS
db2 "create bufferpool BP_32K immediate size 1000 pagesize 32768"
db2 "create system temporary tablespace TEMP_TB pagesize 32768 bufferpool BP_32K"
db2 "create user temporary tablespace USER_TEMP_TB pagesize 4096 bufferpool IBMDEFAULTBP"
db2 "create regular tablespace REG_TBSPC_4K pagesize 4096 bufferpool IBMDEFAULTBP"
db2 update db cfg using LOCKTIMEOUT 240 APPLHEAPSZ 1024
db2 terminate
db2stop
db2start

Note : If the DB2 database server is not on the same machine as the Cognos server you need to follow the first step in the list below. Otherwise, move on to the second step.

  1. After creating the DB2 database, you need to copy certain DB2 jar files to the Cognos installation directory. Since the database you created in previous step is remote, you need to catalog the database so that Cognos server can access the database you have created. Open the DB2 command line processor on the Cognos machine and run the following commands for cataloging the remote database:
    Listing 2. Cataloging the remote database
    catalog tcpip node <node name>  remote <db2 server IP> server <db2 instance port>
    catalog db <db name> at node <node name>
    db2stop
    db2start

    Now the database will be cataloged on the Cognos server.

  2. On the Cognos machine, copy the DB2 jar files (db2jcc.jar, db2java.zip, and db2jcc_license_cu.jar) from db2_install_dir\sqllib\java\ to the Cognos lib directory. Normally, you would be copying from the path c:\program files\IBM\SQLLIB\java to c:\program files\cognos\c8\webapps\p2pd\web-inf\lib.

    After copying the DB2 jar files, change the extension of the file from .zip to .jar, so db2java.zip will be changed to db2java.jar in the Cognos lib path.

Step 4: Configure the IBM HTTP server as Web server for Cognos

  1. After Installing the IBM HTTP Server, check to see whether or not it was installed properly. You should get the HTTP server main page when you type http://localhostin your browser.
  2. Add the alias for Cognos in the httpd.conf file. This file will be located in the following path: c:\program files\ibm\http server\conf\httpd.conf. Add the lines shown in Listing 3 at the end of the file.
    Listing 3. Editing the httpd.conf file
    ScriptAlias /cognos8/cgi-bin/ "C:/Program Files/Cognos/c8/cgi-bin/"
    
    Alias /cognos8/ "C:/Program Files/Cognos/c8/webcontent/"
        	             <directory "C:/Program Files/Cognos/c8/cgi-bin/" >
            		Options Indexes Includes FollowSymlinks ExecCGI
      		IndexOptions +FancyIndexing 
    		Order allow,deny
           		 Allow from all
       	             </directory>
    
    <Directory "C:/Program Files/Cognos/c8/webcontent/">
        		Options Includes Indexes FollowSymLinks MultiViews
        		AllowOverride FileInfo
        		Order allow,deny
        		Allow from all
    </Directory>

    Note: After updating the httpd.conf file, restart the HTTP server.

  3. Create the virtual directory "cognos8/cgi-bin" in c:\program files\IBM\HTTPServer\htdocs\en_US directory
  4. Make sure that the HTTP server and HTTP admin services are up and running.

Step 5: Configure the content store in the Cognos configuration tool

Next you need to register the content store information in the Cognos configuration tool.

  1. Click on Start->programs->IBM Cognos 8 -> IBM Cognos Configuration. The first screen of the Cognos configuration tool appears as shown in Figure 1.
    Figure 1. IBM Cognos configuration
    Screen capture for configuring group properties
  2. In the screen shown in Figure 1, go to the Explorer pane on the left, and select Local Configuration -> Data access -> Content manager -> Content store. Create a new DB2 database and enter the database server, port number, user id and password for the database, and database name (which was created in step 3,) as shown in Figure 2. If your database in not remote, then you need not enter the database server and port number.
    Figure 2. IBM Cognos configuration - Specifying the content store
    Screen capture where you specify the content store

    In our example, we entered the following values for the content store:

    • Type: DB2 database
    • Database server and port number: 9.184.64.178:50000 (This is the machine name where DB2 resides and the instance port number.)
    • User ID and password: db2inst1/db2inst1 (ID and password to connect to the database)
    • Database namd: cognos (This is the database that you created in step 3.)

Step 6: Test the content store connection and start the Cognos service

  1. Go to Explorer on left pane, and select Data access -> Content store. Right click on the content store option and select Test the connection.
  2. Once you have tested the connection, you need to start the Cognos service. Go to Actions -> Start. Or, you can click the run symbol Run symbol from the tool bar.

Once the service is started successfully, then open Internet Explorer 6.0 or later and type in "http://localhost/cognos8". You will see the IBM Cognos welcome page, which is the launching pad for the administration console, Cognos connection, and report authoring tools. Since Cognos is Web-based, this page can be accessed from any other machine by giving the full IP address of the machine (instead of localhost) on which the Cognos server is installed. The Administration console appears as shown in Figure 3.

Figure 3. IBM Cognos 8 welcome page
Screen capture for configuring group properties

Step 7: Export the cube model in XML format from InfoSphere Warehouse

Use the InfoSphere Warehouse documetnation to create a cube using the Infosphere Warehouse design studio tool. Export the cube data in XML format and save the file.

Step 8: Import the cube model in the Cognos framework manager

Launch the framework manager by selecting Start -> Programs -> IBM Cognos 8 -> Framework manager.

  1. Create a new project in the framework manager by entering the project name and location as shown in Figure 4.
    Figure 4. Creating new project
    entry fields for specifying project name and location
  2. While creating the project, it will prompt you to select the language. The default is "English". Then the Metadata Wizard appears. For now, you can click the Cancel button, and an empty project will be created. After the empty project is created, the screen appears as shown in Figure 5.
    Figure 5. Framework manager
    Framework manager
  3. Next you will create a new data source. In the project viewer pane, right-click on Model and select the Run Metadata Wizard option as shown in Figure 6.
    Figure 6. Framework manager - Run metadata wizard
    Run metadata wizard
  4. When the Metadata Wizard appears, select Data Sources. Click Next and select New. A new data source wizard appears as shown in Figure 7. Enter the name and description for the new data source.
    Figure 7. Create new data source
    Screen where you enter name and description for data source
  5. Click Next and specify the data source connection type as DB2 from the drop down list, as shown in Figure 8.
    Figure 8. Select data source type
    Screen shows selecting DB2 as the type
  6. Click Next and enter the database name. The remaining fields, DB2 connect string and Collation sequence can be empty as shown in Figure 9.
    Figure 9. Specify DB2 connection string
    DB2 connection string screen
  7. On the same page under "Signon" section check the box password and uncheck the other check box, then click on Test the connection as shown in Figure 10.
    Figure 10. Select authentication type
    Select authentication type
  8. Then select the correct dispatcher and enter the user ID and password for connecting to the database. Then click the Test button as shown in Figure 11.
    Figure 11. Test data source connection
    Test data source connection

Now your data source has been created successfully. Once the data source is created you can import the cube model from this data source.

  1. From the framework manager in the project viewer pane, right click on the model. Select the option to Run metadata wizard, then select IBM Metadata Sources as shown in Figure 12.
    Figure 12. Select metadata source
    Screen for selecting the metadata source
  2. Click Next. Select IBM DB2 DW Edition/OLAP option as shown in Figure 13.
    Figure 13. Selecting the metadata type for import
    Selecting the metadata type for import
  3. Browse for the cube model (XML file) as shown in Figure 14.

    Figure 14

    Figure 14. Specifying the cube model file
    Specifying the cube model file
  4. Click Next. You will see a window with the options for import. You do not need to change anything. Just click Next and select the data source which you have created. Click Next, and enter the user id and password for the database. The xml will be imported.
  5. After importing the XML, click next and select the schema name under which your tables exist. Then select the objects that you want to import as shown in Figure 15. Click Finish.
    Figure 15. Importing the objects
    Selecting the objects to import
  6. Now the cube model is imported successfully. You can see all the tables under the model as shown in Figure 16.
    Figure 16. Framework manager - Project viewer
    Framework manager - Project viewer

Step 9: Create and publish the package

Once the model is imported, you need to create the package in the framework manager as shown in Figure 17.

Figure 17. Framework manager - Creating new package
Framework manager - Creating new package

Specify a name for the newly created package. Click Next and select the objects you want to include in package as shown in Figure 18.

Figure 18. Selecting the objects in a package
Selecting the objects in a package

Select the function sets available in the package. Here you can select DB2 as shown in Figure 19.

Figure 19. Selecting function sets
Selecting function sets

Click Finish. You will be prompted whether to publish the package. Click OK. Now you will be given option to define security. You can skip this step as we are not defining any security. Click next and then finish.

Now the package is successfully created and published.

Step 10: Author the report using Report Studio

Open the Cognos Administration Console using "http://localhost/cognos8" or "http://<machine IP>/cognos8". You will see the screen shown in Figure 20. Select the option Create professional reports.

Figure 20. IBM Cognos welcome page - My Actions tab
IBM Cognos welcome page - My Actions tab

The packages that you published earlier will appear here. Select the one which you want from the list as shown in Figure 21.

Figure 21. Selecting a package
Figure 21. Selecting a package

Now you will see the Report Studio screen that gives you the option to create a new report, as shown in Figure 22. There are different kinds of report templates available for different purposes. Here we choose the List report template to display the report in list format.

Figure 22. Create new report
Screen capture for configuring group properties

The Report Studio will open for authoring the report. Since we have selected the List report template, a default list will be created as shown in Figure 23

Figure 23. Report Studio
Screen capture of Report Studio

On the left hand top is the Insertable Objects pane. It shows three icons at the bottom of the pane:

  • source symbolSource: This has all the tables that you imported from the cube.
  • data item symbolData Items: This shows the queries which you have created explicitly.
  • toolbox symbolToolbox: This shows all the components the Report Studio offers to create reports, such as Textbox, Table, Chart, and List. You can drag and drop any of the items available in the toolbox.

Click on the source tab in the Insertable Objects pane. You see the tables that are available in the metadata that we imported. An easy method is to drag the entire table on to the list that we see on the right hand side, or only selected columns from a particular table. Then you will see the column names appearing on the report area in a table format, as shown in Figure 24.

Figure 24. Report Studio - Source tab
Screen capture for Report studio - Source tab

Once this is done you can start running the report by clicking on the Run option from the Menu. You will see different formats for running the reports. Here you can select PDF as shown in Figure 25

Figure 25. Report Studio - Run options
Screen capture for Report studio Run options

When you click on Run, a new window will open and the report will be displayed in the Cognos Viewer, as shown in Figure 26. You can also save this report.

Figure 26. Cognos Viewer
Screen capture of Cognos viewer

Conclusion

This article has shown how simply and quickly Cognos can be leveraged to unlock the value of the information in your InfoSphere warehouse. Take advantage of the samples included with the Cognos installation to see more examples of how Cognos can help you create professional reports.

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Big data and analytics on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Big data and analytics, Information Management
ArticleID=397902
ArticleTitle=Generate Cognos reports using InfoSphere Warehouse Cubes
publish-date=06182009