ETL Project and Framework Jobs

This page provides an overview of the Oracle Data Integrator (ODI) ETL components, including the Oracle Utilities BI project and the ETL Framework scenarios that are shipped with the product.

On this page: 

Overview

The following types of ETL components are shipped with the product:

  • An ODI Designer project (named Oracle Utilities BI)
  • Mappings
  • Procedures
  • Packages
  • Scenarios
  • Models
  • Datastores
  • Variables (Global and Project)
  • Knowledge Modules

The mappings, procedures, packages, scenarios, and project variables are all contained in the Oracle Utilities BI project, whereas the models and datastores are housed in the Models section of ODI. The global variables and knowledge modules are in the Global Objects section.

Designer tab expanded in Oracle Data Integrator Studio. The following options are shown in this tab: Projects, Models, Dimensions and Cubes, Load Plans and Scenarios, Global Objecs, and Labels.

Back to Top

ETL Project Oracle Utilities BI

The image below shows the structure of the ETL project, as seen in ODI Studio's Designer tab.

Oracle Utilities BI option expanded within Designer tab in Oracle Data Integrator Studio.

Back to Top

Product Folders

Source products have respective folders where their individual design components are stored. Nonetheless, some of the source products share components, which are housed in the Shared folder. The respective source folders contain sub-folders for the following types of entities:

  • Dimensions
  • Facts
  • Materialized Views
  • Dashboards

For example, inside the folder NMS, you will find four subfolders, as shown in the image below.

Subfolders for NMS are: Dashboards, Dimensions, and Facts. See NMS folder under Oracle Utilities BI option in the Designer tab of Oracle Data Integrator Studio.

Each subfolder, for its part, contains the folders for the individual entities labelled by the logical name of the entities.

For example, all the design components for the Account Type Dimension of the NMS product will be found inside the Account Type Dimension folder, which is stored in the Dimensions subfolder of the NMS folder.

Note: The Materialized Views folder exists only for CCB, MDM and MWM. The Dashboards folder exists only for NMS.

Back to Top

Shared Folder

The Shared folder houses the design components for the dimensions shared across the products, and it has the same structure as the Product folder. 

Back to Top

Scratch Folder

This folder houses the design components created during the process of integrating source applications with OUAW configuring sources.

Packages option expanded within Scratch folder under Oracle Utilities BI option in the Designer tab of the Oracle Daa Integrator Studio.

Note: Oracle strongly recommends that you create a separate project for ETL customization, and not to modify any of the components in the ETL project that are provided by default.

Back to Top

Project Variables

The project variables are housed in the Variables accordion that is located inside the Oracle Utilities BI project.

Variables option expanded. Go to the Designer tab in Oracle Data Integrator, and then expand the Oracle Utilities BI option to find Variables.

Back to Top

Models

Oracle Data Integrator Studio is shipped with the Framework and the Oracle Utilities BI model folders (and their underlying components).

Model option expanded within the Designer tab in Oracle Data Integrator Studio showing three folders: Source, Framework, and Oracle Utilities BI.

The Framework model folder stores the metadata model and the datastores for all the Oracle Utilities Analytics Warehouse (OUAW) metadata framework tables used by the ETL design components.

The Oracle Utilities BI folder houses subfolders for individual source products, each of which stores models for dimensions, facts, materialized views, staging tables and user defined tables. These models contain the datastores for the respective tables. Besides the folders for structural components of entities, it also houses a Replication folder for components like replication tables and views.

The following image shows the Framework and the Oracle Utilities BI folders structure.

The following folders appear expanded under the Models option: Framework, Oracle Utilities BI, CCB, and Replication. Go to Designer tab in the Oracle Data Integrator Studio to find these folders.

Back to Top

Load Plan and Scenarios

This section contains the scenario folders and scenarios that are shipped with the product.

Note: The scenarios in this section are the same as the ones in Projects.

This section consists of two folders:

  • Framework: Folder that comprises all ETL framework related scenarios. Further details on the framework scenarios are provided in the Framework Scenarios section.
  • Oracle Utilities BI: Folder that comprises all the individual products scenarios. These scenarios are grouped and housed in individual product folders that contain the folders for every entity type.

The following image shows the Framework and the Oracle Utilities BI scenario folders organization.

Framework and Oracle Utilities BI folders appear expanded under the Load Plans and Scenarios option within the Designer tab in the Oracle Data Integrator Studio.

As an example, the following image shows the CCB source product scenario folder content and its organization.

CCB folder appears expanded under Oracle Utilities BI folder. These folders can be found in the Load Plans and Scenarios option in the Designer tab of the Oracle Data Integrator Studio.

Back to Top

Global Objects

The global variables and knowledge modules used in the product can be found in this section. 

Back to Top

ETL Framework Scenarios

Oracle Utilities Analytics Warehouse (OUAW) provides two sets of scenarios for the different ETL functions: the framework scenarios and the Oracle Utilities BI scenarios. This section provides details on the scenarios located inside the Framework scenario folder under the Load Plan and Scenarios section of the ODI Studio client's Designer tab. 

Back to Top

Framework Scenarios

These scenarios are part of the OUAW ELT framework that are used for product deployment, configuration, and housekeeping purposes. The Framework scenarios are grouped under the following categories:

  • Accelerators: This folder's scenarios are used for integration of different sources with OUAW.
  • Data Correction: This folder's scenarios are used for modification of certain staging tables and backfilling of data in case of upgrades.
  • GDPR: This folder's scenarios are used for provisioning General Data Protection Regulation (GDPR) functionality for OUAW.
  • Import: This folder's scenarios are used during OUAW installation for importing ODI objects into the work repository.
  • Replication: This folder's scenarios are used for creation of certain database objects in the replication schema
  • Scheduler: This folder's scenarios are used for daily administration of the OUAW entities. Some of the frequently used scheduler scenarios are the following:

    • B1_CFG_UDX_PROC

    This scenario is used for the configuration of the char mapping feature provided by OUAW.

    B1_CLEANUP_REPLICATION

    This scenario is used to delete the data from all the tables in replication schema. This is used in the event of corruption of data in the replication schema, causing the data to be reloaded again.

    B1_CLEAR_LOGS

    This is used for housekeeping of the execution logs data in the warehouse. The scenario in turn invokes the B1_PURGE_SESSIONS and B1_PURGE_STAGING scenario which clean up the ODI logs and drop the staging tables respectively.

    B1_DWH_LOAD

    This scenario is used for sequential loading of the dimensions, facts and materialized views. The scenario enables the dimension jobs first, then the fact jobs and then the MV jobs. It needs B1_RUN_ALL to be scheduled.

    B1_GATHER_SCHEMA_STATS

    This scenario is used to gather statistics on tables of different schemas of OUAW. 

    B1_INITIAL_SETUP_PKG

    This scenario is used to load data in the Date and Time dimensions and the dummy records with 0 and -99 keys in all the dimension tables.

    B1_PURGE_SESSIONS

    This scenario is used to delete the ODI logs and the job execution data from the B1_JOB_EXEC table. This is invoked by the B1_CLEAR_LOGS scenario.

    B1_PURGE_STAGING

    This scenario is used for dropping the different staging tables from the Staging schema.

    B1_RESET_ENTITY

    This scenario is used to delete the data from the target table and reset the job configuration values in the B1_JOB_CONFIG table.

    B1_RESET_INSTANCE

    This scenario is used to delete the data from the Dimensions, Facts, and Materialized views of a configured instance. For each of the entities configured for a source product instance, it invokes the B1_RESET_ENTITY table scenario.

    B1_RUN_ALL

    The B1_RUN_ALL scenario is used to invoke the configured entities that are enabled for execution. The scenario is required to be scheduled in ODI Designer for the automatic execution of the entity jobs.

    B1_SYNC_CONTEXT

    This scenario is used to load data from the tables in the source database to the tables of replication schema in the target database. It invokes the B1_SYNC_MODEL scenario for every configured group of the replication tables.

    B1_SYNC_MODEL

    This scenario is executed for every configured group of replication tables and in turn calls the B1_SYNC_TABLE for every table in the group. The scenario fetches the data from the source schema and loads it in a temporary schema.

    B1_SYNC_TABLE

    This scenario is used to load data from the temporary schema (created during the B1_SYNC_MODEL scenario execution) into the replication tables.

  • Views: This folder contains the scenarios for the ELT framework related views created in theMetadata schema MDADM.

Back to Top