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?

[former_member442248 · Accepted solution]

Hi

Let me try to answer your questions using some scenarios:

1. Is it common to use solution 2, when solution 1 is possible? From what I have observed it is indeed a common practice where governance is completely screwed. But should it be done NEVER.

2. When/Why should solution 2 be used, instead of solution 1? Technically whatever logic is there in a SP however complex it is can always be translated to a DS mapping hence SP's should never (or minimal) be used. There is a reason behind this i.e. let us say your DS environment is filled with jobs that uses just mappings and SP's and after 10 years you want to migrate the solution to say a cloud platform. SAP gives you IS to generate impact and lineage reports which is very essential to plan such transformations. The tool mentioned cannot read scripts and SP's. Secondly DS guys have basic SQL knowledge but if you say they can work on advanced procedures is something that is not common. Hence support can become a problem. Thirdly in case one want to use SP's why he / she requires DS. DS is more than a scheduler and should be fully exploited if one is paying such a high license fee.

Regards. S

[former_member593418 · Accepted solution]

Hi! Shazin..Thanks for detail explanation.

Some of my requirement cannot be done via DS mapping. Example as below.

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

But, may be I am wrong. Do u have any idea?

[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

[former_member208402]

Since your source and target tables are in same datastore, you could use merge option instead of table comparison which will be a full pushdown.

source your flow should be like below

Source -------> Query ---------> Target

in query set primary key on the columns you want to compare and in the target table options set "use inpit keys" to YES and set "auto correct load" to YES and "allow merge" to YES... This will create "MERGE INTO" query.

[former_member593418]

Hey..Ravi..Thanks for your advice.

Any idea how to design BODS for below requirement?

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

Will u consider using stored procedure as alternative solution?

[former_member781746]

nice information