With so many reports that must be run daily, it can be difficult to determine the best way to track jobs in BW. The author shares her success with creating a custom table to track InfoPackages being transported to production. The table provides users with access to vital and accurate information about the status of their jobs.
After successfully implementing our first phase of BW (Accounts Payable), my team began to wrestle with how to best manage and monitor jobs. With a well-established data warehouse supporting over 1,000 jobs that ran daily, weekly, and monthly, we fully appreciated the value of job tracking and knew how important it would be to accurately follow loads as they became increasingly complicated and numerous. We began by making use of the many capabilities of the BW Monitor, and we implemented all the available cubes from BW Statistics, but we still found some gaps. We were unable to associate a set of load jobs with a particular set of users, for example, and we could not know for sure if a job finished successfully after loading zero records.
We set out to find a way to provide our users with answers when they called asking about the status of their jobs. For instance, how could we determine with certainty that all the A/P data loaded successfully, and that the one job sitting out in the failed queue was not actually for the particular user calling? Moreover, for those jobs that failed to run, we wanted to be able to find specific problems such as difficulties activating the ODS data. In addition to monitoring which jobs did and did not run, we wanted to be able to identify anomalies such as when a job suddenly started running more slowly than usual, which could indicate a change in the source that we needed to be aware of, or if fewer records were loading than we expected.
The solution was to create a new custom Z table to track all the InfoPackages being transported to production. We constructed the table also to provide other information related to the job such as the implementation date, project name, frequency, comments, source, and more to help us recognize the InfoPackage and why the data is being loaded. In addition, our customized table lets us monitor the minimum and maximum number of records we expect to load in each job as well as the minimum and maximum number of seconds we estimate it will take to run the job. Although there were other options such as improving the BW master data and InfoSources related to OTCT* objects in BW Statistics, we decided to go in this direction.
Using our method, every morning we run a report that joins our custom table to the data from the BW Monitor and it gives us
- The last date each job ran
- The number of records loaded in each job
- The number of seconds an individual job ran
- A warning alert if either the runtime or number of records loaded is not within anticipated ranges
In addition to this information, using the free characteristics sourced from this custom table, our users can reorganize the report to find out if all the jobs for their particular project have completed successfully, if all the data from a particular source has been loaded, and if all the jobs created by a particular business group have completed successfully. Our table enables users to group their InfoPackages by an unlimited number of methods not available through the monitor.
Steps to Implement ETL Daily Run Report
Step 1. Create and populate the custom Z table with a record for every InfoPackage to be tracked. You can have any number of columns, but you must include a column to store the InfoPackage ID to join it to the monitor data. Some examples of additional table columns that may be useful include job name, project name, load frequency, technical contact, source, min/max average number of records, and min/max average load time.
Step 2. Load the data from the custom table into a BW ODS. Note that the data is overwritten every time you load.
Step 3. Create an ODS based on the RSREQDONE table, and schedule an InfoPackage to load it every morning when the nightly run is complete. BW automatically stores all InfoPackage run statistics in a table called RSREQDONE. If you set the aggregation setting of the Run Date field in the ODS to be MAX, each record shows only the last time that particular job ran. We toyed with the idea of keeping history in this table, but that made it more difficult to run a quick report showing only the last run. Besides, if a person is looking to see how a particular object was loaded historically, the BW Monitor is sufficient. The status field, which appears in the SAPGUI as a stoplight, can easily by translated to text by using a formula like:
IF( REPLACE_ALL( ‘’, ‘’, REPLACE_ALL( ‘@’, ‘’, Status ) ) = ‘0A’, ‘RED’, IF( REPLACE_ALL( ‘’, ‘’, REPLACE_ALL( ‘@’, ‘’, Status ) ) = ‘08’, ‘GRN’, ‘YLW’ ))
If you do not translate the status field, it appears as a code like @0A when you load the ODS.
Step 4. Create an InfoSet Query that joins the two ODSs on the InfoPackage ID. The report in Figure 1 is based on the InfoSet Query. The Range Warning field is a formula comparing the ranges from the custom Z table to the actual numbers coming from the monitor statistics.
Figure 1
Another Way to Create an ETL Daily Run Report
Karen Olsen
Karen Olsen, Business Intelligence Analyst/Developer for W. W. Grainger located in Lake Forest, IL, has been working in the data warehousing field for the past six years in various roles. Her current responsibilities at W.W. Grainger include implementing SAP Business Warehouse and converting an existing data warehouse to the new BW environment.
If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.
