Rational Reporting
2 © Copyright IBM Corporation 2012
Module 1: RRDI and IBM Rational Insight
Introduction
Module 2: IBM Rational Insight Data
Warehouse
Module 3: IBM Rational Insight and IBM
Cognos Data Manager
Module 4: IBM Rational Insight and IBM
Cognos Framework Manager
Module 5: Third-party Integrations
Target audience:
IBM
®Rational
®Reporting for Development Intelligence
(RRDI) and Rational Insight users who want to understand
the data warehouse
Objectives: After reading this document, you will be
able to:
Understand the structure of the data warehouse
Prerequisites:
Read module 1
4 © Copyright IBM Corporation 2012
Your primary sources of information are the information
centers for Rational Insight and the Rational solution for
Collaborative Lifecycle Management (CLM).
These presentations are intended as conceptual
introductions.
The goal in using the Rational Insight data warehouse is to consolidate and
store data from a variety of data sources (Rational product data sources
and other data sources) into one data store to provide the means to
report on and analyze data for software and systems delivery.
From an architectural perspective, the warehouse is divided into multiple
areas, with each area serving a specific purpose. However, the structure
ultimately provides the ability to transparently navigate and report on the
data at aggregated and detail levels across the repository.
Each area of the warehouse is delineated by one or more schemas. The
warehouse includes the following areas:
1. Configuration area
2. Operational data store (ODS)
3. Metrics tables (in traditional data warehousing, known as data marts)
6 © Copyright IBM Corporation 2012
The configuration area of the data store is delineated by the
CONFIG schema.
This schema name is static and an alternative name cannot be
specified during the warehouse setup.
The CONFIG schema simply hosts a set of tables that provide
information about the warehouse setup. For example:
The version of the warehouse
Control information such the audit trail of ETL (extract, transform, and
load) execution
Version migration setup and execution information
The configuration areas is proprietary to the application and no
user customizations or alterations are allowed in this area.
Operational data store
Metrics tables
Data warehouse
The operational data store (ODS) is the area of the data store
where data from all the external data sources is consolidated
and stored according to subject area.
8 © Copyright IBM Corporation 2012 Operational data
Metrics tables
Data warehouse
Fact table
Fact table
Dimensions
Dimensions
Star schema
Star schema
The data in the metrics tables is built from data in the
operational data store.
The Star schema derives its name from the
visual representation of the fact table and its
related dimensions, which resemble a star.
The fact table is the star’s nucleus and the ray
endpoints are the dimension. Each ray or link in
the picture symbolizes the fact table’s relation
(foreign key) to the dimension table it is
referencing.
The snowflake schema is a hybrid of the star
schema. It derives its name from the visual
representation of the fact table and its related
dimensions, and the dimensions and their
related dimensions. The fact table is the
nucleus of the snowflake. At the end of each
branch radiating from the nucleus is a
dimension. Dimensions themselves can have
branches radiating from them where another
dimension sits at the end.
10 © Copyright IBM Corporation 2012 Module 5 - Report Customization 10
Use metrics tables data for:
Aggregated data
Historical trending
Cross-product reports
Ready-to-use examples:
Build Health
Blocking Defects
Defect Distribution
Tested Requirements to
Implemented Requirements
Data is represented in Report Studio
as packages. When you launch
Report Studio, you are prompted to
select a package.
Package Package Measures Measures Dimensions Dimensions Metrics tables Metrics tablesTables in the operational data store (ODS) that store
artifact data have a certain structure and set of
mandatory columns that identify the record within
Rational Insight and identify the record from the
perspective of a point product data store.
When consolidating data in the operational data store,
it is imperative to be able to locate and understand
the source from which it came. The following
information is required to identify records:
1. Primary key column(s)
2. Natural or primary key from point product data store
3. Data source identification
12 © Copyright IBM Corporation 2012
All tables in the operational data store (ODS) that
contain artifact details contain a primary key column of
type integer. This column serves to uniquely identify an
artifact within the data warehouse.
The values for the primary keys are generated during
the ETL process.
The naming convention adopted in the tables is
<artifact>_ID and is the first column listed in the table.
The natural or primary point product keys are the
primary or natural keys of an artifact record from the
point product perspective. They are of type integer or
type varchar and are stored by the ETL as part of a
record’s information in the operational data store.
They are used as part of the identification of the
records during the ETL process, either for an insertion
or update.
The integer column is EXTERNAL_KEY1 and the
varchar variant is EXTERNAL_KEY2.
14 © Copyright IBM Corporation 2012
Data source identification is a varchar field in the table
that uniquely identifies a data source.
The value stored is the UUID value generated by the
XML configuration tool and stored in the XDC file for a
particular resource group.
Each resource defined in an XDC file has the
DATASOURCE_ID field that contains this value. This
value is extracted and stored by the ETL as part of a
record’s information in the operational data store. The
column in the tables that stores this information is
SOURCE_ID.
RESOURCE_ID
– Primary key of the Resource Table
– Type integer
– Value generated in Data Manager as a
surrogate key
EXTERNAL_KEY1
– Type integer
– Stores the natural key value for a record
EXTERNAL_KEY2
– Type varchar
– Stores the natural key value for a record
SOURCE_ID
– Type varchar
– Stores the resource UUID (origin) of a record
Minimal key of a record
– EXTERNAL_KEY1,
EXTERNAL_KEY2,SOURCE_ID
16 © Copyright IBM Corporation 2012
RRDI data warehouse and Rational Insight data warehouse
schema differences:
CLM 3.0.1
Rational Insight 1.0.1.1
The operational data store (ODS) (schema RIODS) and the Star Schema
(schema RIDW) are identical.
CLM has some additional CLM-specific
data that it stores in another schema
(RICALM), which is not present in
Rational Insight.
Rational Insight has some additional
business analytics data that it stores
in another schema (RIBA), which is
not present in CLM.
For more information
CLM Information Center - Reporting topic
–
https://jazz.net/help-dev/clm/index.jsp
IBM Rational Insight Information Center
–
http://publib.boulder.ibm.com/infocenter/rentrpt/v1r0m1/index.jsp
Rational Insight data models
–
https://jazz.net/help-
dev/clm/topic/com.ibm.rational.reporting.overview.doc/ topics/
c_reference_datadictionary.html
Jazz.net library
–
https://jazz.net/library
/
RRDI FAQ on jazz.net
Legal notices
The following paragraph does not apply to the United Kingdom or any other country
where such provisions are inconsistent with local law: INTERNATIONAL BUSINESS
MACHINES CORPORATION PROVIDES THIS PUBLICATION "AS IS" WITHOUT
WARRANTY OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF NON-INFRINGEMENT,
MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. Some states do not allow disclaimer of express or implied warranties in certain transactions, therefore, this statement may not apply to you.
This information could include technical inaccuracies or typographical errors. Changes are periodically made to the information herein; these changes will be incorporated in new
editions of the publication. IBM may make improvements and/or changes in the product(s) and/or the program(s) described in this publication at any time without notice.
If you are viewing this information in softcopy, the photographs and color illustrations may not appear.
Any references in this information to non-IBM websites are provided for convenience only and do not in any manner serve as an endorsement of those websites. The materials at those websites are not part of the materials for this IBM product and use of those websites is at your own risk.
Information concerning non-IBM products was obtained from the suppliers of those
products, their published announcements or other publicly available sources. IBM has not tested those products and cannot confirm the accuracy of performance, compatibility or any other claims related to non-IBM products. Questions on the capabilities of non-IBM
20 © Copyright IBM Corporation 2012
Legal notices
This information contains examples of data and reports used in daily business operations. To illustrate them as completely as possible, the examples include the names of
individuals, companies, brands, and products. All of these names are fictitious and any similarity to the names and addresses used by an actual business enterprise is entirely coincidental.
This information contains sample application programs in source language, which illustrate programming techniques on various operating platforms. You may copy, modify, and
distribute these sample programs in any form without payment to IBM, for the purposes of developing, using, marketing or distributing application programs conforming to the
application programming interface for the operating platform for which the sample programs are written. These examples have not been thoroughly tested under all conditions. IBM, therefore, cannot guarantee or imply reliability, serviceability, or function of these programs. The sample programs are provided "AS IS", without warranty of any kind. IBM shall not be liable for any damages arising out of your use of the sample programs.
