Exploring SAP Datasphere new features: Capturing Delta Changes in Your local table and Transformation Flow

[Bruno_Beghini]

Introduction

On September 20^th, 2023, SAP has realized new features for SAP Datasphere, in this blog I am focusing on “Capturing Delta Changes in Your local table” which can be very useful in plenty of scenarios where changes on the source needs to be capture. I will perform also a quick walk-through the new Transformation Flow that can load those delta changes from one source table to a target table.

For further details please check:

https://help.sap.com/whats-new/48017b2cc4834fc6b6cae87097bd9e4d?locale=en-US

  

Capturing Delta Changes in Your local table

 

How does it work?

 

When creating a new local table you are able to switch the “Delta Capture” on:



When this is done a delta capture table will be automatically created <table_name>_Delta and two additional fields are added to the table definition:

Important:

• You can change both business name and technical name but you can't change the data type (read-only).


• The delta capture columns can't be set as key column.


• They can't be deleted if the toggle is switched on.


• Once the table is deployed, the toggle can't be switched off.

 

Here we can see exactly the difference of a local table with delta capture disabled (Left) and a local table with delta capture enabled (right):

As you can realize there are two objects (active records table and delta capture table) that are responsible for showing data accordantly for when consume for the following SAP Datasphere apps:

	Table with active records only	Table with delta capture columns	More information
Table Editor - Data Preview	Not used	Used as source object	Data Preview is available once deployment is completed, and show only table with delta capture columns. Viewing or Previewing Data in Data Builder Objects
Table Editor - Data Maintenance	Not used	Used as source and target objects	You can perform table maintenance once deployment is completed.Maintain Local Table Data
Table Editor - File Upload	Not used	Used as source and target objects	You can update a table with delta capture by uploading a new csv file, after deployment is completed. Load or Delete Local Table Data
Transformation Flow	Used as source objects	Used as source and target objects	Source: You can choose between source with "Delta Capture" or "All Active Records". See Add a Source Table Target: It might depends of the combination of the load type used and the table type (local table with or without delta capture). See Processing Changes to Source and Target Tables and Add or Create a Target Table
Replication Flow	Not used	Used as target object	Creating a Replication Flow
View Builder	Used as source object	Not used	Only active records table is visible in Data Builder Landing Page Repository Tree as shown in View Builder, Transformation Flow, Data Flow, Replication Flow, ER Modeler Association targets list Graphical view - Calculated column - currency conversion tables list Creating a Graphical View
ER Model	Used as source object	Not used	Only the active records table is visible in the repository tree, but you can create local tables with delta capture. See Create a Table in an E/R Model
Data Flow	Used as source object	Not used	Only the active records table is visible in the repository tree. SeeAdd a Source
Business Builder	Used as source object	Not used	Table with delta capture column is hidden when searching for entities. Only the "Active Record" table can be selected.

 

Reference:

https://help.sap.com/docs/SAP_DATASPHERE/c8a54ee704e94e15926551293243fd1d/154bdffb35814d5481d1f6de14...

 

Below I will show case the difference of creating and loading a local table with and without delta capture option enabled:

• Local Table without delta capture (Delta Capture Disabled)
  
  
  
  1. Create table based on file
  
  
  
  

Table creation based on file

Note: Date, Sales Rep, Location and Product were set as Key fields

Table created successfully with the flat file data.

1. Loading flatfile data

Now lets imagine the scenario where you need to change some data in the flatfile and upload the same record again (record with same key). E.g.: below file the Plan value of 20230101;Sales RepA: Location A; Product 01 has changed from 7100 to 7000.

Duplicated Key Error

 

After trying to upload the file the system shows unique constraint violated error, that means the file contain duplicated records considering the defined key, or that the file contains a record that is already in the table (that is the case).

This behavior is by design, SAP Datasphere only uses “insert” to add records to a local table via file upload functionality, alternatively you can enable the flag “Delete Existing Data Before Upload”, that way the system will delete all existing entries before reloading it. For this you must have the complete file considering the local table data will be truncated before uploading the new data. Note: this is not required if the flat file contain only new data (data with different key).

Using the “Delete Existing Data Before Upload” option:

Using Data Editor:

Updates in the file content can also be done using Data Editor, this can be very handy for small adjustments on the data:

• Local Table with delta capture ( Delta Capture Enable)
  
  
  
  1. Create table using Delta Capture
  
  
  
  

Now  using the same table as a template I will create a new table with the Delta Capture Option activated. Note: Currently it is not possible to change this flag after the table has been deployed.

Just for simplification purposes and time optimization I am using the "save as" option to create a copy of the existing table, but it can also be created from scratch if needed.

Now we are able to switch the delta capture on:

Keep in mind that after deploying it is not possible to set this functionality off.

As you can see in the image below two additional fields were added (Change Type and Change Date):

These fields are used to control the delta of the table content.

Uploading initial data

Now we have just uploaded the initial data using the same file previously utilized:

This is how the data looks like after the first load:

 

Uploading same file with changes

As a next step we are going to change the plan value of the first record of the file and upload it again:

Then after loading the file we can see the new values with the new change date:

Note: As mentioned in the help.sap.com documentation the Change Type for Flat files is always “I” and in case you need to delete some specific records you need to use data editor or upload a file with only the necessary records and use the deletion flag, as demonstrated below:

Deleting data:

The first record is deleted from the flat file:

And the file was uploaded again using the flag:

As you can realize the first record got the deletion flag:

In this case when the deletion flag is active the system eliminates the record from the active table but keep the records in the delta table with the change type ‘D’ in order to capture the deletion in the delta mechanism.

 

 

 

Note: if you dele the table content using the delete data from table(
)option:

Both delta and active records table will be empty:

 

Modifying the data using data editor

In the example below the following procedures were performed:

• Row 1: New inserted record


• Row 2: Deleted record


• Row 3: Updated record


• Row 4: Existing record

As displayed above the change type and change date works accordantly.

 

Transformation Flow

Now I am going to show how it works together with the brand new “transformation flow“:

• First you need to create a new transformation flow und data build:

• Then you can click on the edit icon on the left side of the view transformation:

• View transformation: here is where you can perform all the necessary joins, calculations, aggregations and filters you might need, first you just need to drag and drop the table you want to use as source:

• Then you can add all necessary transformation, in this example I will add a calculated field that indicates if the actual is equal or higher than the plan (Target Achieved):

CASE WHEN Actual >= Plan THEN 'YES' ELSE 'NO' END

 

• You can go back to the previous screen and add a new target, in this case we are going to use the default target_1:

Note: since we are using as source a delta capture enabled local table the delta capture option on the new table (Target_1) is already enable by default.

 

• Deploy the transformation flow:

 

• Using Data preview option we can see the data in any step of the transformation, here is the content of the source after calculating the “Target Achieved” column:

• In the left pane of the transformation flow we can choose the load type (“Initial” or “Initial and Delta”):

• And in the top are we can run the transformation flow or also add a schedule on the calendar icon in the left pane:

• After running the transformation flow you can check the status on the left pane or on the Transformation Flow monitor:

 

 

• Now I will reload the flat file an then perform some changes (also deletions) and trigger the transformation flow execution:

Data loaded to the Target Table before changes:

Source Table after changes (Deletions, Updates and Inserts as you can see in the Change type column):

 

In the DIM (Data integration Monitor) you can check the Run details:

Additional details can be seen in the messages, example when you click in “view detail” of “Reading data from the ''View Transform'' operation and writing it to the target table.” The system will display the operation used to write the data in the target table.

If you realize here the “Change Date” is used to control the deltas that have already been loaded during my previous tests and allow only the new data to be loaded.

• This is how the target looks like after the transformation flow run:

 

Conclusion

In my opinion those new features are great improvements that brings enormous benefits to the business in terms of agility and scalability and for sure there is much more to come.

For more information about the next improvements regarding SAP Datasphere, please check its roadmap here:

https://roadmaps.sap.com/board?PRODUCT=73555000100800002141&range=CURRENT-LAST#Q4%202023

Also, in case of some improvement needed please submit your idea here:

https://influence.sap.com/sap/ino/#/campaign/3433