Introduction
Recently, while working on a Greenfield BW/4HANA project, I found myself asking a lot of questions about the inner workings of the SAP Operational Delta Queue (ODQ) which replaced the traditional BW delta queue (RSA7) in SAP ECC system. What is ODP framework? What is the location of ODQ? How ODQ behaves for different delta types? What are important ODQ tables? How ODQ retention period is maintained? Having done some digging into the ODQ itself, I discovered more detail than I’d seen online, so thought it would be worth sharing my findings in this blog.
The ODP Framework
Operational Data Provisioning (ODP) is an SAP Netweaver based Framework that became available from Netweaver 7.30 (in the context of BW: Netweaver 7.40). It provides a new way to connect ERP to other systems and different BW systems. It is a flexible framework which allows unifying the data transfer between source and target system.
The standard BW Extractors (SAPI) present in ECC or S/4 systems have been made ODP compatible via the ABAP program ‘BS_ANLY_DS_RELEASE_ODP’ (SAP Note 2232584). These extractors are not only used in context of BW but also for other targets (e.g. ODATA, Data services etc.). However, it is worth noting that with BW/4HANA as the data target, only ODP extractors are supported. Figure 1 below gives you an overview of the ODP framework.
Figure 1: Operational Data Provisioning (ODP) Overview
Integral to the Operational Data Framework is the Operational Delta Queue (ODQ). It is a physical queue that encapsulates data for a consumer. Here, the source application is termed as a provider of the Operational Delta Queue (ODQ), and the target application of the delta queue is referred to as the ‘consumer’ or ‘subscriber’. In most cases, ODQ is the data store in the source system (see section – “Location of the ODQ” for details).
Consumers are categorised by their type (for example, Data Services, BW, OData, etc.). In the context of BW, a consumer (i.e. BW) can have multiple subscriptions (i.e. different DTPs) of the same delta queue (DataSource). A subscription occurs when the consumer requests data. Every subscription (i.e. each DTP in a BW context) has a unique transaction number, for example {2019-03-18 05:35:39 008537 UTC}. Please see figure 2 below.
Figure 2: Transaction ODQMON: the delta queue monitor for BW subscriber
Here are some of the key features of the ODQ:
◉ Enables “extract once, deploy many” architectures for data sources. For example, BW and BODS can extract from the same delta queue (ODQ) of the extractor.
◉ Unified configuration of monitoring for all provider and subscriber types.
◉ Highly efficient data compression (>90%).
◉ Provides service quality level Exactly Once In Order (EOIO), i.e. takes loads from source systems because data is extracted once and then distributed over ODP as needed, be it in real-time or after defined time-intervals.
◉ Intelligent parallelisation options for subscribers in high volume scenarios.
◉ Monitoring of the PSA is replaced with that of the ODQ (Transaction code: ODQMON).
◉ Data cannot be changed in ODQ (a feature that previously did exist with the PSA).
Location of the ODQ
ODQ is generally present in the S/4 or ECC source system for SAP standard extractors. You can see in Figure 3 where the ODQ is present in the source system for ODP_SAP and ODP_SLT type sources. However, if data is accessed from an SAP HANA database system (via delta-enabled HANA Information Views or ABAP CDS views), ODQ exists in the target BW system (see the BW system 2 in fig. 3). When a BW system (BW system 1 in fig. 3) is extracting data from an InfoProvider on another BW system (BW system 2 as per fig. 3), via an ODP-BW source system, ODQ is present in the source BW system.
An important point to note is that in the context of delta-enabled SAP HANA views or ABAP CDS views, the operational delta is managed by the target i.e. the BW4HANA system. However, if the target system is Data Services or OData, only full load is possible as there are technical limitations to create and run the ODQ in source S4 or ECC system for SAP HANA views or ABAP CDS views. Data Services and OData can only extract delta data from ERP extractors, SLT and BW systems, as for these providers ODQ is created in the source system.
Figure 3: Overview of Key ODP DataSources connecting to SAP BW (Source: training.sap.com/learninghub)
Delta Type Process for ODQ
The Operational Delta Queue is mainly used for the below delta types for ODP extractors:
Delta Type ‘D’ – The SAP application writes delta records directly into ODQ (PUSH) for ODP extractors with delta type ‘D’. Each data record is either stored in the ODQ individually on saving/updating the corresponding transactions in the application (for example, direct delta in the LO Cockpit) or is written in groups of delta data records (after updating the transaction) to ODQ using application-specific jobs (LO Queued delta). In each case, the delta data records are in the ODQ for the SAP source system before the delta update is requested from the target system (for example BW). In the case of delta updates for the DataSource, the ODQ is read, and the data records that exist there are transferred to the target system (for example, BW).
Delta Type ‘E’ – The ODP data source determines the delta through the extractor on request. The extractor must be capable of providing the delta records for the DataSource on demand (PULL). The delta data records that have been determined are placed in the ODQ by the extractor and are transferred from there to the requesting target system (for example BW). If you have carried out delta initialisations for more than one SAP BW system, or several delta initialisations for the same BW system, the extractor has to stage delta records for all the delta initialisations and store them in all the ODQs that are specific to the target systems (for example BW.) This means there is a one-to-one relationship between each ODQ and each subscription.
Figure 4: Delta Type Process for ODQ (Source: training.sap.com/learninghub)
Figure 4, above, shows a comparison of two different SAP BI content DataSources with different delta processes and how ODQ is filled with delta records:
◉ Datasource 2LIS_02_ITM (Purchasing data (item level)) with delta type D (push):
◉ As this is delta type PUSH, the delta data records from the application are posted to the delta queue before they are extracted from ODQ as part of the delta update. If the DataSources are from the LO cockpit, this push takes place either with a direct delta (directly from the application, V1 – updating) or with a queued delta (from the application to the extraction queue (V1), and from there to ODQ with a collective run).
◉ Datasource 0FI_GL_4 (GL: Line items) with delta type E (PULL):
◉ As this is a ‘PULL’ delta-type, the delta data records are determined during the delta update by the Datasource extractor, updated to the ODQ, and passed on to the SAP BW directly from there.
ODQ Tables
The ODQ uses the following three tables to store data:
1. ODQDATA_C Contains compressed Init request data
2. ODQDATA_F Contains compressed Full request data
3. ODQDATA Contains compressed Delta request data
DTP’s fetch data directly from the ODQ. The first time you run a DTP, the ODQ performs a delta initialisation where a request for the DataSource is generated, and table ODQDATA_C is filled. In the second run, the ODQ does a delta update with those records that were created or changed (including deletions) since the last load. The delta records are stored in the ODQDATA table in a compressed format.
Figure 5: ODQ Tables (Source: training.sap.com/learninghub)
ODP Extraction Test
ODP DataSource extraction can be tested with the program RODPS_REPL_TEST (in place of RSA3 in traditional BW). However, RODPS_REPL_TEST acts as a separate additional subscriber, so it is not a simulation (unlike RSA3). A new subscriber is created in the queue entries for the DataSource in ODQMON. You should remember to manually reset/end the subscription in ODQMON for these replication tests created by your checks – if you don’t stop the test subscription, the delta table ODQDATA might grow quite significant over time and have an impact on general ODQ loading performance. This is because an ODP source uses only one delta queue, which is shared between the various subscribers. The delta data is only cleaned up if all subscribers picked the delta.
If the subscription is terminated, the created requests are subject to the regular ODQ clean-up job and will disappear after the retention time.
ODQ Retention Period
The data in ODQ is retained for reconciliation and recovery. Although it’s not intended to replace the PSA’s long term persistency, you have control over how long to keep the queue’s data after it has been successfully sent to all targets. Recovery of more than just the latest data loads depends on retention period settings. The default is 24 hours for any data in the queue that is flagged as retrieved or as cancelled.
The job to reorganise delta queues is created by default when delta initialisation request is executed from the subscriber, e.g. BW. The time and schedule of the job can be changed manually by program ODQ_CLEANUP or by selecting “Reorganize delta queues” under the Goto menu in transaction ODQMON (Figure 6).
Figure 6: ODQ Retention period
All data in the delta queue is considered business-critical and is therefore not deleted until it has been flagged as either retrieved or invalid.
The default setting of an initialisation request is “Data with low relevance”, which will result in the data being deleted after ten days if extracted to BW, and works in the same way if set to “Data with average relevance”.
ODQ Tips and Tricks
Following are a few issues, I faced while loading data from ODP extractors in the source SAP ECC system to the target BW/4HANA system.
◉ If the delta extraction fails due to some error in the source ECC or S4 system, the ODQ in source system (transaction ODQMON) shows a failed request. There is no way you can delete this failed request without impacting deltas. So you first need to fix the error and then re-run the extraction request. To do this, you can check the errors from the error logs and fix that in the ECC/S4 system. Once the error is fixed, the extraction request can re-run to load the data successfully. Once extraction is successful, execute the DTP in BW to load data in BW from this request and other following delta requests. In this way the delta request can be restored without impacting the delta mechanism. Please see figure 7 – Failed request in ODQMON.
Figure 7: Failed request in ODQMON
◉ The above scenario is where the request fails in the source system. If the delta request fails in the target system, it is easy to delete the data load request and subsequently can be reloaded into the target system. However, if the target system is BW and the data is loaded successfully but fails during activation in an aDSO due to erroneous invalid characters in the records, you cannot re-load and activate the data in BW even if you fix the incorrect records in the source system (for example ECC or S4). This is because it is not possible to delete or change any request in ODQMON. If the request is successfully loaded with erroneous records in ODQ, it will get loaded to BW unless you re-initialise or write an ABAP code in the target BW system to clear the invalid characters from the records. I hope that SAP provides an option to overcome this scenario.
No comments:
Post a Comment