Key Generation Transformation in SAP BODS
Key Generation Transformation
Key Generation Transformation is used to generate artificial keys for new rows in a table. The transform looks up the maximum existing key value of the surrogate key column from the table and uses it as the starting value to generate new keys for new rows in the input dataset. The transform expects a column with the same name as the Generated key column of the source table to be a part of the input schema.
- The source table must be imported into the DS repository before defining the source table for this transform. The fully qualified Table name e.g. DATASTORE.OWNER.TABLE should be specified.
In this Scenario Customer data exists in data base table as shown in Figure1, Then we have loaded new customers data into the same table but customer numbers are not available for newly added customer data in source data as Shown in Figure2. By using Key Generation Transform we can generate customer numbers for newly added customers Shown in Figure3 (based on existing data we can generate increment value).
- Figure1 shows Sample Existing Customer’s data in data base table
Figure 1: Initial Data in Database Table
- Figure2 shows Sample Source data for newly added Customer’s data in table\file without customer number column.
Figure 2: Sample Source Data (files or Database Table)
- Figure3 shows Sample target data after adding new Customer’s data in data base table
Figure 3: Sample Target Data
- In project area we have to create project ,Job, work flow and data flow shown in figure 4
- Below figure5 shows ETL flow of our Job.
- first load existing customer’s data by using “Imperio_KeyGeneration_Initial file” into table import that table and use this table as target.
- Create flatfile by using “Imperio_KeyGeneration_Source” file drag into work area.
- Drag Query transformation , KeyGeneration transforms in to work area connect all objects same as fig5.
- Shown in figure 6 drag all columns schemain into schema out and create new output column for “customer number” with primary key.
- Shown in figure7 select data store and actual table in “Table name”column,select customer number column as Generated key column and enter increment value.
- Actual data in Database table
- newly added customers in file(source data)
- Shown following figure 10 target data in database table after adding new customers
- Use the below files as sources for working out on the transformation
Note: First load initial data (Use Imperio_KeyGeneration_Initial file) into database table, import that table and use this imported table as target and then by using the source ( Imperio_KeyGeneration_Sourcefile) and load data to Target(imported table).
- Import the below .atl file in the Data Services Designer to find the Job for the above transformation.