Implementation of data mastership through the XML data configuration files
When defining the mappings in XML data configuration files, you query information from the XML ODBC driver. The system table System.DIMENSIONMAPPINGS can be used to query information about available dimension mapping tables. All dimension mapping tables are under the schema DimensionMappings. All tables have the same structure, that is, a source_value and a target_value. They can be used to query the value mappings.
Implementation of data mastership in the ETL process
In the data warehouse artifacts for the common dimension, only those artifacts that behave as the master (target of the mapping) or those artifacts that are not mapped to any other artifacts appear in the operational data store. Other artifacts that are associated with the common dimension are linked to the target artifact of the original artifact directly. For example, if a UCM Project RI is mapped to a ClearQuest project Insight, only the ClearQuest project Insight appears in the PROJECT table, and the UCM activities belong to the UCM project RI will be directly linked to the ClearQuest project Insight in the data warehouse. For each of the common dimensions, including PROJECT, RELEASE, ITERATION, PRODUCT, COMPONENT and RESOURCE, staging mapping tables with M_ prefixed to their names are created as tables used in ETL to store the mapping relationship.
Because the logic is generic, reusable ETL builds and jobs are created under the Others/DimensionMapping folder. You need to change the data source in the build to the data source where you defined the mappings, change the connection name in Connection variable defined in the job to the connection name of the data source where you defined the mappings, and add the job to your job.
When loading the source artifacts, the build needs to look up the staging mapping table for this common dimension to see if the specific artifact appears in the staging mapping table as the mapping source. If the lookup returns matched results, the record in the staging mapping table is updated. If the lookup does not return matched results, the artifact is delivered normally to the operational data store. For each common dimension, a lookup with _Source in the name has been created to search source data in the staging mapping table.
The lookup dimension used in the transformation model to search the common dimension searches both the operational data store table and the staging mapping table. For data from the operational data store table, the lookup returns the surrogate key of the record. For data from the staging mapping table, the lookup returns the surrogate key of the target artifacts. For the common dimensions, the usually used lookups to search data with EXTERNAL_KEY1 and EXTERNAL_KEY2 are used.
Data mastership involves multiple data sources. Therefore, the ETL job must reflect the overall data flow. The above implementation for data mastership is specific to the ETL process and has no impact on other components. In the sample catalog, a TestManager project is mapped to a ClearQuest ALM project, and TestManager users to ClearQuest ALM users with the same name. If you see errors when opening the XML data configuration file for TestManager ETL, update the dimension mapping tables to point to the XML data configuration file used for ClearQuest ETL in your system.