![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
Before you start
The IBM® Rational® Portfolio Manager Open Data Access feature helps you extract, transform, and load (ETL) data from Rational Portfolio Manager into a third normal form (3NF) staging database model. The result is a data area that enforces referential integrity and eliminates data redundancies, among other advantages. This makes it easier to create reports.
This tutorial provides a sample scenario that illustrates how to create reports by using the IBM® Cognos® 8 Business Intelligence software suite with Rational Open Data Access.
To work through the exercise in this tutorial, you need to have this software installed and configured:
- IBM Rational Portfolio Manager (client, middleware, database) on DB2
- IBM Rational Portfolio Manager Open Data Access on DB2 v9.5
- IBM Cognos 8.3 Business Intelligence
- IBM Cognos 8 BI Server
- IBM Cognos 8 BI Data Manager
- IBM Cognos 8 BI Framework Manager
- IBM Cognos 8 BI Reporting
This tutorial shows the basics of these tasks:
- Using Data Manager to pull data from the Rational Open Data Access database and organize it into a star schema.
- Using Framework Manager to model the metadata
- Using Cognos Report Studio to create a simple report based on the published metadata (see Figure 1).
- Creating a dimensional model in Framework Manager and a report that allows drill-ups and drill-downs.
Figure 1. Sample report
It would require separate tutorials to describe how to properly create an ETL in Data Manager and a metadata model in Framework Manager, along with the underlying theory and alternatives. Therefore, this tutorial does not go into details better covered in the Cognos documentation. Instead, it describes the main steps, as well as the elements that are necessary for building them. For more information on Data Manager and Framework Manager, please see the user guide documentation for each.
There are many ways to design a star schema, because the result depends on your reporting needs. To keep things simple, this tutorial shows only how to create the tables that you need to build the simple report, as shown in Figure 1. We omit the other tables in the General Health star schema.
The fact table to be constructed in this example is SCOPE_HEALTH, which contains quantitative information about scope elements, such as the total number of defects, the number of defects closed, etc.
The dimension table to be constructed is PROJECT_DIM, which contains project information such as the name, state, currency, etc.
Figure 2 shows the design of these tables.
Figure 2. Sample database schema
Note:
For the PROJECT_DIM table, the number of description fields (DESC_FIELD_COLUMN_IDXX and DESC_FIELD_VALUEXX) actually goes up to 20, but is abbreviated for this tutorial.
Table of contents
Next steps from IBM
Tags
Use the slider bar to see more or fewer tags.
Popular tags shows the top tags for this particular content zone (for example, Java technology, Linux, WebSphere).
My tags shows your tags for this particular content zone (for example, Java technology, Linux, WebSphere).
Popular tutorial tags |
My tutorial tagsSkip to tags list
Popular tutorial tags |
My tutorial tags
