LO EXTRACTION
Data Extraction Logistics
A technique to extract logistics information and consists of a series of a standard extract structures (that is, from a more BW perspective, standard datasources), delivered in the business content. Data Extraction is the process of loading data from OLTP to OLAP (BW/BI).
Data extraction Illustration
Data can be extracted in two modes
1. Full Load – Entire data which available at source is loaded to BW/BI
2. Delta load - Only the new/changed/deleted data is loaded.
1. Full Load:
Document posting means creating a transaction, writing into the Application/transaction tables. So whenever sales order is created (document posted), it transaction is written into the database tables/ application tables/transaction tables (Ex.EKPO, EKKO, VBAK, VBAP).Whenever the user are doing a full load, setup tables are used.
2. Delta Load:
Various types of update methods are discussed below.
Serialized V3
1. Queued Delta
2. Direct Delta
3. Unserialized V3
Before that we need to understand V1, V2, V3 updates. These are different work processes on the application server that makes the update LUW from the running program and execute it. These are separated to optimize the transaction processing capabilities. These are different work processes on the application server that makes the update LUW from the running program and execute it. These are separated to optimize the transaction processing capabilities.
Step-by-Step Maintenance:
- Go to transaction code RSA3 and see if any data is available related to your DataSource.
- If data is there in RSA3 then go to transaction code LBWG (Delete Setup data) and delete the data by entering the application name.
SETUP TABLES: Access to application tables are not permitted, hence setup tables are
there to collect the required data from the application tables. When a load fails, the user can re-run the load to pull the data from setup tables. Data will be there in setup tables. Setup tables are used to Initialize delta loads and for full load. Its part of LO Extraction scenario. With this option, the user avoid pulling from R/3 directly as we need to bring field values from multiple tables. The user can see the data in the setup tables. Setup table name will be extract structure name followed by SETUP. Set up table names starts with 'MC' followed by application component '01'/'02' etc and then last digits of the datasource name and then followed by SETUP Also we can say the communication structure (R/3 side, the user can check it in LBWE also) name followed by 'setup'.
The setup tables are the base tables for the Datasource used for Full upload. So if the user are going for only full upload full update is possible in LO extractors. Full update is possible in LO extractors. In the full update whatever data is present in the setup tables (from the last done in it) is sent to BW.But setup tables do not receive the delta Data from the deltas done after the init. So if user’s full update should get ALL data from the source system will need to delete and re-fill setup tables.
- Go to transaction SBIW
--> Settings for Application Specific Datasource
--> Logistics
--> Managing extract structures
--> Initialization
--> Filling the Setup table
--> Application specific setup of statistical data
--> perform setup (relevant application)
- In OLI*** (for example OLI7BW for Statistical setup for old documents: Orders) give the name of the run and execute. Now all the available records from R/3 will be loaded to setup tables.
- Go to transaction RSA3 and check the data.
- Go to transaction LBWE and make sure the update mode for the corresponding DataSource is chosen and maintained Ex: Direct/Queue delta.
- Go to BW system and create infopackage and under the update tab select the initialize delta process. And schedule the package. Now all the data available in the setup tables are now loaded into the datasource.
- Now go to the BW System and Replicate the related Datasource from the Exact Source system.
9. Now in the BW system create transformations from the datasource to the Infoprovider and DTP to load the data and execute the load.
Delta Method
With this process the new information that is generated daily in the source system must be sent to BW. The principal reason for this is the information volume that is imported to BW every day.
This information is stored en the delta queue (RSA7 Transaction). It is
taken from here when BW asks for information to the source system. In the Delta Queue will only be the information that has not been sent to BW and the last request.
Delta Process
Forming deltas with after, before and reverse images that are updated directly in the delta queue; an after image shows the status after the change, a before image the status before the change with a negative sign and the reverse image also shows the negative sign next to the record while indicating it for deletion. This serializes the delta packets. The delta process controls whether adding or overwriting is permitted. In this case, adding and overwriting are permitted. This process supports an update in an ODS object as well as in an InfoCube. (Technical name of the delta process in the system: ABR)
The extractor delivers additive deltas that are serialized by request. This serialization is necessary since the extractor within a request delivers each key once, and otherwise changes in the non-key fields are not copied over correctly. It only supports the addition of fields. It supports an update in an ODS object as well as in an InfoCube. This delta process is used by LIS Datasources. (technical name of the delta process in the system: ADD)
Forming deltas with after image, which are updated directly in the delta queue. This
serializes data by packet since the same key can be copied more than once within a
request. It does not support the direct update of data in an InfoCube. An ODS object must always be in operation when the user update data in an InfoCube. For numeric key figures, for example, this process only supports overwriting and not adding, otherwise incorrect results would come about. It is used in FI-AP/AR for transferring line items, while the variation of the process, where extractor can also send records with the deletion flag, is used in capacity in BBP. (technical name of the delta process in the system: AIM/AIMD)
Update Modes
Before elaborating on the delta methods available for LO datasources it is necessary to
Understand the various update modes available for the logistics applications within the SAP ECC 6.0 system.
The following three update methods are available;
a) V1 Update
b) V2 Update
c) V3 Update
While carrying out a transaction, for e.g. the creation of a sales order, the user enters data and saves the transaction. The data entered by the user from a logistics application perspective is directly used for creating the orders, having an integrated controlling aspect, and also indirectly forms a part of the information for management information reporting. The data entered by the user is used by the logistic application for achieving both the above aspects, but the former, i.e. the creation of the order takes a higher priority than result calculations triggered by the entry. The latter is often termed as statistical updates. The SAP system treats both these events generated by the creation of order with different priorities by using two different update modes for achieving the same, the V1 update and the V2 update, with the former being a time critical activity. Apart from these two update modes SAP also supports a collective run, called the V3 update, which carries out updates in the background. The update modes are separately discussed below.
V1 Update:
A V1 update is carried out for critical or primary changes and these affect objects that has a controlling function in the SAP System, for example the creation of an sales order (VA01) in the system. These updates are time critical and are synchronous updates. With V1 updates, the program that outputs the statement COMMIT WORK AND
WAIT which waits until the update work process outputs the status of the update. The
program then responds to errors separately. The V1 updates are processed sequentially in a single update work process and they belong to the same database LUW. These updates are executed under the SAP locks of the transaction that creates the update there by ensuring consistency of data, preventing simultaneous updates. The most important aspect is that the V1 synchronous updates can never be processed a second time. During the creation of an order the V1 update writes data into the application tables and the order gets processed. The V1 updates are carried out as a
priority in contrast to V2 updates, though the V2 updates are usually also processed straight away.
V2 Update
A V2 update, in contrast with V1 is executed for less critical secondary changes and are pure statistical updates resulting from the transaction. They are carried out in a separate LUW and not under the locks of the transaction that creates them.They are often executed in the work process specified for V2 updates. If this is not the case,
the V2 components are processed by a V1 update process but the V1 updates must be
processed before the V2 update. They are asynchronous in nature.
V3 Update
Apart from the above mentioned V1 and V2 updates, the SAP system also has another update method called the V3 update which consists of collective run function modules. Compared to the V1 and V2 updates, the V3 update is a batch asynchronous update, which is carried out when a report (RSM13005) starts the update (in background mode). The V3 update does not happen automatically unlike the V1 and V2
Updates. All function module calls are then collected, aggregated and updated together and are handled in the same way as V2 update modules. If one of the function modules increments a statistical entry by one, this is called up 10 times during the course of the transaction. Implementing the same as a V2 update runs 10 times after the V1 for the same has been completed; i.e. the database is updated 10 times. But when executed as a V3 update, the update can be executed at any time in one single operation with the same being carried out in one database operation at a later point in time. This largely reduces the load on the system.
Delta Queue Functions
The LO datasource implements its delta functionality using the above update methods either individually or as a combination of them. SAP provides different mechanisms for pushing the data into the delta queue and is called update modes. The different update modes available with LO datasources are;
- Direct Delta
b. Queued Delta
c. Un-serialized V3 Delta
Direct Delta (V1 update)
A direct delta updates the changed document data directly as an LUW to the respective delta queues. A logistics transaction posting leads to an entry in the
application tables and the delta records are posted directly to the delta queue using the V1 update. The data available in the delta queue is then extracted periodically to the BI system.
Advantage of direct delta
a. Writing to the delta queue within the V1 posting process ensures serialization
by document.
b. Recommended for customers with fewer documents.
c. Extraction is independent of V2 updating.
d. No additional monitoring of update data or extraction queue required.
Disadvantage of direct delta
a. Not suitable for scenarios with high number of document changes.
b. Setup and delta initialization required before document postings are resumed.
C.V1 is more heavily burdened.
When using this update mode, no document postings should be carried out during delta
Initialization in the concerned logistics application from the start of the recompilation run in the OLTP until all delta init requests have been successfully updated successfully in BW. The data from documents posted is completely lost if documents are posted during the reinitialization process.
Queued delta (V1 + V3 updates)
In the queued delta update mode the logistic application pushes the data from the concerned transaction into an extraction queue by means of the V1 update. The data is collected in the extraction queue and a scheduled background job transfers the data in the extraction queue to the delta queue, in a similar manner to the V3 update, with an update collection run. Depending on the concerned application, up to 10,000 delta
extractions of documents can be aggregated in an LUW in the delta queue for a datasource. The data pushed by the logistic application can be viewed in the logistics queue overview function in the SAP ECC 6.0 system (transaction LBWQ). SAP recommends the queued delta process for customers with a high amount of documents with the collection job for extraction from extraction queue to be scheduled on an hourly basis.
Benefits
When the user need to perform a delta initialization in the OLTP, thanks to the logic
of this method, the document postings (relevant for the involved application) can be
opened again as soon as the execution of the recompilation run (or runs, if several
and running in parallel) ends, that is when setup tables are filled, and a delta init
request is posted in BW, because the system is able to collect new document data
during the delta init uploading too (with a deeply felt recommendation: remember
to avoid update collective run before all delta init requests have been successfully
updated in the user BW!).
By writing in the extraction queue within the V1 update process (that is more
burdened than by using V3), the serialization is ensured by using the enqueue
concept, but collective run clearly performs better than the serialized V3 and
especially slowing-down due to documents posted in multiple languages does not
apply in this method.
On the contrary of direct delta, this process is especially recommended for
customers with a high occurrence of documents (more than 10,000 document
changes - creation, change or deletion - performed each day for the application in
question.
Extraction is independent of V2 update.
In contrast to the V3 collective run an event handling is possible here, because a
definite end for the collective run is identifiable: in fact, when the collective run for
an application ends, an event (& MCEX_nn, where nn is the number of the
application) is automatically triggered and, thus, it can be used to start a subsequent
job.
Limits
V1 is more heavily burdened compared to V3.
Administrative overhead of extraction queue.
The job uses the report RMBWV311 for collection run and the function module will have the naming convention MCEX_UPDATE_<Application>, MCEX_UPDATE_11 for sales
orders. In the initialization process, the collection of new document data during the delta initialization request can reduce the downtime on the restructuring run. The entire extraction process is independent of the V2 update process.
Un-serialized V3 Update (V1/V2 + V3 Updates)
In this mode of delta update the concerned logistic application writes
data to update tables which further transfers data to the delta queue by means of a
collection run call V3 update. Once the data is updated to the update tables by the logistic applications, it is retained there until the data is read and processed by a collective update run, a scheduled background job, the V3 update job, which updates all the entries in the update tables to the delta queue.
As the name suggests the update is un-serialized, i.e. this mode of update does not ensure serialization of documents posted to the delta queue. This means that the entries in the delta queue need not correspond to the actual sequence of updates that might have happened in the logistic application. This is important if the data from the datasource is further updated to a DSO in overwrite mode as the last entry would overwrite the previous entries resulting in erroneous data. An un-serialized delta update when used should always update data either to an infocube or to a DSO with key figures in summation mode. It is also advised if the un-serialized V3 update can be avoided to documents subjected to a large number of changes when it is necessary to track changes.