BODS: ETL data by stored procedure in BODS

[former_member593418]

Requirement:

Extract data from source_table, then insert data into target_table if data not exist and update data in target_data if data exist.

Assume that source_table and target_table are in same datastore.

Solution 1: Using data_flow with transforms object..

Typical Data_flow design:

source_table -> QUERY transform -> TABLE_COMPARISON -> target_table

Solution 2: Using data_flow, which contains a single script that calling stored procedure in datastore.

Typical Data_flow design:

Create a stored procedure that contain logic that extract data from source_table, and insert or update data in target_table. And even more logic can be added into this stored procedure like send out email if exception occurred, and using conditional structure (if/else statement), and repetitive structure (loop) in order to write complex business logic to ETL data.

#calling stored procedure in script

sql('my_datastore_name', 'exec my_stored_procedure');

Question:

1. Is it common to use solution 2, when solution 1 is possible?

2. When/Why should solution 2 be used, instead of solution 1?

Reference:

Why use Stored Procedures?

http://mysqlstoredprocedure.com/index.php?option=com_content&view=article&id=51&Itemid=40

Different ways to execute/call Stored Procedure in BODS [Gokul Gawande] https://blogs.sap.com/2015/08/28/different-ways-to-executecall-stored-procedure-in-bods/

[former_member593418]

The reason I asked this question is that, I came from Oracle PL/SQL stored procedure development background. It is more convenience for me to write ETL logic inside stored procedure.

It takes me a lot of time, simply to understand how Table_Comparison transform is working, in order to design data_flow with upsert operation.

And I get stuck on requirement as below,

https://answers.sap.com/questions/681893/bods-how-to-load-data-row-by-row-in-sequence-with.html

Although finally i came out with a complicated batch_job that contains a lot of data_flow that connected together in long chain. But, actually it can be resolved alternatively by simply writing all ETL logic inside single stored procedure, that called by BODS script in data_flow.

But, just wonder is it considered a bad practice to use stored procedure in BODS?

[Rishabh_Awasthi]

Hello,

I second Shazin's point. There is no need for an enterprise class ETL tool to just schedule stored procs. And further, any SQL code in the ETL breaks the impact and lineage analysis.

DS has a feature called "View Where Used" which you can use before making changes to any object to identify where the impact could be.

Here is good read from Sean that will clear you queries.

Regards,
Rishabh