As you load dirty data into BW, your load may fail. If not, the system assigns a master data ID (SID) to each field value and stores the record in the master data tables. The system never uses these values since they are really errors, but they remain in the SID tables and clutter the system.

These invalid entries in the SID tables generally do not cause problems until users decide to drill down on a characteristic. They then see all the entries in the SID tables (unless they select from possible entries in the InfoCube for the drop-down box, which is not always possible with a line-item dimension or an ODS, as I’ll discuss later). Depending on where the invalid value is on the SID tables, users might have to scroll through many invalid values before they see the value that they want to select.

You can apply very simple solutions when it happens to a regular characteristic such as 0CUSTOMER, 0MATERIAL, and 0VENDOR. However, SAP delivers no functionality to remedy this situation for time characteristics such as 0CALMONTH, 0CALDAY, and 0FISCYEAR (Figure 1).

Figure 1

Complete list of time characteristics

Figure 2 displays the first of a few hundred values of the SID table of 0CALMONTH when a user drills down on the 0CALMONTH time characteristic. Most business environments do not display the calendar months of years 1001, 1002, and so on. These records probably exist because of incorrect loads. It is extremely annoying for a user who wants to filter on a query to have to go through several pages of invalid calendar months.

Figure 2

The 0CALMONTH time characteristic including junk values

Option 1: InfoCube/InfoProvider Solution

The easiest solution to this problem lies in the InfoObject definition of the time characteristic InfoObject. Set Query Def. Filter Value Selection to Only Values in InfoProvider so that BW doesn’t display erroneous SIDs (Figure 3). This solution works very well for queries against InfoCubes and you can use it with BW Release 2.0 and up.

Figure 3

Set Query Def. Filter Value Selection to Only Values in InfoProvider

Option 2: ODS Solution

However, the first solution does not work when writing queries against an Operational Data Store (ODS) because an ODS has no dimensions. This setting does not apply to ODS. Thus, regardless of this setting, the F4 drop-down values of a query against an ODS still display all SID table records. In this case, you need to use the solution I’ll explain next.

As I mentioned earlier, for a regular characteristic InfoObject, you can perform simple operations to hide incorrect data. You can delete the invalid values in the master data tables by right-clicking and executing the Delete master data function in the context menu of a characteristic in the Administrator Workbench (Figure 4). Note that the system only deletes data that other objects do not use to clear all unused master data for that InfoObject. Unfortunately, the system does not offer that option for time characteristic InfoObjects.

Figure 4

Select Delete master data from the context menu

Option 3: Manually Delete Master Data

Therefore, your only choice to resolve this issue in the time characteristics is to use a manual program that deletes other master data: RSDMD_DEL_BACKGROUND via ABAP Editor, transaction SE38 (Figure 5). This option is available in BW Release 2.0 and higher.

Figure 5

Use program RSDMD_DEL_BACKGROUND to delete invalid SIDs

Execute this program and enter the following in the selection screen (Figure 6):

Figure 6

Enter these parameters while running program RSDMD_DEL_BACKGROUND

P_IOBJNM: InfoObject (e.g., 0CALMONTH)
P_CHABNM: InfoObject (e.g., 0CALMONTH)
P_DELSID: selected
P_DELTXT: unselected
P_OBJ: RSDMD
P_SUB: MD_DEL
P_EXT: BG_0CALMONTH
P_NOPROT: unselected

To make sure that the program executed correctly, open Application Log (transaction SLG1) and enter the following:

Object: RSDMD
Subobject: MD_DEL

See Figure 7 for the selection screen and Figure 8 for the log results.

Figure 7

Application Log selection screen

Figure 8

Log results for program RSDMD_DEL_BACKGROUND

This program checks the entire system for any used SIDs and then deletes the unused entries from all the master data tables of the InfoObject. This program searches for InfoCubes, ODSs, InfoObjects (compound and navigational attributes), aggregates, queries, and hierarchies.

See Table 1 for a list of the function modules that program RSDMD_DEL_BACKGROUND uses.

Prevent Inconsistencies

To prevent any inconsistencies resulting from deleting master data, I recommend performing an RSRV check (and repair, if necessary) for the list of InfoCubes and ODS objects that use the time characteristics (Figure 9). This checks that all records in the InfoCube have an SID and if not, it creates SIDs for any missing records. For InfoCubes, execute Consistency of the Time Dimension for an InfoCube. For ODS, execute Foreign Key Relationship of Reporting-Relevant ODS Object and SID Table Characteristics.

Figure 9

List of InfoCubes and ODS objects that use time characteristics

It is a good idea to run the master data delete (RSDMD_DEL_BACKGROUND) program periodically to clean up the master data in the system. I recommend that you write a shell program to serve as a call function to call another program —for example, perform (rname) in program (pname) — to provide RSDMD_DEL_BACKGROUND with the list of all the InfoObjects in the system. This way you can execute the program once for everything in the system. You can schedule this shell program to run regularly. You can get the list of all InfoObjects in the system from table RSDIOBJ, a directory of all InfoObjects.

Julie Hartono

Julie Hartono has more than six years of experience as an SAP BW consultant. Currently with Alpha Net Consulting, LLC, she has helped several large clients implement BW. Data conversion and extraction, development of custom DataSources, and data modeling are among her best skills. Julie received mySAP.com Delta Certification for Business Information Warehouse last year.

You may contact the author at julyhartono@sbcglobal.net.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.