Use IBM Cognos tools with DB2 to develop Business Intelligence reports

This article describes the complete process for generating Business Intelligence reports using IBM® Cognos® Business Intelligence tools with an IBM DB2® database. Also, the step-by-step processes for data creation using IBM DB2, data modeling using Cognos Framework Manager, and developing reports using Cognos Report Studio are covered in this article.

Share:

Deepak Jindal (djindal3@in.ibm.com), Software Engineer, IBM China

Photo of author Keepak JindalDeepak Jindal is a Software Engineer working in DB2 Tools Continuing Engineering Team at IBM India Software Labs. He is currently working on DB2 Administration Tools components.



Rachit Arora (rachitar@in.ibm.com), Software Engineer, IBM China

Rachit AroraRachit Arora is a IBM Certified Solution Developer for QualityStage and DataStage. He has a Bachelor of Engineering degree from Thapar University, Patiala. He has been involved in over 10 POCs on data quality in emerging markets. He has been part of the team developing data quality rules for India. He is involved in product development of IBM InfoSphere Streams.



29 September 2011

Also available in Chinese Russian Vietnamese

Overview

Organizations need to show their data in reports to help them with their decision making processes. Since every organization has huge amounts of complex data, the process to represent this data as an efficient and effective business report is very crucial. This data needs to be organized, modeled and represented in an efficient manner for better intelligence. This can be done using IBM Cognos Business Intelligence tools with IBM DB2 Database.

This article will help you to understand all of the concepts, starting from the data creation at DB2, data modeling done at Framework Manager, data metadata import/export, to Cognos framework and intelligence report creation using IBM Cognos Business Intelligence tools like Report Studio. This will enable you to develop your own intelligence applications and reports using Cognos and DB2 together, which otherwise is a very complex process.

Creating Cognos BI reports using IBM DB2 involves the following five main processes.

  • Create a DB2 database
  • Export DB2 database metadata to Framework Manager
  • Dimensional modeling in Framework Manager
  • Create the Cognos package and publish it to Cognos Connection
  • Design the report using Cognos Business Intelligence tools

Software requirements

This article uses the following software

  • IBM Cognos Express v9.0.0
  • IBM Cognos Express Framework Manager
  • IBM DB2 V9.1

IBM Cognos Express is a Business Intelligence solution with integrated reporting and analysis modules. It is a pre-configured BI solution that is easy to install and easy to use. IBM DB2 V9.1 Enterprise Server edition database has been used as the content store. Both IBM Cognos Express and IBM DB2 are installed on a Microsoft Windows machine.


Create a DB2 database

Data is the building block for any Business Intelligence report. In this article, you will use Cognos Express Manager with DB2 as the content store for the data. This DB2 database is used to build the Cognos Business Intelligence report.

You will also look at an example of a company having different kinds of products that it sells in different geographies. The Cognos report will show the revenue trends for this company on the basis of their products, geographies, and the years in which the company is operating.

Please note that the database has been simplified to keep the article simpler to understand. In actual scenarios, the database could be more complex.

Table 1. The BRANDS table stores the data about brands of the company
ColumnsColumn TypeConstraintRemarks
BRAND IDBIG INTPRIMARY KEY
BRAND NAMEVARCHAR

For example, the Brands are Personal Care, Household Care, Food & Beverages.

Table 2. The CATEGORY table stores the data about the categories under each brand of the company
ColumnsColumn TypeConstraintRemarks
CATEGORY IDBIG INTPRIMARY KEY
CATEGORY NAMEVARCHAR
BRAND IDBIG INTFOREIGN KEYThis references BRAND ID column of Brands table

For example, under Personal Care Brand, the product categories are Cosmetics, Hair Care, Skin Care, and so on.

Table 3. The PRODUCTS table stores the products under each category of the company
ColumnsColumn TypeConstraintRemarks
PRODUCT IDBIG INTPRIMARY KEY
PRODUCT NAMEVARCHAR
CATEGORY IDBIG INTFOREIGN KEYThis references CATEGORY ID column of CATEGORY table

For example, under Cosmetics product category, the products are Product 1, Product 2, and so on.

Table 4. The REGION table shows different regions in which the company is operating
ColumnsColumn TypeConstraintRemarks
REGION IDBIG INTPRIMARY KEY
REGION NAMEVARCHAR

For example, the regions are Asia Pacific, Americas, Europe, and so on.

Table 5. The COUNTRY table stores different countries under each region in which the company is operating
ColumnsColumn TypeConstraintRemarks
COUNTRY IDBIG INTPRIMARY KEY
COUNTRY NAMEVARCHAR
REGION IDBIG INTFOREIGN KEYThis references REGION ID Column of REGION Table

For example, under Asia Pacific, countries are INDIA, CHINA, and so on. Similarly under Americas, the countries are US and MEXICO.

Table 6. The YEAR table stores the data of the years in which company has operated
ColumnsColumn TypeConstraintRemarks
YEAR IDBIG INTPRIMARY KEY
YEARVARCHAR

For example, years are 2009 and 2010.

Table 7. The QUARTER table stores the quarter in each year for which the company has operated
ColumnsColumn TypeConstraintRemarks
QUARTER IDBIG INTPRIMARY KEY
QUARTERVARCHAR
YEAR IDBIG INTFOREIGN KEYThis references YEAR ID Column of YEAR Table

For example, under 2009, Jan-Mar and Apr-Jun are two quarters. Similarly under 2010, Apr-June and July-Sept are two quarters.

Table 8. The MONTH table stores the month for each quarter in which the company has operated
ColumnsColumn TypeConstraintRemarks
MONTH IDBIG INTPRIMARY KEY
MONTHVARCHAR
QUARTER IDBIG INTFOREIGN KEYThis references QUARTER ID Column of QUARTER Table

For example, under Quarter Jan-Mar, months are Jan and Feb. Similarly under Jul-Sep, months are Aug and Sep.

Table 9. The REVENUE table stores the revenue earned by selling a product in a specific country at a specific month
ColumnsColumn TypeConstraintRemarks
PRODUCT IDBIG INTFOREIGN KEYThis references PRODUCT ID column of PRODUCT table
COUNTRY IDBIG INTFOREIGN KEYThis references COUNTRY ID column of COUNTRY table
MONTH IDBIG INTFOREIGN KEYThis references MONTH ID column of MONTH table
REVENUEBIG INT

For example, Company earned 40000 by selling Product 1 in India in January 2009.

The Foreign Key relationships in the database that is shown here can be designed in Framework Manager as well. If these foreign key relationships are created in the database, then these relationships will be created automatically while importing the database in Framework Manager. But if these relationships are not created in the database, then these can be created in Framework Manager.

For understanding how to create relationships in Framework Manager, the Foreign Key Relationships are not created in the database. Instead, you will create these relationships in Framework Manager.


Export DB2 database metadata to Framework Manager

Framework Manager is the metadata modeling tool that is used to create relationships, dimensions etc. in the data and then publish this modeled data to Cognos Frameworks. To perform metadata modeling, the metadata database created earlier needs to be imported in Framework Manager.

This involves the following two processes:

  • Creation of data source for DB2 database in Cognos
  • Export the metadata of data using the created data source

Creation of data source for DB2 database in Cognos

  1. Open Framework Manager and select Create New Project. Give a project name for the Framework Manager project. Click Next and select the Data Sources option on the Metadata wizard, as shown in Figure 1.
    Figure 1. The Select Metadata Source wizard
    This figure shows the wizard to Select Metadata Source.
  2. From the Select Data Source page, all available data sources are listed down. Since you have not created a data source yet, the screen is empty. To create a new data source, click the New button as shown in Figure 2.
    Figure 2. Select new data source
    This figure shows the wizard page to select new data source
  3. The New data source page lets you create the data source for the database that you created on DB2. Enter the name of the data source that you want to create and click Next. For example, Figure 3 shows the name specified as Cognos_DB for the data source.
    Figure 3. Enter name of the data source
    This figure shows the window to enter name of the data source.
  4. From the Type drop down, select DB2, as shown in Figure 4.
    Figure 4. Specify the connection type
    This figure shows the window to specify the Connection Type.
  5. Enter all the necessary communication details like DB2 database name, connection string, user ID and password to connect to the database, as shown in figure 5.
    Figure 5. Specify the connection parameters
    This figure shows the window to specify the Connection Parameters.
  6. Click Test Connection to test if your connection to the database is successful. The connection status message should say Succeeded, as shown in Figure 6.
    Figure 6. Testing the connection status
    This figure shows the pop window to test the Connection Status.
  7. Click Finish to create the data source. The Cognos_DB data source has been created for the DB2 database.

Export the metadata using the created data source

  1. Once the data source has been created, the data source name will appear in the Select Data Source wizard of Framework Manager, as shown in Figure 7.
    Figure 7. Select the data source
    This figure shows the window to select the Data Source.
    Select the data source and click Next.
  2. Accept the default settings and click Import to import the metadata of the data from DB2 database to the Framework Manager, as shown in Figure 8.
    Figure 8. Import metadata
    This figure shows the pop up wizard for importing meta data.
  3. Once the metadata is imported, the Framework Manager project will open, as shown in Figure 9.
    Figure 9. The Framework Manager project
    This figure shows the Framework Manager Project
  4. Double-click the COGNOS_DB diagram to see the imported tables, as shown in Figure 10.
    Figure 10. The imported tables
    This figure shows the imported tables in framework manager.

Dimensional modeling in Framework Manager

Framework Manager is a metadata modeling tool that is used to manage data for analysis and reporting. In this process, dimensional modeling is done on the imported metadata in Framework Manager. In dimensional modeling, data is stored in two types of tables. One is Dimension table and other is Fact table. The Fact table contains the measurements of the business (like Revenue in this case), and the Dimension table contains various dimensions on which facts are calculated (like Products, Regions and Year). This data is then represented into hierarchies with levels so that you can have a summarized view of a particular hierarchy, and then can drill down or drill up into that particular hierarchy to have detailed analysis of various levels in a hierarchy.

For example, Year can be one of the hierarchy under which you have specified Quarter and Month as the levels. You can analyze the report based on the data that is generated year-wise, and then you can drill down from year to quarter and from quarter to month to have a more detailed analysis

First of all, you need to create relationships between different query subjects in Framework Manager. You will create the same relationships that get created when a foreign key constraint is created in a database. If the foreign key is already created in the database, then these relationships will be created by Framework Manager automatically while importing. If the database does not have foreign key constraint, then the same relationships can be created in Framework Manager.

Create relationships between query subjects in Framework Manager

  1. To create a relationship, right-click the query subject and select Create, then select Relationship, as shown in Figure 11.
    Figure 11. Create relationship between query subjects
    This figure shows how to create relationship between query subjects
  2. Specify the relationship of the Category query subject with another query subject like Brands, as shown in Figure 12 to create a relationship between Category and Brands on the basis of Brand ID in both the tables.
    Figure 12. Relationship definition
    This figure shows how to create relationship definition.
  1. When you click OK, the relationship between these two query subjects is created. This relationship shows that Brands has one or more category, and each category has one and only one Brands, as shown in Figure 13.
    Figure 13. Relationship created
    This figure shows the Relationship between two tables.
  2. Similarly, create the relationships between the rest of the query subjects as shown in Figure 14.
    Figure 14. All relationships created
    This figure shows all Relationships.

You have created the relationships between different query subjects. Please note that this relationship is identical to the foreign key constraint being designed in the database. So if you create the foreign key relationships at the time of designing the database while importing the database, these relationships would be automatically created by Framework Manager.

This is one of the simplest examples of a Star Schema where Revenue (which is the Fact table) is referencing the Product, Month and Country tables (which are dimensions tables). The Cognos reports will be developed on this Star Schema.

After creating the relationships, you will specify the dimensions and measures in the Dimension Map of Framework Manager.

Create dimensions and measures in Framework Manager

  1. Click the Dimensions Map tab of the Framework Manager project screen, as shown in Figure 15. Since you have not created any dimensions and measures yet, the imported data for the DB2 database is being listed on the top left side of the Framework Manager Project. Using this data, you will create the dimensions and measures.
    Figure 15. Dimension map
    This figure shows a Dimension Map.
  2. Drag the Brands field from the COGNOS_DB under Cognos_Project and drop it under the Dimensions view. This will create a new dimension for Brands, as shown in Figure 16.
    Figure 16. Creating dimensions in Framework Manager
    This figure shows how to create dimensions in Framework Manager.
  3. Once Brands dimension is created, drag the Category Name field from COGNOS_DB and drop it below the Brands dimension in the Dimensions view, as shown in Figure 17.
    Figure 17. Creating level under a hierarchy in Framework Manager
    This figure shows how to create level under a hierarchy in Framework Manager.
  4. Similarly, drag and drop the Product Name from Project Viewer and drop it below the Category_Name. Specify the name of this dimension as ALL PRODUCTS. The final output is shown in Figure 18 where the dimension ALL PRODUCTS has been created that includes the BRANDS of the company. From BRANDS, you can drill down to PRODUCT categories of the Brands in the report. From PRODUCT category, you can drill down to actual Products in the report.
    Figure 18. Creating level under a hierarchy in Framework Manager
    This figure shows how to create level under a hierarchy in Framework Manager
  5. Now one dimension called ALL PRODUCTS has been created. Using the same techniques, create two more dimensions called TIME and REGIONS as shown in Figure 19.
    Figure 19. Creating other dimensions in Framework Manager
    This figure shows how to create other dimensions in Framework Manager
  6. So far you have created three dimensions called ALL PRODUCTS, TIME and REGIONS. Now you will create Measures. In this case, Revenue is the only measure, so drag Revenue from COGNOS_DB and drop it under the Measures tab on top right corner, as shown in Figure 20.
    Figure 20. Creating measures in Framework Manager
    This figure shows how to create measures in Framework Manager

This completes the dimensional modeling for our data. Cognos will use these dimensions and measures for designing reports.

The next step is to create the package and publish it to Cognos Frameworks so that report can be designed using this package.


Create the Cognos package and publish it to Cognos Connection

After creating the dimension modeling, you will now publish this to Cognos Framework for report designing. To do this, you will create the package in Framework Manager

  1. Right click the package in Project Viewer and select Create Package. Type Cognos_Package as the name of the package, as shown in figure 21.
    Figure 21. Creating package in Framework Manager
    This figure shows how to create a Package in Framework Manager.
  2. Select the objects that you want to include in this package. Once you publish this package to Cognos Framework, these objects will be used to design the reports. Since you have created the dimensions and measures, you don't need to publish the query subjects, you will only select the dimensions and measures that you have created and include these in the package, as shown in Figure 22.
    Figure 22. Define objects
    This figure shows the defined objects.
  3. Once the package is successfully created, click Yes to publish the package, as shown in Figure 23.
    Figure 23. Package created
    This figure shows the pop up notification that package has been created.
  4. In the Publish Package wizard, accept the default package location in the content store, as shown in Figure 24, or provide your own location.
    Figure 24. Publish Package wizard
    This figure shows the wizard to publish a Package.
  5. Click Publish to publish the package at the specified location, as shown in Figure 25.
    Figure 25. Package published
    This figure shows a published package.

Designing the report using Cognos tools

You will now create a Business Intelligence report using Report Studio.

  1. Go to the Cognos main page, as shown in Figure 26, which is used to administer all Cognos-related tasks.
    Figure 26. Cognos main page
    This figure shows the Cognos Main Page.
  2. From the Cognos home page, select Create Professional Reports with Report Studio to create the Business Intelligence report for the company. Go to the location where you have published the package and click the package name, as shown in Figure 27. The main page of Report Studio is used to create the Business Intelligence Report for the company. Along with Report Studio, Cognos has Query Studio and Analysis Studio for report designing. Report Studio is used for this article.
    Figure 27. Select Cognos package
    This figure shows the window to select Cognos Package.
  3. Click Create a new report or template, as shown in Figure 28.
    Figure 28. Create a new report
    This figure shows the window to Create a new report.
  4. The page shown in Figure 29 is where you will start designing the report. On the left-hand side under Insertable Objects, there are three dimensions and measures listed that you will use to create the report.
    Figure 29. Cognos Report Studio
    This figure shows the Cognos Report Studio.
  5. Click Table and insert the table with two rows and two columns. After the table is created, you can start creating the charts for the report. From Insertable Objects, drag Chart from the list and drop it under one of the columns. Select the Column option from the Chart group and choose any of the listed column charts, as shown in Figure 30.
    Figure 30. Creating charts in Report Studio
    This figure shows the wizard for selecting and creating Charts in Report Studio.
  6. Drag Regions from Insertable Objects and drop it under Categories(x-axis). Similarly drag Brands and drop it under Series. Drag Revenue and drop it under default Measures(y-axis). This completes the designing of one of the charts for the Revenue Report between all products of the company sold in different regions, as shown in Figure 31.
    Figure 31. Cognos Report Studio
    This figure shows how to create Reports in Cognos Report Studio.
  7. Click Data and select Drill Behavior, so you can specify that the report should be able to drill down on the basis of the dimensional modeling structure, as shown in Figure 32.
    Figure 32. Drill Behavior in Report Studio
    This figure shows how to add Drill Behavior in Report Studio.
  8. Select the Allow drill-up and drill-down option and click OK, as shown in Figure 33. This option now enables the report to drill down and drill up within one dimension. For example, under Regions Dimension, you can drill down from Regions to Countries like from Asia Pacific region to India, Brazil, China, and so on.
    Figure 33. Allow drill up and drill down in Report Studio
    This figure shows the wizard to allow Drill Up & Drill Down in Report Studio.
  9. Run the report to see the chart similar to the one shown in Figure 34.
    Figure 34. Cognos Report Viewer
    This figure shows the Cognos Report Viewer.
  10. Drill down under this report to get more detailed analysis. For example, as shown in Figure 35, you can drill down under Regions and Brands to have a more detailed analysis. This report now shows the Revenue earned by the company by selling different brands in all regions.
    Figure 35. Cognos Report showing revenue earned from all brands in all regions
    This figure shows the Cognos Report for revenue earned from all brands in all regions.
  11. Click Regions to see the report show the revenue earned by selling different brands in different regions, as shown in Figure 36.
    Figure 36. Report showing revenue earned from all brands in different regions
    This figure shows the Cognos Report for revenue earned from all brands in different regions.
  12. Click Asia Pacific Region to drill down and show the revenue earned by selling different brands in Asia Pacific region, as shown in Figure 37.
    Figure 37. Report showing revenue earned from different brands in Asia Pacific region
    This figure shows the Cognos Report for revenue earned from different brands in Asia Pacific region.
  13. Click HOUSEHOLD CARE to see the revenue earned by selling Household Care Products in Asia Pacific Countries, as shown in Figure 38.
    Figure 38. Report showing revenue earned from HouseHold Care products in Asia Pacific region
    This figure shows the Cognos Report for revenue earned from HouseHold Care products in Asia Pacific region.
  14. You could create a similar report in Report Studio which would look like the one shown in Figure 39.
    Figure 39. Report Studio
    This figure shows how to create multiple reports in Report Studio.
  15. The final report is shown in Figure 40.
    Figure 40. Final Report
    This figure shows the Final report created in Report Studio.
  16. Once you click on every chart to get more detailed analysis, the report gets drilled down as shown in Figure 41.
    Figure 41. Final report after drilling down
    This figure shows the Final report after drilling down at first level.
  17. If you drill down further yet, the report will look similar to Figure 42.
    Figure 42. Further drill down in the final report
    This figure shows the Final report after drilling down at second level.

Conclusion

This article has shown how Cognos Business Intelligence tool can be used with DB2 database to generate complex Business Intelligence reports. After reading this article, the reader will be able to learn how to create the data sources for databases in Cognos Frameworks, import the metadata of the database, perform dimensional modeling in Framework Manager and finally design the report using Report Studio. This will enable the readers to quickly develop their own Cognos intelligence reports in a simple manner.

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=761565
ArticleTitle=Use IBM Cognos tools with DB2 to develop Business Intelligence reports
publish-date=09292011