About ETL Architecture
Typically, the extract-load-transform process has two main steps: The first step is the extract and stage load step and the second step is the load transform step.
The extract and stage load step is generated from a combination of the main interface and the temporary interface. The load transform step is generated as a result of the integration knowledge module (IKM). In this on-premises example, step 1 issues a SQL statement on the source that joins the GL_SET_OF_BOOKS table with the GL_PERIOD_TYPES table. The join is executed on the source database and the resulting data is staged. Then, a second join occurs at the load transform stage between the W_DOMAIN_G table and the temporary stage table, which results in the loading of the stage table W_LEDGER_DS.
Note:
Oracle Database is the only database type supported for the Oracle BI Applications repository schemas and the Business Analytics Warehouse.
There are four main stages: The first, unique to cloud sources, is the Source Dependent Data Store (SDS) stage, which loads and incrementally maintains replicated data into a Source Dependent Data Store schema from cloud sources, for example Fusion Cloud, Taleo Cloud, and so on. A Health Check stage generates a diagnostic report identifying problematic source data that might cause ETL failure, or data loss, or corruption in the data warehouse. The Source Dependent Extract (SDE) tasks then extract data from either SDS schema tables in the case of cloud sources, or source dimension and fact tables in the case of on-premises sources, and load the data into universal dimension and fact staging tables. The Source Independent Load (SIL) tasks are common and load data from the universal staging tables into the warehouse staging tables. This figure depicts a dependency between the dimension table and the fact table. Therefore, the SIL DIM must be executed before the SIL FACT to resolve the dimension key. The SIL DIM has a database sequence that generates the key, and then the SIL FACT looks up that key when loading the fact staging table.
