0 Flares Twitter 0 Facebook 0 Google+ 0 LinkedIn 0 Email -- Pin It Share 0 0 Flares ×

Key Generation Transformation

Key Generation TransformationSummary:

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.

Prerequisite:

  • 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.

Example Scenario:

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
CustomerNumber Customer Name Address
100000 Jock USA
100001 Abraham USA

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.
CustomerName Address
Roshan IND
Krishna IND

Figure 2: Sample Source Data (files or Database Table) 

  • Figure3 shows Sample target data after adding new Customer’s data in data base table 
CustomerNumber Customer Name Address
100000 Jock USA
100001 Abraham USA
100002 Roshan IND
100003 Krishna IND

Figure 3: Sample Target Data

  • In project area we have to create project ,Job, work flow and data flow shown in figure 4
Key Generation Transformation in SAP BODS
Figure 4: Job Hierarchy
  • 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.
Key Generation Transform in BODS
Figure 5: Job ETL Flow
  • Shown in figure 6 drag all columns schemain into schema out and create new output column for “customer number”  with primary key.
Key Generation Transform in SAP BODS
Figure 6: Query Transformation
  • 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.
Key Generation Transform in BODS 1
Figure 7: Key_Generation Transformation
  • Actual data in Database table
Key Generation Transform in BODS 2
Figure 8: Initial data in Database table
  • newly added customers in file(source data)
Key Generation Transform in SAP BODS 1
Figure 9: New added Customers in file\table (Source data)
  • Shown following figure 10 target data in database table after adding new customers
Key Generation Transformation in SAP BODS 2
Figure 10: Target data after adding customers into table

Attachments:

Source Files:

  • Use the below files as sources for working out on the transformation

Click Here to Download Source data File 1

Click Here to Download Source data File 2

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.

Click Here to Download ATL File.

that’s it.

Enjoy 🙂

0 Flares Twitter 0 Facebook 0 Google+ 0 LinkedIn 0 Email -- Pin It Share 0 0 Flares ×