Data Flow in BW and Extracting Data from mySAP Components

Data Flow in BW and Extracting Data from mySAP Components is the point of discussion in this post.You need to do this in the following scenario.

You want to look at the process of extracting cost center master data and transaction data from an R/3 source system to ensure that R/3 cost center data is available for analysis.Since the R/3 cost center numbers are 10 characters in length, you need to convert the master data and the transaction data into the same format as the COSTC## InfoObject that is already available.

Administrator Workbench

The Administrator Workbench is the tool that maintains, controls, and monitors the Business Information Warehouse.The Administrator Workbench is therefore used on all the layers of the BW system Ÿ to design all the components of the Business Information Warehouse including customizing/maintenance.
to schedule the transfer of data from several sources of data.
Ÿto load data.
Ÿto monitor the loading and updating of data.

Functions in the Administrator Workbench

Designing/Maintaining the Data Warehouse
Logical source systems
InfoObjects with master data, hierarchies, and so on
InfoCubes and aggregates
Assigning InfoSources to InfoCubes
Scheduling of extraction jobs
Grouping of extraction jobs
Monitoring the transfer and update of data
Status display and statistics on loading processes

Using Business Content delivered by SAP eliminates much of the work involved in configuring a BW system. The Administrator Workbench activates the Business Content.If you need to create new BW objects to meet the particular needs of your company, the functions in the Administrator Workbench enable you to do this easily.You use the scheduler to plan the periodic transfer of data from your source systems.The monitor helps you to monitor the transfer of data into InfoCubes.

Administrator Workbench: Additional Functions

Activating BW statistics
Maintaining aggregates
Maintaining master data
Maintaining hierarchies
Managing InfoCubes
Checking, deleting, or reconstructing indexes
Reconstructing InfoCubes
Compressing InfoCubes
Analyzing the status of InfoCubes

BW statistics activate an InfoCube delivered by SAP. This InfoCube provides you with information about the system performance. This is a very useful function, because it shows you how the BW system is being used and if there are any areas that need to be improved.One way to improve the performance of queries in BW is to use aggregates. Aggregates represent a summarized version of the InfoCube. They are optimized for queries that access the non-summarized characteristics.

The SAP DemoCube gives you a demonstration scenario modeled on the requirements of a sales company. The demonstration scenario consists of an InfoCube, queries, and data in the form of flat files. When you activate the SAP DemoCube, all the required objects are generated automatically and you are able to see the underlying structure in the Administrator Workbench.

The tools in the Administrator Workbench not only enable you to create new InfoCubes, they also allow you to maintain the contents of an InfoCube. If you click with the right mouse-button on an InfoCube and select the Maintain InfoCube Contents option, the system displays a list of analyses to help you improve the performance of the InfoCube.

Source Systems

All systems that provide the SAP Business Information Warehouse with data are called source systems.

Types of source system include:
SAP R/3 OLTP systems from release 3.1I onwards
External systems (non-SAP OLTP systems, flat files, data providers, and so on).
There must be a path of communication between the BW system and each of the source systems: 
ŸSAP R/3 OLTP and BW system
LE (Application Link Enabling)
or tRFC (Transactional Remote Function Call)
ŸExternal Systems and the BW system
BAPI (Business Application Programming Interface)
The data extraction process:
ŸSAP R/3 OLTP with APIs (Application Programming Interface)
ŸExternal systems with tools or files from third-parties.
APIs (Application Programming Interface) delivered by SAP must be installed on the R/3 system.

All systems that provide data for extraction into the SAP Business Information Warehouse

Types of source system
SAP R/3 systems
External source systems (non-SAP systems, flat files, data providers)
A Data Source extracts and updates data from the source system

Data Sources and Info Sources

An InfoSource is a quantity of information that logically belongs together and that has been summarized into a single unit. InfoSources contain transaction data (this data is stored in InfoCubes) and master data (attributes, texts, and hierarchies - this data is stored in separate tables). 

InfoSources describe all the information that is available for a business process or a type of business process (for example, cost center accounting). Transfer structures support the transfer of data in a DataSource between a source system and a connected SAP BW system. The transfer structure transports the DataSource-data from a source system to an SAP BW system and uses transfer rules to pass the data on to the InfoSource.

The communication structure is independent of the source system and is generated from the InfoSource. It is filled from the transfer structure in accordance with the transfer rules. The communication structure contains all the fields in an InfoSource. This process of extracting and transferring cleansed data to the communication structure is known as data staging.

Replicating DataSources for R/3 Source Systems

The source system extraction tables form the basis of a DataSource in an R/3 source system. The data elements that describe the data (usually in a table view) create the structure in the source system. With an R/3 source system, the DataSource Replication step copies the DataSource extraction source
structure as a template from the source system to the BW system.


An InfoSource is a quantity of information that logically belongs together and that has been summarized into a single unit.The InfoSource is located in the BW system and contains the communication structure. The communication structure delivers the data that you want to send to the InfoCube. The communication structure includes the InfoObjects that represent the logical collection of data for the InfoSource.

The DataSource in the source system contains the extraction source structure and the transfer structure. The extraction programs use these structures to access and extract the data from the source system.The transfer structure in the BW system is linked by transfer rules to the InfoSource. These rules allow incoming data to be cleansed and modified.InfoSources include transaction data master data (attributes, texts, hierarchies).

Types of InfoSource:

Transaction data
Master data: Attributes, texts, hierarchiesCommunication structures are generated from the InfoSource.The DataSource provides the template for the transfer fields that determine and generate the InfoSource including the transfer structures.

Defining Transfer Rules

The transfer rules determine which fields in the transfer structure are transferred to which fields in the communication structure. Detailed conversion rules can be created to change or enhance the data.For each InfoObject in the communication structure you specify exactly one of the three different types of transfer rules:

The fields are transferred from the transfer structure and are not modified.ŸYou can assign a fixed value to a field.ŸCreate/assign local transfer routines. Local transfer routines are ABAP programs that you can modify or transfer. The routine affects only the InfoObject that you select in the relevant communication structure.You must activate the transfer rules before they can be used in the system.

Update Rules: Features

Several operations are included in the update rules:

ŸInfoObjects are updated 1:1 using a simple MOVE.Ÿ More complex calculations are done in ABAP /4 routines. The ABAP editor is embedded directly in the maintenance screens for the update rules - you do not have to generate a user exit. Customer-specific tables and tables generated by BW (master data tables, hierarchy tables, and so on) are accessed using routines. This means that to change information quickly, you need to maintain only the table contents and not the update rules.Ÿ Currency conversions for key figures. Ÿ Special conversions for time dimensions are predefined (for example, from Calendar day to Calendar year).

In general, update routines have only one result value. However, in the Key Figure Calculation tab strip, you are able to create a routine with a results table. This means that the associated key figure routine has a results table instead of a result value. You are able to create as many key figure values as you like from one data record.In the routines editor, the calculated characteristic values are stored in the ICUBE_VALUES structure. You change these values accordingly, fill the field for the associated key figure, and use this key figure to fill the RESULT_TABLE result table.

Extraction and Update

In the Administrator Workbench the scheduler requests the data from a source system. You create requests called InfoPackages, and specify in the scheduler when you want the extraction to take place.The data loading process itself is observed in the monitor. The monitor is also a function of the
Administrator Workbench.

Extractors are part of the data staging mechanism in SAP source systems. An extractor fills the extraction source structure of a DataSource with data from datasets in the SAP source system.  There are application-specific extractors, such as the BW Content extractors (FI, CO, HR, LO Cockpit) and the generic extractors (LIS, FI-SL, CO-PA).

There are also the generic extractors that are not application-dependent. You use these extractors to define extraction processes directly in a database table, a view, or using a SAP query.Generic extractors use the available information systems such as LIS, CO-PA (Profitability Analysis), FI-SL, or user-definable database tables, views, or InfoSets. In the source system these extractors access reporting tools that have already been adjusted to meet customer requirements.

Related Posts

SAP Business Warehouse Infoobject Continued

SAP Business Warehouse Infoobject Continued

SAP Business Warehouse Info object is discussed in the previous topic and here is the continuation for that.n In BW the term master data refers to attributes,hierarchy information, and texts. Master data attributes in BW correspond closely with master data objects in R/3.

Example: 0SOLDTO attributes in BW are derived from the fields in the customer master table in R/3.The data (stored in a cluster of tables) inside the highlighted area is the Info Cube. Any information outside the Info Cube is additional information that can be used by all the other Info Cubes. Hierarchy information can also be shared like this.

Modeling Attributes: Decisions

If you use navigation attributes, the system performance is not as good as when you integrate the attribute directly into a dimension table.Using navigation attributes means that you do not need to reconstruct the fact table if the data changes.Aggregates cannot be generated from time-dependent navigation attributes. Aggregates can also not be generated from display attributes, whether they are time-dependent or not.

An attribute that has been set as a display attribute delivers only descriptive information in the query results. You are not able to use a display attribute to navigate in a set of data.An attribute that has been designated a navigation attribute has all the navigational capabilities of a dimension characteristic, without actually being in the dimension itself.You can make an attribute time-dependent if you need to determine separate time intervals in which each of the attribute values is valid. You are not, however, able to use a time-dependent attribute to construct aggregates.

Surrogate ID (SID) Tables in the Extended Star Schema

A SID table uses numeric fields as indexes. This makes it possible to access data more quickly. It also reduces the amount of time and effort needed to stage the data, because it means that the system does not need to access large master data tables unnecessarily.SID values are 4 byte integers. Using numeric fields as indexes also improves system performance, compared to if you use long alphanumeric values.

SID tables (pointer tables) provide the technical link to the master data and hierarchy tables that are outside the dimensions of a star schema.Surrogate ID (SID) Tables in the Extended Star Schema Benefits:
Large dimension tables are built using the combination of numeric SID values of each characteristic in the dimension. Secondary indexes for each dimension improve system performance.External information (attributes of the characteristics and external hierarchies) is stored separately (shared) and linked to the Info Cubes.You can report on historical relationships as well as the current status of the data without any major reduction in system performance.Several different languages can be supported without reducing system performance.

SAP BW: Extended Star Schema

The BW extended star schema is different to the basic star schema. It is subdivided into a solution dependent part (InfoCube) and a solution-independent part (attribute tables, text tables, and hierarchy tables) that is also shared among the other Info Cubes.The dimension attributes of the dimension tables are called characteristics. The dimension attributes located in the master data table of a characteristic are called the attributes of the characteristic.

The great challenge when designing a solution is to decide whether to store a dimension attribute in a dimension table (and therefore in the InfoCube) or in a master data table.Data is loaded separately into the master data tables (attribute tables) text tables and hierarchy tables. n The SID table is the link between the master data and the dimension tables.

Star Schema in BW  with Tables

In BW 2.0 the system creates SID tables for navigation attributes automatically whenever you create an Info Object with navigation attributes. This means that the OLAP processor reads only the SID tables when a query is executed. There is no need to read the master data tables for the navigation attribute, and this improves the system performance.Master data tables are named based on their time dependency. For example, for InfoObject 0MATERIAL the tables are named as follows:

ŸP-table = PMATERIAL - time-independent master data
ŸQ-table = QMATERIAL - time-dependent master data
ŸM-view = MMATERIAL - a combination of P and Q

Mater Data Tables and SIDS

These tables or views are created when the corresponding options are selected from the maintenance screens for a characteristic. None of the tables are mandatory. If you do not select the option for creating master data, no tables of this type are generated.You need to be aware of the following relationships: The P-table, for example, is linked to the Xtable, and the Q-table is linked to the Y-table.

Master data tables and views of InfoObjects:

Example: Info Object 0MATERIAL 

P-table = PMATERIAL (time-independent master data)
Q-table = QMATERIAL (time-dependent master data)
M-view = MMATERIAL (a combination of P and Q)
SID Tables for InfoObjects:
S-table = SMATERIAL  table to link material SIDS with material numbers
X-table = XMATERIAL A table to link material SIDs with SIDs for time-independent navigation attributes.
Y-table = YMATERIAL A table to link material SIDs with SIDS for time-dependent navigation attributes.

Extended Star Schema: Tables

The number of tables that are generated depends on the definition in the Info Object maintenance. The S-table links time-independent and time-dependent attributes to the dimension tables of an Info Cube. The P-table and the Q-table are displayed in a view (M-table)navigation attributes are read using the view (M) and the master data tables (P and Q) of the associated Info Object, before they are linked using the X-table or the Y-table of the higher-level Info Object to the dimension tables of the Info Cube.

Navigation Attributes in the Extended Star Schema

The first step in reading data usin g the material group (0MATL_GROUP) navigation attribute, is to read the data using the SID table of the navigation attribute.The SID of the navigation attribute links to the Y-table of the characteristic (in this case, MATH_TH_C) - this applies to time-dependent navigation attributes. The X-table is used for navigation attributes that are non time-dependent.In the Y-table, the key date of the query determines the data record that is valid for this particular time period.In the dimension table, the SID of the material characteristic determines the DIM ID.The DIM ID links the dimension table to the fact table. n When data is read using the OCALMONTH characteristic, there is a direct link between the SID table of the Info Object and the dimension table. Again, it is the DIM ID that accesses the fact table.

The LIST SCHEMA Transaction

The LISTSCHEMA transaction shows not only the InfoCube tables, but all the other tables in the extended star schema right down to the attribute tables. Hierarchy tables and text tables are not shown.

The LISTSCHEMA transaction provides:

The LISTSCHEMA Transaction Indented and nested lists of all the InfoCube tables and supporting tables. The data browser transaction accesses each table.Review table design and links AND  Navigate to table contents.Other options: InfoCube contents tab or LISTCUBE transaction

Review nested structure
Other options: ABAP Dictionary Browser

Table Partitioning

Use: You can use the partitioning function to divide the entire dataset of an InfoCube into several smaller units that are independent of one another and do not contain any redundant information. Subdividing the data in this way can improve the system performance when you are reporting on the data or deleting data from the InfoCube.

Requirements: The only way to implement the partitioning process is to use one of the two partitioning criteria - calendar month (0CALMONTH) or fiscal year/period (0FISCPER). The InfoCube must contain at least one of these two InfoObjects.Functions: When you activate the InfoCube, the fact table is saved to the database, with a number of partitions that correspond to the value area. You are able to determine this value area yourself.

Example: You select the 0CALMONTH partitioning criteria and determine a value area of 01.1998 to 12.2003 ==> 6 years * 12 months + 2 = 74 partitions are created (2 partitions for values that lie outside of the area, that is < 01.1998 or > 12.2003).

You can also determine the maximum number of partitions that are created on the database for the fact table of the InfoCube. You choose the 0CALMONTH partitioning criteria, determine a value area of 01.1998 to12.2003, and set the maximum number of partitions to 30 ==> The value area results in: 6 years * 12 calendar months + 2 marginal partitions = 74 single values. The system generates a partition every three months, meaning that one partition represents a separate quarter. 6 years * 4 partitions per year + 2 marginal partitions = 26 partitions generated on the database.

Related Post