A data warehouse is successful only if the delivery of information can be trusted on an ongoing basis. Companies need to be confident that key corporate data is accurate, especially in light of recent legislation in the U.S. and Europe to hold businesses to a higher level of accounting and reporting standards.

To this end, SAP initiated its Data Consistency Project (see the sidebar below). As part of this project, SAP has provided automatic and manual check tools to help ensure the consistent delivery of data to end users. I'll describe these tools and show how you might use them.

In most cases, you use these check tools to resolve unusual, unpredictable problems — for example, correctness of master data or hierarchy tables. You might want to use them to routinely check the integrity of your system in situations where you have large changes in your data warehouse or a lot of inexperienced users. BW has two main check tools: transaction RSRV and the aggregate check tool.

The RSRV Check Tool

The most important check tool is transaction RSRV, which allows you to analyze and repair BW objects. With it, you can check your complete data model from both a BW functional and technical perspective. It guarantees the referential integrity of the star schema models for your InfoCubes, and it makes sure that all underlying tables are correct and that the data model is consistent from a technical and an administrative perspective.

Additionally, you can check many of the underlying database tables, database parameters, and system parameters using RSRV (Figure 1). It is updated by most support packages. If you execute RSRV before and after installing a support package, you might get different check results and references to different SAP notes. This is important for technical parameters, particularly from the database perspective, as they can change often based on updated information. Each check that is implemented in RSRV contains additional information. You can right-click on the check itself to get documentation via the Description option (Figure 2). This tells you what the check is and, in most cases, if a repair option is available and what that repair accomplishes.

Figure 1

Transaction RSRV allows you to check database tables and parameters as well as system parameters

Figure 2

The Description option explains what a check does and what options are available

As you can see in Figure 3, SAP BW 3.x has the ability to do combined tests based on a particular area, such as master or transactional data for one InfoCube. You can run a series of inter-related checks on one or all objects without having to individually select all the checks and pre-select all objects or all InfoCubes for every check. This provides you with an easy process to select the checks you wish to run, and it allows you to select all of them at once. This gives you more thorough checking and also reduces the time for scheduling and administrating the RSRV check process.

Figure 3

Transaction RSRV analysis and repair using combined checks

• Master data — data structure and value consistency
• Transaction data — InfoCube Dimension, database, and data consistency; ODS object key relationships; and so on
• Hierarchy — definition and data consistency
• Aggregates — definition and data consistency
• PSA and documents

Technical checks include:

• Database parameters and partition integrity
• Database indexes, statistics, database, and star schema information

Figure 4 shows how you can schedule these checks.

Figure 4

Schedule checks by first going to RSRV and following the menu path Edit>Test package>Schedule (1). In the pop-up window (2), enter a description for the check and click on Execute. Then enter the appropriate times on the subsequent screen (3).

Repairs Using RSRV

When a check process identifies an error, you have three levels of feedback from the log: green, yellow, and red. Green means that everything is fine. Yellow indicates there could be a problem; clicking on the monitor item in yellow identifies the issue that you may or may not want to fix. Red indicates that an error was found based on the rules implemented in the check tool.

Automatic repairs are available whenever possible, and they can be started directly from the check results. These repairs are based on the results stored in the application log. Do not click on the Repair option without first analyzing the results. Some repairs require a significant amount of time, and you might lose some of the diagnostic information, making it difficult to track down the source of the problem. Use the application log to diagnose the problem and find the cause. You might find that the cause is a process problem that the Repair option won't fix.

You might also find functional issues based on design or configuration that would require a significant change to the system to repair. You would have to analyze the potential impact of this, as there might be a valid reason for keeping the design model or configuration value intact.

Aggregate Check Tool

Available with BW 3.0 onwards, this tool checks the data consistency of your aggregate based on the InfoCubes or aggregates from which it was built. If you are using many aggregates and have a lot of data changes, it is important to make sure they are all consistent, or else longer run times could result. The aggregate check tool helps to provide confidence in the aggregate process. It is available via the ABAP program RSDDK_CHECK_AGGREGATE, and therefore it can be executed in the foreground or background. You can also schedule it in a process chain via the ABAP process type, and you can configure the automatic aggregate check tool from within the Aggregate Definition screen. This allows you to schedule automatic aggregate check processes when you perform aggregate maintenance for an InfoCube.

Three aggregate checking modes are available:

Complete check (mode A): This mode rebuilds your selected aggregates into internal tables and then compares them to the results in the checked aggregate against which the check program is built. The new internal table is not physically stored as an aggregate in the BW system because the internal table is dropped after execution. The execution time for check mode A is the same time needed to actually build the original aggregate. So, if you have an aggregate that takes hours to build (if you're doing a full rebuild), it will take hours to build this check table because you are really building the same table as if it was an aggregate itself.

Check of key figure sums (mode Q): This mode summarizes aggregates and sources (InfoCube or parent aggregate) for all characteristic values. It then calculates the sum for all the key figures to make sure that the sum of the checked aggregate and the sum of the parent is the same. This is similar to a file checksum approach. Mode Q is three to four times faster than mode A.

Check aggregates (mode C): This mode creates new aggregates summarized over all characteristics (except package and unit dimension). It uses a fixed-value combination, so it is a very fast process. In the Maintenance for Aggregate screen in the Administrator Workbench, you'll see that additional aggregates are generated. Manually delete these after the check is complete. Although this mode is very fast, additional aggregates are created and have to be maintained.

Figure 5 shows all the options for the aggregate check tool. The Parent Check option checks that the parent aggregate is consistent (if there is a parent-child relationship), and the Write Log File option writes a log file — important if you execute the ABAP program in the background.

You can execute the ABAP program online or in the background. SAP recommends that you set up selection options via a variant and regularly execute the aggregate check tool in the background using mode C for all your critical aggregate and InfoCube combinations. The best time to do this is during off-peak hours.

Figure 5

Available options for the aggregate check tool

A new option in BW 3.0B SP9 or 3.1 SP8, Selection of Automatic Aggregate Check Mode in the Extras menu in the Maintenance for Aggregate screen, allows you to schedule these checks automatically within the aggregate maintenance processes. This option is not enabled by default, as the additional execution time required by these checks affects performance.

To set up this option, first pick the check mode (Figure 6). The modes here are listed by their text values, not by C, Q, or A. Then choose when you want to execute the check. Be aware of what else is happening when you run these checks, especially with complex, parallel loading scenarios. You also have the option to specify execution after a change run or roll-up completes, or after a data deletion. This ensures that your aggregates stay consistent once you've deleted data.

Figure 6

Selections to automatically schedule aggregate checks