Share this page

# (Optional) Force a full ETL

Automated incremental ETL (extract, transform, and load) jobs run regularly to update the data from the PPM system to the Operational Reporting database schema. In case of instability or frequent errors during incremental ELT runs, you can force a full ETL to update the data.

To force a full ETL:

1. Create an empty reporting schema and grant necessary privileges to it:

   - In RDS environments, run the sample_setup_rds_reporting_sys.sh script:

     1. Navigate to the <Op_Report_Home>\install\sample directory, and open the sample_setup_rds_reporting_sys.sh file in a text editor.

     2. Uncomment the parameters listed in the following table, replace the placeholders with valid values, and then save and close the file.

        Parameter | Value
        --- | ---
        RDS admin user of Reporting DB | RDS admin user name for the Operational Reporting database. This refers to the master username of the RDS DB instance. Example value: admin
        Reporting DB Schema Name | Operational Reporting database schema name. Example value: RPT_SCHEMA Note: The PPM Center database schema name must be in all capital letters.
        Reporting DB data_tablespace_name | Operational Reporting database to store data. Example value: RPT_DATA_TS Note: The Operational Reporting database tablespace name must be in all capital letters.
        Reporting DB temp_tablespace_name | Operational Reporting database temp tablespace. Example value: RPT_TEMP_TS Note: The Operational Reporting database temp tablespace name must be in all capital letters.
        Reporting DB index_tablespace_name | Operational Reporting database to store index. Example value: RPT_INDEX_TS Note: The Operational Reporting database index tablespace name must be in all capital letters.
        Reporting DB TNS Name | Identifies the Oracle instance that runs the Operational Reporting database schema. TNS name is configured in the tnsnames.ora file. Example value: RPT
        Reporting DB DATA_NOLOGGING_TABLESPACE | Separate tablespace that requires no redo log for the Operational Reporting database to store data. Example value: RPT_DATA_TS_NL
        Reporting DB INDEX_NOLOGGING_TABLESPACE | Separate tablespace that requires no redo log for the Operational Reporting database to store indexes. Example value: RPT_INDEX_TS_NL

     3. Run the sample_setup_rds_reporting_sys.sh script.

     4. During the script run, provide the RDS admin user password when prompted.

        Tip: The RDS admin user password is the master password of the RDS DB instance. For this password, do not use the automatically generated password. Otherwise, the installation of the Operational Reporting would fail.

     5. Navigate to the <Op_Report_Home>\install\log directory and check the setup_rds_reporting_sys.log file for errors.

        Note: Running the sample_setup_rds_reporting_sys.sh script drops the old reporting schema and creates a new reporting schema. The new reporting schema is reusable, therefore, you do not need to run this script if you want to re-install the Operational Reporting package.

   - In non-RDS environments, run the sample_setup_reporting_sys.sh script.

2. Run the sample_setup_cdc.sh script:

   1. Navigate to the <Op_Report_Home>\install\sample directory, and open the sample_setup_cdc.sh file in a text editor.

   2. Uncomment the parameters listed in the following table, replace the placeholders with valid values, and then save and close the file.

      Parameter | Value
      --- | ---
      PPM DB Schema Name | This value should exist in the Oracle tnsnames.ora entry. Example value: PPM_SCHEMA Important: The PPM Center database schema name must be in all capital letters.
      PPM DB data_tablespace_name | PPM Center database tablespace name. This refers to the existing data tablespace in PPM Center database schema. PPM Center schema stores this in the KINS_TABLESPACES table. Example value: PPM_DATA_TS Important: PPM Center database data tablespace name must be in all capital letters.
      PPM DB temp_tablespace_name | PPM Center database temp tablespace name. This refers to the existing temp tablespace in PPM Center database schema. PPM Center schema stores this in the KINS_TABLESPACES table. Example value: PPM_TEMP_TS Important: PPM Center database temp tablespace name must be in all capital letters.
      PPM DB index_tablespace_name | PPM Center database index tablespace name. This refers to the existing index tablespace in PPM Center database schema. PPM Center schema stores this in the KINS_TABLESPACES table. Example value: PPM_INDEX_TS Important: PPM Center database index tablespace name must be in all capital letters.
      Full tnasame.ora entry to PPM schema | Full tnsnames.ora entry for the PPM database schema. For HOST, specify the IP address of the PPM database server. For PORT, specify the PPM database port. For SERVICE_NAME, specify the SID in the tnsnames.ora file for the PPM database. Example value: `"(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=16.89.27.63) (PORT=1522)) (CONNECT_DATA=(SERVER=dedicated) (SERVICE_NAME=MDB1106A)))"`

   3. Run the sample_setup_cdc.sh scrip.

3. Run the sample_setup_all.sh script:

   1. Navigate to the <Op_Report_Home>/install/sample directory, and open the sample_setup_all.sh file in a text editor.

      Note: Make sure that you make the file executable.
      For example: chmod +x sample_setup_all.sh

   2. Uncomment the parameters listed in the following table, replace the placeholders with valid values, and then save and close the file.

      Parameter | Description
      --- | ---
      Reporting DB Schema Name | Operational Reporting database schema name. Example value: RPT_SCHEMA The Operational Reporting database schema name must be in all capital letters.
      Reporting DB data_tablespace_name | Name of the data tablespace for the Operational Reporting database. Example value: RPT_DATA_TS The Operational Reporting database data tablespace name must be in all capital letters.
      Reporting DB temp_tablespace_name | Name of the temp tablespace for the Operational Reporting database. Example value: RPT_TEMP_TS The Operational Reporting database temp tablespace name must be in all capital letters.
      Reporting DB index_tablespace_name | Name of the index tablespace for the Operational Reporting database. Example value: RPT_INDEX_TS The Operational Reporting database index tablespace name must be in all capital letters.
      Reporting DB TNS Name | Identifies the Oracle instance that runs the Operational Reporting database schema. TNS name is configured in the tnsnames.ora file. Example value: RPT
      PPM DB Schema Name | OpenText PPM database schema name. This value should exist in the Oracle `tnsnames.ora` entry. Example value: PPM_SCHEMA The OpenText PPM database schema name must be in all capital letters.
      PPM DB data_tablespace_name | OpenText PPM database data tablespace name. This refers to the existing data tablespace in the OpenText PPM database schema. The OpenText PPM schema stores this in KINS_TABLESPACES table. Example value: PPM_DATA_TS Important: The OpenText PPM database data tablespace name must be in all capital letters.
      PPM DB temp_tablespace_name | OpenText PPM database temp tablespace name. This refers to the existing temp tablespace in the OpenText PPM database schema. The OpenText PPM schema stores this in KINS_TABLESPACES table. Example value: PPM_TEMP_TS The OpenText PPM database temp tablespace name must be in all capital letters.
      PPM DB index_tablespace_name | OpenText PPM database index tablespace name. This refers to the existing index tablespace in the OpenText PPM database schema. The OpenText PPM schema stores this in KINS_TABLESPACES table. Example value: PPM_INDEX_TS The OpenText PPM database index tablespace name must be in all capital letters.
      Full tnsnames.ora entry to PPM schema | Full tnsnames.ora entry for the OpenText PPM database schema. For HOST, specify the IP address of the OpenText PPM database server. For PORT, specify the OpenText PPM database port. For SERVICE_NAME, specify the SID in the tnsnames.ora file for the OpenText PPM database Example value: `"(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=16.89.27.63) (PORT=1522)) (CONNECT_DATA= (SERVER=dedicated) (SERVICE_NAME=MDB1106A)))"`
      DB_LINK_NAME to PPM | Name of the link to the OpenText PPM database. This value is generated in the Operational Reporting database schema. Example value: PPM_DB_LINK The name of the link to the OpenText PPM database must be in all capital letters.
      Reporting DB DATA_NOLOGGING_TABLESPACE | Separate tablespace that requires no redo log for the Operational Reporting database to store data. Example value: PPM_DATA_TS_NL
      Reporting DB INDEX_NOLOGGING_TABLESPACE | Separate tablespace that requires no redo log for the Operational Reporting database to store indexes. Example value: RPT_INDEX_TS_NL
      INSTALLATION_TYPE | What is used to install the Content Pack, only Oracle GoldenGate is supported. Default value: OGG If you use others than Oracle GoldenGate, set the value to NULL.

   3. Run the sample_setup_all.sh script.

   4. During the script run, provide the following passwords when prompted:

      - OpenText PPM database server schema password

      - Operational Reporting database server schema password

   5. The script performs a sanity check on PPM database:

      - If the sanity check fails, an error message pops up. You must fix the errors following the suggestions in the error message.

      - If the sanity check passes, continue with step f.

   6. The script run creates a log file in the <Op_Report_Home>/install/log directory. Check the setup_all.log file to make sure that no errors occurred. If the setup_all.log file indicates that compilation errors occurred, run the following command:

      `Select * from user_objects where status = 'INVALID'`

      If no rows are returned, you can safely ignore the warning.

4. Run the sample_load_data.sh script to import the OpenText PPM data into the Operational Reporting database.

   1. Navigate to the <Op_Report_Home>/install/sample directory, and open the sample_load_data.sh file in a text editor.

   2. Uncomment the parameters listed in the following table, replace the placeholders with valid values, and then save and close the file.

      Parameter | Description
      --- | ---
      Reporting DB Schema Name | Operational Reporting database schema name. Example value: RPT_SCHEMA The Operational Reporting database schema name must be in all capital letters.
      Reporting DB TNS Name | Identifies the Oracle instance running the Operational Reporting database schema. the TNS name is configured in the tnsnames.ora file. Example value: RPT
      Reporting DB index_tablespace_name | Name of the index tablespace for the Operational Reporting database. Example value: RPT_INDEX_TS The Operational Reporting database index tablespace name must be in all capital letters.
      DB_LINK_NAME to PPM | Name of the link to the PPM Center database. This link is created automatically during the setup_all script run. Example value: PPM_DB_LINK
      ETL start date (mm-dd-yyyy) | Start date (in mm-dd-yyyy format) for the PPM Center data to load into the Operational Reporting database schema. Example value: 01/01/2009
      ETL end date (mm-dd-yyyy) | End date (in mm-dd-yyyy format) for the PPM Center data to load into the Operational Reporting database schema. Example value: 01/01/2011 Note: The ETL end date you specify is converted based on the fiscal year. For details, see the Installation and Administration Guide.
      Reporting DB data_tablespace_name | Name of the data tablespace for the Operational Reporting database. Example value: RPT_DATA_TS The Operational Reporting database data tablespace name must be in all capital letters.
      Request dimension ETL start date (mm-dd-yyyy) | Start date (in mm-dd-yyyy format) for the PPM Center request data to load into the Operational Reporting database schema. Example value: 01/01/2009 Note: If your PPM Center database contains data for old, but active requests, you can include that data without importing all data from that time period.
      Reporting DB DATA_NOLOGGING_TABLESPACE | Separate tablespace that requires no redo log for the Operational Reporting database to store data. Example value: PPM_DATA_TS_NL
      Reporting DB INDEX_NOLOGGING_TABLESPACE | Separate tablespace that requires no redo log for the Operational Reporting database to store indexes. Example value: PPM_INDEX_TS_NL
      FORCE FULL ETL | Must remain set to its default value (FALSE).

   3. Run the sample_load_data.sh script.

   4. During the load script run, provide Operational Reporting database schema password and the Operational Reporting admin user password, as prompted.

      Tip: The Operational Reporting admin user password is the master password of the RDS DB instance. For this password, do not use the automatically generated password. Otherwise, the installation of the Operational Reporting would fail.

   5. The script creates a load_data.log file in the <Op_Report_Home>/install/log directory. Check the log file to make sure that no errors occurred.

      Note: You can run the sample_load_data.sh only once. If you discover any incorrect data, you have to re-install the Operational Reporting package. To re-install the package, you start from step 2 and then run the load script.

5. Force a full ETL by running the sample_full_etl_all.sh script.

   Tip: You can run this script periodically to ensure that any changes in PPM data are synched to the reporting schema.

   1. Navigate to the <Op_Report_Home>/install/sample directory, and open the sample_full_etl_all.sh file in a text editor.

   2. Replace the parameter placeholders with valid values and then save and close the file.

      The following parameters are unique to this script, other parameters are the same as those in sample_setup_all.sh and sample_load_data.sh. For descriptions of these parameters, see sample_setup_all.sh parameters and sample_load_data.sh parameters.

      Parameter | Description
      --- | ---
      RPT_SCHEMA_PWD | Operational Reporting database schema password.
      PPM_SCHEMA_PWD | PPM Center database schema password.
      FORCE FULL ETL | Must be set to TRUE.

   3. Run the sample_full_etl_all.sh script.

https://admhelp.microfocus.com PPM 8942
Tips to improve your search results... OpenText™ Project and Portfolio Management Help Center PPM Help 26.2-26.3 All View Local Help Center View Online Help Center Version: