SAP Datasphere – Delta Extraction

[Martin_Kuma]

Optimizing Dataloads requires delta functionality.

Here a How-To for a Generic Delta-like extractor within DSP.

Prerequisite is a ‘last change date/timestamp’ or a ‘unique document counter’ in the source view, based on which the delta will be determined.

In DSP two components will be used:

• The Delta table, which contains the ‘Extractor’ and ‘Last Extraction’ Date. This date provides the info to the Extractor itself on what to load – the Delta. It always loads data from the Last Extraction Date till Today. The Delta Table is general for all Extractors.
• Actual Extractor, from where we load the Delta data. The Extractor has several steps which include the Extraction View, Datapshere transparent table and Extractor Delta updater. Each Extractor has its own Task Chain and own 3 Task Chain steps. 

Objects Used:

Type	Usage
Table	Extractors and Last Extraction Dates
Extractor	Task Chain controls the actual Extractor
SDA Table	Remote Table
View	Data Load View used for the Delta Extraction
DSP Table	Local Table used to persist all data
Data Flow	Flow pushes the Delta data into Local table
Transformation Flow	Flow updates the Last Extraction Date

How To:

Add new entry into the Delta Table

Create Delta View with selection using the entry from Delta Table

Create Local Table (type FACT so it can be used in Queries, Analytical Models) with all Dimensions as Key

Create Task Chain with 3 Steps:

1. First Step is the Data Load View persisting the Delta Data (using objects from #2 and #3)
2. Second Step is Data Flow loading data from Delta View to Local Table
3. Third Step is Transformation Flow updating the Delta Table

 #1 Delta Table

Use: Extractors and Last Extraction Dates

Type: Local Table

Semantic Usage: Dimension

Key: Extractor

Fields: Date, Extractor Description, Extractor Type, Upper and Lower Safety Interval and a Last Changed timestamp

 #2 Delta View with selection using the entry from Delta Table

Use: Loading the Delta Data

Type: View

Semantic Usage: Relational Dataset

Key: Not relevant / none as used for the Delta Data only

View uses an SDA/Remote source

Example: SELECT    … FROM "[SDA/Remote table with change date/timestamp]"

AND "[delta_field]" >= ( SELECT "DATE" FROM "[DELTA Table]" WHERE "EXTRACTOR" = '[extractor_name]' )

The Remote Table is used as the interface to Source System. The table has to have a Delta pointer. Either a Date, TimeStamp or Document Counter, what can be used to calculate the Delta.

Type: SDA Table

Semantic Usage: Relational Dataset

Key: Not relevant / none as used for the Delta Data only

#3 Local Table (type FACT so it can be used in Queries, Analytical Models) with all Dimensions as Key

Local table is holding all extracted data. To be used for Query-like Views or Analytical Models. Important is to set the Key to all inbound fields / dimensions including texts.  

Type: Local Table

Semantic Usage: Fact

Key: All Fields/Dimensions (including Texts, …) except Key Figures

#4 Task Chain:

The Extractor is controlled via a Task Chain:

View providing the Delta Data from the Extractor is based on the Delta table (View is persisted)

The Delta Data (Persisted data) are then moved to a local (FACT) table with UPSERT (Data Flow)

Delta Date is updated after success (Transformation Flow)

Data Flow

Pushing the Delta Data from Delta View into the Local Table holding all the data

Type: Data Flow

Uses:

Delta view persisting the Delta data

Local Table holding all data

 Transformation flow

Updates the Last Extraction Date of the Extractor

Type: Transformation Flow

Uses (same object as source and as a target): Delta Table for the Delta Extraction