Extracting updated data from SAP R/3 to BOBJ DS

[former_member1052991]

Hi all,

I have a doubt reg extracting updated data from SAP to BOBJ DS. For example i am extracting 50000 records from SAP table to BOBJ DS say its taking half an hour, after extracting data 10 more records have added in the same table. Do we need extract the whole record again or do we have some other way to extract only those 10 records from table.

Please help me. I have searched in forums but cudnt find the exact solution fyi.

Thanks,

Guna

[Former Member]

What is you BODS version? Do you have create or update timestamps in your source?

(ie, datetime fields which will determine when the records were changed or when the new records were added to the source)

If so, you can go for Source based CDC method.

Regards,

Suneer.

[former_member1052991]

Hi Suneer,

Thanks for your reply. We are using BOBJ DS XI 3.2. Can you pls explain source based CDC method?

Thanks,

Guna

[Former Member]

Okay, here is how it works.

You load the Create timestamp and Update timestamp fields into your target.

In the beginning of your next load (incremental / delta), SELECT the MAX (Create timestamp) and MAX (Update timestamp) from the target and assign it to two variables, in a script. Say,

$G_UPDATE_TIMESTAMP = SELECT MAX (Update timestamp) FROM TARGET_TABLE;

$G_CREATE_TIMESTAMP = SELECT MAX (Create timestamp) FROM TARGET_TABLE;

In your Data Flow, you can use a WHERE condition in a Query transform and the condition would be:

WHERE

Update timestamp > $G_UPDATE_TIMESTAMP or

Create timestamp > $G_CREATE_TIMESTAMP

The target table should have an option set as "Auto Correct Load" to "YES" inorder to avoid a Primary Key constraint in case of Update records.

Note : This really depends on how the Create timestamp / Update timestamp behaves in your source when a new record is added / updated. ie. If the Update timestamp also updates when a new record is created, you can load your delta with one Update timestamp. ie .

WHERE

Update timestamp > $G_UPDATE_TIMESTAMP

I suggest you to understand how you would determine a newly added or changed data from your source as the Delta extraction will depend on this.

Regards,

Suneer.

[former_member1052991]

First we load the data into a table which is in Datastore right?

After that only we ll use that data as a source and ll create data flow.

Can we create a condition at the time of extraction from SAP R/3 itself.

[Former Member]

Whenever you load, be it FIRST load or incremental, you are loading the data to the target Database, through a Data Store.

Suppose all your 50000 records are there in the target after the first load.

In your Delta load, you run a script against the target, as mentioned above.

Now that you got the MAX value of Create and Update timestamp, the Query transform inside R/3 Data Flow can have a WHERE condition with these MAX values. All these are pre-built.

When you load the data, you can have a conditional which determines whether the load is FULL / DELTA. If you choose DELTA, it will route to a Data Flow which will have all these conditions mentioned. If you choose FULL, it will be a normal FULL load (the way you loaded all the 50,000 records already)

Regards,

Suneer

[former_member1052991]

Hi Suneer,

Thanks a lot for your reply again. But still i dint get you sorry. Let me explain what we are doing here.

1. Create data store with the type of SAP Application, say DS_ERP.

2. After creating the data store right click->import by name-> table - MARA

3. Now the MARA data will be available in data store(DS_ERP), say table name as BO_MARA.

4. Create a dataflow [ source as BO_MARA, query transform and destination as a table]

5. Do Transformation.

6. Transformed data will be available in target table.

Pls tell me where are we creating conditions here.

Thanks,

Guna

[Former Member]

Apart from stepd 1,2, you need to create a Target Data store and a table to load your MARA data.

 3. Now the MARA data will be available in data store(DS_ERP), say table name as BO_MARA 

.

Why do you want to change the table name? Are you planning to stage the data anywhere?

4. Create a dataflow source as BO_MARA, query transform and destination as a table

Data transport object is required if you are using R/3 Data Flow.

This would suffice just for a full load, if all your transformations can be incorporated inside the Query transform.

5. Do Transformation.
6. Transformed data will be available in target table.

Only after executing the job that the transformed "data" will be available in the target.

The above part mentions only about a Source, Query transform, Source Data Store and table.

Apart from these

You would require a Script object to write the condition, to SELECT the MAX timestamps. You would require Variables to be created in your job to redirect your job path and to restrict the data extracted.

Use your DataFlow in a Conditional object. If load type is first, in the then part use the dataflow which you might have already designed. Else part will have a replicated Data Flow with the above mentioned WHERE condtion in Query transform.

Suggest you to refer Desiger document and DI supplement for SAP as well. Inform if you are stuck.

Regards,

Suneer Mehmood.

[former_member1052991]

Thanks suneer. Let us try n ll come back if hav any queries.

[Former Member]

Hi ,

I am also facing the problem. i am not able to get a clear picture of how the scripts are to be written.Could you explain in detail steps pls

Thanks,

OMAR

[Former Member]

You require a script object for this. If it has to be passed to a database, you will have to go for SQL() function. Suggest you to go through the post and come up with any doubts if you have any or if you are stuck anywhere.

Regards,

Suneer Mehmood.