ODS Update / Insert / Delete

[Former Member]

HI.

I have 2 ODS's with the same structure. The key to these ODS's is customer,material and month.These ODS's are populated with data from different sources. If for the same combination of customer,material and month data comes for both ODS's then the 2nd ODS has the most reliable data.

For example,for ODS 1 if the data is

CUST MAT MONTH QTY

customer1 material1 022005 60

customer1 material2 022005 70

customer1 material3 022005 50

and for ODS 2 if the data is

CUST MAT MONTH QTY

customer1 material1 022005 40

customer1 material2 022005 80

The final valid data is

CUST MAT MONTH QTY

customer1 material1 022005 40

customer1 material2 022005 80

The data loaded from the 1st ODS must be overwritten and if there are any extra records they must be deleted.

Any ideas on how to implement this? I dont think we can use 0recordmode for this?

Pls help. Any direction in this regard would be of great help.

Suja

[somnathkumar]

I would implement this scenario in the following manner.

Create a 3rd ODS. Create update rules from the 1st and 2nd ODS to the 3rd. Now create a process chain in which data is first uploaded from ODS1 to ODS3 and only if it is successful do the load from ODS2 to ODS3.

This way, if there is data coming ODS2, it will always overwrite the data coming from ODS1 because it is loaded second.

As Eugene has suggested, you should also have a characteristic which tells you which ODS the data came from.

This approach does not require ABAP coding also but uses the overwrite functionality of ODS to fulfil your requirement.

[Former Member]

The solution does not work the way it is expected because there may be additional materials in ODS1 that are not deleted.

I would do the following:

Load ODS 1 and 2 and activate the data in both.

In the update rules from ODS 1 to ODS 3 do the following coding in the start routine (basic idea).

LOOP AT DATA_PACKAGE into l_d_dp_line.
  SELECT * FROM /BIC/A<ODS2>00 INTO l_d_<ODS2> 
         UP TO 1 ROWS
         WHERE CUSTOMER = l_d_dp_line-customer
         AND   CALWEEK  = l_d_dp_line-calweek.  
  IF SY-SUBRC = 0.
*   A record for this customer and week exists in ODS 2.
    DELETE DATA_PACKAGE.
  ENDIF.
ENDLOOP.

The ODS 3 is updated only with those records from ODS 1 where there are no corresponding records in ODS 2. If you need another key instead of customer and calweek, simply choose it.

Note that the coding is not performant. If you have huge data volumes you should load the data from ODS 2 into a global variable and do a read table. Ask an ABAP developer to optimize it or if you don't find a good one, ask here again.

Best regards

Dirk

[Former Member]

Sorry, should have been calmonth instead of calweek.

Best regards

Dirk

[Former Member]

hai,

we can write the code in startroutine at update rule

we can delete the duplicate recods or extra record by using read command

regards,

reddy

[Former Member]

Hi,

Its a new 3rd ODS.

Suja

[Former Member]

Hi Suja,

First, but maybe not best thought.

Add to the 3rd ODS a new char: parent (ODS1 or ODS2).

Load data from ODS 2 first.

During load from the 1st ODS in ABAP look at the tables (active or change log depending on type of update) of ODS1 (or actibe table of ODS3) to find out if there are data from reliable ODS2 and should the data from ODS1 be omitted.

The situation certainly is going to be more complicated. It depends on type of update (delta or full) and periodicity of update - once in a month, may be here corrective data (repeating of last loads)?

Best regards,

Eugene

Message was edited by: Eugene Khusainov

[Former Member]

Hi,

Not all records/customers data in ODS1 will be there in ODS2.

By this I mean, if ODS 1 has customer1 and customer2 data with 5 materials each and ODS 2 has customer1 and customer3 with 4 materials each, then the target ODS will have customer1 with 4 materials,customer2 with 5 materials and customer3 with 4 materials. Hope this is clear.

Suja

[Former Member]

Ok,

Target ODS - is it the 3rd ODS or should it be the 2nd one?

Best regards,

Eugene

[Former Member]

Hi Suja,

From your remark that data loaded from the 1st ODS must be overwritten I guess that data from both ODS are loaded further into cube?

And if your 2nd ODS has the most reliable data why do you use the 1st ODS at all?

Best regards,

Eugene