Expand +



Operational Reporting Using the SAP BW Operational Data Store

by Lothar Henkes | SAPinsider

January 1, 2001

by Lothar Henkes, Rainer Hvltke, and Kay Roberts, SAP SAPinsider - 2001 (Volume 2), January (Issue 1)

Operational reporting hinges on the storage of large volumes of highly detailed business information, and then making that information accessible to a wide variety of users - basically, anyone who needs details regarding a customer's account information, or a buyer's most recent purchase, or an account's most recent payment, and so forth. With mySAP Business Information Warehouse (BW) 2.0B, SAP offers an optimized way to go about this: the BW Operational Data Store (ODs).

     The ODs supports efficient operational reporting by enhancing multidimensional OLAP analysis. Multidimensional OLAP analysis is a "business-friendly" view of data, where report labels are in business terms and the data is analyzed using drilldown, slice-and-dice tools. For example, a retail manager can access revenue by department and store for the last four quarters for any given set of products. In addition, the ODs provides a critical data layer that allows for the consolidation, integration, cleansing, and transformation of data.

     Based on this technology, the ODs provides three major functions within a BW server. It offers

  • A place to cleanse data

  • A place to consolidate data from multiple source systems - both SAP and non-SAP - such as combining legacy and R/3 sales orders

  • A place to integrate data from multiple source system transactions, such as combining Sales and Finance data

     This article provides you with the details you need to leverage ODs to efficiently implement and maximize the operational reporting capabilities of your own BW implementation.

BW Basics

The SAP Business Information Warehouse (BW) is an integrated, ready-to-go data warehouse solution. Along with the ODs, it offers a metadata repository, intelligent extractors for collecting data from SAP and non-SAP source systems, a metadata manager, a data manager, and even a shiny, new Web-enabled query capability. BW includes a first-class query environment, including Microsoft Excel and Web interfaces. What's more, BW components are tightly integrated with the SAP Administrator Workbench and the new BW Business Explorer (BEx).

     BEx is a tool in BW that provides reporting on a single ODs Object or on one or many InfoCubes. The BEx also provides Web reporting capabilities. For general information on these and other SAP BW features, see

The Operational Data Flow in BW ODs

Figure 1 illustrates the BW architecture from the perspective of the data integration process. Highlighted in blue, you see the BW, and its relationship both to data sources and to systems it feeds. The graphic displays six major elements of the BW architecture:

  • Persistent Staging Area (PSA)
  • Metadata stored in the PSA
  • ODs
  • Transaction data
  • Master data, which parallels the transaction data
  • InfoCubes

Figure 1 Flow of Information in Operational Reporting Using SAP BW

     For details on the terminology of operational reporting with SAP BW, see Figure 2.

      In operational reporting, information flows from the data source to the Persistent Staging Area to be stored as master data, and simultaneously to the ODs (which stores ODs Objects) or InfoCubes (the data container for multidimensional reporting). Data cleansing and transformation between ODs Objects and InfoCubes allows integration and adjusts the granularity of the data analysis.

     The information is then available from BW to populate end-user reports and/or downstream systems.

How to Get Started with the ODs

There are four prerequisites for successful operational reporting with the BW ODs These steps provide the foundation for data warehouseing success in general, and specifically in operatinal reporting with BW.

PSA (Persistent Staging Area)

The PSA is the area in BW where transaction and master data from the various source systems are stored initially,m without modifications.

From the PSA, the transaction data can be loaded either directly to InfoCubes or to the BW ODs Master data is loaded from PSA into its respective Master Tables (i.e., Master Data Tables, Text Data Tables, and External Hierarchy Tables).

SAP BW ODs (Operational Data Store)

A data management layer that stores transaction data in a flat database tables. ODs data can be cleansed, transformed, merged, and/or consolidated with non-SAP data and/or data from other business processes.

ODs Object

An object that serves to store consolidated, integrated, and transformed transaction data at the document (or atomic) level. An ODs Object describes a consolidated dataset from one or more InfoSources. This dataset can be evaluated by a BEx Query or InfoSet Query.

An ODs Object contains a key (e.g., "document number" or "item"), as well as data fields that can also contain character fields as key figures ( e.g., "customer"). ODs Object data can be updated with a delta update into InfoCubes and/or other ODs Objects in the same system or across systems. In contrast to the multi-dimensional data storage of InfoCubes, ODs Object data is stored in transparent flat database tables.

SAP Remote Cube

Virtual cubes in BW that support "most recent" reporting by connecting directly to the OLTP system at the time a query is executed.

Info Set Query

A tool that provides reporting on one or multiple ODs Objects. InfoSet Query provides Web reporting capabilities (previously known as ABAP Query or SAP Query).

Figure 2 SAP BW Terminology
  1. First Things First: Develop Good Business Requirements
    Data warehouses, including the BW, must focus on the business and what is needed to manage the business. Business requirements are the base for developing a successful data warehouse. With this solid foundation, the opportunity for a positive impact is enormous. (All this, of course, begs the question, why is requirements analysis so often a low priority?)

  2. Clean Up the Master Data
    Check that your master data is clean and complete (with no missing data that is required for reporting). Master data is a common weak link in data warehouse operations, and many projects lose time and credibility because of deficient master data. Master data is important because users need up-to-the-minute values of slowly changing dimensions. Even if your OLTP system maintains a conformed data environment with shared master data, there can still be a problem. We recommend that business managers be accountable for accuracy of the master data under their control.

  3. Perform Validation Testing on All Source Systems
    Be sure transaction data is valid by performing thorough validation testing on all source systems that feed the BW. By doing this, you can verify both their configuration and extraction processes, and ensure that the data meets your organization's business requirements. Avoid the temptation to use old requirements. The validation testing process is quite straightforward; however, many organizations do not allow enough time to do an adequate job of testing. Consequently, users find that reports are incorrect, and, of course, blame the data warehouse. 4. Ensure a Reliable Refresh Process A reliable refresh process means that new and changed data is loaded to the data warehouse on schedule, so that users see updated information when they expect it. This may be hourly, daily, or weekly, depending on business requirements, data volumes, and the available load window. It takes time and teamwork to establish a smooth refresh process. A well-managed team will automate the process, and will be able to plan for likely problems, such as vacations and peaks in data volumes.

  4. Ensure a Reliable Refresh Process
    A reliable refresh process means that new and changed data is loaded to the data warehouse on schedule, so that users see updated information when they expect it. This may be hourly, daily, or weekly, depending on business requirements, data volumes, and the available load window. It takes time and teamwork to establish a smooth refresh process. A well-managed team will automate the process, and will be able to plan for likely problems, such as vacations and peaks in data volumes.

How ODS Meets the Challenges of Operational Reporting

The four key challenges associated with operational reporting are the need for:

  • Detailed data n Recent or real-time data
  • Accurate, up-to-date status information
  • Access by many users

BW and ODS, working together, answer these specific, operational reporting needs as follows:

     Need for detailed data: The BW meets this need by supplying ready-made data models that match the granularity of the transaction data. These data models can be easily enhanced. SAP-delivered data models are stored in the BW metadata repository and are accessed via the BW Administrator Workbench. They reside in BW as InfoSources, InfoCubes, and Queries. Data models are shipped with Business Content and can be easily activated.

     Need for recent/real-time data: Operational reporting usually requires very recent, or even real-time, data. BW meets this need in two ways, so that you can select the best method for your situation. The first option is for data to be loaded to the BW very frequently. The second option is for end-user queries to be linked directly to ODS Objects2 and to an R/3 transaction.

     Need for status information: A common business requirement for operational reporting is non-numeric data that represents status; for example, an order status flag that has various values depending on the current process. The value might be "new," "confirmed," "delivery started," "delivery complete," and so on. Writing status information to a data warehouse can be tricky, because the system will need to overwrite the status values while new records are loaded for the same transaction. The ODS solves this problem by automatically overwriting certain character data type fields such as status flags. This feature saves space in the InfoCubes that are fed by the ODS, which improves the performance of end-user reports. Although InfoCube architecture is not designed for efficient overwriting of data records required by status fields, InfoCubes do support very good database inserts and reads.

     Need for access by many end users: By their very nature, operational reports are needed by a large number of users. For example, it is the daily task of Purchasing employees to check purchase orders or of inventory control staff to check material movements. This need can be easily expanded to all departments within a company, and to many users (e.g., field sales) who need remote access. The BW allows you this kind of flexibility with its support for Web-enabled queries. These queries are accessed via any Web browser and require little training for end users. As an added benefit of using Web reporting methods, the IT department is not required to maintain any extra components on users' desktops.

Important Design Considerations for Operational Reporting with SAP BW

With the foundation laid for effective operational reporting, the design of your BW system builds on this base. The recommended design for BW operational reporting includes these components:

  • PSA (Persistent Staging Area)
  • ODS (Operational Data Store)
  • Business Explorer (BEx) Analyzer queries, with links to an SAP source system
  • InfoCubes (optional) for management reporting

The following sections provide checklists that highlight the features and considerations to keep in mind as you implement this design for operational reporting.

Optimize Data Loads and Extraction

Fine-tune the extraction process: You only want to extract data that is actually needed for reporting now and in the likely future. Do not extract unnecessary internal system fields. A lean extraction is a fast extraction that minimizes the performance impact on your source system.

Plan for initial data loads: Plan for large initial data loads followed by ongoing delta loads. Depending on the application-specific extractor capability, delta loads are conducted under program control (i.e., under control of the extractor program, with no manual steps needed) or by selecting data via the load scheduler. This does not require any specific customizing.

Load the master data: Load your master data to the BW master data tables prior to every load of corresponding transaction data. This speeds up the transaction data loads significantly, and ensures that the master data in end-user reports matches the transaction data. Otherwise, end users will see values on their queries without text, attribute values, and/or hierarchy assignments.

Make the Best Use of PSA, ODS, and InfoCubes

Load data to the PSA: The PSA is used for temporary inbound storage, so during or after every load of new data, make sure you delete old, unnecessary data. Remember, data cleansing and transformations in order to apply business rules are not done at the PSA. The PSA is simply where transaction and master data from the various source systems is stored without modification. After the data is loaded, use the PSA to perform initial quality checks on the new data. If you find it is loaded into the ODS correctly, there is no need to keep it in the PSA.

Load data from the PSA to the ODS: Load transaction data from a PSA table to the BW ODS Object in order to perform all global data cleansing and transformation to enterprise-wide values. Global data cleansing is a transformation that applies throughout the entire data warehouse. If business requirements call for integrating data from more than one business process, you can load the data into a second-layer ODS Object that is integrated with the first layer. For example, if you want to integrate order and delivery processes, a single, second-order ODS Object would contain both sets of data, but the basis for the combination would be two ODS Objects: the order ODS and the delivery ODS.

Load transaction data into InfoCubes: You can also have transaction data loaded into InfoCubes for management reporting, if desired. (This step is optional and not always needed.) Recall that InfoCubes are the central BW data containers that are put together according to a star schema. For operational reporting, we recommend that most reporting be done directly against the ODS Object(s) and that InfoCubes be used to store summarized data. This summarized data is suited for management reporting, where less detail is required. With this design, one big benefit is that both the detailed operational reporting and the corresponding management reporting use an identical data source, providing consistent numbers with the same master data and time reference. In addition, InfoCube technology provides full multidimensional capabilities.

Use the Right Tool for Reporting

Use the Business Explorer Analyzer for ODS and InfoCubes data: Reports from the data stored in the ODS and InfoCubes are produced using the BW's Business Explorer Analyzer (BEx). True multidimensional reporting is done using InfoCubes. With the ODS, document-level details can be stored and then analyzed with the BEx reporting tool.

Use SAP Remote Cubes for real-time data: BW offers the option of reporting against virtual cubes (called SAP Remote Cubes; see Figure 2), which support "most recent reporting" by connecting directly to the OLTP system at the time of query execution. This can be used to meet the requirement for real-time data. For example, a query on ODS document-level data is linked to an SAP Remote Cube query, which does not physically store the data, but extracts it only during query execution.

Use InfoSet Query for reporting on multiple ODS Objects: Reporting on multiple ODS Objects is not supported with BEx queries. This means that when the reporting requirements need a join of multiple ODS Objects, another reporting tool is used: InfoSet Query.

From these checklists, it is clear that different situations call for very different approaches to storing information. The major differences among the three types of data storage methods - ODS Objects, InfoCubes, and SAP Remote Cubes - are highlighted in Figure 3.

  ODS Objects InfoCubes SAP Remote Cubes
Data Type Transaction Data Transaction Data Transaction Data
Data Storage Flat database tables Multidimensional data storage Data not physically stored in BW
Features Supports overwriting of document records and tracks changes for delta update of InfoCubes, even if the source delivers no delta information. Used for management reporting and strategic questions, where less detail is required. Especially useful for aggregated data from multiple ODS objects. Good for database inserts and reads. Used when information (e.g., status) of document is constantly changing. Not designed for the efficient overwriting of data records that is required by status fields. Used for most recent reporting
Figure 3 Advantages of Three Data Storage Methods in BW

Features in Operational and Management Reporting

A variety of features are supported by ODS and InfoCube reporting, including:

  • Variables
  • Navigation
  • Display of master data attributes
  • Hierarchies
  • Ability to drill through to other InfoCubes and ODS Objects, and to R/3
  • Web reporting
  • Currency conversion
  • Authorization checking

The next sections outline the benefits of BW ODS in operational reporting for SAP's FI and B2B components.

Operational Reports for Financials

An operational reporting scenario in FI (Financials) might display a typical Accounts Payable report with Purchase Order (PO) information, as shown in Figure 4, where you can view order quantity and value, grouped by status of the POs.

     BW 2.0B provides improved coverage for detailed Accounts Receivable and Accounts Payable operational reports. The new SAP-delivered data models (called Data Sources) enable extensive analysis on the line-item level, including detailed invoice, dunning, and payment information.

     Due to the large line-item data volume, the new Data Sources provide a change (delta) mechanism. After the initial load of existing data into BW, only the changed records are transferred (on a daily basis, for example) to BW. The SAP-delivered extractors can be extended to include additional information from standard SAP database tables.

Figure 4 PO Information Available from SAP F1

Operational Reports for B2B Procurement

Figure 5 reflects a common B2B Procurement report with detailed Purchase Order information, such as order number, cost center, and GL account number. SAP Business-to-Business Procurement uses ODS Objects for reporting in two scenarios: in the procurement process, and in bid invitations. In this example, we will go through the procurement process.

     Document data for purchase orders, confirmations, and invoices is loaded into BW from the procurement process at the account assignment information level of detail. Since the status of these documents - which is itself reported on - changes continually, the ODS Object has been chosen as the technical storage structure. The document data is stored here, one ODS Object for every document type.

     In addition to single document reporting, users will often need aggregated data as the starting point of their analysis. The user may be a strategic buyer, for example, who, in addition to accessing a single document, will also want to look at issues like order volume bundling. He needs to evaluate what he has procured from particular vendors over a long period of time, how intensively particular catalogs have been used, and so on.

     For this purpose, the three ODS structures are updated from the ordering process into an InfoCube. This InfoCube no longer contains all the detail data (it does not contain the order number, for example, only the status) and is therefore more suited to strategic questions. If, however, another user - an analyst -wants to pursue an interesting result from her InfoCube analysis at the order document level, she can jump from the aggregated data of the InfoCube to the reports on the order documents in the ODS Object.

Figure 4 PO Information Available from SAP F1


As you can see, there are many options in configuring SAP BW to meet your business reporting needs. But by taking the steps we have described - from simply defining your business requirements to using the right reporting tool - your users will see minimal impact on performance, while gaining improved access to the highly detailed reporting information available in BW.

     For further details on the BW Operational Data Store, see the white paper at Select Product Background ->BW 2.0B ->BW Operational Data Store White Paper.

Lothar Henkes began his work at SAP in 1990 as a consultant for Logistics. From 1993 to 1995, he worked for SAP America in Foster City as a consultant and project manager. He is now part of the BW Product Management team and is responsible for the rollout of new product features.

Rainer Hvltke is a developer in the Warehouse Management Development Group of SAP's Business Information Warehouse and is responsible for the ODS development project. He has over nine years of SAP experience and has worked in the MM Purchasing and Logistics Information Systems development groups.

Kay Roberts is a Platinum consultant in SAP's Business Information Warehouse Regional Implementation Group within SAP's BW Development organization, with over six years of SAP experience. She has held positions in systems, consulting, training, marketing, and sales.

An email has been sent to:

More from SAPinsider


Please log in to post a comment.

No comments have been submitted on this article. Be the first to comment!