Oracle® Healthcare Foundation

Executing Terminology Loader ETLs Using the Scheduler

You can schedule ETLs on any enterprise scheduler to match the frequency (daily, weekly, and so on) of the source data acquisition.

To schedule ETLs:

1. Connect to the Terminology Loaders Work Repository, and select Designer.

2. Expand EHA_HLI_Integration project > Global > Packages > MASTER_EXECUTION_PLAN > Scenarios

3. Expand the desired scenario.

4. Right-click Scheduling, and select New Scheduling to build the schedule.

Executing Terminology Loader ETLs Using the Informatica Workflow Manager

Update Global Settings

Review and update the following configurations in the HDI_ETL_GLBL_PARAM_G table of the HMC schema:

Configure Rules and Terminology

• Review and enable or disable the default data validation rules in the HDI_ETL_RL_G table of the HMC schema.

• Review the seeded terminology standardization configurations in the HDI_ATRB_CD_SYS_LKUP_G and HDM_ATRB_CD_SYS_LKUP_G tables in the HDI and HDM schemas respectively.

  You must disable the seeded configurations if the terminology standardization feature is not used or the seeded configurations are not relevant.

• Review the seeded terminology validation configurations in the HDI_ATRB_CD_TYP_LKUP_G and HDM_ATRB_CD_TYP_LKUP_G tables in the HDI and HDM schemas respectively.

  You must disable the seeded configurations if the terminology validation feature is not used or the seeded configurations are not relevant.

• (Optional) Configure the late arriving data rules in the HDI_RL_REFRNTL_INTGRTY_G table in the HMC schema.

For more information, see Oracle Healthcare Foundation Administrator's Guide.

Schedule Load Plans

A load plan is a group of related workflows. The load plans created in Warehouse Integration Loader consists of multiple command tasks grouped together as per the dependencies, which executes the workflows. You can modify the load plans to disable, delete, insert, or modify the command task. When you modify the load plan, make sure you delete or add dependencies between workflows.

You can schedule ETLs on any enterprise scheduler to match the frequency (daily, weekly, and so on) of the source data acquisition.

Before executing or scheduling the load plan:

• Create the following environment variables, and associate them with the integration service.

  • PMUSER: Repository user who executes the workflow.

  • PMPASS: Encrypted repository user password.

  Restart the integration service for the variables to take effect.

• Assign the integration service to load plans.

You can schedule any of the load plans as per your execution frequency.

Setting Up the Environment

1. Drop the foreign keys according to the guidelines in the Oracle Healthcare Foundation Administrator's Guide.

2. Make sure that the HDI data load is complete.

3. Make sure that the source data does not contain multiple versions of the same record. The initial load ETLs cannot handle multiple versions.

4. Process any additional rules and configurations, and load the master data tables to the HDM tables.

Executing ETLs

When you use an enterprise scheduler, ETLs start automatically at the scheduled time or after the HDI data load is complete. You can monitor the progress of ETLs through the PowerCenter Workflow Monitor.

Restarting Failed ETLs

When an ETL fails during the data loading process:

1. Clean up the partially loaded data manually.

   • When a target table (for example, HDM_ENC) is loaded by a single source table (for example, HDI_ENC), use the following query to clean up the partially loaded HDM data.

     For example,

     DELETE FROM HDM_ENC WHERE REQUEST_ID= (SELECT REQUEST_ID FROM HMC.HDI_ETL_LOAD_DT_G WHERE WRK_FLOW_NM='wf_SIL_DI_HDI_ENC');

     COMMIT;

   • When a target table (for example, HDM_PRTY) is loaded by multiple sources (for example, HDI_PT, HDI_SVCPRV, and so on), delete the partially loaded data of the failed ETL. Assuming that the HDI_PT ETL has processed the data to the HDM tables but the HDI_SVCPRV ETL failed while processing data to HDM_PRTY, delete the partially data loaded by the HDI_SVCPRV ETL.

     For example,

     DELETE FROM HDM_PRTY WHERE INTEGRATION_ID LIKE 'SVCRPV%' AND REQUEST_ID= (SELECT REQUEST_ID FROM HMC.HDI_ETL_LOAD_DT_G WHERE WRK_FLOW_NM='wf_SIL_DI_HDI_SVCPRV');

     COMMIT;

2. Restart the ETL.

   Each workflow consists of following structure:

   s_SIL_DI_Create_Rule_Param_File [Start]
                      3 tasks to capture and update load summary details
   s_SIL_DI_<HDI table>_SQLVALID
                      3 tasks to capture and update load summary details
   s_SIL_DI_<HDI Table>_<HDM Table> [Master session]
   -> Point 1
                      3 tasks to capture and update load summary details
   s_SIL_DI_<HDI Table>_<HDM Table> [Child session 1]
   -> Point 2
                      3 tasks to capture and update load summary details
           s_SIL_DI_<HDI Table>_<HDM Table> [Child session 2]
   ->      Point 3
                              3 tasks to capture and update load summary details
   s_SIL_DI_HDI_ETL_LOAD_DT_G [Load Date]
   ->      Point 4
                      3 tasks to capture and update load summary details
   s_SIL_DI_HDI_ETL_EXCPTN_LOG_TMP [Exception]
   -> Point 5 [End]
                      3 tasks to capture and update load summary details
   

Name: s_enc_hdm_enc_stat.prm
[Global]
$$workflow_name=wf_SIL_DI_HDI_ENC
$$session_name=s_SIL_DI_HDI_ENC_HDM_ENC
$$session_status=SUCCEEDED
$$source_success_count=15
$$target_success_count=12

• , Create a parameter file under the $PMSourceFileDir/$PMFolderName/ directory, and restart the workflow from the subsequent session that updates the load summary, or directly update the job details in the JB_EXCTN table.

  The following is the sample parameter file (when a_enc_hdm_enc or c_enc_hdm_enc fails):

  Name: s_enc_hdm_enc_stat.prm
  [Global]
  $$workflow_name=wf_SIL_DI_HDI_ENC
  $$session_name=s_SIL_DI_HDI_ENC_HDM_ENC
  $$session_status=SUCCEEDED
  $$source_success_count=15
  $$target_success_count=12
  

  where, $$source_success_count and $$target_success_count are the sum of records across all source and target instances, and read from Workflow Monitor for the session under consideration.

For multiple sessions at the same level, restart the ETL from the level task from where the multiple sessions originate.

Setting Up the Environment

• For the first incremental load, create the foreign key indexes on the HDM table according to guidelines in the Oracle Healthcare Foundation Administrator's Guide.

• Schedule an automatic statistics gathering for the HDM and HDI schema using the stale option.

• Make sure that the HDI data load is complete.

• Process necessary rules and configurations, and load the master data tables to the HDM tables.

Running ETLs

When you use an enterprise scheduler, ETLs start automatically at the scheduled time or after the HDI data load is complete. You can monitor the progress of ETLs through the PowerCenter Workflow Monitor.

Restarting Failed ETLs

1. Correct the partially loaded data.

   When an incremental ETL fails, correct the partially loaded data in the HDM schema before restarting the ETL. The Warehouse Integration Loaders provide a common ETL, wf_m_SIL_DI_Restartability, which corrects the HDM data. Before running this ETL, configure the failed session in the HDI_ETL_GLBL_PARAM_G table under the parameter RESTART_SESSION.

   Sample Update Command:

   UPDATE <HMC SCHEMA NAME>.HDI_ETL_GLBL_PARAM_G SET PARAM_ VAL ='<Failed Session Name>'WHERE PARAM_NM='RESTART_SESSION';

   COMMIT;

   When multiple sessions fail, correct the data of each failed HDM table by running the wf_m_SIL_DI_Restartability ETL for each failed session.

   Only master and child sessions loads data to the HDM tables, and other sessions are used for tracking date, processing rules, handling exceptions, and so on.

2. Restart the ETL

   Each workflow consists of following structure:

   s_SIL_DI_Create_Rule_Param_File [Start]
                      3 tasks to capture and update load summary details
   s_SIL_DI_<HDI table>_SQLVALID
                      3 tasks to capture and update load summary details
   s_SIL_DI_<HDI Table>_<HDM Table> [Master session]
   -> Point 1
                   3 tasks to capture and update load summary details
   s_SIL_DI_<HDI Table>_<HDM Table> [Child session 1]
   ->      Point 2
                      3 tasks to capture and update load summary details
   s_SIL_DI_<HDI Table>_<HDM Table> [Child session 2]
   -> Point 3
                   3 tasks to capture and update load summary details
   s_SIL_DI_HDI_ETL_LOAD_DT_G [Load Date]
   -> Point 4
                   3 tasks to capture and update load summary details
   s_SIL_DI_HDI_ETL_EXCPTN_LOG_TMP [Exception]
   -> Point 5 [End]
                      3 tasks to capture and update load summary details
   

   When the ETL fails:

   • Between Exception and End: Restart the workflow from the Exception.

     For multiple sessions at the same level, restart the ETL from the level task from where the multiple sessions originate.

Load Summary

The Warehouse Integration Loaders capture the ETL execution summary in the JB table of the enterprise schema and the EXCPTN table.

The execution statistics includes component (Warehouse Integration Loaders and Application Toolkit), job group name (package name), job name (interface name), job type (initial or incremental), job start date, job end data, job status (running, succeeded, or failed), source record count, and target record count.

The exception statistics includes exception type (reject, reprocess, or suspend) and the exception count.

ETL Exceptions

The ETL logs any record that fails for mandatory rules, data validation rules, late arriving data rules, or data quality rules during the data loading process, in the HDI_ETL_EXCPTN_LOG_TMP table in the HMC schema.

Any reprocessing rules, the records that can be reprocessed in the subsequent loads are retained in the HDI_ETL_EXCPTN_LOG_TMP table, and all other exceptions are moved to the HDI_ETL_EXCPTN_LOG_G table.

Setting Up the Environment

• Drop the foreign keys according to guidelines in the Oracle Healthcare Foundation Administrator's Guide.

• Make sure that the HDM data load is complete.

• Make sure that the selection or inline code configurations are valid in the HCD_ETL_ENTY_SELCTN_PARAM_G entity.

Running ETLs

When you use an enterprise scheduler, ETLs start automatically at the scheduled time or after the HDM data load is complete. You can monitor the progress of ETLs through the PowerCenter Workflow Monitor.

Restarting failed ETLs

When an ETL fails during the data loading process, rerun the workflow from the failed session.

Setting Up the Environment

1. For the first incremental load, create the foreign key indexes on the HDM table according to guidelines in the Oracle Healthcare Foundation Administrator's Guide.

2. Schedule an automatic statistics gathering for the HDM and HCD schema using the stale option.

3. Make sure that the HDM data load is complete.

Executing ETLs

When you use an enterprise scheduler, ETLs start automatically at the scheduled time or after the HDM data load is complete. You can monitor the progress of ETLs through the PowerCenter Workflow Monitor.

Restarting Failed ETLs

When an ETL fails during the data loading process, rerun the workflow from the failed session.

Setting Up the Environment

Make sure that the HDM data load is complete.

Executing ETLs

When you use an enterprise scheduler, ETLs start automatically at the scheduled time or after the HDM data load is complete. You can monitor the progress of ETLs through the PowerCenter Workflow Monitor.

Setting Up the Environment

Ensure that the HDM data load is complete.

Executing ETLs

When you use an enterprise scheduler, ETLs start automatically at the scheduled time or after the HDM data load is complete. You can monitor the progress of ETLs through the PowerCenter Workflow Monitor.

Restarting Failed ETLs

When an ETL fails during the data loading process, rerun the workflow from the failed job.