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.
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 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 www.sap.com/bw.
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
- Transaction data
- Master data, which parallels the transaction data
||Flow of Information in Operational Reporting Using SAP
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
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
|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).
||SAP BW Terminology
- 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?)
- 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
- 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.
- 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
- 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
Optimize Data Loads and Extraction
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.
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.
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
Make the Best Use of PSA, ODS, and InfoCubes
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.
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
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
Use the Right Tool for Reporting
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.
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.
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.
||SAP Remote Cubes
||Flat database tables
||Multidimensional data storage
||Data not physically stored in BW
||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
||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:
- Display of master data attributes
- Ability to drill through to other InfoCubes and ODS Objects, and
- 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
||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.
||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
For further details on the BW Operational
Data Store, see the white paper at http://service.sap.com/bw.
Select Product Background ->BW 2.0B ->BW Operational
Data Store White Paper.
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
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.