PPM data transfer during ETL
The following sections describe how OpenText PPM data is transferred during the ETL process.
Common dimension data transfer
The COMMON ETL job transfers all OpenText PPM data (that fall within the interval defined by the specified start and end dates) and that are shared by Time Management, Resource Management, and Financial Management into the Operational Reporting database schema.
Demand Management data transfer
A full ETL for Demand Management(DM) transfers all project data (that fall within the interval defined by the specified start and end dates) from the Demand Management tables into the Operational Reporting database schema. A subsequent incremental DM ETL job loads Demand Management data that have changed since the last DM ETL job run.
Portfolio Management data transfer
A full ETL for Portfolio Management (PfM) transfers all project data (that fall within the interval defined by the specified start and end dates) from the Portfolio Management tables into the Operational Reporting database schema. A subsequent incremental PfM ETL job loads Portfolio Management data that have changed since the last PfM ETL job run.
Project Management data transfer
A full ETL for Project Management (PM) transfers all project data (that fall within the interval defined by the specified start and end dates) from the Project Management tables into the Operational Reporting database schema. A subsequent incremental PM ETL job loads Project Management data that have changed since the last PM ETL job run.
Time Management data transfer
A full ETL for Time Management (TM) transfers all time-sheet data (that fall within the interval defined by the specified start and end dates) from the Time Management tables into the Operational Reporting database schema. A subsequent incremental TM ETL job loads Time Management data that have changed since the last TM ETL job run.
Resource Management data transfer
A full ETL for Resource Management (RM) transfers all resource capacity, demand, and actual effort data (that fall within the interval defined by the specified start and end dates) from the Resource Management tables into the Operational Reporting database schema. A subsequent incremental RM ETL job loads Resource Management data that have changed since the last RM ETL job run.
Financial Management data transfer
The FM incremental ETL job transfers all Financial Management data (that fall within the interval defined by the specified start and end dates) from the Financial Management tables into the Operational Reporting database schema. A subsequent incremental FM ETL job loads Financial Management data changed since the last FM ETL job run.
Compare table discrepancies
You can compare the discrepancies in specific tables between the PPM and the Operational Reporting database schemas after a specific date.
To compare table discrepancies:
-
Navigate to the <Op_Report_Home>\install directory.
-
Open the table_compare.bat (or table_compare.sh) file in a text editor, and then replace the parameter placeholders with valid values, as described in the following table.
Parameter
Description
RPT_SCHEMA_NAME
Operational Reporting database schema name
RPT_TNS_NAME
Operational Reporting database
TNS namePPM_DBLINK_NAME
Name of the link to the PPM database
You retrieve the PPM_DB_LINK_NAME using this SQL within the reporting schema:
select parameter_value from rpt_params where parameter_name ='PPM_DB_LINK_NAME'
LAST_UPDATE_DATE Specify the date from which to compare the tables. TABLE_NAMES Specify the tables for comparison. Separate multiple tables with commas.
-
Run the table_compare.bat (or table_compare.sh) script.
-
The compare results are stored in the table_comparison_result_report.txtfile in the <Op_Report_Home>\DB\install\compare directory.