Delta extraction of ADSO from SAP BW bridge into SAP Datasphere via Transformation Flow

[wilson_kurian3]

With the latest version of SAP Datasphere (2024.6), we now have the delta extraction feature for the ADSO in SAP Datasphere, BW Bridge and it simplifies the data replication from BW Bridge to Datasphere.

More info on this topic is given in the help page below.

https://help.sap.com/docs/SAP_DATASPHERE/c8a54ee704e94e15926551293243fd1d/4c088d1f9bc04d95b7b7cb3801...

How it works

Previously in SAP Datasphere, when importing the ADSO (from BW Bridge) via the “import entity” feature there was only 1 remote table that was visible as source representing the “active” data table of the ADSO. With the new delta feature, we have the option to import 2 remote tables for the ADSO with the naming conventions adopted as ADSO Name- “Reporting” and ADSO Name - “Extraction”. The Reporting table represents the active data table as before, but the new “Extraction” table represents the delta changes of the ADSO.

When previewing the data of the “Extraction” table we get the same data set as the “Reporting” table. In order to get the delta only records from the “Extraction” table we must use a Transformation Flow to extract the delta records into a target local table in SAP Datasphere. This local table can then be used as a source for further modelling of the delta records or send to other outbound destinations via the Replication Flow. These outbound destinations could be SAP or non-SAP by taking advantage of the SAP Datasphere, Premium Outbound Integration Service.  

Prerequisite

• The ADSO in BW Bridge should be a standard one with “write change log” enabled.
• The ADSO should have at-least 1 primary key defined.
• The properties of the primary key/keys should have the Master Data Check enabled for reporting.

Example

In this example I have taken a very simple case of updating a master data object to see the delta changes. I import a standard ADSO called Plant Master Data (Tech name – ZPLANT_MD) from my BW Bridge into the BW Bridge space in Datasphere and then I will share the “Extraction” table with my own space. In my own space I will then create a Transformation Flow which will extract delta only records from the “Extraction” table into a target local table. I will then make some changes to the source data so we can see that the Transformation Flow extracts only the changed records to our target local table.

1. Creation of an ADSO in BW Bridge

In my BW Bridge, I created a standard ADSO with 2 Info objects (for simplicity). It is imported that at-least there is 1 primary key and the key has the Master Data Check enabled for reporting.

2. Importing ADSO from BW Bridge into Datasphere

In the BW Bridge space, under the Data Builder I used the “import entity” feature to import the ADSO via the BW Bridge connection. I searched for my ADSO and now I have the option to import and deploy 2 remote tables. As you can see below, by default Datasphere distinguished both tables by giving an extension to the table viz - “Reporting” and “Extraction” extensions. In this case I am only interested in the “Extraction” table, and I import that.

3. Check the delta table features

Once the table is imported you can see the below new properties of the table

• The table is “delta capture” enabled and the actual source delta table is denoted by an extension “ _delta”.
•  In addition, under the Columns Tab, there are 2 New Timestamp fields – Change Type and Request TSN which are used in the Input Parameters tab
• In addition, under the Input Parameters Tab there are 3 new parameters which are used to filter the data for delta only records when extracting the data via the Transformation Flow.

4. Creation of Transformation Flow in Datasphere

I shared the ADSO “Extraction” table with my own space and then in my space I created a Transformation Flow to extract it to a local Datasphere table with the “Delta Capture” and “Initial and Delta” options as per below.

5. Initial full run of the Transformation Flow

I ran the initial run of the Transformation Flow and checked the results of the target table. I got 287 records which are same as in my Active table entries.  

In my example below the Plant = 1000 = Hamburg and there is no entry for Plant = 1700. I will use these entries to perform some changes in the source so that delta changes are captured.

6. Creating changes for delta extraction

I changed my source data and in this example, I have done the following changes to my source data.

1. Update - changed Plant 1000 = Hamburg to Plant 1000 = Hamburg (updated)
2. Insert - Added 1 new record, Plant 1700 = London

I triggered the delta DTP in BW Bridge which updated the ADSO with the changed records. In this example I can see in my “BW Cockpit” that my ADSO has 2 new changed records.

 

7. Delta run of the Transformation flow to fetch delta only records

To fetch those changed records I ran the Transformation flow again and it fetched 2 records as it recognized that the ADSO has 2 new records. This is the key part of the scenario where the delta only records are extracted as previously only full extraction was possible from the ADSO of the BW Bridge. Now when I preview my target table, I can see that the records are updated and added accordingly.

[abhimanyu_sharma]

In case of huge volume how do we perform delta init without data transfer.

so that i can load historical data using full mode with filters and then switch on the delta ? 

does data sphere provides this capability? 

[MKreitlein]

Hello @wilson_kurian3 

Wow, really helpful Blog. So far I have not yet worked with an ABAP Bridge, but somehow my feeling while reading was: "Oh! I assumed so far that the BW Bridge is similar to BW4 and that extracting data from a Changelog - even if into SAP Datasphere - would be the same like within BW?!

Did you also test the behaviour of a key figure?

If your DSO contains a value 100, and you update it to 50. I assume the Extraction table will not contain two records, -100 and +50, it will only contain one record with 50. Right?

BR, Martin

[wilson_kurian3]

Hi Abhimanyu, if you delete the change log table of your source ADSO in the Bridge then the Transformation Flow will only pick the delta records even if you select the "Initial and Delta" option in the TF. This way you can get only the delta records from the extraction table. The full load you can use the standard "Reporting" table and apply filters to fetch whatever dataset you need.

[wilson_kurian3]

Hi Martin, yes you are correct it will have the changed record only.

[abhimanyu_sharma]

Hi ,

init will delta will try to fetch all the records from source ADSO as it is the initial load.

if my requirement is to send 100 million records to my local table or outside of DWC; initial with delta try to bring all the 100 million record in the first run.. isn’t it ?? 

in BW we have delta init without data transfer option is there where it will set the pointer and the next delta will send the data from the last delta pointer date. 

[abhimanyu_sharma]

Please check this : Init with delta picks data from Active table first time.

Does it mean SAP Data sphere have different mechanism where Init with delta pick data from Change log ?

[wilson_kurian3]

Hi Abhimanyu, DSP has a different method here. That's why I said, if you only want delta records then clean up the change log table in your source ADSO and then load delta into the ADSO and then do the TF so that the Extraction table won't pick up the initial load and will only pick up the delta records. The Extraction table works little different here because it looks at the change log table and then uses the parameters to define the delta records. If you want full load then you can directly you the Reporting table.

[abhimanyu_sharma]

Thanks for the clarification @wilson_kurian3