Oracle^® Business Intelligence Applications Installation and Configuration Guide > Configuring Oracle BI Applications Consumer >

Logical Delete of Records From Fact Tables

If a fact record is deleted physically in the source OLTP system, you can logically delete that record from the data warehouse. To do this, a target staging table called <FACT table>_PE needs to created for each fact table and a mapping needs to be created that can load this table.

For example in order to delete records from W_ACD_EVENT_F, a new staging table needs to be created as W_ACD_EVENT_F_PE where PE stands for primary extract. The structure of this table will be same for any fact table as it will have only two columns: INTEGRATION_ID and DATASOURCE_NUM_ID. A new mapping SDE_Universal_<Fact>_Primary_Extract needs to be created to load this the primary extract staging table with all the INTEGRATION_ID and DATASOURCE_NUM_ID records that have not been deleted and are currently present in OLTP system.

The following example illustrates this process. In this example, there are five records in the OLTP and in the data warehouse table W_ACD_EVENT_F, as follows:

For any reason, records with an Integration_Id = 3 and a Datasource_num_Id = 0 are deleted from the source system. Then Our primary extract mapping should load the remaining four records in the W_ACD_EVENT_PE table, as follows:

This will be followed by the SIL_IdentifyDelete and the _SoftDElete mappings, which are already available in the data warehouse repository, as installed out-of-the-box. The SIL_ACDEventFact_IdentifyDelete mapping will do a left outer join on W_ACD_EVENT_F and W_ACD_EVENT_F_PE to find out the missing records in W_ACD_EVENT_F_PE, and to check whether the missing record is not already deleted (DELETE_FLG='Y') in the W_ACD_EVENT_F. If not, it will load those records in the staging table W_ACD_EVENT_F_DEL.

The SIL_ACDEventFact_SoftDelete mapping will read records from the W_ACD_EVENT_F_DEL and update the DELETE_FLG of those records in W_ACD_EVENT_F to 'Y', as follows: