The Unifier ETL Fails With No Error in the Log - The Last Step to Complete is 'Complete update on W_XLAT_S' - ODI Error The number of child sessions in error state is greater or equal to the MAX_CHILD_ERROR parameter (Doc ID 2802397.1)

Last updated on MARCH 29, 2022

Applies to:

Symptoms

When attempting to execute the Unifier ETL, the status will show as failed with no obvious error captured in the log.

The log will look similar to the following.

Thu Aug 26 08:30:49 UTC 2021.... Starting loadPlan LOAD_PLAN_DAILY
Load plan [LOAD_PLAN_DAILY] started, instance id = xxxx
[RUNNING] Analytics Daily
  [DONE] STATUS_DAILY_RUN_START
  [DONE] Status daily run start, duration = 2 seconds
  [RUNNING] Case Data Source Type
  rootStep_caseWhen
  Load dimension and fact tables
  Case SCD Enabled
  Case Act PPA Enabled
  Case Res PPA Enabled
  [RUNNING] Pre-ETL Tasks
  Case EVM Enabled
  Pre-ETL Tasks for Textura
  Case ACX EVM Enabled
  Case OBIEE Cache Purge Daily
Complete insert on ETL_PROJECTLIST
Complete populate W_DAY_D
  [DONE] PROC_POPULATE_GLOBAL_DATE_DIMENSION
  [DONE] Populate Global Date Dimension, duration = 5 seconds
  [DONE] PROC_FILL_PROJECT_LIST
  [DONE] Fill Project List, duration = 3 seconds
  [DONE] PROC_CREATE_UDF_COLUMNS
  [DONE] Create Udf Columns, duration = 5 seconds
  [DONE] PROC_CREATE_CODE_COLUMNS
  [DONE] Create Code Columns, duration = 1 seconds
Complete load on W_OBI_UNMAPPED_COLS_S
Complete update on W_OBI_SA_S
Complete update on W_XLAT_S
Complete update on W_XLAT_S
Complete update on W_XLAT_S
Complete update on W_XLAT_S
Complete update on W_XLAT_S
Complete update on W_XLAT_S
Complete update on W_XLAT_S
Complete update on W_XLAT_S

ERROR
-----------------------
The associated error in Oracle Data Integrator (ODI) is the following.

ODI-1519: Serial step "Analytics Daily (InternalID:3424)" failed because child step "Case Data Source Type (InternalID:3426)" is in error.
ODI-1521: Case step "Case Data Source Type (InternalID:3426)" failed; the selected child "When UNIFIER (InternalID:3451)" is in error.
ODI-1520: Case-When/Else step "When UNIFIER (InternalID:3451)" failed because child step "rootStep_caseWhen (InternalID:3452)" is in error.
ODI-1519: Serial step "rootStep_caseWhen (InternalID:3452)" failed because child step "36) SCENARIO_BP_DIM_AND_FACTS (InternalID:3459)" is in error.
ODI-1217: Session SCENARIO_BP_DIM_AND_FACTS (26258) fails with return code 7000.
ODI-1226: Step OdiWaitForChildSession fails after 1 attempt(s).
ODI-1241: Oracle Data Integrator tool execution fails.
Caused By: com.sunopsis.dwg.function.SnpsFunctionBaseException: ODI-30059: The number of child sessions in error state is greater or equal to the MAX_CHILD_ERROR parameter

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Either initiate the ETL from Analytics Administration or allow to run on its schedule.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.