How to Enable Soft Delete Functionality
in ODI BI Apps 11.1.1.8.1
Solution By Arnaud Bourgogne and Eric Tremblay
Web Page by Eric Tremblay
ODI 11.1.1.7.0 Patch 18204886
ODI BI Apps 11.1.1.8.1
DB 11.2.0.3 patchset 7
Siebel Version 8.1.1
What is the issue?
The issue in our ODI BI Apps Project has an Objective Record (which is an Objective in the Siebel CRM system but not covered by this version of BI Apps) that was deleted from our Siebel source system, so this means that our Fact Table needs to reflect this change. We have to figure out is that this Objective Record has been deleted in our Siebel source system. This Objective Record no longer exists in our Siebel source system but it still exists in our Fact Table. We need to delete this Fact from our Fact Table. We are going to set this Fact Record as Deleted by simply marking it as Deleted or in another words a Soft Delete or also known as a Logical Delete.
Day 1:
Primary Extract
The primary extract mappings perform a full extract of the primary keys from the source system. Although many rows are generated from this extract, the mapping only extracts the INTEGRATION_ID from the source table and we also add the #DATASOURCE_NUM_ID. This is to build our reference point to list all our Keys in the source system which will then be used later to compare with the new source data to see if anything was deleted on our source system. The primary extract mappings loads these two Keys into staging tables that are marked with a *_F_PE suffix in our case in the WC_OBJECTIVE_F_PE table.
The figure below provides an example of the beginning of the extract process.
Image 1
Day 2:
Identify Delete and Soft Delete
Image 2 shows the sequence of events that happens on two day. During which the information in the source table has changed. On day one (Image 1), the data is extracted from a source table and loaded into the *._F_PE table. On day two (Image 2), Record number three is deleted and a new Record (4) is received, creating a disparity between our source and fact tables.
Image 2 shows the primary extract and delete process that occurs on day two. Information is extracted and loaded into the *._F_PE from the source. The initial extract brings record four into the *._F_PE (OBI Warehouse). Then, using a primary extract mapping, the system extracts the Key IDs and the Source IDs from the source table and loads them into a primary extract staging table.
Image 2
The extract mapping compares the keys in the primary extract staging table with the keys in the most current *_F_PE table. It looks for records that exist in the *._F_PE Table but do not exist in the staging table (in Image 3, record three), and sets the delete flag to Y, causing the corresponding record to be flagged as deleted.
Image 3
Also, the extract mapping also looks for any new records that have been added to the source (Image 4), and which do not already exist in the Oracle Business Analytics Warehouse; in this case, record four. When the extract and load mappings run, the new Record (4) is added to the warehouse.
Image 4
Primary Extract
SDE Primary Extract *.PE ODI Mappings
SDE_SBL_ObjectiveFact_Primary.WC_ObjectiveFact_F_PE_SQ_Primary
Description:: What we are interested here is building a list of all the INTEGRATION_ID and the #DATASOURCE_NUM_ID. This Primary Extract is to build find the list of INTERGRATION_IDs.
In this case both ROW_ID, INTEGRATION_ID have the same input S_QTA_PLAN.ROW_ID||'~'||S_QTA_POSTN.ROW_ID||'~'||S_QTA_OBJASGN.ROW_ID Our implementation differs a bit from the norm since we are building our INTEGRATION_ID for our needs but you can simply use your INTEGRATION_ID which is a unique key for your data.
In our case we are doing a join with many tables to figure out exactly what we need and build a unique Reference List of all our data and build a unique INTEGRATION_ID.
We need to Filter on the #INITIAL_EXTRACT_DATE': S_QTA_POSTN.CREATED >= TO_DATE_VAR('#INITIAL_EXTRACT_DATE')
SDE_SBL_ObjectiveFact_Primary.WC_ObjectiveFact_F_PE
Description: This is the final step for our *_F_PE table which will bring in all the data from our Yellow Mapping in our case SDE_SBL_ObjectiveFact_Primary.WC_ObjectiveFact_F_PE_SQ_Primary which has a temporary table called SQ_S_OBJECTIVEFACT_PRIMARY
In the *._F_PE Table you will find the list of keys.
Select * from WC_OBJECTIVE_F_PE;
Package : SDE_SBL_ObjectiveFact_Primary
Identify Delete
Mapping SIL_ObjectiveFact_IdentifyDelete.WC_OBJECTIVE_F_DEL
Description: This mapping we are building our list that needs to be flaged to DELETED by comparing our WC_OBJECTIVE_F_PE and WC_OBJECTIVE_F then results of this mapping goes into WC_OBJECTIVE_F_DEL. It`s important to truncate the WC_OBJECTIVE_F_DEL every time to make sure we have the right records to Mark as deleted.
Overview Source Code Notes for IdentifyDelete:
Filter:
WC_OBJECTIVE_F.CHANGED_ON_DT > TO_DATE_VAR('#LAST_ARCHIVE_DATE')
WC_OBJECTIVE_F.DELETE_FLG='N'
WC_OBJECTIVE_F_PE.INTEGRATION_ID IS NULL AND WC_OBJECTIVE_F_PE.DATASOURCE_NUM_ID IS NULL
Join:
(SUBSTR(WC_OBJECTIVE_F.INTEGRATION_ID,
TO_NUMBER((LENGTH(WC_OBJECTIVE_F.INTEGRATION_ID))- TO_NUMBER( (LENGTH(WC_OBJECTIVE_F.INTEGRATION_ID)+0)))
,TO_NUMBER((LENGTH(WC_OBJECTIVE_F.INTEGRATION_ID))-9)) = WC_OBJECTIVE_F_PE.INTEGRATION_ID (+)
AND WC_OBJECTIVE_F.DATASOURCE_NUM_ID = WC_OBJECTIVE_F_PE.DATASOURCE_NUM_ID (+))
Description: Shows the primary extract and delete process that occurs on day two. Information is extracted and loaded into the *._F_PE from the source. The initial extract brings record four into the *._F_PE table. Then, using a primary extract mapping, the system extracts the INTEGRATION_ID and the #DATASOURCE_NUM_ID from the source table and loads them into a primary extract staging table.
Verify the results with:
Select * from WC_OBJECTIVE_F_DEL;
Package: SIL_ObjectiveFact_IdentifyDelete
Soft Delete
SIL_ObjectiveFact_SoftDelete.WC_OBJECTIVE_F
Description: This is the final step where we take our list of deleted facts WC_OBJECTIVE_F_DEL and do an INNER JOIN with our fact table WC_OBJECTIVE_F to update the DELETE_FLG to 'Y'. Which marks our Fact as deleted.
Package: SIL_ObjectiveFact_SoftDelete
Verifications
Verify before execution the mapping which Facts will be marked as deleted:
select * from
WC_OBJECTIVE_F, WC_OBJECTIVE_F_DEL
where
WC_OBJECTIVE_F.INTEGRATION_ID = WC_OBJECTIVE_F_DEL.INTEGRATION_ID
AND WC_OBJECTIVE_F.DATASOURCE_NUM_ID = WC_OBJECTIVE_F_DEL.DATASOURCE_NUM_ID;
Verify after execution the results of the Soft Delete with:
Select * from WC_OBJECTIVE_F where DELETE_FLG = 'Y';
Load Plan Modifications
Web Pages that inspired me :ORACLE BI APPS 11.1.1.8.1 – How to Enable Soft Delete ProcessSoft Delete Functionality in Oracle BI ApplicationsOracle Data Integrator (ODI) – Logical or Soft DeletesDefault settings for the soft delete option in Oracle BI APPS
;)
;)
;)
;)
;)
;)
;)
;)
;)
;)
;)
;)
;)
;)
;)
;)
;)
;)
;)
;)
;)
; "IBM InfoSphere DataStage")
;)
;)
