SAP has restructured the ODS for BW 3.0B. It now provides much more functionality for deleting individual data load requests from the ODS, while maintaining consistency with the subsequent data targets. However, the process is not straightforward or fully automated. You must take care to understand and properly follow the sequence of actions to correctly perform this task.

I recently had to learn about deleting requests from the ODS using this new functionality. Now, I'd like to share what I learned and make you aware of complications that might arise when the data has been activated, and when the data has subsequently been updated into further data targets. First, let me give you a little background on the relevant changes in BW 3.0B.

In BW 3.0B, the “new data” ODS table of 2.1C (/BIC/Aodsname10 or /BI0/Aodsname10) no longer exists; it has been replaced by an “activation queue table.” (In the Administration Workbench monitor, the system actually reads from this new activation queue table, /BIC/Aodsname40 or /BI0/Aodsname40, when displaying new data for ODS objects.) The subtle difference between the two is that the new table is in the same format as the change log table (it contains the request ID and is, in fact, a PSA table), whereas the 2.1C new data table is in the format of the active data (it does not contain the request ID). Hence, it was not possible to delete by request prior to Release 3.0B since the requests were already compressed.

Figure 1 shows the three tables that make up the BW 3.0B ODS. Initially, requests are loaded into the activation queue (new data). When those requests are activated, they are deleted from the activation queue and simultaneously loaded into the active data table and the change log.

Figure 1

The three tables that make up the ODS in BW 3.0B

Different Scenarios, Different Deletion Processes

Before you can delete a request from the ODS, you need to consider its state and location. Three possible scenarios exist once the request is in the ODS:

1. The request has not been activated or updated to the data targets. This is the simplest case. The request exists only in the activation queue, where you can simply select and delete it.
2. The request has been activated but not updated to data targets. The request now exists in the active data table as well as the change log. Since the active data table does not contain the request number, it is not possible for the system to simply delete the request. Requests activated after the one you now wish to delete present an additional complication.
   
   Deleting an active request that has not yet been updated into subsequent data targets is a relatively simple task from the administrator's point of view: The request is selected and deleted. BW then deletes the selected request plus all subsequently issued requests (this deletion is termed “roll back”). It then reloads the subsequent requests (“roll forward”). This is achieved via another hidden ODS table (the “roll-back” table, /BIC/Aodsname50 or /BI0/Aodsname50).
3. The request has been activated and updated to data targets. This scenario is the most difficult case because it has the roll-back/roll-forward complications plus the issue of maintaining consistency with the subsequent data targets. Deleting the request from the ODS in this case does no good, since it merely deletes the entries from the active data and change log tables. It does not put a reversal entry in the change log for backing out the request from the subsequent data targets.
   
   In this case, you must first manually delete the request from the subsequent data targets to maintain consistency with them. In fact, you must also delete the request that updated the subsequent data target as well as any subsequent requests in the data target from this source ODS. Once you have deleted the request and subsequent requests from the data target, only then can you reset the data mart status of the requests in the source ODS (starting with the latest request), and then finally delete the required request from the ODS. The process for deleting updated requests follows.

Deleting Updated Requests

You can see on the Requests tab of the ODS Manage Data Targets screen (Figure 2) whether the request has been updated in subsequent data targets. If it has, a green check mark will be in the Data Mart Status of the Request column. Clicking on one of the green check marks brings you to the screen shown in Figure 3, which provides details about the selected request.

Figure 2

The Manage Data Targets screen indicates which requests have been updated

Figure 3

Details about a specific request

From here, you can click on the monitor button in the fourth column from the left. The Monitor screen (Figure 4) shows that the request has been updated in a further data target. Clicking on the Manage button takes you to the data target, from where you can delete this specific request. Click the Manage (data targets) button and then in the next screen select the Requests tab. Now select the request and click the delete button.

Figure 4

Go to the Monitor screen to see if a request has been updated

Once you've deleted the request from the data target, you can then reset the data mart status in the ODS. Remember, you must start with the latest request in the ODS first. If you click on the Data Mart Status of the request in the Requests tab of the ODS Manage Data Targets screen (Figure 2), and if this request has been deleted from the data target, you will then be presented with the option to reset the status so that the ODS thinks the request has not yet been updated in the data target (Figure 5). You should do this working back from the latest request in the ODS, up to and including the actual request you want to delete from the ODS. Once you have reset the status, the green check mark disappears from the Data Mart Status column of the Manage Data Targets screen, as shown in Figure 6 (you might need to refresh the screen). You can now safely delete the request from the ODS.

Figure 6

The green check mark disappears, indicating that the request is no longer updated

Note that a number of requests might have been loaded into the ODS and then together have been loaded as one request into the subsequent data targets. The ODS might then contain many requests relating to one request in the subsequent data targets. If you delete this request from the data target, you must delete all related requests from the ODS (not just the one you wanted to delete) to maintain consistency.

You can see if requests in an ODS have been loaded to subsequent data targets as one request by looking at the Reconstruct tab on the Manage Data Targets screen and scrolling to the right of the list of requests. You'll see three request IDs. Request ID is the number for the load into the ODS (and is the same as the first column on the Requests tab in Figure 6). Change log ID is the id of the ODS activation (and is the same as the fourth column on Requests called PSA ID of Request is Generated with Activation). Update ID is the ID of the load into subsequent data targets (and uses the same number shown in the first column of the Requests tab on the Manage Data Targets screen).

For example, Figure 7 shows that request 1032 was first loaded into the ODS, then activated as request 1033, and finally uploaded to data targets as request 1034. Requests 1036 and 1037 were loaded to the ODS and then activated together in one activation request 1038. Then request 1039 was loaded to the ODS and activated in request 1040. The last three requests (1036, 1037, 1039) were then uploaded from the ODS to subsequent data targets in request 1041.

Figure 7

An example of how you can see which requests were activated or loaded to subsequent data targets as one

As well as being aware of the need to delete requests from the subsequent data target that have been loaded as one request, you also need to bear in mind that the deletion of requests is based on the ODS activation request. Consequently, if the request you want to delete from the ODS was activated in the ODS with other requests, you must also delete those other requests. You can see if a request was activated with other requests by looking at the PSA ID of Request Is Generated with Activation column on the Manage Data Targets screen. For example, Figure 8 shows that requests 771, 774, 775, and 778 have the same PSA ID of 811, indicating that they were all activated together. If you try to delete request 771 from the ODS, BW 3.0B would insist that you also delete requests 774, 775, and 778 (Figure 9).

Figure 8

Requests that have the same PSA ID were activated together

Figure 9

You must delete all requests with the same PSA ID

I will now explain the various situations that can arise when you might want to delete multiple requests from an ODS that have been loaded as one to the data targets. Figure 10 shows examples of multiple requests that were loaded as a single request.

Figure 10

The sequence of events in which multiple requests from the ODS were loaded to the data targets as one

In this example, you have a number of different scenarios to illustrate what steps you must take to delete a request from the ODS and maintain consistency with the data targets. I've presented them below in ascending order of complexity.

1. Deleting request 10 from the source ODS is straightforward since no data targets have been updated.
2. If you want to delete request 7, you must first delete it from the InfoCube data target before deleting it from the ODS. Since request 7 was loaded into the InfoCube with request 9, you must manually delete request 9 from the data target. However, this delta request 9 also contains the data loaded into the source ODS in request 8. Therefore, after deleting request 9 from the InfoCube, you must delete both request 7 and request 8 from the source ODS. (Remember that when selecting requests 7 and 8 for deletion from the source ODS, BW also deletes the subsequent request 10 and then reloads it.)

1. To delete request 2 from the ODS, you must first delete it from the target ODS and InfoCube. You also need to delete all later updates—requests 3, 5, 6, and 9—to the subsequent data targets. Once the requests are deleted from the data targets, reset the Data Mart Status in the ODS in the reverse order of the requests: 8, 7, 4, and then 2. Once this is done, it is possible to delete request 2 from the ODS. The system will actually delete requests 10, 8, 7, 4, 2, and then, if possible, roll forward requests 4, 7, 8, and 10.

Figure 11

Message displayed when you try to pematurely delete an updated request

Sometimes roll forward is not possible, and you will see the pop-up window shown in Figure 12. It shows that where MIN, MAX, or MOVE aggregation is used in an ODS object, it is even harder to delete requests. In fact, the only way to do so is to delete the request you want plus all later requests. It is not possible to roll forward later requests and re-add them. If you have not used MIN or MAX, click on OK and then re-add the requests you want to keep.

Figure 12

This pop-up window appears when the ODS object uses MIN, MAX or MOVE aggregation

It is very important to understand the significance of this restriction. You might assume that the restriction only applies to key figures, since characteristics are not accumulated and do not have MIN or MAX aggregation. However, the restriction also includes characteristics. If you have a characteristic as even one of your ODS data fields, then the update rule for the characteristic is only allowed to be set to “overwrite” (MOVE). In Figure 12, you will see that MOVE prevents automatic roll forward. Since most ODS tables contain characteristics in the data fields, this severely reduces the usefulness of 3.0B's ability to delete requests.

Of course, it is possible to manually set the status of the request you deleted to red and then manually re-add subsequent requests. This circumvents the restriction but involves more work.

Alternative Method of Removing Requests from the ODS

I've shown you how to delete a request from the ODS in BW 3.0B, but it is still possible to use the earlier method of Release 2.1C. With previous releases of BW, you had to back out data from an ODS that had been incorrectly loaded. Many people created a “reversal” flat file, which contained the same loaded data but with the key figures multiplied by -1 (where key figures are set to “addition” in the update rules), or with the key figures set to zero (where the update rules are set to “overwrite”). The flat file was then loaded as “normal” to the ODS. This set the key figures in the ODS to zero, and the load automatically generated the necessary changes in the change log to keep the subsequent data targets consistent.

Depending on your situation, it might still be easier to follow this path than to use the new features to delete individual requests. For example, various local companies might submit flat files for loading to a global system. One company then realizes it submitted the wrong figures, and so you have to back out the loaded file. However, several other companies' files might have subsequently been loaded. You could use the new method in BW 3.0B to delete the incorrect file, but because of the roll-forward restriction (not automatically performed when you have a characteristic as a data field in your ODS), you will probably have to manually reconstruct the later files. An easier and quicker solution is to just load a reversal file for the company that submitted the wrong data and then load the correct submission.

You might assume that reloading a correct submission will overwrite the previous file, and generally this is correct. However, the wrong file might contain records not on the corrected file, and these records would not be overwritten by the corrected file. For this reason, you must set all values in the ODS from the wrong file to zero (using the reverse file) before loading the corrected file.

Obviously, the type of aggregation is also important here, since this method works with the SUM aggregation method. If the aggregation is MAX, the update of the key figures in any subsequent InfoCube is affected. For instance, if the wrong file contained a record with value 999, and this was the highest value for a particular characteristic combination, then the InfoCube will contain the value 999. Any other records with a value lower than 999 will not affect the value in the InfoCube (since it stores the maximum of all values). In this case, loading a reverse file will not work since it will contain either a 0 or -999 value. Because these values are lower than 999, the InfoCube ignores them and keeps the 999. Consequently, you have not backed out the incorrect data.