Loading a Table and Adding a Surrogate Primary Key. The goal of this section is to configure the Surrogate Key Generator transformation. In this sample job, the surrogate key is generated using the default settings. By default, the transformation generates key values based on the largest value in the key. Hi all, I need to generate sequential id for my target table without using Sequence Generator Transformation, because the use of the sequence transformation introduces issues a number of risks as the current key value is maintained within the repository. Table comparison transformation is used to compare the two data sets and check what are the records were changed and updates, inserted and deleted. Comparing two data sets, this transform can generate the difference between them as a resultant dataset with each row of. Oct 24, 2013  Post subject: Re: Key Generation Function in BODS Scripts If you are using Oracle then use an Oracle Sequence. Much more efficient than Max and it supports concurrent jobs.

1. Introduction

Key Generator Transformation In Bods 2017

This document specifies technically, how to Capture Change Data during extraction from SAP R/3 system.

2. What is Change Data Capturing.

After Initial load is completed, we identify the changed or new record in the source system and update the target system with the changed record. Identifying and loading only changed data is called Change Data Capturing. This type of load is used to maintain the history data in the target system (SCD Type 2).

There are two CDC methods – Source based CDC and Target based CDC.

Source-based CDC evaluates the source table to determine, what record has changed and extracts only changed records to load into target tables.

Target-based CDC extracts all the data from the source, compares the source and target rows using table comparison and then loads only the changed rows into the target system.

3. Extraction using Target-based CDC

For Target Based CDC, we need to have Full and Delta load Data flows. For the Full Refresh, you can dump the data from SAP R/3 or BW into the Staging Table and then, load to the Target table (Oracle Database).

1) Full Refresh- Extracting data from SAP R3 table as a dump (Example., we will extract EKPO – Purchase item table data).

a) First, the data is extracted from SAP R3 system (say from table EKPO) and loaded to Oracle Staging table as shown in the below screen shot.

b) From staging table the data is loaded to the target table with a sequence number, that is generated using Key Generation transform.

For more information on, how to extract SAP R/3 Data into Oracle for Full Refresh. Refer the below link How to Extract SAP R/3 Data to Oracle using Business Objects Data Services

2) Delta Refresh – The changed records are identified from the source system and loaded to the target system as a new record. This is SCD type 2 method.

a) After Initial Load (Full Refresh), all the data from EKPO table is extracted and loaded to the staging table.

b) Create a Data Flow for delta refresh (EKPO_DELTA_TGT_LOAD) which involves Query, Table Comparison, History Preservation and Key Generator transformation as shown in the below screen shot.

c) Let us consider Column NETPR (Net Price in purchasing document) value changes frequently in SAP R3 EKPO table. The changed data needs to be captured into our target system as a new record,

d) The data are extracted from EKPO table and first loaded to the STG table.

e) Query Transform is used to map columns from the Source system. Also this transform can be used as filter from source system to target system using a condition in the Where clause.

f) Table Comparison transform is used to compare the data from the source table with the target table to determine the changes. This transform treats the changed data as insert. Here we compare the NETPR column from source and target to determine the changes.

Key Generator Transformation In Sap Bods

g) History preserving transform is used to convert the updated rows to Insert rows based on the columns in which values are changed. This transform produces a row with the changed column data to target, instead of overwriting the target row.

Here in our example, when the NETPR data changes in the source system, this row is inserted as a new row in the target table.

Transformation Name Generator

h) Key Generation transform is used to generate new sequence number for the updated records, which will be used as an insert record in the target table.

Key Generator Transformation In Bods And Dogs

i) Save and Execute the Job.