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.
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:
- Install Cognos server and Cognos BI model
- Install the DB2 client if the database is on a remote DB2 server
- Create and configure the DB2 database
- Configure the IBM HTTP server as Web server for Cognos
- Configure the content store in the Cognos configuration tool
- Test the content store connection and start the Cognos service
- Export the cube model in XML format from InfoSphere Warehouse
- Create the model in the framework manager by importing the cube.
- Create the Cognos package and publish it
- 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.
- 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.
- 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
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
- 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.
- 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.
- Create the virtual directory "cognos8/cgi-bin" in c:\program files\IBM\HTTPServer\htdocs\en_US directory
- 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.
- 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
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: 188.8.131.52: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
- Go to Explorer on left pane, and select Data access -> Content store. Right click on the content store option and select Test the connection.
- 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.
Figure 3. IBM Cognos 8 welcome page
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.
- 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
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
- 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
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
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
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
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
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.
- 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
Click Next. Select IBM DB2 DW Edition/OLAP option as shown in Figure 13.
Figure 13. Selecting the metadata type for import
Browse for the cube model (XML file) as shown in Figure 14.
Figure 14. Specifying the cube model file
- 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.
- 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
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.
Figure 17. 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
Select the function sets available in the package. Here you can select DB2 as shown in Figure 19.
Figure 19. 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
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
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
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
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.
Figure 24. 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
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
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.
- 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®.