Many times I heard these questions:
“How I can delete some selective data from my cube in my data load chain?”
“How I can remove all the data from the source from which I’m loading data? I want to refresh and substitute every time with a full load process and not add the data with a delta load process”
There are different techniques to delete the data in a cube/ODS.
1) Use a selective deletion in a cube/ODS
2) Delete all data in the data target
3) Delete Overlapping Requests from Infocube
The techniques are tightly related to the load scenario. So, take care of some considerations about your scenario in particular about the eventually delta process.
1)
Use a selective deletion in a cube/ODS
Normally is possible to delete data in selective mode in the “Manage Data Target”, you can delete all the data of a request.
Note that in the case of ODS only the last request could be deleted and that action remove the delta process from ODS to others data target if it exists.
You can also delete the data target with the option “Selective deletion” under the tab “Contents”:
In the pop-up you have the possibility to set the selection criteria with the button “DeleteSelection”
Now you can schedule the deletion in batch.
Note that the program is generated dynamically and after the execution will be deleted it too.
If you need to schedule a specific deletion in a process chain use the report RSDRD_DELETE_FACTS to generate a specific deletion report.
In the execution, define the name of a new report. It is the name of report that you’ll call from a chain or in a batch process.
Check the radio button “Generate deletion program”.
Press “Execute F8” and insert the selection criteria for the repetitive deletion. After Press “Execute F8” to store the parameters.
No panic, the deletion program doesn’t run here.
Pay attention to this kind of action, here you delete directly in the cube some information. If there is a delta process chain involved you can have some trouble related.
An example:
The process delta is like this: ODS >>>> Cube
The deletion report generated delete all records in cube with <keySpecAA>.
In the next delta package from the ODS some of these records are changed, as example the amount is increased by 10, so in the cube we receive:
<keySpecAA> & amount = -100
<keySpecAA> & amount = +110
But the result in the cube will be:
<keySpecAA> & amount = 10
It could be a wrong result.
2) Delete all data in the data target
This case is simply to realize with the chain step “Complete deletion of data target”.
3) Delete Overlapping Requests from Infocube
Another option to delete data but only in the InfoCube, is the process step chain “Delete Overlapping Request from InfoCube”.
In the step there is the definition of the Infopackage of reference to define which cube is involved.
You have two ways to choose which data delete. Check your choice in the bottom of the process step definition.
In the first, you must change the Infopackage.
In the second, you can delimiter the condition directly in the process step
The options are quite clear.
This technique is useful when is necessary to reload every time in a full mode in a cube from one source and in the meantime you could have a delta process from another Datasource.
Note that you can define the cancellation of the requests in cube after the complete load of the Infopackage, so is eliminated the risk to make an Infocube not available in case of error of loading.
A normal chain is:
……………
? Delete index (of cube)
? Load data
? Delete overlapping request
? Create index
……………..
A downtime for the reporting on the cube is to consider when the step “delete overlapping request” is running, because in that time you can have some data twice.