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

Row Generation Transform

Row Generation TransformSummary:

The Row Generation transform does nothing else than generating one row after the other with one INTEGER column only.Row_Generation transform produces a dataset with a single column. The column values start with the number that we specify in the Row number starts at option. The value then increments by one to specified number of rows as set in the Row count option. This transform does not allow any input data set.

Prerequisite:  Following setting is required to be done at the Row-Generation transform level, options available under Row_Generation transform:

1)Row number starts at: An integer with which row numbering starts. If you set this option to 1, the first row will be labeled 1, the second row will be labeled 2, and so on. If you leave this blank, numbering will start at zero (0). For added flexibility, you can enter a global variable or substitution parameter.

2) Row count: A positive integer indicating the number of rows in the output data set. For added flexibility, you can enter a global variable or substitution parameter .

3)Join rank: A positive integer indicating the weight of the output data set if the data set is used in a join. Sources in the join are accessed in order based on their join ranks. The highest ranked source is accessed first to construct the join.

4)Cache: Select this check box to hold the output from the transform in memory to be used in subsequent transforms.Select Cache only if the resulting data set is small enough to fit in memory.

Example Scenario:

In this scenario we have two tables out  of which first table is related to Plant -it includes Plant, Number of Items(count),Item number starts(starting number of items) and second table related to Item details- it includes Item number & description of Items. When we select any Plant (i.e. P1) we need to get entire details (Item details like Item number and description) related to that concern plant (i.e. P1) in to separate target (File/Table).We can achieve this scenario by using Row generation.

Note: First we have to load Plant and Item Details from files to database tables (TB_Plant & TB_Itemdetails) by using “Imperio_Rowgeneration_Plant” and “Imperio_Rowgeneration_Item” files as sources (which is provided in Attachments).

  • The following figures1, 2 and 3 are Sample Plant, Item Details and target tables in our scenario.
Plant No.of items Item No.starts
P1 4 1000
P2 4 1005

    Figure 1: Sample Initial data Plant Table

Item Number Description
1000 Baseball Bat
1001 Ball point pen
1002 Bags
1003 Belt

Figure 2: Sample Initial data Item Details Table

Plant Item Number Description
P1 1000 Baseball Bat
P1 1001 Ball point pen
P1 1002 Bags
P1 1003 Belt

Figure 3: Target Data in file or Table

  • This Figure 4 shows project area, we have to create project, Job, work flow and data flow.

Row Generation Transform in SAP BODS

 

  • Below figures 4, 5 and 6 are shows ETL flow of our Job.
  • In figure4  first data flow ,we have loaded data from files(Imperio_Rowgeneration_Plant, Imperio_Rowgeneration_Itemdetails) to table(TB_Plant & TB_Itemdetails).
Row Generation Transform in SAP BODS 1
Figure 5: ETL Flow
  • In this script we have pulled data related Item Number Starts, Number of Items columns from TB_Plant table by using following statements.

Note: In figure6 “$Row_Num_Starts” and “$Row_Count” are Variables, here we are passing values to this variables by using SQL Statements.

Row Generation Transform in BODS 1
Figure 6: Script for SQL Statements
  • Drag Row Generation  ,Query  transformations and template table into workarea after that connect all objects same as figure7.

Row Generation Transform in BODS 2

Pass Variables “$Row_Num_Starts” and “$Row_Count” into Row number starts at and Row Count Fields as of Figure 8.

Row Generation Transform in SAP BODS 2
Figure 8: under Row Generation transform
  • In this Query Transformation we are passing $Plant Variable to Plant Column (to select plant dynamically during Job execution time) in Figure9.
  • We have to take lookup from Table TB_Itemdetails to get ”Description” by comparing Item number from Table TB_Itemdetails and DI_ROW_ID from input schema by using new function call(Function) In figure 10.
Row Generation Transform 1
Figure 9: under Query transform
Row Generation Transform 2
Figure 10: Look up function for Description Column (new output function call )

Source Data:

  • Source data in plant table in figure 11.

Row Generation Transform in BODS 3

  • Source data in Item Details table in figure 12.
Figure 12: Initial data in Item details table
Row Generation Transform in BODS 4

Target Data:

  • Populate Value to Plant Variable at job Execution time shown in figure 13
Row Generation Transform in BODS 5
Figure 13: Global Variable screen at job execution properties
  • Required plant in Target data shown in Figure 14.
Row Generation Transform in SAP BODS 3
Figure 14: Target data

Attachment:

  • Use the below file as source for working out on the transformation

Click Here to Download Source File 1.

Click Here to Download Source File 2.

  • Import the below .atl file in the Data Services Designer to find the Job for the above transformation.

Click Here to Download ATL File.

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