Data Storage in BW
Unit Overview
This unit describes data storage in BW, and explains business-content aspects of maintenance.
Unit Objectives
After completing this unit, you will be able to:
• List the basic concepts of the Business Information Warehouse • Describe BI Content in the area of Enterprise Asset Management • Run different queries in Enterprise Asset Management
Unit Contents
Lesson: Basics ... 38 Exercise 3: Basics... 53 Lesson: BI Content Enterprise Asset Management ... 60 Exercise 4: BI Content Enterprise Asset Management... 69 Lesson: Working with Queries in Enterprise Asset Management ... 75 Exercise 5: Working with Queries in Enterprise Asset Management . 87
For internal u s e by C S C only For internal u s e by C S C only
Lesson: Basics
Lesson Overview
This lesson provides a brief overview of key concepts, the data model, and data flow in BW.
Lesson Objectives
After completing this lesson, you will be able to:
• List the basic concepts of the Business Information Warehouse
Business Example
You want to use the Business Information Warehouse (BW), to run analyses in all system components. Before you do this, you will learn about basic concepts and processes in BW.
Introduction
The Business Information Warehouse (BW) is a system that is independent of other SAP systems. It extracts data from diverse source systems (both SAP and non-SAP systems), restructures and reformats this data, and compiles it into efficient and user-friendly analyses.
BW can run as an independent system together with other SAP or non-SAP systems, or it can be integrated into an SAP ERP system.
For internal u s e by C S C only For internal u s e by C S C only
Figure 22: BW at a Glance
The cornerstone of BW is the BW server , which has preconfigured BI Content for the core areas of your company.
BI Content is preconfigured role- and task-based information models that are based on consistent metadata.
It provides a range of information that specific roles in a company require to perform their tasks. The Business Explorer (BEx) provides your company with flexible reporting and strategic analysis tools, to support the decision-making processes.
Technical Requirements
From a technical point of view, BW is part of SAP Netweaver Business
Intelligence (BI) and therefore is part of the integration and application platform.
SAP NetWeaver facilitates implementation of the Enterprise Services Architecture (ESA). It allows you to standardize business processes across technological boundaries, integrate applications required by your employees, and easily access structured information and format it in line with your needs.
For internal u s e by C S C only For internal u s e by C S C only
Figure 23: Business Intelligence within the framework of SAP NetWeaver SAP NetWeaver is the underlying platform for all SAP solutions. For example, the SAP Business Suite uses all key areas of SAP NetWeaver. The business solution SAP ERP is also based on SAP NetWeaver.
For internal u s e by C S C only For internal u s e by C S C only
Figure 24: Solution SAP ERP
Hint: It is worth differentiating between the license-, software-, and
hardware-specific views. The fact that the SAP ERP license includes SAP NetWeaver and, as a result, the Business Intelligence (BI) component does not necessarily mean that SAP ERP and SAP BW run on the same server. In most cases, SAP BW will run on its own server, primarily to optimize performance.
Basic Concepts: Data Flow
At runtime communication between the source system and BW is broken down into the following steps:
• Provision of source system data • Extraction of data from source system • Storage of data in BW entry layer
• Transformation of data to a consistent format • Loading of data to info provider
• Running of analysis For internal u s e by C S C only For internal u s e by C S C only
Figure 25: Data flow - general view
Data that logically belong together are available in the source system in the form of DataSources.
A DataSource comprises a number of fields that are provided in a flat structure (extraction structure) for data transfer to BW. It also describes the properties of the corresponding extractor for data transfer to BW.
An InfoSource in BW describes the quantity of all available data of a business transaction type (such as Cost Center Accounting). It consists of a number of InfoObjects which are created by a conversion of the source fields in the DataSource.
An InfoObject is a business evaluation object. It is a superordinate term for characteristics, key figures and units.
InfoProvider is the superordinate term for BI objects that represent data views or
into which data is loaded. This data can usually be analyzed using BEx queries. Examples of InfoProviders include InfoCubes or DataStore objects (formerly known as ODS objects).
A Query is an analysis based on an InfoProvider. For Queries multiple front end types are available (f.ex. MS Excel spreadsheet or web template).
For internal u s e by C S C only For internal u s e by C S C only
Figure 26: Data flow within the source system
When a business object is created or changed in the source system (f.ex. a maintenance order), the data are not directly written to the DataSource. The data is first stored in the application tables for use in the source system. In parallel, it is transferred to the communication structures and from there to so called
restructuring tables. From the resctructuring tables the data will be written to the extraction structure of the DataSource.
The extraction structure provides the DataSource with data of a certain business context. It contains the quantity of the fields, which are meant to be generally available in BW.
An extractor is a method (for example, a function module) for loading data from source system datasets to a DataSource extraction structure.
The transfer structure is the structure in which data from the source system is transported into BW. It represents a selection of the fields in the source system's extraction structure.
Caution: Note that both the data model and data flow in Releases BW3.x
and SAP NetWeaver 2004s BI (BW 7.0) differ, even as regards basic concepts such as DataSource and InfoSource.
The following figures give you a rough understanding of the differences between BW 3.x and BW 7.0.
A migration from the 3.x data flow to the 7.0 data flow is possible. For more information, refer to the SAP Help Portal (area for SAP Netweaver) or the relevant BW training course (f.ex. BW310 or BW350).
For internal u s e by C S C only For internal u s e by C S C only
Figure 27: Data flow 3.x (1)
The DataSources and InfoSources used in the 3.x data flow differ from those used in the 7.0 data flow.
The DataSource must be replicated from the source system to the BW server. A Persistent Staging Area (PSA) is the BW entry layer in which inbound data is saved transparently in its original form.
An InfoSource always represents a quantity of InfoObjects that logically belong together. The structure in which they are stored is called a communication
structure.
The use of InfoSources in the 3.x data flow is mandatory.
For internal u s e by C S C only For internal u s e by C S C only
Figure 28: Data flow 3.x (2)
Transfer rules are used in the data flow 3.x and are defined in BI for harmonizing
or cleaning up data from the transfer structure to the communication structure. The prerequisite is that one or more DataSources are assigned to an InfoSource, though the transfer rules are defined for each DataSource. This means that transfer rules are DataSource-specific, and consequently source system-specific.
Update rules are used in the data flow 3.x to update the data from the
communication structure to the data target assigned to it.
InfoCubes are data containers used for reports and analyses in BW. They contain
ordered characteristics and key figures, depending on their size. These are referred to as InfoObjects in BW.
The dimension is a set of characteristics whose content is grouped together under a common superordinate term. If the dimension contains a characteristic whose value already uniquely defines the values of all other characteristics from a business point of view, then the dimension is named after this characteristic.
For internal u s e by C S C only For internal u s e by C S C only
Figure 29: Data flow 7.0
The data flow 7.0 is based on a different technology and is more flexible than the data flow 3.x.
Instead of transfer rules and update rules it contains so called Transformations. The data will be transferred via Data Transfer Processes.
Within the 7.0 data flow, the use of InfoSources is optional.
For internal u s e by C S C only For internal u s e by C S C only
Extractions are scheduled and implemented at specific intervals by BW. This
means that data is not updated online between the source system and BW. As a result, data is obsolete if long periods of time elapse between extractions. The InfoCube 0PM_C07 (maintenance orders: scheduling) is part of the BI Content for Enterprise Asset Management. The following figure shows the data flow between the source system at the bottom via the structures of the BW data warehouse up to the query offered by the BW front end (called the Business Explorer - BEx):
Figure 31: Data Flow Using InfoCube 0PM_C07 as an Example
Basic Concepts: Data Model
The sections outlined below use a Sales and Distribution InfoCube to illustrate a self-contained business dataset that is stored in BW.
For internal u s e by C S C only For internal u s e by C S C only
Figure 32: InfoCube in Sales and Distribution
The InfoCube displayed in the graphic above contains three dimensions: Time, customer, and material.
Each dimension has its own dimension table.
The dimension table groups together the characteristics that logically belong together (the customer name and region belong to the customer dimension, for example). Adhering to this design criterion ensures that the dimensions remain largely independent of each other and that the data volume of the dimension tables remains small, which helps optimize performance.
From a technical point of view, several characteristics values are mapped to an abstract dimension key (DIM-ID) to which the values in the fact table relate. The characteristics selected for an InfoCube are distributed to InfoCube-specific dimensions when the InfoCube is created.
For internal u s e by C S C only For internal u s e by C S C only
Figure 33: Dimension Tables in Sales and Distribution The fact table contains the InfoCube's key figures.
Fact tables and dimensions are linked with each other by identifiable abstract numbers (IDs), that are in the key part of the relevant database table. The key figures of the InfoCube are related to the characteristics of the dimension. The characteristics define the granularity (the fineness) of the key figures in the InfoCube.
Fact tables and dimension tables are relational database tables.
Figure 34: Fact Tables in Sales and Distribution
For internal u s e by C S C only For internal u s e by C S C only
The InfoObjects are grouped in the InfoCube according to the star schema principle, that is, there is a (large) fact table that contains the InfoCube key figures, and several (smaller) dimension tables surrounding it that contain the InfoCube's characteristics (see the sections below).
The characteristics point to the master data and its attributes and text descriptions. All InfoObjects (that is, characteristics with their master data and key figures) are available to all InfoCubes, unlike dimensions, which represent the special organizational structure of characteristics in an InfoCube.
The BW master data is transferred by uploading it from the source system. It can be enhanced with other data from external systems.
Figure 35: Star Schema
Further concepts
A DataStore object (ODS) is used to store clean, consolidated transaction or master data at the document level (basic level). This data can be analyzed in a BEx query. A DataStore object contains key fields (such as document number and item) and data fields that can contain key figures and character fields (such as order status and customer. DataStore object data can be updated in InfoCubes by delta update, in other DataStore objects or master data tables (attributes or texts) in the same system, or across the entire system. Unlike multidimensional data storage for InfoCubes, data in DataStore objects is stored in transparent, non-nested database tables. Fact and dimension tables are not created.
For internal u s e by C S C only For internal u s e by C S C only
A MultiProvider is a special InfoProvider that combines data from several InfoProviders, providing it for reporting. The MultiProvider itself does not contain any data. Its data comes exclusively from the InfoProviders on which it is based. A MultiProvider can be made up of various combinations of the following InfoProviders:
A Virtual Provider is an InfoProvider with transaction data that is not stored in the object itself, but which is read directly in the source system at runtime, i.e. when a query is executed.
Real Time Data Aquisition is a framework for deriving information from data
as the data becomes available.
Real-time data acquisition supports tactical decision making. In terms of data acquisition, it supports operational reporting by allowing you to send data to the delta queue or PSA table in real time. You use a daemon to transfer DataStore objects that have been released for reporting to the DSO layer at frequent regular intervals. The data is stored persistently in BI.
We recommend that you use real-time data acquisition if you wish to transfer data to BI more regularly than the scheduled intervals (every hour or minute), and if reporting data must be updated several times in one day. The DataSource must support real-time data acquisition.
An InfoPackage is the BI object which loads data from the source system to the BI system. It contains all the settings directing exactly how data should be uploaded . The target of the InfoPackage is the PSA table tied to the specific DataSource associated with the InfoPackage. In a production environment, the same data in the same source system should only be extracted once, with one InfoPackage; from there, as many data transfer processes as necessary can push this data to as many InfoProviders as necessary.
Administrator's view
From an administrator's point of view there is a number of preparatory steps to be carried out:
• Identify source system • Activate BI Content
• Customize DataSources and Extract Structures • Set up Data Flow
• Delete and rebuild the restructuring tables for the application • Load data
For more information refer to SAP Help Portal or the relevant BW training courses (f.ex. BW310, BW330, BW350). For internal u s e by C S C only For internal u s e by C S C only
For internal u s e by C S C only For internal u s e by C S C only
Exercise 3: Basics
Exercise Objectives
After completing this exercise, you will be able to:
• Describe the basic structures in BW from the point of view of Enterprise Asset Management
Business Example
You use BW in the integrated ERP system, and would like to get to know its basic structures.
Task 1:
Learn about the data flow in BW from the point of view of Enterprise Asset Management
1. Display the data flow of InfoCube Maintenance Orders: Scheduling. How do you proceed?
What are the technical names of the DataSources and the InfoSources?
Task 2:
Learn about the data model in BW from the point of view of Enterprise Asset Management
1. Display the object overview and data model for the InfoCube Maintenance
Orders: Costs and Allocations and Maintenance Orders: Scheduling.
What are the keys for these InfoCubes?
What is the difference between the object overview and the data model? What is the dimension of the InfoCube? Maintenance Orders: Scheduling? 2. What key figures does InfoCube 0PM_C07 have?
How are these key figures mapped in BW?
What type or data category do the key figures Number of Orders Completed on Schedule and Total Actual Costs have?
What is the technical name in each case?
For internal u s e by C S C only For internal u s e by C S C only
Solution 3: Basics
Task 1:
Learn about the data flow in BW from the point of view of Enterprise Asset Management
1. Display the data flow of InfoCube Maintenance Orders: Scheduling. How do you proceed?
What are the technical names of the DataSources and the InfoSources? a) SAP menu → Information Systems → Business Information
Warehouse → Modeling → Data Warehousing Workbench: Modeling
In the navigation window on the left, click link Info Provider and navigate to the Plant Maintenance component:
Select Product Lifecycle Management → Plant Maintenance. In the Plant Maintenance component, right-click on the InfoCube
Maintenance Orders: Scheduling and select Display Data Flow; then
select Upwards and Downward.
Within the data flow, use the mouse-over function (or a double-click) to find out the names of the DataSources and InfoSources.
DataSources (next to ERP client 800):
• 0PM_OM_OPA_1 (holds the planned costs of maintenance
orders)
• 0PM_OM_OPA_2 (holds the actual costs of maintenance orders)
• 2LIS_17_I3OPER (holds the operation data of maintenance
orders)
• 2LIS_17_I3HDR (holds the header data of maintenance orders)
InfoSources (one level up): • 0PM_OM_OPA_1 • 0PM_OM_OPA_2 • 2LIS_17_OPERATION • 2LIS_17_ORDER For internal u s e by C S C only For internal u s e by C S C only
Task 2:
Learn about the data model in BW from the point of view of Enterprise Asset Management
1. Display the object overview and data model for the InfoCube Maintenance
Orders: Costs and Allocations and Maintenance Orders: Scheduling.
What are the keys for these InfoCubes?
What is the difference between the object overview and the data model?
Continued on next page
For internal u s e by C S C only For internal u s e by C S C only
What is the dimension of the InfoCube? Maintenance Orders: Scheduling? a) SAP menu → Information Systems → Business Information
Warehouse → Modeling → Data Warehousing Workbench: Modeling
In the navigation window on the left, click link Info Provider and navigate to the Plant Maintenance component:
Select Product Lifecycle Management → Plant Maintenance. In the Plant Maintenance component, display the InfoCubes
Maintenance Orders: Costs and Allocations and Maintenance Orders: Scheduling in the Object Overview:
Select the InfoCube, open the context menu, and choose >Object Overview.
Display the Data Model:
Select the InfoCube, open the context menu, and choose >Display Data Model.
Maintenance Orders: Costs and Allocations has the key 0PM_C01. Maintenance Orders: Scheduling has the key 0PM_C07.
The object overview shows a flat view of all characteristics and key figures.
The data model shows a view of characteristics and key figures that is structured according to dimensions.
The InfoCube Maintenance Orders: Scheduling has the following dimensions (visible in the Data Model):
• Maintenance Planning Plant/Maintenance Planner Group • Functional Location/Equipment
• Assembly
• Order Type/Maintenance Activity Type • PM Processing Phase/PM Planning Indicator • Plant/Work Center
• Controlling Area/Currency Type • Time
• Data Package • Unit
2. What key figures does InfoCube 0PM_C07 have? How are these key figures mapped in BW?
For internal u s e by C S C only For internal u s e by C S C only
What type or data category do the key figures Number of Orders Completed on Schedule and Total Actual Costs have?
Continued on next page
For internal u s e by C S C only For internal u s e by C S C only
What is the technical name in each case?
a) SAP menu → Information Systems → Business Information
Warehouse → Modeling → Data Warehousing Workbench: Modeling
In the navigation window on the left, click link Info Provider and navigate to the Plant Maintenance component:
Select Product Lifecycle Management → Plant Maintenance. In the Maintenance component, select the InfoCube Maintenance
Orders: Scheduling. Open the context menu, and display the Data Model.
The following key figures appear in the data model's Key Figures folder: • Planned Work for Operation
• Number of Orders
• Number of Orders with Status Completed • Number of Orders to be Executed Immediately • Number of Orders Completed on Schedule • Number of Orders with Status Not Executed • Number of Orders with Status Outstanding • Number of Planned Orders
• Lead Time for Order in Calendar Days • Lead Time for Order in Workdays • Number of Unplanned Orders