You can configure incremental extract for project facts for PeopleSoft. There are two ways to configure incremental extract. This information does not cover the advantages or disadvantages of each method, or advise how to choose which method to adopt.
Using Database Triggers - see Configuring Incremental Extract for Project Facts for PeopleSoft Using Database Triggers.
Using Materialized Views - see Configuring Incremental Extract for Project Facts Using Materialized Views.
You can implement the incremental extract solution to mitigate the performance issues for incremental ETL from PeopleSoft for Project Budget/Cost/Revenue/Commitment/Forecast/Cross Charge/Retention Facts using a database trigger solution.
You can facilitate changed data capture (CDC) for PeopleSoft OLTP based on database triggers.
By default, CDC using GoldenGate and ODI is supported. If you do not have a license for GoldenGate, then the solution outlined here can be followed for CDC and incremental loading out the relevant project fact tables.
Supported Versions
Supported DB: Oracle / SQL Server
Supported Oracle BI Applications releases: 11.1.1.7.1 onwards
Supported Apps releases: PeopleSoft 8.9 onwards
Overview
The PeopleSoft ESA application does not populate the DTTM_STAMP column in PS_PROJ_RESOURCE correctly; this restricts the ability to devise incremental load logic around this column, which leads to a performance overhead while loading the Project Budget, Forecast, Commitment, Cross Charge, Cost, Retention and Revenue facts.
Note:
This approach is only supported for Oracle / SQL Server db.The section specifies:
The code to be deployed in the source system (PeopleSoft apps).
The ODI XML files which has to be imported to the ODI repository.
The BI metadata repository (that is, the RPD file) changes for successfully implementing this solution.
Summary
A Trigger on PS_PROJ_RESOURCE will be created in the OLTP which will insert PKs of changed rows into the PROJ_RESOURCE_UPD_AUD table (Refer to the Steps section for code).
A View (OBIEE_PS_PROJ_RESOURCE_VW) will be created on the PS_PROJ_RESOURCE and this trigger table (PROJ_RESOURCE_UPD_AUD) and this view (OBIEE_PS_PROJ_RESOURCE_VW) is what will be used in the SDE fact extract source. (Refer to the Steps section for code).
The deleted rows will be captured in the PE tables via ETL from the Trigger table (PROJ_RESOURCE_UPD_AUD ) filtered on update_type = 'D'.
Rest of the delete strategy interfaces will also be updated to properly handle the soft delete logic.
In the ODI model layer the resource name for the object PS_PROJ_RESOURCE table will be replaced by the View on MV (OBIEE_PS_PROJ_RESOURCE_VW).
The deleted rows will be captured in the <fact>_DEL tables via ETL from the OBIEE_PS_PROJ_RESOURCE_DEL_VW.
The SIL fact interface will properly handle the soft delete logic once we set the values of variables to:
- SOFT_DELETE_PREPROCESS = 'N' (This will not populate the <fact>_PE table)
- SOFTDELETE_FEATURE_ENABLED = 'Y'
Summary of steps:
Create the trigger and db objects.
Run the full ETL.
Modify the data in OLTP.
Run the SDE fact extracts.
Run the SIL fact loads.
Run the soft delete ETLs.
Assumptions
There will be some performance impact on the OLTP application due to the presence of the trigger.
Actual deletes from PS_PROJ_RESOURCE will be treated as Soft Delete in Oracle Business Analytics Warehouse.
The customer need to run truncate on the trigger table PROJ_RESOURCE_UPD_AUD from time to time (say every week) in order to ensure it does not become too big such that it begins to impact ETL run times.
Database Changes Required for an Oracle Database
If your source OLTP database is an Oracle database instance, then execute the SQL script in the file psft_orcl_trigger.txt, which is located in the installation folder <Oracle Home for BI>/biapps/etl/src_specific/PSFT/oracle.
For example:
/*ORACLE SCRIPT TO IMPLEMENT INCREMENTAL SOLUTION FOR PEOPLESOFT ADAPTOR FOR OBIA PROJECT ANALYTICS */ DROP TABLE PROJ_RESOURCE_UPD_AUD; / CREATE TABLE PROJ_RESOURCE_UPD_AUD( ROW_WID number(10), BUSINESS_UNIT varchar2(5) NULL, PROJECT_ID varchar2(15) NULL, ... ... And so on.
Database Changes Required for an MS SQL Database
If your source OLTP database is an MS SQL Server database instance, then execute the SQL script in the file psft_mssql_trigger.txt
, which is located in the installation folder <Oracle Home for BI>/biapps/etl/src_specific/PSFT/ms_sql_server
.
For example:
/*MSSQL SCRIPT TO IMPLEMENT INCREMENTAL SOLUTION FOR PEOPLESOFT ADAPTOR FOR OBIA PROJECT ANALYTICS */ /* Replace <DB> with the actual schema name */ USE <DB> DROP TABLE PROJ_RESOURCE_UPD_AUD; CREATE TABLE PROJ_RESOURCE_UPD_AUD( ROW_WID INT IDENTITY(1,1) PRIMARY KEY, ... ... And so on.
Perform these implementation tasks to configure the incremental extracts for project facts for PeopleSoft using database triggers.
In Oracle BI Applications Configuration Manager, make these changes.
The PeopleSoft ESA application does not populate the DTTM_STAMP column in PS_PROJ_RESOURCE correctly; this restricts the ability to devise incremental load logic around this column, which leads to a performance overhead while loading the Project Budget, Forecast, Commitment, Cross Charge, Cost and Revenue facts.
You can facilitate changed data capture (CDC) for PeopleSoft OLTP based on fast refresh of Materialized View (using MV log). By default, CDC using GoldenGate and ODI is supported. If you do not have a license for GoldenGate, then follow this solution for CDC and incremental loading of the relevant project fact tables.
This approach is only supported for Oracle databases.
The topic specifies:
The code to be deployed in the source system (PeopleSoft apps).
The ODI XML files which has to be imported to the ODI repository.
The RPD changes for successfully implementing this solution.
Supported Versions
Supported DB: 10.2.0.4 with patch (RDBMS patch 9580103), Oracle 11i onwards
Supported Oracle BI Applications releases: 11.1.1.7.1 onwards
Supported Apps releases: PeopleSoft 8.9 onwards
Summary
An MV log on PS_PROJ_RESOURCE will be created in the OLTP (Refer to the Steps section for code).
A PK constraint based on the unique index on PS_PROJ_RESOURCE will need to be created.
An MV will be created on PS_PROJ_RESOURCE with an additional column of LAST_UPDATE_DT which will be populated based on the sysdate. This new field will be indexed. (Refer to the Steps section for code).
A View will be created on the MV and this is what will be used in the SDE fact extract source. (Refer to the Steps section for code).
A one time complete refresh of MV will be done.
Prior to the daily ETL run the MV will be fast refreshed. MV refresh will be integrated in the Load Plan, for details refer to the Steps section below. Oracle automatically purges the MV log once a fast refresh is run (since the MV log can grow substantially it is recommended to run a daily ETL run so that the MV fast refresh is quick.).
In the ODI model layer the resource name for the object PS_PROJ_RESOURCE table will be replaced by the View on MV (OBIEE_PS_PROJ_RESOURCE_VW).
The deleted rows will be captured in the <fact>_DEL tables via ETL from the MV log filtered on DMLTYPE$$ = 'D'. This ETL will be run prior to the fast refresh as the data will be truncated otherwise.
The SIL fact interface will properly handle the soft delete logic once we set the values of variables to:
- SOFT_DELETE_PREPROCESS = 'N' (This will not populate the <fact>_PE table)
- SOFTDELETE_FEATURE_ENABLED = 'Y'
Summary of steps:
Create the MV log/MV/View.
Do a one-time complete refresh of MV.
Run the full ETL.
Modify the data in OLTP.
Run the Primary delete capture ETLs.
Fast refresh the MV (MV log gets truncated automatically).
Run the SDE fact extracts.
Run the SIL fact loads.
Assumptions
There will be some performance impact on the OLTP application due to the presence of MV log and there is a potential concern with MV Logs refresh time if the MV is not refreshed frequently. Oracle recommends refreshing it on a daily basis to avoid this problem.
This solution requires Oracle RDBMS version 10.2.0.4 with patch (RDBMS patch 9580103) or version 11.1.2.0 or above. If Oracle database behavior when updating a Materialized view based on a prebuilt table changes, this solution might need to be modified.
If someone create another MV using the same MVlog (for whatever reason), then one would have to refresh all depending MVs before the log get purged.
Actual deletes from PS_PROJ_RESOURCE will be treated as Soft Delete in Oracle Business Analytics Warehouse.
Database Changes
Run the following steps in the OLTP database in the instance in the order specified:
ALTER TABLE PS_PROJ_RESOURCE ADD CONSTRAINT PS_PROJ_RESOURCE_PK PRIMARY KEY (BUSINESS_UNIT,PROJECT_ID,ACTIVITY_ID,RESOURCE_ID) USING INDEX PS_PROJ_RESOURCE; CREATE MATERIALIZED VIEW LOG ON PS_PROJ_RESOURCE NOCACHE LOGGING NOPARALLEL WITH SEQUENCE; CREATE TABLE OBIEE_PS_PROJ_RESOURCE_MV AS SELECT * FROM PS_PROJ_RESOURCE WHERE 1=2; ALTER TABLE OBIEE_PS_PROJ_RESOURCE_MV ADD (LAST_UPDATE_DT DATE DEFAULT SYSDATE); CREATE MATERIALIZED VIEW OBIEE_PS_PROJ_RESOURCE_MV ON PREBUILT TABLE REFRESH FAST ON DEMAND AS SELECT * FROM PS_PROJ_RESOURCE; CREATE VIEW OBIEE_PS_PROJ_RESOURCE_VW AS SELECT * FROM OBIEE_PS_PROJ_RESOURCE_MV; CREATE VIEW OBIEE_PS_PROJ_RESOURCE_DEL_VW AS SELECT business_unit, project_id, activity_id, resource_id FROM (SELECT business_unit, project_id, activity_id, resource_id, dmltype$$, CASE WHEN sequence$$ = MAX(sequence$$) over (PARTITION BY business_unit, project_id, activity_id, resource_id ) THEN sequence$$ ELSE NULL END AS sequence$$ FROM mlog$_ps_proj_resource) WHERE sequence$$ IS NOT NULL And dmltype$$ ='D';
Refresh the Materialized view.
It will help to fast refresh the MV during the incremental run, for example, using the command EXECUTE DBMS_MVIEW.REFRESH('OBIEE_PS_PROJ_RESOURCE_MV', 'C');.
Determine which indexes to create on the MV, by looking at the extract sql, and running a query plan.
For example, an index is required on the LAST_UPDATE_DT field and a unique index on the PK fields.
Perform these implementation tasks to configure the incremental extracts for project facts for PeopleSoft using materialized views.
Perform these updates in Oracle BI Applications Configuration Manager.