Wednesday, December 26, 2007

Optimize FI-GL Processing with Intelligent Lookup Tables and Pseudo Delta Loads

SAP BW 3.x does not allow you to run two delta loads back to back. Learn a workaround involving DataStore objects and a pseudo delta load that allows you to do this. You can use this functionality to improve system performance in processing high data volumes when implementing complex business logic.


Key Concept

An intelligent lookup table is a DataStore object with data required as a lookup when processing high data volumes. An intelligent lookup table can optimize data processing in a multi-layer business warehouse setup. Usually it is a subset of a large data set containing only the records you need for a particular data load. This subset contains only relevant details corresponding to business logic related to your project. An example of an intelligent lookup table is a DataStore object containing accounting document numbers mapped to vendor codes and purchase order (PO) numbers. During each delta load, this DataStore object contains only those records that relate to the data set that you’re currently processing.

A purchasing group in a multi-billion dollar corporation wanted to improve its operational reporting capabilities by daily updates of purchasing performance indicators and management dashboards. The operational purchasing group sources and purchases all non-production goods and services including external resources and consultancy. I helped to implement a multi-layer BW data model to fulfill its needs using intelligent lookup tables and a pseudo delta approach.

This setup provides a capability to implement complex business logic when moving line items to the second level. At the same time, it substantially speeds up data processing time — 30 million transaction lines used to run for three days in the old setup, but with the intelligent lookup tables it took 26 hours.

The project requirements included complex filtering logic for invoices and purchase orders. The project team and I used a multi-layered BW setup to ensure the solution is scalable and reusable. First, all documents are loaded to the first level because projects other than this one might use them. Second-level DataStore objects contain only project-related documents with certain business logic applied. On the third level, the BW system aggregates data in the reporting InfoCubes.

No comments:

Blog Archive