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-premise 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 that 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 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 may cause ETL failure or data loss or corruption in the data warehouse. The SDE (source dependent extract) 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-premise sources, and load the data into universal dimension and fact staging tables. The 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.
