Has a user ever called you about a reporting performance problem even though all your scheduled tasks ended properly? Or, has a reporting user complained to you about obviously false data in a report? More and more important decisions are being made based on BW reports, but problems with query performance and data correctness make your BI installation less reliable (and your job less enjoyable).

For example, what about the case where an InfoPackage for loading data into an InfoCube ends properly, when the data in the InfoCube is visible, but either the performance is nasty or the data itself is buggy due to a failure in processing or booking data into an InfoProvider without causing a short dump? Your manual checks, process chains, or third- party scheduling tool do not spot the problems.

I recently faced a problem where the routine for automatically calculating the month (0CALMONTH) from day (0CALDAY) in one of my update rules did not fill the month correctly (initial value only), but did not trigger an error message. A user spotted that bug because the total of that query, reporting the last six months of billing data, was too low. Data from the last five days had to be repeated from the PSA after the SAP support folks fixed a conversion problem.

BW’s technical content, in the form of Excel workbooks, allows administrators to monitor loads and CPU time, but none of it would have allowed me to spot the problem before the user ran the report. What you need is an audit workbook, which bundles all your future test queries, even Web-based ones. Unlike BW’s technical content, which works in terms of quantity (number of records, deleted records, query execution time, and so on), workbook auditing gives you an overview of data correctness or estimations about data growth, answering questions like, “When will my storage capacity become insufficient?”

Figure 1 shows the results of such a query. The workbook I used contains 13 audit queries to provide an overview of hard disk capacity and estimations.

I’ll show you how to create an audit workbook and automate this functionality to check for data correctness. Then, I’ll demonstrate how you can audit BW metadata and query performance. The following instructions apply to BW 3.0B, although the process works with BW 2.x systems.1

Figure 1

Audit workbook query results

Auditing for Data Correctness

Building an audit query to spot faulty uploads to an InfoProvider like the one in my example is easy. Let’s start with an InfoCube. Data stored inside an InfoCube is always connected to a request ID, which is automatically entered into one of the InfoCube’s dimensions. If data is loaded into the InfoCube on a daily basis, there should be no key figure values inside your InfoCube without a request ID. Values related to the “#” (not assigned) key figure will be false. All you have to do to audit this false data is create a MultiProvider (or MultiCube if you are working in BW 2.x), reporting only on the Request ID that covers all your important InfoCubes. The basic steps to create such a MultiProvider follow:

1. Select at least one characteristic of your choosing and assign it to a dimension.
2. Select all available key figures and activate the InfoCube.
3. Use this InfoProvider to create the first audit query.

Once you’ve activated the MultiProvider, proceed with the following steps. They create a query to track problems that might not be reported by the Administrator Workbench Monitor. You can apply this technique to other types of examples.

1. Create your query in Query Designer. BW automatically inserts the Request ID and InfoProvider in the Data Package dimension (Figure 2).
   
   
   
   

   Figure 2

   BW automatically fills the Data Package dimension for your query
   
   
   
2. Restrict the Request ID to find the false rows. Do this in either Query Designer or in an Excel worksheet to the value “#” (not assigned), as shown below.
   
   
   
   With luck, the restricted query result shows no entries:
   
   
3. Create an exception in Query Designer. This enables the missing link: having the query automatically send an alert via Reporting Agent’s exception reporting. Because you want to see any false value, you can use only one exception (Figure 3). If you expect negative values in your audited objects, choose a range with high negative and positive values. If you hide results, use the All option under Cell Restrictions. Now go to Administrator Workbench (transaction code RSA1), select Reporting Agent>Exceptions, and choose your query.
   
   
   
   

   Figure 3

   Choose only one exception to receive alerts
   
   
   To enable the alerts, complete the following steps:
   
   
   • Right-click on the query and select New Settings. Then enter a technical name and description and select the Parameters tab.
   • Drag and drop your exception, Follow-Up Actions, and Characteristics you’d like to see in your email from left to right. The result should be similar to Figure 4. Double-click on the email icon to set the recipients (internal SAP users or external users) and additional information about the detail level of alert you’d like to receive. Activate the package and leave the dialog.
     
     
     
     

     Figure 4

     Drag and drop the items you want to see in your email alert to the right side
     
     
     
   • Create a new scheduling package in the right frame of Administrator Workbench by selecting the document-shaped “create new packages” icon. Enter a description and save the dialog. Drag and drop your newly created exception package from the left frame underneath your schedule package. Right-click on your schedule package and schedule the run. The result shown in either your email client or SAP Office inbox looks like Figure 5 in the event of incorrect data.
     
     
     
     

     Figure 5

     An email alert showing incorrect data

You can use the process to create queries that track many types of false data fields coming from R/3 or your OLTP systems. For example, say you want an InfoCube to have today’s transactions for campaigns that ended today. You set the transaction date equal to the campaign end date. For the audit report, calculate the audit, subtract the value for the transaction date from the campaign end date, and set it to be always positive. Create the condition “only display the top 10” for the result, and create an exception for non-zero values. If all top 10 results are not zero, the exception reporting batch process informs you. You can also spot key figures that exceed the maximum possible value you are estimating for the reporting period.

Auditing Metadata and Performance

Wouldn’t it be great to have BW check its internal system on an automated base as well? SAP ships a nice tool, Analysis and Repair of BW Objects (transaction code RSRV) with BW.2 It helps you investigate many parameters (Figure 6) that are not displayed in BW’s Administrator Workbench.

Figure 6

The execution view of RSRV

It is easy to spot problems occurring beneath the Administrator Workbench or reporting surface using RSRV. My example presents a test for missing units in a fact table. You can apply this method to all your checks on important transactional or master data.

In BW, currencies or amounts are compounded objects. What is a currency value of 200? Is it USD or ASD or EUR? To get an adequate result for that unit, it is necessary to aggregate key figures. In my example, no red LED or warning occurred while loading data into that InfoCube. However, the fact tables contain initial entries without correlating units. This leads to an improper report.

Using RSRV is simple. Once you execute it, follow these steps:

1. Expand the tree view and drag and drop the desired test under Available Tests into the right frame (Selected Tests). Right-clicking on a test and selecting Description displays further information about it.
2. Enter requested parameters such as InfoObject or InfoCube name, if necessary. Do this by clicking on the selected test in the right frame.
3. Run the test by selecting the execute icon. A green light means that the result is OK, yellow is a warning, and red indicates an error. My example results in an error.

Let’s investigate the problem by selecting the display icon, which leads you to the Log monitor (Figure 7). Double-click on the red entry to see details in the right frame. Clicking on the detail icon (magnifying glass) in the right frame displays the false entries below.

Figure 7

The Log monitor view of a problem result

As you can see, it is easy to investigate problems with transactional data hierarchies or even database parameters (which usually show up with error or warning indicators). Using RSRV, you can create individual tests by just dragging and dropping tests from the left to the right side of the window. Once you’ve created your tests, you can automate them using these steps:

1. Follow the menu path Edit>Test package>Save. In the next dialog box, enter a description and package ID (technical name you can use later on). Click on the execute icon to save the test package.
2. Schedule the test package. You can do this manually by selecting Edit> Test package>Schedule, or you can use process chains with BW 3.x.
3. View the automated result. Select Application Log from the main menu and then Display logs>From Batch.

Whenever you execute RSRV, the results are already being refreshed in details. By using different test scenarios, you might run database checks on Mondays, master data checks on Tuesdays, and transactional checks on Wednesdays. It’s that easy to keep track of your system by using standard SAP technology. You can integrate all information from RSRV into queries as well. Just extract data from BW tables BALHDR and BALDAT.

Joerg Boeke

Joerg Boeke is an SAP NetWeaver BW solution architect and senior consultant working with BIAnalyst GmbH & Co.KG, with 19 years experience in SAP NetWeaver BW, having worked on it since SAP BW 1.2A. He offers significant expertise in the SAP NetWeaver BW reporting area, including design, data integration, data visualization, performance optimization, and the cleanup of existing SAP NetWeaver BW systems. He is the author of SAP BW 7.x Reporting - Visualize your data.

You may contact the author at Joerg.boeke@bianalyst.de.

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