Data dictionaries for reporting

Report authors can access the Rational® solution for Collaborative Lifecycle Management (CLM) data through the data warehouse or access live data from the applications using reportable REST APIs. The application-based data dictionaries in this section show how elements map to artifacts in CLM.

CLM includes a data warehouse, to which each of the CLM applications contributes data. The data dictionary topics in this section detail the relationship between application-specific data (physical data related to artifacts and other elements in CLM applications) and the related queries that need to be incorporated into a report to get the required application data in the report output. When authoring reports, you can reference these mappings to determine which queries to use.

Using the data dictionaries to author Cognos-based reports

The data in the data warehouse can be incorporated into Cognos-based reports using a Framework Manager (FM) model, which is an intermediary between the physical schema in the data warehouse and the report. In the Cognos reporting tools, you add queries on elements of the data model, and those queries return information based on the data that is stored in the data warehouse.
CLM and Rational Insight share the same data warehouse and Framework Manager (FM) model, which is why the FM model contains many terms that are not specific to CLM artifacts. The model needs to be general enough that other products can contribute data into the same model.

For example, work items from the Change and Configuration management (CCM) application are stored in the Request table in the data warehouse along with change requests from Rational ClearQuest®. This design enables the creation of reports from both products, and any other integrated products.

The following tables demonstrate some common examples of mismatches between the names of elements in the application interfaces, names of the REST API resource, and the names of the data warehouse tables and metadata model queries that they map to.

Table 1. Example: Quality management mismatches
Quality management UI element REST API resource Data model subject
Test Schedule TestPhase Iteration
Test Execution record ExecutionWorkItem Execution work item
Test Environment Configuration Configuration
Table 2. Example: Change management mismatches
Change management UI element REST API resource Data model subject
Contributor contributor Resource
Work item workItem Request

The data dictionaries listed in the first section of this topic demonstrate detailed mappings for all of the CLM application data that is collected and stored in the data warehouse operational data store (ODS). There are many cases where data warehouse tables and columns do not contain CLM application data because they are used for other purposes.

Using the data dictionaries to author document-style reports

Live data from the applications can be incorporated into document-style reports in IBM® Rational Publishing Engine by using a reportable REST API, which serves as an intermediary between the application data and the live report.

Use the data dictionaries for reference and use the information on Creating document-style reports and Customizing document-style reports, as well as the REST API documentation on Jazz.net, to help you determine how to build the reports.

The REST API is used to retrieve application information when reports and data collection jobs are run. The API is designed to work with a broader context than just CLM, so some of the terms that are used in the REST API schema are not specific to CLM.

For example, work items from the Change and Configuration management (CCM) application can be retrieved by a report that is also retrieving data onchange requests from Rational ClearQuest.

Explanation of columns in the data dictionaries

Each column in the data dictionary is useful depending on what you are trying to accomplish. For example:
  • If you are authoring a live report, use the information in the UI Element and UI Field columns to find the data that you want to report on, then use the REST API Resource and REST API Property path to determine the resource URL that you must add to the report definition or document template.
  • If you are authoring a data warehouse report, reference the information in the UI Element and UI Field columns to find the data that you want to report on, and then use the FM Model Path, FM Model Query Subject, and FM Model Query Item to determine which queries to add to your report definition and where to find them in the Cognos tools.
In the full dictionary, the columns flow from left to right in a similar way that the data flows in the data flow process:
  • Input or automatic creation of application data to
  • Output from REST API to
  • XML data configuration (XML to JDBC) mapping to
  • Data warehouse operational data store (ODS) mapping to
  • Metadata model (FM Model) representation, as seen in Report Studio (Cognos tools) to
  • Report output.
Table 3. Application data: UI element and field
Column name Description
UI Element The application artifact. Unless otherwise specified in the dictionary, UI elements map directly to a REST API resource; a data warehouse table; and an FM Model Query Subject.

For example, CCM Work items are expressed by the REST API Resource URL as Request; are stored in the data warehouse REQUEST table; and can be added to report definitions using the REQUEST Query Subject and related Items.

UI Field An attribute of the UI element, often a visible field in the element. The values can be entered by users or specified automatically by the process based on various factors. Not all rows have a specified UI Field because some artifacts are automatically generated without user input.

For example, a user creates a test plan and names it Test plan 1. The name Test plan 1 is user-specified in the Name field and stored in the data warehouse in the TESTPLAN table, NAME column. The creation date is not a field that the user can input a value for, but a create date is automatically assigned when the test plan is created and that information is stored in the data warehouse in the TESTPLAN table, CREATION_DATE column.

Table 4. Output from REST API
Column name Description
REST API Resource The value provided in the REST API Resource URL. REST API Resources often correspond directly to UI elements, but the names of resources do not always correspond to the names of the elements.
REST API Property Path Path used in the resource REST API Resource URL to specify a property to retrieve data for.
Table 5. XML data configuration (XML to JDBC) mapping
Column name Description
XDC Resource In the Data Mapping tool, the resource that you must find to locate the mapping table.
XDC Data Mapping Table In the Data Mapping tool, within the XDC resource, the name of the data mapping table that you must navigate to in order to find the corresponding information or the column name.
XDC Column Name In the Data Mapping tool, within the XDC resource and the Data Mapping Table, the name of the column where the corresponding data is mapped to.
Table 6. Data warehouse operational data store (ODS) mapping
Column name Description
Data Warehouse Schema The signifier of the data warehouse database where the information is stored. This is expressed as a variable because the database can be anything you specify during data warehouse configuration.
Data Warehouse Table The table where data is stored for the corresponding application information. Tables often correspond directly to UI elements, but the table names do not always correspond to element names. Tables and their names often correspond directly to FM model query subjects.
Data Warehouse Column The column within a table in the data warehouse where data is stored for the corresponding application information.
Table 7. Metadata model representation, as seen in Report Studio
Column name Description
FM Model Path The path in the FM model, as depicted in the Report Studio user interface, where you can find the query subject and query item.
FM Model Query Subject FM model query subjects often correspond directly to UI elements, but the names of query subjects do not always correspond to the names of the elements. FM model query subjects often correspond directly to tables in the data warehouse and the names usually correspond as well.
FM Model Query Item The item, within a query subject, in the FM model that calls corresponding application data.
Table 8. Description of mapping
Column name Description
Description Details about the mapping identified in the row. The description often explains what you can expect to see in the output when the data in this row is included in a report.

video icon Watch videos

CLM playlist
Jazz.net channel
User Education channel

learn icon Learn more

CLM learning circle
Agile learning circle
Learning circles

ask icon Ask questions

Jazz.net forum
developerWorks forums

support icon Get support

Support Portal
Deployment wiki
Support blog