All About LO....SAP BW - LO EXTRACTION MADE SIMPLE

SAP BW - LO EXTRACTION MADE SIMPLE:

PART 1:

EXTRACTION:

CONTENTS:
1) INTRODUCTION
2) WHY GOING EXTRACTION?
3) Dimensions of extraction process?
4) FREQUENTLY USED IMPORTANT TERMS IN LO:
5) EXTRACTION TYPES
6) LO Cockpit
7) TRANSACTION CODES IN USE FOR LO EXTRACTION
8) LO- EXTRACTION APPLICATIONS
9) DELTA EXTRACTION-ABR MECHANISM
10) UPDATES & UPDATE MODES AVAILABLE IN LO- EXTRACTION
A. DELTA UPDATES:
i. V1
ii. V2
iii. V3
B. DELTA UPDATE MODES:
i. DIRECT DELTA
ii. QUEUED DELTA
iii. UNSERIALIZED V3 UPDATE
11) FEATURES & DIFFERENCES BETWEEN V1 V2 & V3 UPDATES
12) LO-EXTRACTION (logistics data)
13) NAMING CONVENTIONS
a. NAMING CONVENTION FOR TRANSACTION DATA SOURCE
b. NAMING CONVENTION FOR EXTRACT STRUCTURE
c. NAMING CONVENTION FOR SETUP TABLES
d. TABLE REPRESENTATION:
NAMING CONVENTION FOR TRANSACTION DATA SOURCE, EXTRACT STRUCTURE & SETUP TABLES

Extraction:
Extraction? What is extraction?
Extraction itself means extracting data.
From where?
For what?
Why?

Just think! Not arduous. But not easier said than done!
I think you are familiar with modeling, which we can say that, it’s an art of designing. So what u has designed?
Answer: modeling of InfoCubes & D.S.O DataStoreObject or ODS which are well known as DataTargets.

By what? InfoObjects, InfoAreas, Idoc, PSA transfer methods, and also u know update rules, transfer rules. ohhhh. Its gud. Gradually after building a data model i.e. InfoCube-just an ESS an extended Star Schema, just another step is how u fillllllllllll the InfoCube?

Now u can get an answer, that’s by extraction-extractor-extractor mechanism.
Am I right?
Yeah! Just its filling the InfoCube!
How u fill?
Much more about SAP interfaces!

SAP has an amazing feature i.e. it supports external data from third party ETL Tools. E.g. Ascential Software, ETI. And Informatica through Staging BAPI to load data and meta data from many different databases and file formats. SAP entered into strategic partnership with Ascential Software and bundling their DataStage ETL software package with SAP BW.
Extraction programs read the data from EXTRACT STRUCTURES and send it to DATA STAGING MECHANISMS in a required format.

Logistics is an important aspect in an enterprise and forming a core part of ERP application. For every application above stated in table the applications such as Purchasing, Inventory Controlling, Shop Floor Controlling, Quality Management, Invoice Verification, Shipment SD Sales BW, LE Shipping BW, SD Billing BW, Plant Maintenance BW, Customer Service BW etc.



Day to day thousands of transactions occur form as a database volume. Important point to notice here is that, use of Microsoft Excel is opted by small organizations, having low volume of data transactions or transaction data.
But when we have large number of transaction data updated daily, ERP software must be chosen. Thus we are choosing SAP implementation and as we know it has its own pre delivered business content for all applications, and according to the requirement we must install and change the business content.

After data modeling we go to Extraction and then reporting. So we must opt a different mechanism in extraction so called extraction mechanism i.e. alternate method to reduce or decrease performance related issues and minimize the impact on OLAP (Transaction System). Thus we choose LO-Extraction as an alternate to meet the real time analytic requirements.

The main conclusion here is that we need exact data for reporting, i.e. eliminate unwanted data or repeated data from millions of records. I mean to say, Right data for right Reporting.

E.g. for more clarification, we will not recruit same person many times for one interview process. Here discussing about data. Here person may be data, interview may be InfoCube, interviewer may be BW Delta Queue, and organization may be BI or BW. Here, so the extracted data is analyzed in reporting making drill down across and so on etc in reporting.

SAP BW can extract and load data from a variety of source systems including Non-SAP source systems:
Data from non-SAP data sources can be loaded into SAP BW in one of three ways:
1. Flat file source systems (loaded through flat files)
2. External source systems—connect to external systems with third-party custom extraction tools (using staging BAPIs).
3. External data providers such as Nielsen

SAP source systems:
1. SAP R/3 source systems (after release 3.1H)
2. SAP BW systems—using Myself
3. Other SAP products


Methods of Data Load into SAP BW:
There are four methods to update the DataTargets (ODS or InfoCube) in SAP BW:
1. Update PSA, then data targets. This is the standard data transfer method. Data are first updated in the PSA and can be subsequently updated.

2. Update PSA and data targets in parallel. This is a way of carrying out a high-performance update of data in the PSA and one or more InfoCubes.

3. Update PSA only. You can store data in the PSA and bypass InfoCube or ODS objects. For example, if a high volume of data needs to be extracted into SAP BW, the PSA can be loaded several times a day (e.g., every 15 minutes) and the InfoCube and ODS can be loaded subsequently once a day (outside of business hours when there is limited on-line activity and load on the BW server). This is one method to improve overall performance and manage high volumes of data.

4. Update data targets only. You can load the InfoCubes and bypass the PSA. This is one way to reduce the overhead associated with the PSA. The drawback of this method is that you lose the ability to do the following:

The preferred method for loading data into SAP BW is to use the PSA if possible.





2) What are the dimensions of extraction process?
Four dimensions are generally used to describe the different methods and properties of extraction processes:
Every extraction process can be viewed along these four dimensions.
1. Extraction mode.
2. Extraction scenario.
3. Data latency.
4. Extraction scope.
For further explanation see sap online guidance or Willey’s Mastering the SAP Business Information Warehouse



3) FREQUENTLY USED IMPORTANT TERMS IN LO:

ABR MECHANISM : ABR Delta Mechanism is a PUSH DELTA, pushes the data from APPLICATION to the QUEUED DELTA by means of V1 or V2 Update.
COMMUNICATION STRUCTURE : In the Communication Structure, data from an InfoSource is staged in the SAP (BW) System. The Communication Structure displays the structure of the InfoSource. It contains all (logically grouped) of the InfoObjects belonging to the InfoSource of the SAP (BW) System. Data is updated in the InfoCubes from the Communication Structure. InfoCubes and Characteristics interact with InfoSources to get Source system data.
DATA SOURCE : The table ROOSOURCE has all details about the DataSource. You can give the input as your DataSource name and get all relevant details about the DataSource
EXTRACTOR : Extractor enables the upload of business data from source systems into the data warehouse
EXTRACT RULES : Extract rules define how the data will be moved from extract structure to transfer structure.
EXTRACT STRUCTURE Extract Structure is a record layout of InfoObjects. In the Extract Structure, data from a DataSource is staged in the SAP (R/3) SourceSystem. The Extract Structure contains the amount of fields that are offered by an Extractor in the SAP (R/3) Source System for the data loading process. For flat files there is no Extract Structure and Transfer structure.
EXTRACTION : Extraction is an alternate method for extracting data from the direct database (which is the result of ‘n’ number of transaction data updated daily through OLTP, resulting huge volume of data) mainly to meet OLAP requirements.
SETUP TABLES : Lo uses the concept of Setup tables, from where the application tables’ data is collected. Setup tables are used as data storage. Setup tables are used to Initialize delta loads and for full load.
TRANSFER RULES : Transfer rules transform data from several transfer structures from source system into a single communication structure or InfoSource.
Data is transferred 1:1 from the Transfer Structure of the SAP (R/3) Source System into the Transfer Structure of the SAP (BW) System, and is then transferred into the SAP (BW) System Communication Structure using the TransferRules. In the transfer rules maintenance, you determine whether the communication structure is filled with fixed values from the transfer structure fields, or using a local conversion routine.
TRANSFER STRUCTURE Transfer Structure maps data source fields to InfoSource InfoObjects.
The Transfer Structure is the structure in which the data is transported from the SAP (R/3) Source System into the SAP (BW) System. In the Transfer Structure maintenance, you determine which Extract Structure fields are to be transferred to the SAP (BW) System. When you activate the DataSource of the SAP (R/3) Source System from the SAP (BW) System, an identical Transfer Structure like the one in the SAP (R/3) Source System is created in the SAP (BW) System.
UPDATE MODES : DELTA DIRECT DELTA, QUEUED DELTA, UNSERIALIZED V3 UPDATE, SERIALIZED UPDATE
UPDATE RULES Update rules transform data from communication structure in an InfoSource into one or more data targets using chars, KF and time char
The update rules specify how the InfoObjects (Key Figures, Time Characteristics, and Characteristics) are updated in the DataTargets from the Communication Structure of an InfoSource. You are therefore connecting an InfoSource with an InfoCube or ODS object. The update rules assign InfoObjects in the InfoSources to InfoObjects in data targets. Update rules are independent of the source system or data source and are specific to data targets.
Example: you can use update rules to globally change data independent of the source system.
UPDATES : V1- Synchronous, V2-Asynchronous, V3- Batch asynchronous updates

4) EXTRACTION TYPES:


SAP BW Extractors:
SAP BW extractors are the programs in SAP R/3 that enable the extraction and load of data from SAP R/3 to SAP BW. SAP provides extractors that are programmed to extract data from most applications of SAP R/3 and delivered as part of the business content. Extractors exist in release 3.1H and up.

There are three types of extractors in SAP BW:
1. SAP BW content extractors. For extraction of data from SAP R/3 application-specific data (e.g., FI, CO, HR, LO cockpit)
2. Customer generated, application specific (generic extractors). For extraction of cross-application data in SAP R/3 (e.g., LIS, FI/SL, CO/PA)
3. Generic extractors, cross application. For extraction of data from SAP R/3 across application database table/views and SAP query.






5) LO COCKPIT-WHAT IS IT, WHAT IT DO & WHAT IT CONSIST OF?

The LO cockpit is a tool to manage the extract of logistics data from SAP R/3 and It consists of:
• New standard extract structures
• New DataSources
The functionality of the LO cockpit includes
• Maintaining extract structure.
• Maintaining DataSources.
• Activating the update.
• Controlling the V3 update.


6) TRANSACTION CODES IN USE FOR LO EXTRACTION:
TCODE DESCRIPTION
LBWE : LO DATA EXTRACTION: CUSTOMIZING COCKPIT
LBWF : BW LOG
LBWG : DELETION OF SETUP DATA – MANUALLY
NPRT : LOG FOR SETUP OF STATISTICAL DATA
OLI*BW : INITIALIZATION OR SETUP (STATISTICAL)
LBWQ :
OLI8BW : DELIVERY
OLI9BW : BILLING
RSA2 : DATA SOURCE METADATA IN SAP ECC 6.0 9DATA SOURCE REPOSITORY)
RSA3 : TO CHECK ANY DATA THAT IS AVAILABLE RELATED TO YOUR DATA SOURCE
RSA5 : ACTIVATION OF BUSINESS CONTENT DATA SOURCE
RSA6 TO SEE THE ACTIVATED DATASOURCE
RSA7 : DELTA QUEUE ( DETA IMAGE & REPEAT DELTA)
SBIW : IMG FOR SAP BI EXTRACTION
SE11 : TO SEE APPLICATION TABLES STORED
SE37 CHECKING EXTRACTOR
SM13 : TO CHECK THE VARIOUS UPDATES THAT GETS PROCESSED DURING THE PROCESS OF UPDATING THE SALES DOCUMENT CHANGE
SM37 : TO KNOW THE JOB STATUS
SMQ1 : CLEAR EXTRACTOR QUEUES
SMQ2 : TO CHECK THE VARIOUS UPDATES THAT GETS PROCESSED DURING THE PROCESS OF UPDATING THE SALES DOCUMENT CHANGE
PROCESS KEYS
PROGRAM RMCVNEUA : SETUP THE INFORMATION FOR SALES DOCUMENTS
TMCLVBT : IT MAINTAINS THE PROCESS KEYS & THEIR DESCRIPTION
TMCLVBW : IT MAINTAINS THE PROCESS KEYS FOR EACH APPLICATION



7) LO- EXTRACTION APPLICATIONS:

As we know that SAP BW has its own business content i.e. SAP Pre Delivered Business Content. This is the touch stone of SAP BW making pioneer in the ERP market. LO makes it as one stop for all i.e. The Transaction Data Source available in LO – COCKPIT. We can see this source data by entering Transaction Code: LBWE

LO- EXTRACTION APPLICATIONS
APPLICATION NUMBER : APPLICATION NAME APPLICATION NUMBER : APPLICATION NAME
02 : Purchasing 11 : SD Sales BW
03 : Inventory Controlling 12 : LE Shipping BW
04 : Shop Floor Controlling 13 : SD Billing BW
05 : Quality Management 17 : Plant Maintenance BW
06 : Invoice Verification 18 : Customer Service BW
08 : Shipment

8) DATA FLOW IN BW & BI:

Here we can see that BI-System extracts the data as a ONE TIME activity for the INIT DATA LOAD. After successful data load, the SETUPTABLES/RESTRUCTING TABLES to be deleted to avoid redundant storage.
Important points here to know in SAP BW is
i. Installing Business Content (which contains SAP delivered Standard Data Sources comprising of data structures and Extraction Programs. Actually SAP delivered Business Content is D Version, so we must activate the Business Content “A Version” to use.
ii. Customizing
iii. Deploying

LO uses concept of SETUPTABLES to carry out the INITIAL DATA EXTRACTION process. The data Extractors for HR, FI extracts data directly by accessing APPLICATION TABLES. I.e. LO Data Sources do not use application tables directly. SETUPTABLES are also known as RESTRUCTURING TABLES, which are CLUSTER TABLES, which holds the respective APPLICATION DATA. When we run init load or Full load in BW, the data will be read from Setup Tables for the first time, total data will be read and the delta records will be updated to Delta Queue by V3 job runs and delta records can be extracted from Delta Queue. After successful run of the init, setup Tables are deleted.

Important terms:
INIT/FULL LOAD: First Time (START)
DELTA: Only Changes (AFTER START)


9) DELTA EXTRACTION-ABR MECHANISM:
ABR MECHANISM:
The LO data sources support ABR Delta Mechanism (Push Delta Mechanism) which is compatible to ODS/DSO & InfoCube. USE OF ABR DELTA:
The ABR Delta creates delta with:
• AFTER: No MINUS symbol, updates records
• BEFORE: MINUS SYMBOL, Delta Before Image

So what are pull data and Push data?
Data is pulled from delta queue (SourceSystem) and pushed into Data Ware House when a transaction is saved. SAP BW system follows Pull Scenario & SAP BI system follows Push Data. The ABR Delta creates (-) symbol, Delta before Image for the data which is deleted & After Image provides status after change with addition of records. The ABR delta Mechanism uses V1 or V2 update where a delta is generated for a document change with addition of change (i.e. document postings done), the program LO Updates the application tables for a transaction pushes/triggers the data. Data extraction in BW is extracting data from various tables in the R/3 systems or BW systems. There are standard delta extraction methods available for master data and transaction data. You can also build them with the help of transaction codes provided by SAP. The standard delta extraction for master data is using change pointer tables in R/3. For transaction data, delta extraction can be using LIS structures or LO cockpit. Delta Queue for an LO DataSource is automatically generated after successful initialization and can be viewed in transaction RSA7 or in Transaction SMQ1 under the name MCEX.


10) UPDATES & UPDATE MODES AVAILABLE IN LO- EXTRACTION:
First when we are going to update methods, first we must know about updates, because updates are used in update methods!

A. UPDATES:
i. V1 - Synchronous update
ii. V2 - Asynchronous update
iii. V3 - Batch asynchronous update

i. V1 - Synchronous update:• V1 update is defined as direct delta is synchronous type uses time critical technology and its nature of update is automatic, where the process done only once and never 2nd time.
• It is used for single update work process & belongs to a same LUW (Logical Unit Of Work), reads data from documents & uses direct delta & writes to application tables.
• V1 update can be scheduled any time, with a mandatory step of locking users to prevent simultaneous updates.
• V1 is carried out for critical/primary changes & these affects objects that has controlling functions in SAP System
• During the process of creation of an order, the V1 update writes data into the application tables and the order gets processed.

ii. V2 - Asynchronous update:
• V2 update is defined as queued delta, is asynchronous statistical update type uses non-time critical (for updating statistical tables of transaction tables) and its nature of update is never automatic, uses report RMBW311.
• The process not done in a single update & carried out in separate LUW, reads data from documents and uses extractor Queue & writes to application tables.
• V2 update is scheduled hourly, with a mandatory step of locking users to prevent simultaneous updates.
• A V2 is executed for less critical secondary changes & are pure statistical updates resulting from the transaction.
• V1 updates must be processed before V2


iii. V3 - Batch asynchronous update:• V3 is defined as Unserialized is collective update / synchronous with background schedule or batch synchronous update, uses delta queue technology and the nature of update is never automatic, which runs in background uses report RSM13005.
• The process can be done at any time after V1, V2 updates. i.e. V3 uses V1 or V2 +v3 update, reads data from documents & uses delta queue collective run call and writes to application tables.
• V3 can be scheduled at any time, with a mandatory step of locking users to prevent simultaneous updates.


B. UPDATE MODES:
The LO DataSource implements its delta functionality using the above discussed V1, V2 & V3 update methods, individually or by combination of them.
P1 2002.1 is the upgraded version. So what are the update modes that are available with LO DataSource as of P1 2002.1?

i. DIRECT DELTA
ii. QUEUED DELTA
iii. SERIALIZED V3 UPDATE
iv. UNSERIALIZED V3 UPDATE

i. DIRECT DELTA : V1 update:

Document postings and update sequence is 1:1. I.e. the direct delta V1 updates directly the document positions to the BW Delta Queue and this is extracted to BI System periodically.

In doing so each document posting with delta extraction is posted for exactly one LUW in the respective BW delta queues.

Users are locked but any postings are done so is completely lost. Because from start of recompilation run in OLTP until all init requests have been successfully updated in BW.



Advantages:
SUITABILITY : Suitable for customers with fewer documents and no monitoring of update data/extraction queue required
SERIALIZATION BY DOCUMENT : Posting process ensures serialization of document by document, while writing delta to Delta Queue within V1
EXTRACTION : Extraction is independent of V2 update

Disadvantages:
SUITABILITY : V1 is more heavily burden& Not suitable f high number of documents
Re-initialization process
(User Locks) : Setup and initialization required before document postings are resumed.
IMPLEMENTATION : less

ii. QUEUED DELTA V2 (V1+V3) update: **SAP RECOMMENDS**

Delta queues essentially are tables capturing the key values of changed or inserted records or the entire transaction recordsData is pushed to extraction queue by means of V1 update and to delta queue same as V3 update. As we know that V3 is a collective run the data collected in extraction queue and scheduled background.SAP recommends queued delta for customers with high amount of documents.

Uses REPORT RMBWV311 for collective run and the naming convention is MCEX_UPDATE_ for example for sales its MCEX_UPDATE_11s.

Advantages:
SUITABILITY : Suitable for customers with high amount of documents (SAP RECOMMENDS)
SERIALIZATION BY DOCUMENT : No serialization as it’s a statistical update, which is run after V1
EXTRACTION : Extraction is independent of V2 update
IMPLEMENTATION : **SAP RECOMMENDS**

Disadvantages : No disadvantages


iii. UNSERIALIZED V3 UPDATE

Data extracted from application tables is written to update tables using un-serialized V3 update mode. By using collective update the so extracted data is processed. The Important point to consider here is that the data is read without a sequence from update tables and are transferred to BW delta queue. Un serialized update, which is run after V1 & V2 doesn’t guarantee serialisation of the document data posted to delta queue. This method is not suggestible as the entries in the delta queue are not equal to the updates that made to application. This method V3 results erroneous data, when data from DataSource is updated to DSO in overwrite mode, so the previous data is over written by the last update. But this V3 is suggestible when updating data to ODS or InfoCube. V3 runs and process the data after V2 with which the data is processed.

Advantages
SUITABILITY : Suitable for customers with high amount of documents
SERIALIZATION BY DOCUMENT : No serialization as it’s a statistical update, which is run after V1 & V2 but the un serialised V3 doesn’t guarantee serialisation of the document data.
EXTRACTION : collective
DOWN TIME : Not efficient
IMPLEMENTATION : LESS

Disadvantages
SUITABILITY : Not suggested if documents subjected to large changes with tracking changes



iv. SERIALIZED V3 UPDATE

Here the tables are updated consistently and the main problem in this method is, the user locks not there and the document postings occur as we are extracting data. It’s arduous as the data will not have consistency. E.g. document changed twice or thrice when extracting data.
Hey: the important difference we to analyze here is!
Serialized V3 Vs Un-serialized V3
As the term serialisation itself mean, sequentially i.e. Data is read sequentially and transferred to the BW Delta Queue. Un-serialised mean, the process is not in a sequence i.e. data is read in the update collective run without taking the sequence into account and then transferred to the BW delta queues.

As per the data flow, serialized and un-serialized V3 updates, I can say that these are twins, with different process.



11) DATA FLOW CHARTS OF DIRECT DELTA, QUEUED DELTA, SERIALIZED V3 & UNSERIALIZED V3 UPDATES:

12) FEATURES , DIFFERENCES BETWEEN V1 V2 & V3 UPDATES: (COMPARISION TABLE):

13) NAMING CONVENTIONS:
 NAMING CONVENTION FOR TRANSACTION DATA SOURCEü
 NAMING CONVENTION FOR SETUP TABLESü
 NAMING CONVENTION FOR EXTRACT STRUCTUREü
 TABLE REPRESENTATION:ü