on 02-25-2006 3:29 PM
Hi BW experts,
we load the data from source system on the monthly basis.
The biz requirement is to keep 3 month history.
in other words, any requests older than 3 months must be deleted from the cube. If I don't want to implement archiving function here, how can I delete the old requests in the best way?
Any feedback is highly appreciated!
Warm regards,
Lilly
hi
If you have already compressed the cube there is no other way other than the selective delete which will delete records in the Cube based on the conditions that you have provided. You can do this in the contents tab of Manage view of the cube and then pressing "Selective Deletion". This may or may not delete the data loaded by the old requests unless you can come up with the exact conditions.
If you have not compressed the cube, it is easy. Go to the Requests tab in the Manage view of the cube. Diplay only the old requests using the Request Display which is displayed below the list of requests in the cube and select all the requests and press delete button. This can also be done automatically in the Process chains by using "Delete Overlapping Requests from InfoCube" Process type.
it can also be done by writing abap rotne in delete tab
If you want to delete e.g. last three months request, you can use option in infoPackage/Data target/select data Targets/Automatic Loading ..., and write routine which return (delete) only this request from cube with last timestamp (e.g.). Something like this:
program conversion_routine.
DATA: rid type rsreqdelstruc,
rtime like rsreqdelstruc-TIMESTAMP.
form compute_TEST_LAD
tables l_t_request_to_delete structure rsreqdelstruc
using l_request like rsreqdone-rnr
changing p_subrc like sy-subrc.
rtime = 0.
loop at l_t_request_to_delete.
IF rtime < l_t_request_to_delete-timestamp.
rtime = l_t_request_to_delete-timestamp.
rid-rnr = l_t_request_to_delete-rnr.
endif.
delete l_t_request_to_delete.
endloop.
append rid to l_t_request_to_delete.
clear p_subrc.
endform.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Lilly...
You want ur cube to have the last three month's data.
maybe u can do a selective delete ..thats an option ...but i think it can be done in a better way by writing a routine in the deletion tab u can find in the infopackage (its there in the datatarget tab) ..what i mean here is u can write a routine by which the deletion would take place....
Best Regards,
Marc..
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
hi Lilly,
how's the data volume and extraction time for 1 month data ? if no complex transformation, and the extraction is fast enough, there is option 'delete entire content of data target' in infopackage, this will delete all the infocube data before the upload performed, then you can scheduled automatically the infopackage with selection for 3 months data, and try by using infopackage routine
if selection is by date
data: l_idx like sy-tabix,
l_date like sy-datum.
read table l_t_range with key
fieldname = '[your field date]'.
l_idx = sy-tabix.
*....
DELETE l_t_range
WHERE iobjnm = '[date infoobject name]'
and fieldname = '[your field date]'.
L_t_RANGE-SIGN = 'I'.
L_t_RANGE-OPTION = 'BT'.
*BT = between
L_t_RANGE-LOW = sy-datum - 90.
L_t_RANGE-HIGH = sy-datum.
append l_t_range.
modify l_t_range.
p_subrc = 0.
$$ end of routine - insert your code only before this line -
endform.
if selection is by month
data: l_idx like sy-tabix,
l_month(6) type c.
read table l_t_range with key
fieldname = '[your field date]'.
l_idx = sy-tabix.
*....
DELETE l_t_range
WHERE iobjnm = '[date infoobject name]'
and fieldname = '[your field date]'.
L_t_RANGE-SIGN = 'I'.
L_t_RANGE-OPTION = 'BT'.
*BT = between
concatenate sy-datum3(2) sy-datum0(4) into l_month
L_t_RANGE-LOW = lmonth - 3.
L_t_RANGE-HIGH = l_month.
append l_t_range.
modify l_t_range.
p_subrc = 0.
$$ end of routine - insert your code only before this line -
endform.
this is one of the ways. hope this helps.
hi Lilly,
how's the data volume and extraction time for 1 month data ? if no complex transformation, and the extraction is fast enough, there is option 'delete entire content of data target' in infopackage, this will delete all the infocube data before the upload performed, then you can scheduled automatically the infopackage with selection for 3 months data, and try by using infopackage routine
if selection is by date
data: l_idx like sy-tabix,
l_date like sy-datum.
read table l_t_range with key
fieldname = '[your field date]'.
l_idx = sy-tabix.
*....
DELETE l_t_range
WHERE iobjnm = '[date infoobject name]'
and fieldname = '[your field date]'.
L_t_RANGE-SIGN = 'I'.
L_t_RANGE-OPTION = 'BT'.
*BT = between
L_t_RANGE-LOW = sy-datum - 90.
L_t_RANGE-HIGH = sy-datum.
append l_t_range.
modify l_t_range.
p_subrc = 0.
$$ end of routine - insert your code only before this line -
endform.
if selection is by month
data: l_idx like sy-tabix,
l_month(6) type c.
read table l_t_range with key
fieldname = '[your field date]'.
l_idx = sy-tabix.
*....
DELETE l_t_range
WHERE iobjnm = '[date infoobject name]'
and fieldname = '[your field date]'.
L_t_RANGE-SIGN = 'I'.
L_t_RANGE-OPTION = 'BT'.
*BT = between
concatenate sy-datum3(2) sy-datum0(4) into l_month
L_t_RANGE-LOW = lmonth - 3.
L_t_RANGE-HIGH = l_month.
append l_t_range.
modify l_t_range.
p_subrc = 0.
$$ end of routine - insert your code only before this line -
endform.
this is one of the ways. hope this helps.
Hi Lilly,
Just change status of requests to 'red' and delete them.
regards
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.