Row Generation Transform in SAP BODS
Row Generation Transform
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.
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|
Figure 1: Sample Initial data Plant Table
|1001||Ball point pen|
Figure 2: Sample Initial data Item Details Table
|P1||1001||Ball point pen|
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.
- 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).
- 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.
- Drag Row Generation ,Query transformations and template table into workarea after that connect all objects same as figure7.
Pass Variables “$Row_Num_Starts” and “$Row_Count” into Row number starts at and Row Count Fields as of Figure 8.
- 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.
- Source data in plant table in figure 11.
- Source data in Item Details table in figure 12.
- Populate Value to Plant Variable at job Execution time shown in figure 13
- Required plant in Target data shown in Figure 14.
- Use the below file as source for working out on the transformation
- Import the below .atl file in the Data Services Designer to find the Job for the above transformation.