Skip to main content

Cloud

BI Cloud Service: How to Automate Delete Jobs with DataSync

If you have used ETL tools like Informatica or ODI, you probably know that it is possible to perform delete operations on a target database, like a data warehouse. But if you are using Oracle BI Cloud Service with the Schema Database as a data warehouse for BICS, you won’t be able to connect to this cloud database with conventional ETL tools but you are able to use Data Sync, a lite ETL tool provided by Oracle as part of the BICS subscription. However, Data Sync doesn’t currently support issuing direct Delete statements on the BICS Schema Database. In this blog I aim to show you how you may still leverage Data Sync’s job automation capabilities and include delete operations as part of the automated jobs.

This approach of automating delete jobs for BICS consists of 3 main tasks:

  1. Define Delete triggers on the source tables that are expected to have records deleted from: For example, if my order fact table (W_ORDERS_F) in the BICS database sources data from an on-premise table called ORDERS, I will create a trigger on the ORDERS table in the on-premise database as follows. The trigger populates the primary key of any deleted records into a new table called ORDERS_DEL. ORDERS_DEL is created in the source database specifically to allow us to keep track of the record IDs that got deleted from the source table.

An example of the trigger script on a SQL Server Database as a source. (This script assumes that a table ORDER_DEL already exists in the source database and consists of 2 columns: ORDER_ID and DELETION_DATE):


create TRIGGER ORDERS_Delete_TR

ON ORDERS

AFTER DELETE

AS

INSERT INTO ORDERS_del

SELECT d.ORDER_ID, GETDATE() FROM Deleted d

go


 

  1. Create a DataSync data mapping task as part of your Data Sync project: The purpose of this task is to copy over the content of the Delete table (ORDERS_DEL) created in step 1 from the source database to the Cloud BICS database. A new table (W_ORDERS_F_DEL) in the BICS database is created to hold the content of ORDERS_DEL. Note that you can use the DELETION_DATE from ORDER_DEL as an incremental selection criteria as you chose the “Update table” option in Data Sync’s Load Strategy for the new data mapping from ORDERS_DEL to W_ORDERS_F_DEL.
  1. Define an Insert/Update trigger on the target BICS Delete table (W_ORDERS_F_DEL) populated in step 2: The purpose of this trigger is to delete any records inserted into the BICS Delete table (W_ORDERS_F_DEL) from the corresponding BICS Fact or Dimension table. These triggers may be defined on the BICS database using the APEX (Application Express) SQL Workshop. Following is an example of the trigger script to delete records from W_ORDERS_F.

create or replace trigger “W_ORDERS_F_DEL_T1”

AFTER

insert or update on “W_ORDERS_F_DEL”

for each row

begin

DELETE FROM W_ORDERS_F

WHERE W_ORDERS_F.ORDER_ID = :new.ORDER_ID ;

end;


 

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Mazen Manasseh, Director of Business Analytics

Mazen is a Director of Business Analytics at Perficient and an accomplished professional services leader with 20 years of being a customer advocate. An analytics solutions delivery expert in functional domains covering Supply Chain, Financials, HCM, Projects and Customer Experience. Being a thought leader in the business analytics space, he conducted numerous business training sessions and spoke at technology conferences around analytics and machine learning.

More from this Author

Follow Us
TwitterLinkedinFacebookYoutubeInstagram