Selective deletes from ODS and Cube

[Former Member]

Hello,

I have an ODS, which has 4 key fields - Material, Plant, Date, Type.

During a delta update, I want to be able to go up to the ODS and Cube and delete all of the records for all Material/Plant combination that are found in the Delta load.

However the incoming delta does not have the full key combination of the records that need to be deleted. Another words it is not a one to one key match. As a result I can not simply move "D" to 0recordmode indicator!

I also want all the records for a the Material/Plant combination in the ODS and Cube to be deleted, before the received delta data is posted.

Is there a way to do it? Please let me know.

Thanks

Cerish Chemparathy

[Former Member · Accepted solution]

We did what you asked. However the records are not getting deleted from the ODS. Neither is the offsetting entry being sent to the cube. The new records are being added as usual. Any idea why the deletion is not happening?

[Former Member · Accepted solution]

Cerish,

DId you mean you did what I asked or were you replying to Siggi?

If it was meant for me, could you paste the code that you wrote and I'll check on it. Also, could you put a break point in the code and check if the records were adding to the ODS properly. This logic works and I used it sometime last year successfully

Good Luck

Gova

[Former Member · Accepted solution]

Hello Gova,

I was replying to you. I clicked on "Reply to Post" instead of "Reply". Anyway here is the code. I debugged it, the DATA_PACKAGE looks good when at the point it is leaving the routine. I dont see anything being deleted in the ODS. It is as though it is ignoring the records we insert.

• Local type declarations

types :

begin of ty_key,

plant type /bi0/oiplant,

material type /bi0/oimaterial,

/bic/zdemandty type /bic/oizdemandty,

/bic/zreqdate type /bic/oizreqdate,

end of ty_key,

begin of ty_mat,

plant type /bi0/oiplant,

material type /bi0/oimaterial,

end of ty_mat.

• Local data declarations

data :

l_lines type i,

lt_data type table of data_package_structure,

lt_key type table of ty_key,

lt_mat type table of ty_mat,

ls_mat type ty_mat,

ls_active type /bic/azssdeman00.

field-symbols :

<key> type ty_key,

<data> type data_package_structure.

• Copy data package

lt_data = data_package[].

refresh data_package.

loop at lt_data assigning <data>.

clear ls_mat.

ls_mat-plant = <data>-plant.

ls_mat-material = <data>-material.

collect ls_mat into lt_mat.

endloop.

• Get all records from the active ODS table for that Material/Plant

select plant material /bic/zdemandty /bic/zreqdate

from /bic/azssdeman00

into table lt_key

for all entries in lt_mat

where plant = lt_mat-plant

and material = lt_mat-material.

• Add delete records to Data Package

loop at lt_key assigning <key>.

clear data_package.

data_package-plant = <key>-plant.

data_package-material = <key>-material.

data_package-/bic/zdemandty = <key>-/bic/zdemandty.

data_package-/bic/zreqdate = <key>-/bic/zreqdate.

data_package-recordmode = 'D'.

append data_package.

endloop.

• Add original data records to Data Package

append lines of lt_data to data_package.

[Former Member · Accepted solution]

Cerish,

Hmm... can you do the following verifications.

When the datapacket is leaving the routine, did you get the count of the records.

For all the MAT+PLANT combinations in the datapacket, can you get the number of records from the active table of the ODS (lets say X).

Is the size of the datapacket at the time its leaving the datapacket = original no of records + X?

Also, once you load the data in the ODS, can you check the "Activation Queue" table to see if you can see the newly added 'deletion records there.

And once you activate the request, can you check the change log to see whats going on there?

Good Luck

Gova

[Former Member · Accepted solution]

Hello Gova,

Yes, at the point when it is leaving the start routine the DATA_PACKAGE table does have N + X records. I can even see that the routine is passing that data back to it caller. However, the active data never gets updated with the deleted records. Only the original N records are there. So ofcourse, there is noting in the Change Log about these deleted records.

Regards

Cerish

[Former Member · Accepted solution]

Cerish,

Did you check the "Activation Queue" or the "New table." That is what I was asking you.

If the datapacket is correct when leaving the start routine, and if its not there in the start routine, its a mystery. Can you check this and also check if 0RECORDMODE is set to D for these records in "New table"...

Let us know

Gova

[Former Member · Accepted solution]

Hello Gove,

We checked the Active Table, not the New Table. the New table is empty.

Regards

Cerish

[Former Member · Accepted solution]

New table will be empty once you activate it. You need to check the new table "before activation".

Load the data again and check the data without actiating it.

Gova

[Former Member · Accepted solution]

Hello Gova,

I am confused. What do you mean by "activating it"?

Regards

Cerish

[Former Member · Accepted solution]

Cerish,

You are loading data into a standard ODS object. Correct.

When you load data, it goes into New table or activation queue. When you activate the data, it moves into active data and changes are then logged into Change Log table.

check this link for more on this

http://help.sap.com/saphelp_nw04/helpdata/en/e3/e60138fede083de10000009b38f8cf/frameset.htm

If your ODS is enabled for "Automatic actiation" disable it by right click-> change-> Settings-> remove the check mark for automatic activation.

Gova

[Former Member · Accepted solution]

Hello Gova,

We found out the problem. The ABORT was not 0. Also, we had some errors in the update rules at the Cube. Thank you very much for your help.

Regards

Cerish

[Former Member]

Hi,

check out transaction DELETE_FACTS, it might also be of some help!

regards

Siggi

[Former Member]

Try this.

Let me rephrase your requirement. Your ODS has data at MAT, PLANT, DATE, TYPE level. But during the subsequent loads you want to delete all data from the ODS for the combination of MAT and PLANT(for all dates and types).

In the start routine of the update rule to the ODS, add records to the ODS using the following logic. By this logic you are looking up the ODS for the MAT + PLANT key combination and adding all those to the beginning (IMPORTANT) of the Datapacket. Now for all those records make RECORDMODE = D. Once this is done, load from ODS to Cube is automatically taken care of.

i am only giving you the outline of the code... should be easy to build on it.

Code:

DATA:

INT_DELETEPACK is defined with ODS Key (MAT, PLANT, DATE, TYPE only),

INT_DATAPACK TYPE TABLE OF DATA_PACKAGE_STRUCTURE.

Another int table (TAB_TEMP) with only MAT and PLANT

*Copy contents of DataPackage into IntDataPack1.

INT_DATAPACK[] = DATA_PACKAGE[].

REFRESH DATA_PACKAGE.

From the datapacket put records into TAB_TEMP and delete adjacent dulpicates such that we have all the MAT+PLANT combinations.

From active table of ODS, select MAT, PLANT, DATE, TYPE for all entries for MAT+PLANT combination into INT_DELETEPACK

now LOOP AT INT_DELETEPACK.

add all the records in the INT_DELETEPACK to Datapacket by making

DATA_PACKAGE-RECORDMODE = 'D'.

Now add all the original records to the datapacket.

• The following loop must be after the first loop

LOOP AT INT_DATAPACK INTO S_DATA.

APPEND S_DATA TO DATA_PACKAGE.

ENDLOOP.

Hope that helps. Let me know if you have any questions.

Gova