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.

Sections in this topic:

Links to the CLM data dictionaries
Using the data dictionaries to author Cognos-based reports
Using the data dictionaries to author document-style reports
Explanation of columns in the data dictionaries

Links to the CLM data dictionaries

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.

Differences between data model terms and CLM application terms: 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.

Differences between REST API terms and CLM terms: 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.