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:

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

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

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

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
   
   
   
2. Click Next. Select IBM DB2 DW Edition/OLAP option as shown in Figure 13.
   
   
   Figure 13. 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
   
   
   
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
   
   
   
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
   
   
   

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.

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.

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

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.

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

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.

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

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

• Source: This has all the tables that you imported from the cube.
• Data Items: This shows the queries which you have created explicitly.
• Toolbox: 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.

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

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.

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

• Cognos Business Intelligence Reporting: Learn more about Cognos 8 reporting capabilities.
  
  
• IBM Cognos 8 Business Intelligence Resources: Grow your knowlegege with white papers, fact sheets, webcasts, and more.
  
  
• Cognos 8 Business Intelligence Analysis: Learn more about Cognos 8 data analysis capabilities.
  
  
• Cognos page on developerWorks: Connect to resources and grow your Cognos skills.
  
  
• Data Warehousing page on developerWorks: Connect to resources and grow your InfoSphere Warehouse skills.
  
  
• Integration of InfoSphere Warehouse data mining with IBM Cognos Reporting: This 4-part series of articles introduces the integration architecture for integrating data mining with Cognos, and then goes into depth on several techniques.
  
  
• Browse the technology bookstore for books on these and other technical topics.
  
  

Get products and technologies

• Download a free trial version of IBM InfoSphere Warehouse 9.7 Enterprise Edition.
  
  
• Download IBM product evaluation versions or explore the online trials in the IBM SOA Sandbox and get your hands on application development tools and middleware products from DB2®, Lotus®, Rational®, Tivoli®, and WebSphere®.
  
  

Discuss

• Check out developerWorks blogs and get involved in the developerWorks community.
  
  

About the authors

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

Rate this article

Comments

Back to top

Table of contents

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

Next steps from IBM

• Try: Cognos Business Intelligence
• Demo: Video: Cognos 8 BI
• Community: Cognos BI
• Buy: Cognos Business Intelligence

Dig deeper into Information Management on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.

Special offers