Using Cognos 8 BI with Rational Portfolio Manager

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.

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.

Back to top

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.

6. 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.

7. Click OK.
3. 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.

4. Click OK.
4. Package and publish the model, using the Publish the package section as a reference.

Back to top

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.

4. 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.
5. Drag the Dimensions.SCOPE_HEALTH.DEFECT_PENDING field into the Measure section of the crosstab (see Figure 37).

6. Go to Data > Drill Behavior.
   1. In the Basic tab, check the check box for Allow drill-up and drill-down.
   2. Click OK.
7. 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.

5 of 8 | Previous | Next

Comments

Back to top

Table of contents

• Before you start
• Extract, transform, and load from the staging to the warehouse schema
• Framework Manager
• Cognos
• Dimensional model
• Summary
• Resources
• About the author

---

5 of 8 | Previous | Next

Next steps from IBM

• Try: Cognos Business Intelligence
• Demo: Video: Cognos 8 BI
• Community: Cognos BI
• Buy: Cognos Business Intelligence

Dig deeper into Rational on developerWorks

IBM SmartCloud trial. No charge.

Unleash the power of hybrid cloud computing today!

---

Public and private solutions in one trial.

Special offers