![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
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
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.
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
| Columns | Column Type | Constraint | Remarks |
|---|---|---|---|
| BRAND ID | BIG INT | PRIMARY KEY | |
| BRAND NAME | VARCHAR |
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
| Columns | Column Type | Constraint | Remarks |
|---|---|---|---|
| CATEGORY ID | BIG INT | PRIMARY KEY | |
| CATEGORY NAME | VARCHAR | ||
| BRAND ID | BIG INT | FOREIGN KEY | This 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
| Columns | Column Type | Constraint | Remarks |
|---|---|---|---|
| PRODUCT ID | BIG INT | PRIMARY KEY | |
| PRODUCT NAME | VARCHAR | ||
| CATEGORY ID | BIG INT | FOREIGN KEY | This 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
| Columns | Column Type | Constraint | Remarks |
|---|---|---|---|
| REGION ID | BIG INT | PRIMARY KEY | |
| REGION NAME | VARCHAR |
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
| Columns | Column Type | Constraint | Remarks |
|---|---|---|---|
| COUNTRY ID | BIG INT | PRIMARY KEY | |
| COUNTRY NAME | VARCHAR | ||
| REGION ID | BIG INT | FOREIGN KEY | This 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
| Columns | Column Type | Constraint | Remarks |
|---|---|---|---|
| YEAR ID | BIG INT | PRIMARY KEY | |
| YEAR | VARCHAR |
For example, years are 2009 and 2010.
Table 7. The QUARTER table stores the quarter in each year for which the company has operated
| Columns | Column Type | Constraint | Remarks |
|---|---|---|---|
| QUARTER ID | BIG INT | PRIMARY KEY | |
| QUARTER | VARCHAR | ||
| YEAR ID | BIG INT | FOREIGN KEY | This 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
| Columns | Column Type | Constraint | Remarks |
|---|---|---|---|
| MONTH ID | BIG INT | PRIMARY KEY | |
| MONTH | VARCHAR | ||
| QUARTER ID | BIG INT | FOREIGN KEY | This 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
| Columns | Column Type | Constraint | Remarks |
|---|---|---|---|
| PRODUCT ID | BIG INT | FOREIGN KEY | This references PRODUCT ID column of PRODUCT table |
| COUNTRY ID | BIG INT | FOREIGN KEY | This references COUNTRY ID column of COUNTRY table |
| MONTH ID | BIG INT | FOREIGN KEY | This references MONTH ID column of MONTH table |
| REVENUE | BIG 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
- 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
- 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
- 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
- From the Type drop down, select DB2,
as shown in Figure 4.
Figure 4. Specify the connection type
- 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
- 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
- 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
- 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
Select the data source and click Next. - 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
- Once the metadata is imported, the Framework Manager project will
open, as shown in Figure 9.
Figure 9. The Framework Manager project
- Double-click the COGNOS_DB diagram to see the
imported tables, as shown in Figure 10.
Figure 10. The imported tables
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
- 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
- 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
- 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
- Similarly, create the relationships between the rest of the query
subjects as shown in Figure 14.
Figure 14. All relationships created
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
- 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
- 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
- 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
- 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 PRODUCTShas 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
- 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
- So far you have created three dimensions called
ALL PRODUCTS,TIMEandREGIONS. Now you will createMeasures. 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 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
- 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
- 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
- Once the package is successfully created, click Yes
to publish the package, as shown in Figure 23.
Figure 23. Package created
- 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
- Click Publish to publish the package at the specified
location, as shown in Figure 25.
Figure 25. Package published
Designing the report using Cognos tools
You will now create a Business Intelligence report using Report Studio.
- 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
- 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
- Click Create a new report or template, as shown in
Figure 28.
Figure 28. Create a new report
- 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
- 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
- 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
- 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
- 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
- Run the report to see the chart similar to the one shown in Figure 34.
Figure 34. Cognos Report Viewer
- 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
- 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
- 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
- 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
- You could create a similar report in Report Studio which would look
like the one shown in Figure 39.
Figure 39. Report Studio
- The final report is shown in Figure 40.
Figure 40. Final Report
- 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
- If you drill down further yet, the report will look similar to Figure
42.
Figure 42. Further drill down in the final report
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.
Learn
- Learn about IBM Cognos Business
Intelligence Query and Reporting capabilities
- Watch a demo to learn more about IBM
Cognos Express
- Read the Generate Cognos Reports using InfoSphere Warehouse Cubes
developerWorks article to learn about developing Cognos Business
Intelligence Reports using InfoSphere Warehouse data models
- Visit the Cognos page on IBM
developerWorks where you can browse through Cognos resources and
develop your Cognos skills
- Watch developerWorks on-demand demos
ranging from product installation and setup demos for beginners, to
advanced functionality for experienced developers.
- Get the resources you need in the Information Management
area on developerWorks, to advance your skills on a wide variety
of IBM Information Management products.
- Learn more about Information Management at
the developerWorks
Information Management zone. Find technical documentation, how-to
articles, education, downloads, product information, and more.
- Follow developerWorks on
Twitter.
- Watch developerWorks on-demand demos
ranging from product installation and setup demos for beginners, to
advanced functionality for experienced developers.
Get products and technologies
- Download a free
version of IBM Cognos Express V9.5
- Download a free
trial version of IBM DB2 V9.7 Enterprise
Edition
- Build your next
development project with IBM trial
software, available for download directly from developerWorks, or
spend a few hours in the SOA Sandbox learning how to
implement Service Oriented Architecture efficiently..
Discuss
- Get involved in the My developerWorks community.
Connect with other developerWorks users while exploring the
developer-driven blogs, forums, groups, and wikis.
- Participate in the DB2 discussion forum.
- Participate in the Cognos Express discussion
forum.
- Check out the developerWorks
blogs and get involved in the developerWorks
community.
Deepak 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 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.
