Using Cognos 8 BI with Rational Portfolio Manager

This tutorial provides a sample scenario that shows how to create reports by using IBM® Cognos® 8 Business Intelligence software suite with the Open Data Access tool in Rational Portfolio Manager. Open Data Access is a new feature that provides an easier to understand database model to help you create reports.

Share:

My-An Nguyen (nguyenm@ca.ibm.com), Software Developer, IBM

Author photoMy-An Nguyen is a software developer on the Rational Portfolio Manager team, where she and her colleague Myriam Matwiy investigated the practical use of Cognos Business Intelligence software with the Rational Portfolio Manager Open Data Access feature.



29 January 2010

Also available in Chinese

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.

Prerequisites

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

What you will learn

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
Report showing defect information per project

Terms in this example

SCOPE_HEALTH is the name of the database table that contains information related to scope elements.

ScopeHealth is the name of the Cognos Data Manager fact build.

Essentially, the fact build defines a work flow that populates the SCOPE_HEALTH table with relevant data. Thus, ScopeHealth and SCOPE_HEALTH are related, but they are not the same thing.

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.

Star schema

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
Scope health fact table linked to Project table

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.


Extract, transform, and load from the staging to the warehouse schema

Data Manager provides a graphical interface to help simplify the creation of data warehouses and ETL jobs. It also offers validations and referential integrity checks so that issues can be identified and resolved before data is loaded into the warehouse.

In order to produce the schema illustrated in Figure 2, the following needs to be created in the Data Manager catalog:

  • Project reference dimension
  • Project dimension build
  • Scope health fact build

Figure 3 depicts what the catalog structure will look like.

Figure 3. Data Manager catalog structure
Shows final expected structure in Data Manager

Project reference dimension

Data Manager requires that each dimension have a reference dimension to hold its reference structures and templates.

  • A reference structure provides the model for the dimension.
  • A template defines the attributes required in the reference structure, as well as the behavior of those attributes.

Figure 4 shows what to expect in the visualization pane when the reference structure is complete:

Figure 4. Data Manager project reference dimension
Expected Project Reference Dimension construction

To start building:

  1. Create a new catalog in Data Manager by using the properties of your Rational Open Data Access database.
  2. Right-click Library > Connections, select Insert Connection, and enter connection details for the Rational Open Data Access database. Insert another connection for the data warehouse database to be populated.
  3. Right-click Library > Dimensions and select Insert Reference Dimension from the drop-down menu.
  4. Fill out the form to create the new reference dimension Project_Dim, and then click OK. This will create a new item under Library > Dimensions named Project_Dim that has a subfolder named Templates.
  5. Right-click Library > Dimensions > Project_Dim > Templates, and select Insert Template from the drop-down menu.
  6. Fill out the forms to create a new template.
    1. In the General tab:
      1. Give the template a name. In this example, it is called ProjectDimensionTemplate.
      2. Optionally, fill out the other fields.
    2. In the Attributes tab:
      1. Create all the attributes (fields) needed for the project dimension (see Figure 5).
      2. If the PROJECT_DIM table has already been created in the warehouse before using Data Manager, then you can use the Import Table button. Otherwise, use the Add button to create each field. When an ETL using this template runs, Data Manager will automatically create the table.
      3. Set the behavior of the PROJECT_ID attribute to Business Key, as appropriate, and its Value to True.
    3. Click OK.
Figure 5. Data Manager project dimension template, attributes
PROJECT_ID property row highlighted
  1. Right-click Library > Dimensions > Project_Dim and select Insert Hierarchy from the drop-down menu.
    1. Enter ProjectHierarchy as a name.
    2. Optionally, fill out the other fields.
  2. Select Library > Dimensions > Project_Dim > ProjectHierarchy.
  3. In the visualization pane, right-click the DataStream object and select Insert Data Source from the drop-down menu.
  4. Fill out the forms:
    1. Fill out the General tab.
    2. In the Query tab,
      1. Under the Database field, select the connection to the Rational Open Data Access database.
      2. Enter the query shown in code Listing 1 and then test it:
Listing 1. Query for the ProjectHierarchy object
SELECT PROJ.PROJECT_ID, 
       WBSELEM.NAME, 
       WBSELEM.REFERENCE_NUMBER AS REFERENCE_NUMBER, 
       WBSELEM.EXTERNAL_ID AS EXTERNAL_IDENTIFIER, 
       CURR.DESCRIPTION AS CURRENCY, 
       S.NAME AS STATE, 
       RTF.NAME AS DESC_FIELD_COLUMN_ID01, 
       ASSIGNEDRTF.RTF_DATA AS DESC_FIELD_VALUE01 
FROM  RODA.PROJECT PROJ JOIN RODA.WBS_ELEMENT WBSELEM ON PROJ.PROJECT_ID = WBSELEM.WBS_ID
       LEFT JOIN RODA.CURRENCY CURR ON PROJ.CURRENCY_ID = CURR.CURRENCY_ID 
       LEFT JOIN RODA.STATE S ON WBSELEM.STATE_ID = S.STATE_ID 
       LEFT OUTER JOIN 
       (RODA.WBS_ASSIGNED_RTF ASSIGNEDRTF INNER JOIN RODA.RTF RTF ON 
              ASSIGNEDRTF.RTF_ID = RTF.RTF_ID) ON PROJ.PROJECT_ID = ASSIGNEDRTF.WBS_ID
              AND ASSIGNEDRTF.RTF_ID = 5581
  1. In the Result columns tab, click Refresh.
  2. Click OK.

You should now see these elements in the visualization pane:

  • A new database icon connected to a data source icon
  • A disconnected DataStream object.
  1. In the visualization pane, right-click the newly generated DataStream object, and select Properties from the drop-down menu.
  2. Follow the instructions to map data source items to data stream items.
    1. Try using the Auto Map function.
    2. Click OK.
    3. When you are finished, in the visualization pane, you should see a new arrow that connects the data source item to the DataStream object.
  3. Right-click the ProjectHierarchy object in the visualization pane, and select Insert Level from the drop-down menu.
    1. In the General tab
      1. Give the level a name. In this example, it is called ProjectDimensionLevel.
      2. Optionally, fill out the other fields in this tab.
    2. In the Attributes tab
      1. Select the newly created template in the Template drop-down menu. In this example, it is ProjectDimensionTemplate. After it is selected, the Available Attributes pane should be populated with all of the template's attributes.
      2. Add all of the items in the Available Attributes pane to the Chosen Attributes for this level.
      3. In the Chosen Attributes pane, make sure to specify which field is an "Id" (PROJECT_ID, in our case) and which is a "Caption" (NAME, in this case).
    3. Click OK.
  4. Right-click the ProjectHierarchy object in the visualization pane, and select Mapping from the drop-down menu.
  5. Follow the instructions to map each data stream items by using the Map button, dragging, or double-clicking Level Attributes. Click OK when you are finished. Now you should see in the visualization pane a new arrow connecting the DataStream item to the ProjectHierarchy object.
  6. Save the Data Manager catalog.

Project dimension build

A dimension build delivers data into a dimension table. It gets data from the reference structure and delivers it to a specified target, using the template to determine the behavior of each column (see Figure 6).

Figure 6. Data Manager - Project Dimension build
Expected Project Dimension build construction

Review Figure 2 for the structure of the PROJECT_DIM table.

To create this dimension build, follow these steps:

  1. Right-click Builds and JobStreams and select Insert Dimension Build from the drop-down menu.
  2. Fill out the forms to create the new dimension build.
    1. In the General tab
      1. Give the build a name. In this example, it is called ProjectDimension.
    2. In the Dimension tab
      1. For the Dimension to be delivered drop-down menu, select the Project_Dim reference dimension that was created in the previous section.
      2. The Hierarchy/Lookup to be delivered drop-down menu should then be automatically populated.
      3. For the Deliver into database drop-down menu, select the connection object representing the data warehouse database to be populated.
      4. Enable the check box Remove Unused Foster Parents.
      5. Set other properties as needed.
    3. Click OK.
    4. Under Builds and JobStreams, this will create a new item named ProjectDimension.
  3. Select Builds and JobStreams > ProjectDimension.
  4. In the visualization pane, you should see two items:
    1. A ProjectHierarchy icon connected to a dimension build icon (ProjectDimension)
    2. A disconnected database object
  5. In the visualization pane, right-click the ProjectDimension object, and select Insert Table from the drop-down menu.
  1. In the Table tab, enter a table name. Our example uses PROJECT_DIM.
  2. In the Columns tab
    1. Select the ProjectDimensionTemplate from the Use template drop-down menu.
    2. Map each column to the proper ProjectDimensionLevel using the Map button.
    3. Click OK.

Scope Health fact build

A fact build performs the ETL to populate fact tables. It acquires data from a specified data source and, after performing the required transformations on the data, delivers it to a target.

Figure 7. Data Manager - Scope Health fact build
Expected Scope Health fact build construction

See Figure 2 for the structure of the SCOPE_HEALTH table.

  1. Right-click Builds and JobStreams and select Insert Fact Build from the drop-down menu.
  2. Follow the instructions to create the new fact build. At a minimum,
    1. In the General tab, specify a name for the build. In our example, we used ScopeHealth as a name.
    2. In the Input tab, set the Duplicate Key Handling setting to Merge records with duplicate keys.
    3. This will create under Builds and JobStreams a new item named ScopeHealth.
  3. Select Builds and JobStreams > ScopeHealth.
  4. Right-click anywhere in the visualization pane, and select Insert Data Source from the drop-down menu. We will start by creating the SelectClosed element shown in Figure 7. It must be noted that the following queries might look strange, but because, in Step 2, we set the Duplicate Key Handling setting to "Merge records with duplicate keys," combined with other settings, Data Manager will aggregate the result sets into the expected sum.
    1. Fill out the General tab, and specify the name as SelectClosed.
    2. In the Query tab, enter the query from Listing 2 and test it. Also, make sure that the Database field is populated with the correct data source.
Listing 2. Query for selecting closed scope elements
SELECT  PROJECT_ID,
        CASE    WHEN TYPE_ID IN (4, 65) THEN 1 ELSE 0 END AS CHANGE_REQUEST_CLOSED,
        CASE    WHEN TYPE_ID IN (5, 66) THEN 1 ELSE 0 END AS RISK_CLOSED,
        CASE    WHEN TYPE_ID IN (6, 67) THEN 1 ELSE 0 END AS ISSUE_CLOSED,
        CASE    WHEN TYPE_ID IN (213, 235) THEN 1 ELSE 0 END AS REQUIREMENT_CLOSED,
        CASE    WHEN TYPE_ID IN (226, 233) THEN 1 ELSE 0 END AS DEFECT_CLOSED,
        CASE    WHEN TYPE_ID IN (227, 234) THEN 1 ELSE 0 END AS SERVICE_REQUEST_CLOSED
FROM    RODA.CRI_ELEMENT
WHERE   STATE_ID IN ( 4910, 4920, 4930, 4939, 5386, 5417, 6117 )
    1. In the Result columns tab, click Refresh.
    2. Click OK.
    Repeat Step 4 for the other Data Source elements.
    1. Listing 3 shows the query used in the SelectPending element.
Listing 3. Query for selecting pending scope elements
SELECT  PROJECT_ID,
        CASE    WHEN TYPE_ID IN (4, 65) THEN 1 ELSE 0 END AS CHANGE_REQUEST_PENDING,
        CASE    WHEN TYPE_ID IN (5, 66) THEN 1 ELSE 0 END AS RISK_PENDING,
        CASE    WHEN TYPE_ID IN (6, 67) THEN 1 ELSE 0 END AS ISSUE_PENDING,
        CASE    WHEN TYPE_ID IN (213, 235) THEN 1 ELSE 0 END AS REQUIREMENT_PENDING,
        CASE    WHEN TYPE_ID IN (226, 233) THEN 1 ELSE 0 END AS DEFECT_PENDING,
        CASE    WHEN TYPE_ID IN (227, 234) THEN 1 ELSE 0 END AS SERVICE_REQUEST_PENDING
FROM    RODA.CRI_ELEMENT 
WHERE   RODA.CRI_ELEMENT.CRI_ID NOT IN
        (
        SELECT  RODA.TRANSFERED_CRI_WBS.WBS_ID 
        FROM    RODA.TRANSFERED_CRI_WBS 
        WHERE   RODA.CRI_ELEMENT.CRI_ID = RODA.TRANSFERED_CRI_WBS.CRI_ID
        ) 
        AND STATE_ID NOT IN 
        (
        4910, 4920, 4930, 4939, 5386, 5417, 6117
        )
  1. The query used in the SelectTotals element is in Listing 4:
Listing 4. Query for selecting total per scope elements
SELECT  PROJECT_ID,
        CASE    WHEN TYPE_ID IN (4, 65) THEN 1 ELSE 0 END AS CHANGE_REQUEST_TOTAL,
        CASE    WHEN TYPE_ID IN (5, 66) THEN 1 ELSE 0 END AS RISK_TOTAL,
        CASE    WHEN TYPE_ID IN (6, 67) THEN 1 ELSE 0 END AS ISSUE_TOTAL,
        CASE    WHEN TYPE_ID IN (213, 235) THEN 1 ELSE 0 END AS REQUIREMENT_TOTAL,
        CASE    WHEN TYPE_ID IN (226, 233) THEN 1 ELSE 0 END AS DEFECT_TOTAL,
        CASE    WHEN TYPE_ID IN (227, 234) THEN 1 ELSE 0 END AS SERVICE_REQUEST_TOTAL
FROM    RODA.CRI_ELEMENT
  1. The query used in the SelectTransferred element is in Listing 5:
Listing 5. Query for selecting transferred scope elements
SELECT  PROJECT_ID,
        CASE    WHEN TYPE_ID IN (4, 65) THEN 1 ELSE 0 END AS CHANGE_REQUEST_TRANSFERRED,
        CASE    WHEN TYPE_ID IN (5, 66) THEN 1 ELSE 0 END AS RISK_TRANSFERRED,
        CASE    WHEN TYPE_ID IN (6, 67) THEN 1 ELSE 0 END AS ISSUE_TRANSFERRED,
        CASE    WHEN TYPE_ID IN (213, 235) THEN 1 ELSE 0 END AS REQUIREMENT_TRANSFERRED,
        CASE    WHEN TYPE_ID IN (226, 233) THEN 1 ELSE 0 END AS DEFECT_TRANSFERRED,
        CASE    WHEN TYPE_ID IN (227, 234) THEN 1 ELSE 0 END AS SERVICE_REQUEST_TRANSFERRED
FROM    RODA.CRI_ELEMENT
WHERE   RODA.CRI_ELEMENT.CRI_ID IN
        (
        SELECT RODA.TRANSFERED_CRI_WBS.WBS_ID 
        FROM RODA.TRANSFERED_CRI_WBS
        WHERE RODA.CRI_ELEMENT.CRI_ID = RODA.TRANSFERED_CRI_WBS.CRI_ID
        )

In the visualization pane, you should now see:

  • A database icon connected to all the data source icons
  • A disconnected DataStream object.
  1. In the visualization pane, right-click the DataStream object, and select Properties from the drop-down menu.
    1. In the DataStream Items tab, map each field of the data source. You can use the Auto Map function if it is appropriate.
      1. The right pane might be blank at first. Clicking on Auto Map will generate the DataStream items and try to automatically map to items in the left pane.
    2. In the Derivations tab, add a calculation for each number field to avoid having null integer columns when there is no result.
      1. Click on Add.
      2. In the General tab, give the derivation a unique name, such as CHANGE_REQUEST_CLOSED1, as shown in Figure 8.
Figure 8. Derivation - General tab
Screenshot of General tab in Derivation Properties
  1. In the Calculation tab, enter this formula, as shown in Figure 9:
    ifnull(<INSERT_INTEGER_FIELD_NAME_HERE>, 0)
Figure 9. Derivation - Calculation tab
Shows Calculation tab with the 'ifnull' formula entered
  1. Click OK.
  2. Repeat step 6b for all integer fields. Figure 10 shows the result.
Figure 10. Derivations list
Shows full list of derivations
    1. Optionally, fill out the other tabs.
    2. Click OK.
    3. When you are finished, in the visualization pane, you should see new arrows connecting the DataStream item to all four data source objects.
    Right-click anywhere in the visualization pane and select Insert Relational Table Delivery from the drop-down menu.
    1. Fill out the General tab. In our example, we set the name as SCOPE_HEALTH.
    2. In the Table Properties tab:
      1. For the Connection drop-down menu, select the data warehouse connection object.
      2. Enter a Table name. In our example, it is SCOPE_HEALTH. If applicable, use the Browse for table function.
      3. Enable the Automatically add columns to table check box.
      4. If applicable (the target table already exists in the database), use the Import Target Table function.
    3. In the Module Properties tab, set Refresh Type to UPDATE/INSERT and enable the check box for Incoming record keys are unique.
    4. Click OK.
  1. Right-click the Transformation Model object in the visualization pane and select Mapping from the drop-down menu.
    1. Map each derived field from the DataStream by using either the Add > Measure or the Auto Map > Create new Elements as Measure function.
    2. Click OK.
  2. Right-click the Transformation Model object in the visualization pane, and select Show Build Elements from the drop-down menu if it has not been activated yet. You should now see all the fields to be delivered under the Transformation Model box.
    1. Right-click the PROJECT_ID field of the Transformation Model in the visualization pane and select Convert to Dimension from the drop-down menu.
  3. Right-click the PROJECT_ID field (once it has been converted to a dimension) and select Properties from the drop-down menu. We must provide a link between the fact table and the dimension table by associating each dimension element in the fact table with a reference dimension.
    1. In the Reference tab,
      1. Select the Project_Dim dimension in the Dimension drop-down menu.
      2. Select ProjectHierarchy in the Structure drop-down menu.
      3. Enable the checkbox Aggregate
      4. In the table, enable the checkbox for Dimension
    2. Click OK.
  4. Right-click the other fields in the Transformation Model (e.g. CHANGE_REQUEST_TOTAL) and select Properties from the drop-down menu.
    1. In the Aggregation tab, ensure that the Regular aggregate value is set to SUM.
  5. Save the Data Manager catalog.

To run the ETL, right-click Build and JobStreams > ProjectDimension, and then select Execute from the drop-down menu.

Do the same for Build and JobStreams > ScopeHealth.


Framework Manager

In order to create Cognos reports, you must first model the metadata in Framework Manager, package the metadata, and then publish it to the Cognos server. There are two ways to do that.

  • Method 1 is to export the metadata from Data Manager into an XML file and then import that XML file into Framework Manager.
  • Method 2 is to use Framework Manager to generate the metadata from the database tables and then model it further.

Method 1: Use an exported XML file from the Data Manager

An advantage of using Data Manager is that you can export the metadata to an XML file that can then be imported into Framework Manager to produce a model of the data warehouse without having to start from scratch. Although this is convenient, it is still important to verify the results of the import to ensure that the base components are modeled as expected. Also, depending on your reporting needs, you might need to manipulate the metadata further to create reports.

To export the metadata to an XML file:

  1. In Data Manager, right-click Metadata > Collections and select Insert Metadata Collection from the drop-down menu.
  2. Fill out the metadata collection properties pop-up window and then click OK.
  3. Right-click Builds and JobStreams > ProjectDimension and select Add to Metadata from the drop-down menu.
    1. Click Next.
    2. Click Finish.
  4. Right-click Builds and JobStreams > ScopeHealth, and select Add to Metadata from the drop-down menu.
  5. Follow the instructions in the wizard, making sure to:
    1. In the first window, specify the Metadata Collection that was just created in Step 1. Click Next.
    2. In the second window, specify which field is dimensional. In our example, the dimensional field is PROJECT_ID
    3. After the dimensional field is specified, click the ellipsis button () in the Details column to specify the dimension column it maps to. In our example, PROJECT_ID maps to ProjectDimension.PROJECT_DIM.PROJECT_ID, as shown in Figure 11.
Figure 11. Linking the dimension
Shows sequence when adding ScopeHealth to metadata
    1. Click OK.
    2. Click Next, and then Finish.
    Right-click the completed metadata collection and select Export Metadata in the drop-down menu.
  1. Follow the Export Metadata wizard to create and save the XML file.

Then, to import the Data Manager metadata XML file into Framework Manager, follow these steps

  1. In a Framework Manager project, right-click the root namespace folder and select Run Metadata Wizard from the drop-down menu, as shown in Figure 12.
Figure 12. Framework Manager - Run Metadata wizard
Shows Run Metadata Wizard option in drop-down menu
  1. In the first window of the wizard, select Cognos 8 Data Manager (*.xml) as the metadata source.
  2. Click Next.
  3. Select the XML file containing the model exported from Data Manager and click Import.

After the import is finished, there should be new namespaces, such as "Physical Metadata" and "Business View," created in the project (see Figure 13).

Figure 13. Framework Manager - Imported model from Data Manager
Shows imported metadata in Framework Manager

In general, to verify the results of the import action:

  • Check that the imported relationships and associated cardinalities between items are present and correct.
  • In the Data Sources folder, test the imported data source object's connection and fix it if necessary.
  • Use Framework Manager's Verify Selected Objects or Verify Model feature to find overall problems with the model. For example, with dimensions, a field might have been incorrectly assigned the dual role of "business key" and "member caption."

Method 2: Model data from the database

It is possible to generate metadata from the database tables. This type of import will bring in the table definitions, along with their keys and relationships, if they are defined.

Generate metadata from the data source

  1. In a Framework Manager project, right-click the root namespace folder and select Run Metadata Wizard from the drop-down menu, as shown in Figure 14.
Figure 14. Framework Manager - Run Metadata Wizard
Run Metadata Wizard option in drop-down menu
  1. In the first window of the wizard, select Data Sources as the metadata source.
  2. Click Next.
  3. Select the warehouse database object if it already exists, and then click Next. If it is not present yet in the list, click New, create the connection, and then select it.
  4. In the subsequent wizard window, select the tables that you need (see Figure 15, which shows SCOPE_HEALTH selected).
Figure 15. Framework Manager - Selecting source tables
Metadata Wizard: Select Objects window
  1. Click Next and then click Import.
  2. After the import is complete, click Finish. In the Framework Manager project, you will now see:
    • The two imported tables in the root namespace
    • The data source
Figure 16. Framework Manager - Result of the import
Shows metadata generated from the database tables

Create different views and model the metadata manually

It is best to create at least two views (namespaces): the import or database view and the business view. Using two or more views makes it easier to remap items to a new data source.

The import view contains the metadata imported from the data source. Verify that the metadata is set up correctly in the import view:

  • Ensure that the relationships and cardinalities exist between query subjects and that they are correct.
  • Check query item properties to ensure that the data is used and formatted appropriately.

The business view provides a layer to the information in the source data so that it is easier to build reports. You can enhance the metadata in the business view in two ways:

  • Add business rules, such as calculations and filters.
  • Organize the model by creating separate views for each user group and be sure that each view reflects group-specific business concepts.

To create the different views and to model the metadata, follow these steps:

  1. Create a namespace to contain the Import View.
    1. Right-click the metadata collection and then click Create > Namespace. Enter a name for the namespace.
Figure 17. Framework Manager - Creating a Namespace
Shows the drop-down menu path to create a Namespace
  1. Create a second namespace to contain the Business View.
    1. Right-click the metadata collection, click Create > Namespace, and enter a name for this namespace.
  2. Drag the imported query subjects into the Import View namespace.
Figure 18. Framework Manager - Import View Namespace
Result of organizing objects into Namespaces
  1. If the relationships between the imported query subjects have not been detected and included, then create relationships between the tables. For this example:
    1. Right-click the PROJECT_DIM query subject and select Create > Relationship
    2. In the Relationship Expression tab (Figure 19):
      1. Specify the other Query subject to be SCOPE_HEALTH.
Figure 19. Framework Manager - Creating a Relationship
Shows dialogs to specify related query subjects
  1. Verify the cardinality between the two query subjects. For each project, there can be many scope elements, so the cardinality is 1-n from PROJECT_DIM to SCOPE_HEALTH. The Relationship impact section can be useful in verifying that the chosen cardinality is correct.
Figure 20. Framework Manager - Relationship Cardinality
Highlights area to specify cardinality
  1. Model the metadata according to reporting needs. For example:
    1. Create shortcuts to the query subjects in the Import View, and move the shortcuts to the Business View to then further model and customize the metadata for the report.
      1. Right-click the query subject and select Create Shortcut.
      2. Drag the shortcut to the Business View namespace.
      3. After the shortcut has been moved to the Business View namespace, you can rename it.
    2. Create a new query subject in the Business View, specifying that the data is based on the existing model (the data in the Import View), like the DEFECT_HEALTH table in Figure 21.
      1. Right-click the Business View and select Create > Query Subject.
      2. Set the name to DEFECT_HEALTH.
      3. Select the option for Model (Query Subjects and Query Items).
Figure 21. Framework Manager - Creating a new Query Subject
Specifying the query subject's name and properties
  1. From the Import View SCOPE_HEALTH item in the Query Subject Definition tab, drag the following fields into the Query Items and Calculations pane:
    • PROJECT_ID
    • DEFECT_CLOSED
    • DEFECT_PENDING
    • DEFECT_TOTALDEFECT_TRANSFERRED.
Figure 22. Framework Manager - New Query Subject Definition
Defining the structure of the new query subject
  1. Click OK.
Figure 23. Framework Manager - Metadata model example
Show the expected metadata model

Publish the package

Before you can start creating reports, you must package and publish the model .

  1. If a package has not yet been created:
    1. Right-click the folder Packages and select Create >Package from the drop-down menu.
    2. In the wizard that appears, give the package a name and then specify from the package which views or query subjects to include.
  2. Right-click the package and select Publish Packages from the drop-down menu. Follow the wizard to publish.

Cognos

Sample report

Figure 24 shows the sample report that we will create.

Figure 24. Sample report
Report showing defect information per project

As Figure 24 shows, the report simply lists the projects, along with the total number of defects for each, and specifies how many defects are transferred and closed.

Report Studio

Report Studio (or Reporting) is an included Web application that can be used to build any kind of reports needed, from simple to complex.

To start building the sample report:

  1. Launch Report Studio and select the package that you want to use.
  2. Choose the option to Create a new or report or template.
  3. Choose a List report.
  4. Drag a List item from the Toolbox onto the report page.
    1. Report Studio should then automatically create an underlying query named "Query1" for this list. You can access this query definition in the Query Explorer.
  5. Drag the NAME and REFERENCE_NUMBER fields from the [Business View].PROJECT_DIM query subject into the List item on the report page.
  6. As shown in Figure 25, drag the DEFECT_TRANSFERRED, DEFECT_CLOSED, and DEFECT_TOTAL fields from the [Business View].SCOPE_HEALTH query subject into the List item.
    1. Because the relationships have been clearly defined in the Import View and the Business View items are all based on the Import View, you can simply drag fields onto a List without worrying about specifying connections between tables.
Figure 25. Report Studio - Dragging fields into a List
Shows where to drag the fields
  1. In the top menu, select Structure > Headers & Footers > Page Header & Footer to create a header to contain the report name.
  2. Drag a Text Item from the Toolbox onto the Header area and type in the report name.
  3. Format the header (bold, bigger font, centered).
  4. To make the list stretch and fill the whole width of the page, click anywhere on the list and, in the Properties pane, use the Select Ancestor button to navigate up to the List item (see Figure 26).
Figure 26. Report Studio - Accessing List properties
Show how to navigate to the proper item hierarchy
  1. After the last step, you should see that the whole list is selected. Then, in the Properties pane, set the Size & Overflow property Width to 100%.
Figure 27. Report Studio - Setting the Width
Highlights the steps to set the List's width

You can now save and then run the report.

To filter the results by, for example, project ID:

  1. As shown in Figure 28, hover the cursor over the Query Explorer on the Explorer bar, and select the query that was automatically created when the List item was dragged onto the report page. It might be named Query1 or Query2.
Figure 28. Report Studio - Accessing the queries
Shows where to access the Query items
  1. In the Data Items view, you can see all of the columns that were added to the List of the report.
  2. From the Source tab in the Insertable Objects pane, drag the PROJECT_ID query item to the Detail Filters pane.
Figure 29. Report Studio - Adding filters to query
Show where to drag the filter conditions
  1. A Detail Filter Expression window will then appear (see Figure 29).
    1. At the end of the line in the Expression Definition box, type =?PROJECT_ID? to indicate that you want to filter the report so that only the results with a project ID that matches the input will be returned.
      • There are other operators possible besides the equal sign.
      • The question marks surrounding the name PROJECT_ID are necessary to denote it as a parameter.
  2. Click OK.

You can now run the report, and there will now be a prompt page (shown in Figure 30) asking you to select a project ID to filter the results. It is possible to create your own Prompt page and customize the layout and information.

Figure 30. Report Studio - Running report with prompt page
Highlights steps to run report with prompt

Dimensional model

Sample report

So far, you have created a fairly straightforward report. You can also create a report that allows the reader to drill up and drill down, to view different levels of detail for the data, within a predefined hierarchy. For that, you need a dimensional or dimensionally modeled data source.

Using the two tables that you have created in the Extract, transform, and load from the staging to the warehouse schema section, you will change the metadata model in Framework Manager to turn it into a dimensional model. However, with only those two tables, there will not be an ideal hierarchy (such as, for example, "Year – Month – Week – Day"), but the steps shown are still relevant to modeling hierarchies.

Figure 31 shows the sample report that you will create in this section, which enables drilling up and down.

Figure 31. Sample drill up/down-enabled report
Shows various results of drilling up and down

As shown in Figure 31, the report is a cross tab that shows the number of defects opened by project and project status. You can drill down within the project or project state to see more details.

Framework Manager

The types of dimensions with which you can work in the Cognos Framework Manager are regular dimensions and measure dimensions.

A regular dimension contains descriptive and business key information and organizes the information in a hierarchy, forming increasing levels of detail.

A measure dimension is a collection of facts.

For this example, you will start with the metadata model in Framework Manager that you created by using Method 2 (generating the metadata from the database tables) in section Method 2: Model data from the database.

  1. Create a new namespace called Dimensions.
  2. In the Dimensions namespace
    1. Create a Measure Dimension named SCOPE_HEALTH.
      1. Right-click the Dimensions namespace and select Create > Measure Dimension.
      2. In the Dimension Definition window that appears, select the SCOPE_HEALTH query subject in the Import View from the Model Objects pane, and drag it to the Measure pane.
      3. Click OK.
    2. Create a Regular Dimension named PROJECT_DIM.
      1. Right-click the Dimensions namespace and select Create > Regular Dimension.
      2. Drag [Import View].PROJECT_DIM.PROJECT_ID from the Available items pane to the Hierarchies pane.
      3. In the Hierarchies pane, rename all occurrences of PROJECT_ID with PROJECT_DIM.
      4. In the Hierarchies pane, select the last level. You should see in the pane underneath that PROJECT_ID had been given the role of _businessKey. If it has not, then set it to _businessKey.
      5. Drag [Import View].PROJECT_DIM.NAME into the last level in the Hierarchies pane. A small drop-down menu will ask to specify the role of this item. Select _memberCaption, as shown in Figure 32.
Figure 32. Framework Manager - Creating dimensions
Shows the action and selection needed to create
  1. Also drag the REFERENCE_NUMBER, CURRENCY, and EXTERNAL_IDENTIFIER fields into the same level, and then specify their roles as No Role. The result is shown in Figure 33.
Figure 33. Framework Manager - PROJECT_DIM
Shows the Project dimension's composition
    1. Click OK.
    Create a second Regular Dimension named STATE_DIM.
    1. Right-click the Dimensions namespace and select Create > Regular Dimension.
    2. Drag [Import View].PROJECT_DIM.PROJECT_ID from the Available items pane to the Hierarchies pane.
    3. In the Hierarchies pane, rename all occurrences of PROJECT_ID to STATE_DIM.
    4. In the Hierarchies pane, select the last level. You should see in the pane underneath that PROJECT_ID had been given the role of _businessKey.
      1. If it has not, then set it to _businessKey.
    5. Drag [Import View].PROJECT_DIM.STATE into the last level in the Hierarchies pane. A small drop-down menu will ask to specify the role of this item. Select _memberCaption. The result is shown in Figure 34.
Figure 34. Framework Manager - STATE_DIM
Shows the State dimension's composition
    1. Click OK.
    Package and publish the model, using the Publish the package section as a reference.
Figure 35. Framework Manager - Dimensions model
Shows the final dimensional model structure

Report Studio

After the dimensional model has been published, Report Studio can make use of the hierarchical information. To create the sample drill up or drill down-enabled report shown in Figure 31:

  1. Create a new list report that uses the newly published package.
  2. Drag a Crosstab item from the Toolbox onto the report page.
  3. As shown in Figure 36, drag the PROJECT_DIM hierarchy from the Dimensions.PROJECT_DIM dimension into the Columns section of the crosstab and select Root members in the menu that appears.
Figure 36. Report Studio - Dragging to Crosstab columns
Highlights where to drag dimensions in a crosstab
  1. Drag the STATE_DIM hierarchy from the Dimensions.STATE_DIM dimension into the Rows section of the crosstab, and select Root members in the menu that appears.
  2. Drag the Dimensions.SCOPE_HEALTH.DEFECT_PENDING field into the Measure section of the crosstab (see Figure 37).
Figure 37. Report Studio - Dragging to Crosstab measures
Highlights where to drag measures in a crosstab
  1. Go to Data > Drill Behavior.
    1. In the Basic tab, check the check box for Allow drill-up and drill-down.
    2. Click OK.
  2. Run the report.

In the report, notice that the crosstab headings behave like links. You can explore further in either of these ways:

  • Click the crosstab headings to drill down.
  • Right-click the crosstab headings and use the context menu to specify drilling up or drilling down.

Summary

This tutorial presented the basic guidelines for creating a report by using IBM Cognos 8 Business Intelligence software suite with Rational Portfolio Manager Open Data Access feature. You saw how to build an ETL in the Data Manager tool that took data from Open Data Access and loaded it into a sample star schema. You then learned different ways to model the metadata in the Framework Manager feature to create both regular and dimensional reports.

Through further experimentation with the Cognos tools, you will find that there are a lot of ways to create reports adapted to your business needs.

Resources

Learn

  • See the official documentation that is included with the installation of IBM Cognos 8 Business Intelligence components:
    • Data Manager User Guide
    • Framework Manager User Guide
    • Framework Manager Guidelines for Modeling Metadata
    • Report Studio Professional Authoring User Guide
  • Get more information about IBM Cognos and Rational Portfolio Manager.
  • Learn about other applications in the IBM Rational Software Delivery Platform, including collaboration tools for parallel development and geographically dispersed teams, plus specialized software for architecture management, asset management, change and release management, integrated requirements management, process and portfolio management, and quality management. You can find product manuals, installation guides, and other documentation in the IBM Rational Online Documentation Center.
  • Visit the Rational software area on developerWorks for technical resources and best practices for Rational Software Delivery Platform products.
  • Explore Rational computer-based, Web-based, and instructor-led online courses. Hone your skills and learn more about Rational tools with these courses, which range from introductory to advanced. The courses on this catalog are available for purchase through computer-based training or Web-based training. Some of the "Getting Started" courses are available free of charge.
  • Subscribe to the IBM developerWorks newsletter, a weekly update on the best of developerWorks tutorials, articles, downloads, community activities, webcasts and events.

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, Rational, Information Management
ArticleID=465978
ArticleTitle=Using Cognos 8 BI with Rational Portfolio Manager
publish-date=01292010