About Multi-Source Environments

Oracle BI Applications supports the loading of data into the Oracle Business Analytics Warehouse from multiple source systems. If the same adaptor is required for two different sources, then this requires the adaptors' model and maps to be duplicated in the ODI repository.

Consider a scenario in which you have a PeopleSoft 9.0 source and an Oracle EBS 11.5.10 source, both loading the target fact table ASTXACT_FG. Loading this target table includes three serial steps:

  1. Initialize Target Table (ASTXACT_FG).

  2. Load Target Table (ASTXACT_FG).

  3. Finalize Target Table (ASTXACT_FG).

This figure shows these load plan steps in ODI Studio.

Data from both sources, PeopleSoft 9.0 and Oracle EBS 11.5.10, is being loaded into the target table (ASTXACT_FG) for the first time, which means a full load is occurred; and, generally, when a full load occurs, the target table is truncated. In this case, the load plans can run in parallel because the data sources are not dependent on each other; however, the load from the second source should not truncate the target table if it already contains data from the first source. The following serial actions are performed in the process:

  1. The Initialize Target Table step truncates the target table.

  2. The Load Target Table step, in turn, has two child parallel steps. Each of these loads the target from each source system.

  3. The Finalize Target Table step creates any indexes and analyzes the table.

The generated load plan ensures that you can have separate load plans for each source, but load plans should not run in parallel. Hence, the table is truncated only at the required time (in this case, only once before any source system loads it).

Note:

Configuration Manager and ODI do not stop load plans from running in parallel; however, it is recommended that you do not do so because of the following reasons:
  • All load plans truncate target tables upon the initial load. If you run load plans in parallel, one load plan can truncate the data loaded by the preceding load plan.

  • The mappings from SILOS onwards are common and not based on the source system. If load plans are run in parallel, you can have a situation in which only partial data is loaded from the second source due to the data that was loaded from the first source. To resolve this issue, make sure that the first load plan completes successfully before the second load plan runs.

    In other words, when you load a fact table, bear in mind that the fact table might be connecting with multiple dimensions and lookup tables to get the final data. When load plans are running in parallel, some of the dimension, lookup, and staging tables can also have data from a second source. This might lead to some lookups and dimensions not returning appropriate value for the second source, since they have not yet been completely loaded.

When extracting data from different source types (Siebel and Peoplesoft for example) or different versions of the same source type (eBusiness Suite 12.0 and eBusiness Suite 12.1 for example), no additional steps are required. However, if extracting data from two or more instances of the same source version (such as two instances of eBusiness Suite 12.0), this is specifically referred to as 'Multi-Instance Support.' Multi-Instance requires additional steps to support. Refer to My Oracle Support document 1633996.1 —OBIA 11g: Multi-Instance Source Support in BI Applications 11.1.1.x.1, for more details.