Understanding ETL in EPM
This topic discusses:
ETL and the EPM Architecture.
Data Flow through EPM.
IBM WebSphere DataStage.
ETL and the EPM Architecture
The PeopleSoft delivered ETL process enables you to extract data from disparate source transaction systems, integrate the data in a single EPM database, transform and enrich the data, and load it into specific EPM data models that are optimized for analysis and reporting. This process is facilitated by the best-in-class data integration platform IBM WebSphere DataStage and PeopleSoft delivered ETL jobs.
The ETL process migrates data across all layers of EPM warehouse structures and consists of two load types:
Stage I Load: Consists of all ETL jobs that extract data from your source transaction system and load it into Operational Warehouse - Staging (OWS) tables. Also included in this type of load (but less common) are ETL jobs that extract data from your source transaction system and load it directly into Multidimensional Warehouse (MDW) tables.
Stage II Load: Consists of all ETL jobs that extract data from the OWS tables and load it into the Operational Warehouse - Enriched (OWE) or the Multidimensional Warehouse (MDW) tables. Also included in this type of load (but less common) are ETL jobs that extract data from the OWE and load it into the MDW.
Image: ETL in EPM
The following diagram depicts the flow of data through each layer of the EPM architecture using ETL.
After your data is extracted from the OWS it is loaded into specialized data models (target warehouse tables designed to aggregate or enrich your data), which are used by the Analytical Applications and EPM Warehouses for reporting and analysis.
Understanding the Flow of Data Through EPM
Each EPM data warehouse requires a unique set of ETL jobs to populate corresponding target tables with data. Data warehouse target tables may have missing or inaccurate data in them if you do not run all applicable jobs in the proper sequence.
The following topics provide an overview of the ETL jobs required to populate each data warehouse layer with data.
Moving Data Into the OWS
You use ETL jobs to move data into the OWS from your PeopleSoft source system. The following is an overview of the steps required to bring data into the OWS:
Run initial setup (OWS) jobs.
Run source business unit extract jobs.
Run shared lookup jobs.
Run CSW OWS jobs (for CSW Warehouse implementation only).
Run CRM OWS jobs (for CRM Warehouse implementation only).
Run FMS OWS jobs (for FMS Warehouse implementation only).
Run HCM OWS jobs (for HCM Warehouse implementation only).
Run SCM OWS jobs (for SCM Warehouse implementation only).
Moving Data Into the OWE
You use ETL jobs to move data into the OWE from the OWS. The following is an overview of the steps required to bring data into the OWE:
Run the setup - OWE jobs.
Run common dimension jobs.
Some EPM warehouses require OWE data.
For these warehouses see steps below in, 'Moving Data Into the MDW.'
Moving Data Into the MDW
There are three methods of bringing data into the MDW:
Extracting data from the OWS and moving it into the MDW.
This is the most common method and the majority of your data is moved into the MDW in this way.
Extracting data from the OWE and moving it into the MDW.
Certain EPM warehouses use this method, which brings enriched, business unit-based data into the MDW.
For example, the Profitability data mart in the FMS Warehouses uses OWE data that is output from the Global Consolidations analytical application.
Extracting source data directly from a PeopleSoft source system and moving it into the MDW.
This method bypasses the OWS and is only used when large volumes of data must be extracted, such as data used for the Marketing data mart in the CRM Warehouse.
You use ETL jobs to move data into the MDW. The following is an overview of the steps required to bring data into the MDW:
Run Global Dimension Jobs for Campus Solutions Warehouse
Run Global Dimension Jobs for CRM Warehouse
Run Global Dimension Jobs for FMS Warehouse
Run Global Dimension Jobs for HCM Warehouse
Run Global Dimension Jobs for SCM Warehouse
Run Local Dimension Jobs for Campus Solutions Warehouse
Run Local Dimension Jobs for CRM Warehouse
Run Local Dimension Jobs for FMS Warehouse
Run Local Dimension Jobs for HCM Warehouse
Run Local Dimension Jobs for SCM Warehouse
Run CSW SKU Jobs
Run CRM SKU Jobs
Run FMS SKU Jobs
Run HCM SKU Jobs
Run SCM SKU Jobs
Run Global-OWE Jobs for CRM Warehouse
Run Global-OWE Jobs for FMS Warehouse
Run Global-OWE Jobs for HCM Warehouse
Run Global-OWE Jobs for SCM Warehouse
Run CRM-OWE jobs
Run FMS-OWE jobs
Run HCM-OWE jobs
Run SCM-OWE jobs
For more information on the jobs required to load data into the MDW for your EPM Warehouse, see your warehouse specific documentation (for example, PeopleSoft EPM: Campus Solutions Warehouse).