Applies to: SAP BW3.5, BI7.0
For more information, visit the Business Intelligence homepage.
Summary
In some business process data loading will be conducted on hourly bases through Timestamp Field, are often required as delta criteria for generic delta extraction. However, in many tables such the timestamp field is not available; instead the creation/change date and time are available. Generic delta needs to function on one field. This article explains how exactly request are processed BW system to at OLPT server and what are the dependent fields are interlinked to fill the TIMESTAMP field on runtime extractor.
Author(s): VIJAY.G.M
Company: Yash Technologies Private Limited
Created on: 3rd October 2011
With respect to above like document, OLTP system pulls the delta records based on following ABAP code,
But in document not reflecting how extractor gets filled at OLPT system by BW system. This document elaborates the bag round technical debugging observation at extract checker.
( erdat >= startdate and ( erfzeit >= starttime OR ( erdat <= enddate and erfzeit <= endtime ) ) )
OR
( aedat >= startdate and ( aezeit >= starttime OR ( aedat <= enddate and aezeit <= endtime ) ) )
Some major observations from the above table:
What are SMQ1 and RSA7?
SMQ1 (Out bound Queue) is the physical storage for all transactions created in the source system.
Delta queue is a virtual store that displays open and unprocessed LUWS against active initialized data sources available in the source system and fetches data from SMQ1 physical storage. In addition to the default structure of data source, there will be five additional fields which will get populated on the fly in Delta Queue.
Following are the fields which get populated on the fly for all data sources irrespective of data source type whether it is application specific (standard) or generic data source, if Data sources are using delta queue for delta processing
As specified in the below screen shot, highlighted fields are populated on the fly when the delta created for the particular data sources.
If the Extractor is using a function module when data is requested from BI
In this case only repeat delta will be visible in RSA7 because delta queue able store last delta records unless and until next delta requested .For example if the data source delta type is AIE (after image via Extractor) and above specified fields will be filled while loading data into BW.
When BW system request to load the initialization and delta update process, immediately two tables get updated at both system to maintain consistent extraction.
ROOSPRMSC ----------- OLTP System Table
RSSDLINIT -----------BW System Table
BW System table ROOSPRMSC has following information.
BW System table RSSDLINIT has following information.
What is LUW and how it will process when we request delta data from BW?
LUW is logical unit of work
LUW Processing
LUW is logical unit of work, The qRFC outbound queue controlled using an Outbound Scheduler (QOUT Scheduler). The QOUT Scheduler prompts the transfer of a LUW to a target system when all previous LUWs in this queue have been processed. When one LUW has been executed, the QOUT Scheduler automatically executes the next LUW in the queue.
In other words when we request delta load from BW, Source system will identify the last delta records which are in form of TID’s by using ROOSPRMSC table and it will delete previous confirmed LUWs(repeat delta table) and Process new LUWs(delta table)
How the source system will identify delta Records? What is GETID? What is GOTID?
ROOSPRMSC table will be used to identify last delta request and last delta LUW which has been loaded into BW
ROOSPRMSC: Control Parameter per Data Source Channel
This table stores all control parameters related to a data load.
Table fields and importance
INITRNR: This field provides the initialization request number
DELTARNR: This field provides the last delta request number
UTC Timestamp: This field provides the timestamp of the last delta request.
GETTID: This field refers to the last but one delta TID
GOTTID: This field refers to the last delta TID (that has reached to BW)
System will delete LUW’s greater than GETID and less than or equal to GOTID
For the next delta TID will be starting the succeeding TID of GOTTID, refer the above screen shots.
What is repeat delta and how it works?
The data is stored in compressed form in the delta queue. It can be requested from several BI systems. The delta queue is also repeat enabled; it stores the data from the last extraction process. The repeat mode of the delta queue is specific to the target system.
In the above example screenshot refers repeat delta LUW which has been loaded into BW for the previous extraction and this repeat delta will be deleted in the time of next delta request
Delta steps:
What is TID?
TID is concatenation of “IPADDRESS in which the record is created”, “Dialog Work Process used in creating service order”, “Timestamp at which the data is posted in SMQ1”, “Sequential number of record”.
In other words,
TID: ARFCIPID+ ARFCPID+ ARFCTIME+ ARFCTIDCNT
TID= Host ID (IP ID) +Process ID +Timestamp+ Transaction ID (LUW -> COMMIT WORK)
ARFCSSTATE, ARFCSDATA, TRFCQOUT
The Delta Queue is constructed of three tables
UNIX hex decimal timestamp converter
http://dan.drydog.com/unixdatetime.html
Example: 4A4D8809 = Friday, July 03, 2009 4:24:41 AM UTC (GMT).
Extractor Debugging Process.
In this way observed the time stamp filled by BW system.
SAP has delivered the standard ABAP program’RAC1_ DIAGNOSIS’ which will diagnosis consistence of delta extraction and loading.
Based on these observation, easily find out the delta missing records.
Refernce Doc : Note 583086 ‐ Diagnostic program for BW Delta Queue
References: http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/40427814-376a-2c10-5589-bc1aaa669...
Business Intelligence homepage.