ETL Architecture

This page describes the ETL architecture for Oracle Utilities Analytics Warehouse (OUAW).

On this page:

Overview

The following diagram shows the different components of the source, the target, and the Oracle Data Integrator (ODI), as well as the relation between them within the ETL architecture.

In this diagram, the ETL architecture has been divided into three sections.

  • Source: This section contains source product instance components and components that are added to the source product environment to facilitate real-time synchronization.

  • Target: This section covers components on the target OUAW database and application server to facilitate the initial and ongoing data loads into the star schemas.

  • Oracle Data Integrator: This section contains the ODI components that are leveraged for the configuration and the ongoing data loads.

After the installation, Oracle Utilities Analytics Warehouse contains the metadata and the ODI components designed to load data into the target star schemas using the replication schema objects. However, no source product instance is associated with OUAW, which is why attaching a source product instance is the first step after installation.

Back to Top

Attach a Source Product Instance to OUAW

Attaching a source product instance to OUAW is the first action to take after installation. It involves the following:

  1. Import Source System Metadata: OUAW imports source product metadata into its metadata structure. This involves the creation of a product instance entry and detecting some of the configurations that have been implemented on the source product.
  2. Create Job Configuration Entries: The job configurations with predefined defaults are created for all entities associated with the source instance. All jobs are disabled by default.
  3. Reverse Engineer Models: This step creates one or more models (Oracle Data Integrator) and executes a custom Reverse-Engineering Knowledge Module (RKM) to import specific tables into each model. The association of tables to models is preconfigured. The tables are reverse-engineered at the time of implementation to ensure that any changes in the table structures are automatically captured.
  4. Enable Ongoing Sync: Once the source models have been created, the Journalizing Knowledge Module (JKM) is executed. This enables journalization on the tables of the model. The replication schema is created and the corresponding replica tables are created for each model. This step also generates the extract, data pump and replicat parameter files for each model. These parameter files control the ongoing synchronization. The extract and the data pump parameter files are deployed on the source, and the replicat is deployed on the target.
  5. Perform Initial Sync: Once these parameter files have been deployed successfully, the initial synchronization is performed. The data volume to be synced depends on how long the source product instance has been active at your location. The data sync is performed by executing an Oracle Data Integrator scenario, which internally utilizes Oracle Data Pump to copy data from the source database to the replica database.
  6. Generate Metadata-Driven Views: Once the replicated tables and the source instance specific metadata have been imported, the metadata-driven views are generated in the replication schema. These views are the sources for the Oracle Data Integrator interfaces that load the dimensions and facts.
  7. Enable Jobs: Having configured OUAW to work with the source instance to load the associated star schemas, the Oracle Utilities Analytics Administration user interface may be used to enable jobs, load the star schemas, and view the loaded data in the dashboards.

Back to Top