Objective:
We might encounter a situation where we need to clean up master data tables and SAP does not allow deletion of master data that is in use. We need to find out what entries are in use and what can be deleted.
Introduction:
There are 3 flags CHCKFL, DATAFL and INCFL in SID tables. These fields gets updated depending upon the where used list of an entry/SID. SID flags are BI: Locked Data Elements and play an important role to keep SAP BW data consistent by providing locking over data.
CHCKFL - Flag: Value in check tables (indicates some entries in master data table is still in use by other objects).
DATAFL - Flag: Value in dimension or available as attribute(some other infocube(s) is/are still using SID – master data table).
INCFL - Flag: Value is built into all inclusion tables (some other hierarchies is still using SID – master data table value).
In other words flag values indicate that data is still linked to other objects in BW.
SID Flag Matrix Revisited:
| SID | CHCKFL Flag: Value in check tables | DATAFL Flag: Value in dimension or available as attribute | INCFL Flag: Value is built into all inclusion tables | Remarks | Master data can be deleted with SID's |
|---|---|---|---|---|---|
| 0 | X | X | X | This entry appears if transaction data loaded before master data. | No |
| Number | - | - | X | SID is in use in Dimentation table of cube(s) or in Hierarchy | No |
| Number | X | - | X | SID is in use in Dimentation table of cube(s) or in Hierarchy and data is also in use in P or Q table (Master data table) | No |
| Number | X | X | X | Is in use in Dimentation table/ any active table of DSO | No |
| Number | - | X | X | Is part of attribute of another master data infoobject and also declared as Nav attribute. | No |
| Number | - | - | - | Yes | |
| Number | X | - | - | Data entered in master data table (or text table; infoobject with only text table) and not in use in any other tables | Yes |
| Number | - | X | X | If an infoset is created on top of infoobject, then SID's will be created for all infoobject included in infoset. | Yes |
How deletion process normally works is:
If an entry is in use by some other object then we are not allowed to delete that master data (along with SID entries). So there are 2 approach that we can follow-
- Identifying all valid master data (from source system) and then perform selective deletion.
- Delete master data: this will only delete records which are not in use/locked by other objects. Later we can check the log to find the where use list.
In below example we are going to follow the 2nd process (although both process are similar):
- Check the SID table of that info object. (in this example we are taking info object Media Type ).
Comparing entries of SID table with FLAG matrix we can conclude that 4 entries were not used in any other object except own Master data. So 4 entries can be deleted.
- Now right click on the master data and select master data:
- Select “With SIDs” this will delete master data along with respective SID entries from SID table.
- After checking the locked status of data system prompt:
- As deletion of locked/ used entries are not allowed, system prompts us again. Select Delete.
- Master data deletion process start a background process”
Note:- Only unlocked entries were deleted along with SID’s.
- Now check the log entries of above process. Log will reveal how many entries were deleted and lock status of remaining entries. Go to TCode- SGL1.
Details:
Object - RSDMD
Subobject - MD_DEL
User - UID used for deletion activity.
- Analyzing the log entries:
- Top 10 lines says that SID’s are in use in P table/ Master data table of an infoobject (where “Media Type” is defined as navigational attribute). As 10 entries are already in use, job ignore those entries.
- 4 entries got deleted (SID 14, 15, 16 and 17). These entries are only inserted into Media Type master data and are not in use in any other object.
Note:- TCode: SGL1 can be used to find other application log process as well. We can even use ST14/DB02 to identify top master data tables.
Feel free to leave your comment(s)
***This article is a continuation of another article "Analysis of SID tables in SAP BW "http://scn.sap.com/docs/DOC-39856
- SAP Managed Tags:
9 Comments
