Posted by Vijay Menasinakai
Applies to: SAP BW3.5, BI7.0
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:
- During initialization, the lower limit value is blank. The higher limit is the current time.
- During delta request, the lower limit value was 30 minutes, this value older than the higher limit value of the previous (i.e. init) delta request. This is due to the fact that we had given a safety lower interval of 1800. As a result, the lower interval was taken as the previous higher interval limit minus 30 minutes (i.e. 1800 seconds)
- There is a time difference of +5:30 Hours (India) between the Time of extraction column (which shows the system time when the delta request was received in the source system) and the Low and High fields. This is due to the fact that the timestamp configured as a UTC timestamp, and the time zone of the system is UTC+5:30 (i.e. five hour thirty minutes ahead of UTC). The same difference exists between the Low and High fields and the Start/End Date & Time, which are the fields obtained when the low and high timestamps were split. The difference in this case is also due to the time zone.
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
- Host ID
- Process ID
- Time Stamp
- Counter
- Counter
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:
- Identify previous delta LUW’s (repeat delta)
- Delete repeat delta LUW’s
- confirm unprocessed delta LUW’s
- Process unprocessed LUW’s
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)
- Host ID= IP address of system
- Process ID= Process ID of LUW (hexadecimal format)
- Dialog process id which is available in decimal format in SM51 and hexadecimal format of Dialog work process will be saved
- Timestamp: The time stamp of delta record posted into outbound queue(SMQ1) and timestamp will be in UNIX hexadecimal timestamp
- Host ID, Process Id and Timestamp will be saved in below tables respectively.
ARFCSSTATE, ARFCSDATA, TRFCQOUT
The Delta Queue is constructed of three tables
- ARFCSDATA: Raw Data, Based on the Extract Structure, but compressed.
- ARFCSSTATE & TRFCQOUT: Pointer tables to Access and control the flow of data to multiple BW systems.
UNIX hex decimal timestamp converter
Example: 4A4D8809 = Friday, July 03, 2009 4:24:41 AM UTC (GMT).
Extractor Debugging Process.
- When BW system request the data load, with help of last delta request, lower timestamp will filled (last extract date = start of next extract date) , for reference execute the extractor on debug mode with following steps.
- While debug mode concentrate on RSA2_SERV_GET_OLTP_SOURCE function module, on that extractor executes TIMESTAMP_CALCULATE Form (line Number 535).
- There some Incremental Conversion will calculated according to lower time stamp and upper time stamp on runtime and passes into extractor I_T_SELECT interface structure, based on this selection, function module logic will perform on records,
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.
No comments:
Post a Comment