Configuring Incremental Extract for Projects Facts for PeopleSoft

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.

Configuring Incremental Extract for Project Facts for PeopleSoft Using Database Triggers

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.

  1. Read the Overview, which includes deploying the appropriate SQL code in your source system - Database Triggers Solution Overview.
  2. Implement the configuration tasks - Configuration Tasks for Database Triggers Solution.

Database Triggers Solution Overview

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:

  1. Create the trigger and db objects.

  2. Run the full ETL.

  3. Modify the data in OLTP.

  4. Run the SDE fact extracts.

  5. Run the SIL fact loads.

  6. 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.

Configuration Tasks for Database Triggers Solution

Perform these implementation tasks to configure the incremental extracts for project facts for PeopleSoft using database triggers.

Updates in Oracle BI Applications Configuration Manager

In Oracle BI Applications Configuration Manager, make these changes.

  1. Set the value of SOFT_DELETE_PREPROCESS to 'N'.
  2. Set the value of SOFTDELETE_FEATURE_ENABLED to 'Y' for the Fact Group Level variables.

Updates in Oracle Data Integrator

Make these updates in Oracle Data Integrator.

  1. In the ODI model layer the resource name for the object PS_PROJ_RESOURCE table will be replaced by the view OBIEE_PS_PROJ_RESOURCE_VW.

    If you are using PeopleSoft 90, then follow this navigation. Otherwise, navigate to the 9.0 folder. That is, in ODI Designer Navigator, navigate to Models, then Peoplesoft 9.0, then peoplesoft 9.0 FNSCM, then FPC-Projects, then open the object PROJ_RESOURCE and change the Resource Name to OBIEE_PS_PROJ_RESOURCE_VW.

    This screenshot is described in surrounding text.
    Description of the illustration GUID-2ABD833C-F87E-45F8-BDD9-A1B862CBCA5F-default.png
  2. Add column LAST_UPDATE_DT TIMESTAMP to the Object PROJ_RESOURCE in ODI model.
  3. Click Save.
  4. Regenerate the appropriate SDE scenarios for all the facts mentioned above.

    That is, in ODI Designer Navigator, navigate to Projects, then Mapping, then SDE_PSFT_90_Folder, navigate to the fact folder e.g SDE_PSFT_ProjectCostLineFact, then Packages, then Scenarios, then right-click on the name and select the Regenerate button.

Modify Temporary Interfaces

Modify temporary interfaces.

  1. In ODI temporary interfaces the mapping for CHANGED_ON_DT needs to be changed to include the LAST_UPDATE_DT column in the code.

    For example, consider the temporary interface for Cost Fact:

    SDE_PSFT_ProjectCostLineFact.W_PROJ_COST_LINE_FS_SQ_PROJ_RESOURCE

    The CHANGED_ON_DT is mapped with:

    RUN_REPLICATED_TRANSACTIONAL('#IS_SDS_DEPLOYED',PS_PROJ_RESOURCE.DTTM_STAMP,PS_PROJ_RESOURCE.CDC$_SRC_LAST_UPDATE_DATE)
  2. Change the update date: 
    RUN_REPLICATED_TRANSACTIONAL('#IS_SDS_DEPLOYED',COALESCE(PS_PROJ_RESOURCE.LAST_UPDATE_DT,PS_PROJ_RESOURCE.DTTM_STAMP),PS_PROJ_RESOURCE.CDC$_SRC_LAST_UPDATE_DATE)
  3. Change the incremental filter, and replace PS_PROJ_RESOURCE.DTTM_STAMP with COALESCE(PS_PROJ_RESOURCE.LAST_UPDATE_DT,PS_PROJ_RESOURCE.DTTM_STAMP).
  4. Regenerate scenarios.

Modify Load Plan

Modify your Load Plan.

The Interface to load the Deleted rows needs to be added to the Load Plan.
  1. In ODI Designer Navigator, navigate to Load Plans and Scenarios, then Your Generated Load Plan and open it.
  2. In the Steps tab, navigate to 1 SDE Extract step, then 2 SDE Fact Group, then Parallel (Persisted/Temporary Staging Table), then 3 SDE PS PROJECT.
  3. Create a Serial Step 'Projects_Identify_Deletes' under the root step and add the identify delete fact scenarios for Project Cost, Budget, Forecast, Commitment, Cross Charge, Retention and Revenue facts steps as shown in the screenshot.
  4. Add scenarios in relevance to Load plan. For instance, scenarios with prefix SDE_PSFT_90_ADAPTOR to be added for PeopleSoft 90 Load plan and ones with prefix SDE_PSFT_91_ADAPTOR should be added for PeopleSoft 91 Load plan.
  5. Click on these newly added tasks, edit them to take the '-1' version as shown in the screenshot.

    This is necessary to ensure that the latest scenario is used in case if there are multiple scenarios.

    This screenshot is described in surrounding text.
    Description of the illustration GUID-5EE126B0-7D7F-4946-9440-328A7B07727A-default.png
  6. Save the Load Plan.

Metadata Repository (RPD) Changes

Change BMM filters for base Facts to filter out Deleted records. It is recommended that these changes are done in an offline mode.

  1. In Oracle BI EE Administration Tool, edit the BI metadata repository (for example, OracleBIAnalyticsApps.rpd).
  2. In the BMM layer and go to Fact – Project Budget.
  3. Click to view LTS'es under this logical fact.
  4. Add the following filter to Fact_W_PROJ_BUDGET_F_Budget_Fact.
  5. Add a similar filter for Budget Fact ITD LTS (Delete Flg = 'N').
    This screenshot is described in surrounding text.
    Description of the illustration GUID-E2865902-C152-46D3-9869-A048AA554430-default.png

    Similarly, modify for Cost/Forecast/Commitment/Cross charge/Revenue/Retention facts.

Configuring Incremental Extract for Project Facts Using Materialized Views

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.

  1. Read the Overview, which includes deploying the appropriate SQL code in your source system - Database Triggers Solution Overview.
  2. Implement the configuration tasks - Configuration Tasks for Database Triggers Solution.

Materialized Views Solution Overview

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:

  1. Create the MV log/MV/View.

  2. Do a one-time complete refresh of MV.

  3. Run the full ETL.

  4. Modify the data in OLTP.

  5. Run the Primary delete capture ETLs.

  6. Fast refresh the MV (MV log gets truncated automatically).

  7. Run the SDE fact extracts.

  8. 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

  1. 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';

  2. 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');.

  3. 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.

Configuration Tasks for Materialized Views Solution

Perform these implementation tasks to configure the incremental extracts for project facts for PeopleSoft using materialized views.

Updates in Oracle BI Applications Configuration Manager

Perform these updates in Oracle BI Applications Configuration Manager.

  1. Set the value of SOFT_DELETE_PREPROCESS to 'N'.
  2. Set the value of SOFTDELETE_FEATURE_ENABLED to 'Y' for the Fact Group Level variables.

Updates in Oracle Data Integrator

The best option to maintain up-to-date custom MVs is to merge their refresh into the ODI Load Plan.The following PLSQL call ensures fast refresh for OBIEE_PS_PROJ_RESOURCE_MV:BEGINDBMS_MVIEW.REFRESH('OBIEE_PS_PROJ_RESOURCE_MV', 'F');END;

  1. In the ODI model layer the resource name for the object PS_PROJ_RESOURCE table will be replaced by the view OBIEE_PS_PROJ_RESOURCE_VW.

    If you are using PeopleSoft 90, then follow this navigation. Otherwise, navigate to the 9.0 folder. That is, in ODI Designer Navigator, navigate to Models, then Peoplesoft 9.0, then Peoplesoft 9.0 FNSCM, then FPC-Projects, then open the object PROJ_RESOURCE and change the 'Resource Name' to OBIEE_PS_PROJ_RESOURCE_VW.

    This screenshot is described in surrounding text.
    Description of the illustration GUID-99876976-BC8D-4F7F-A59F-20A5638A69DE-default.png
  2. Add column LAST_UPDATE_DT TIMESTAMP to the Object PROJ_RESOURCE in ODI model.
  3. Click Save.
  4. Regenerate the appropriate SDE scenarios for all the facts mentioned above.

    That is, in ODI Designer Navigator, navigate to Projects, then Mapping, then SDE_PSFT_90_Folder, navigate to the fact folder, for example, SDE_PSFT_ProjectCostLineFact, then Packages, then Scenarios, then right-click on the name and select the Regenerate button.

Modify Temporary Interfaces

Modify temporary interfaces.

  1. In ODI temporary interfaces the mapping for CHANGED_ON_DT needs to be changed to include the LAST_UPDATE_DT column in the code.

    For example, consider the temporary interface for Cost Fact:

    SDE_PSFT_ProjectCostLineFact.W_PROJ_COST_LINE_FS_SQ_PROJ_RESOURCE

    The CHANGED_ON_DT is mapped with:

    RUN_REPLICATED_TRANSACTIONAL('#IS_SDS_DEPLOYED',PS_PROJ_RESOURCE.DTTM_STAMP,PS_PROJ_RESOURCE.CDC$_SRC_LAST_UPDATE_DATE)
  2. Change the update date: 
    RUN_REPLICATED_TRANSACTIONAL('#IS_SDS_DEPLOYED',COALESCE(PS_PROJ_RESOURCE.LAST_UPDATE_DT,PS_PROJ_RESOURCE.DTTM_STAMP),PS_PROJ_RESOURCE.CDC$_SRC_LAST_UPDATE_DATE)
  3. Change the incremental filter, and replace PS_PROJ_RESOURCE.DTTM_STAMP with COALESCE(PS_PROJ_RESOURCE.LAST_UPDATE_DT,PS_PROJ_RESOURCE.DTTM_STAMP).
  4. Regenerate scenarios.

Modify Load Plan

The Interface to load the Deleted rows needs to be added to the Load Plan.

  1. In ODI Designer Navigator, navigate to Load Plans and Scenarios, then your Generated Load Plan, and open it.
  2. In the Steps tab, navigate to '1 SDE Extract' step > 2 SDE Fact Group> Parallel (Persisted/Temporary Staging Table)>3 SDE PS PROJECT.
  3. Create a Serial Step 'Projects_Identify_Deletes' under the root step and add the identify delete fact scenarios for Project Cost, Budget, Forecast, Commitment, Cross Charge, Retention and Revenue facts steps as shown in the screenshot.
  4. Add scenarios in relevance to Load plan. For instance, scenarios with prefix SDE_PSFT_90_ADAPTOR to be added for PeopleSoft 90 Load plan and ones with prefix SDE_PSFT_91_ADAPTOR should be added for PeopleSoft 91 Load plan.

    These tasks have to be set to 'Restart from Failure'.

  5. Click on these newly added tasks, edit them to take the '-1' version as shown in the screenshot.

    This is necessary to ensure that the latest scenario is used in case if there are multiple scenarios.

    This screenshot is described in surrounding text.
    Description of the illustration GUID-5EE126B0-7D7F-4946-9440-328A7B07727A-default.png
  6. Save the Load Plan.

Metadata Repository (RPD) Changes

Change BMM filters for base Facts to filter out Deleted records. It is recommended that these changes are done in an offline mode.

  1. In Oracle BI EE Administration Tool, edit the BI metadata repository (for example, OracleBIAnalyticsApps.rpd).
  2. In the BMM layer and go to Fact – Project Budget.
  3. Click to view LTS'es under this logical fact.
  4. Add the following filter to Fact_W_PROJ_BUDGET_F_Budget_Fact.
  5. Add a similar filter for Budget Fact ITD LTS (Delete Flg = 'N').
    This screenshot is described in surrounding text.
    Description of the illustration GUID-E2865902-C152-46D3-9869-A048AA554430-default.png

    Similarly, modify for Cost/Forecast/Commitment/Cross charge/Revenue/Retention facts.