ETL Architecture
The following figure displays the ETL process delivered with Oracle Argus Analytics.
Figure 4-1 The Oracle Argus Analytics ETL Architecture
Oracle Argus Analytics has ETLs defined in the Oracle Data Integrator (ODI).
Set up as a recurring job in ODI, the Extraction, Transformation, and Load process (ETL) is designed to periodically capture targeted metrics (dimension and fact data) from multiple Safety databases, transform and organize them for efficient query, and populate the star-schema tables.
While the Oracle Argus Analytics data model supports data extraction from multiple sources, Oracle Argus Analytics includes only source-dependent extract (SDE) mappings/ODI for the Oracle Argus Safety database. However, you can also define SDE mappings from additional external sources that write to the appropriate staging tables. Note that you are responsible for resolving any duplicate records that may be created as a consequence.
The SDE programs map the transactional data to staging tables, in which the data must conform to a standardized format, effectively merging the data from multiple, disparate database sources. This is the architectural feature that accommodates external database sourcing.
The staged data is transformed using the source-independent loads (SILs) to star-schema tables, where such data are organized for efficient query by the Oracle BI Server.
There is one SDE mapping for each target table, which extracts data from the source system and loads it to the staging tables. SDEs have the following features:
- Incremental Submission Mode: Oracle Argus Analytics-supplied ETL uses timestamps and journal tables in the source transactional system to optimize periodic loads.
- Normal load writes one record at a time. It is intended to be used for updates to the data mart, once population has been completed. Normal load is faster, if data volume is sufficiently small. You can also restart load if the load is interrupted.
There is one SIL mapping/interface for each target table. The SIL extracts the normalized data from the staging table and inserts it into the data mart star-schema target table. SILs have the following attributes:
- Concerning changes to dimension values over time, Oracle Argus Analytics overwrites old values with new ones. This strategy is termed as Slowly Changing Dimension approach 1.
- Oracle Argus Analytics's data model includes aggregate tables and a number of indexes, designed to minimize query time.
-
The results of each ETL execution is logged by ODI. The logs hold information about errors encountered, during execution.
For ODI, the EL$_<TABLE_NAME> will store the erroneous data rows per table. Example: For W_PRODUCT_D ETL the error table is EL$_W_PRODUCT_D.
During ETL execution, records which fail to be inserted in the target table (for example, some records violate a constraint) are placed in the ODI error tables, as mentioned above. You can review which records did not make it into the data mart, and decide on appropriate action with respect to them.
For more information, see: