Communication of BW OHD delta load detail to third party systems

Communication of BW OHD delta load detail to third party systems: 

With open hub service APIs like RSB_API_OHS_DEST_GETDETAIL, RSB_API_OHS_DEST_SEND_NOTIFICATION and RSB_API_OHS_DEST_READ_DATA_RAW the delta loads extracted till OHD can be communicated to a third party systems. I would say that it is a kind of push mechanism from BW to 3^rd party though
the data is, in reality, read by 3^rd party system after the notification is sent to it. What if we wanted a pull mechanism where 3^rd party can pull the delta data at its convenience and if we wanted to highlight certain sequences in data read and certain patterns and scenarios in delta data to the 3^rd party system?

We can model our own communication mechanism. Here is one, on how we can model a pull mechanism of OHD data in a 3^rd party system and how delta records related patterns and scenarios in the OHD can be shown to the 3^rd party system.

Requirements: 

1. BW data loads are daily deltas. Data records loaded in a particular day’s delta in OHD should be identifiable with a specific identifier in OHD so that 3^rd party can pick the necessary data at its convenience. For e.g. deltas to OHD is loaded daily but 3^rd party wants to fetch delta only once in a week or once in a month or once in 2 weeks etc.
2. 3^rd party is a relational database and hence will be overwriting old status with recent status as of OHD data fetch. Therefore all before image records in OHD delta should be identifiable to 3^rd party so that it can restrict them in its read statement.
3. If a transaction is deleted in ECC, then all corresponding postings of that transaction has to be filtered out from 3^rd party reporting. So 3^rd party needs a deleted indicator for that transaction in the OHD.
4. There could be a possibility that transactional records in OHD could have undergone multiple changes between last 3^rd party delta fetch and next delta to 3^rd party. So the changes to the transactional records in OHD, even if they were loaded to OHD in different delta requests should be identifiable with a sequence. 3^rd party can then make use of this sequence to identify the latest change.
5. 3^rd party needs freedom of fetching the same delta more than once if need arises.

Solution:

1. In the transformation from DSO to OHD, a unique identifier, like a GUID or a unique numeric pointer etc., is generated which is stamped in all the records of that particular delta request to OHD. Now this unique identifier is entered into a Z Table in BW system along with the OHD table name and data load details like load date, time and status etc. 3^rd party system is given read and write access to this Z table so that it will be able to identify the necessary delta records that it needs to fetch from OHD (using the unique identifier). 3rd party system should also write its data fetch status in the Z table to track progress like 3^rd party fetch date and time and success message. This Z table concept enables 3^rd party to read the deltas how many ever times as they wish.
2. The before images in a DSO change log have RECORDMODE value ‘X’. If OHD gets delta data from DSO, then bring the field RECORDMODE to OHD as well. Now 3^rd party can filter out all records where RECORDMODE is ‘X’ during data fetch from OHD or even later.
3. If a transaction is deleted, the extractor will send this information in ROCANCEL field of the extractor. Map it to the RECORDMODE field of the DSO. Now a deleted transaction will have RECORDMODE ‘R’ in the DSO change log table. If we take the RECORDMODE field in OHD as well, the 3^rd party can identify deleted transactions in OHD with this ‘R’ RECORDMODE value.
4. We can introduce a timestamp field in the first level DSO and while loading from extractor we can derive this field in transformation with load system timestamp. Then even if a transaction undergoes multiple changes on same day or different days, the sequence and in effect the latest status can be identified with this timestamp. Take this timestamp till OHD. 3^rd party system can fetch all delta records relevant for it and then sort on timestamp field descending and pick the latest status alone and ignore others since it is overwrite in 3^rd party anyway.
5. With the help of Z table with OHD load details and delta record GUID details, 3^rd party can fetch relevant deltas more than once as well.

Some considerations for this topic:

1. How to grant accesses to 3^rd party to BW tables like the OHD table for read and Z table for read and write? This should not be a problem for closely knit systems. For others, I guess we can also extend the data read APIs to read and write data in BW tables.
2. How to load from BW cube to a OHD directly for this requirement? I guess ‘before image’ records in a delta can still be highlighted to OHD from cube if we manage to get the before images identified in the cube as well. Rest of the requirements can be solved in same way like a DSO to OHD load.
3. Can we achieve these with the standard APIs itself? I see that there is a sequence identifier in API ‘Data record (binary) with continuation indicator’. So most of the requirements can be achieved with standard APIs. I have doubts on how to give flexibility to 3^rd party to pull data from OHD at its own convenience (may be log the API notification in 3^rd party in a table along with load details and build the functionality in 3^rd party itself) and how to give flexibility to pull data multiple times or at specific intervals (again store the API notification in 3^rd party).

I expect that there will be variety of communication techniques used currently since sending data out of BW to 3^rd party is such a common topic. Share your technique as it will be interesting to see the ideas of the BW world and maybe we can discuss on pros and cons of different techniques.

Arunan. 😀