Data warehouse

Edit online

Data warehouse data makes it possible to report on themes, trends, aggregations, and other relationships among data. Data is collected from the IBM Engineering Lifecycle Management applications, then stored in the data warehouse, where it can be transformed to represent various relationships.

Note: Data warehouse reporting is not available for project areas that are enabled for configuration management. To report on data in configurations, you must either use document-style reports or the Lifecycle Query Engine.
Important: Lifecycle Query Engine (LQE) is supported as a data source for reporting on projects with or without configurations; however, there are considerations.
Figure 1. Data Warehouse details
The data warehouse contains two units: operational data store (which contains operational data from applications) and metrics tables (which are made up of fact tables and dimensions) Operational data store (ODS) Metrics tables
The data warehouse contains two data areas:

Operational data store (ODS)

The operational data area of the data warehouse contains the most recent snapshot of the relational operational data. Here, source data from the applications is stored according to its subject area. For example, a defect work item from one change management project and a defect work item from a different change management project would be stored in the same defect table.

For example, a defect record from IBM ClearQuest® is stored in the same defect table as the change management defect work items.

Operational data is used in reports that look for detailed information.

The ODS data collection extract data from the applications, and load it into the operational data store of the data warehouse.

The data that is loaded from the applications into the data warehouse is formatted into tables. Each table has a set of attributes, and new data is loaded into rows under those attributes.

The relationship between the data that is collected from the application and the location where it is stored in the data warehouse ODS is documented in the application data dictionaries. You can use these dictionaries to determine what values to include in a report definition or document template.

Metrics tables

The data warehouse has multiple metrics tables. The data in each metrics table is loaded from the operational data in the ODS during the data mart data collection process, also known as the Star ETL process.
Figure 2. Data in the operational data store is transformed into star schemas in the metrics tables.
The operational data store contributes data to a set of metrics tables, each of which consists of a single fact table and multiple dimensions.
The data in each metrics table is organized as a star schema, which consists of a centralized fact table and related dimensions. The fact table contains numeric, or aggregated, data. Dimensions are linked to the fact in a star pattern that reflects dimensions, or attributes, of a business.
Figure 3. Fact tables and dimensions are stored in a star schema
A metrics table is also called a "star schema". The fact table contains numeric, or aggregated, data. Dimensions are linked to the fact in a star pattern that reflects dimensions or attributes of a business.

For example, the Defect Arrivals fact table contains numbers that specify how many defects arrived for each combination of Dimensions. To determine how many Critical defects arrived on a specific date, you sum the numbers from the fact table where the defect Severity dimension is "Critical" and the Creation Date dimension is the required date.

Each metrics table’s star schema is populated by the Data-mart data collection.

Metrics table data is used in reports that show aggregated data or trends. Relationships are built between the metrics tables and the operational data area to facilitate drill-through from measures in metrics tables into corresponding individual data in the operational data area. For example, a metrics table might have a measurement of six high-priority open defects for customer X. A relationship links the measured count of six to the six individual defect records in the operational data area.