End Routine Vs Field Routine on Target DSO Key fields
Applies To: SAP BW 7.X transformations. For more information, visit the EDW homepage.
Summary: This document describes to reader whether to choose End routine or Field routine for DSO key fields.
Author: Kuldeep Jain
Company: Altimetrik
Created on: 19 September 2015
Author Bio:
Kuldeep Jain is BW certified and HANA certified Consultant. He got rich experience on various BW Implementation, support and upgrade projects in both SAP BW 3.5 and SAP BI 7.X.
Details: When it comes to write routine just before storing data to Target, we are having two options End Routine or Field Routine. Both routines are having its own advantages but what to chose for DSO Key fields Part.
Scenario: Let assume we are loading data from source DS/DSO to target DSO. One of Key field (Service material) is coming from different lookup table.
- Sales order and Service Material No. is key field of target DSO.
- For dashed transformation rule, we need to decide whether to write end routine or field routine.
Example data for Source table
|
Sales Order |
Material No. |
Quantity |
|
1001 |
Chair |
125 |
|
1001 |
Door |
320 |
|
1001 |
Mug |
540 |
Example data for Lookup Table
|
Material No. |
Service Material no. |
|
Chair |
Table |
|
Door |
Window |
|
Mug |
Bucket |
Target table (Should be)
|
Sales Order |
Service Material No. |
Quantity |
|
1001 |
Table |
125 |
|
1001 |
Window |
320 |
|
1001 |
Bucket |
540 |
Case 1: Lookup through End routine
When we write lookup code in End routine target DSO data will store data like below table:
Target table data (when using End routine lookup)
|
Sales Order |
Service Material No. |
Quantity |
|
1001 |
Bucket |
540 |
Reason for one row: When data moves from SOURCE_PACKAGE to RESULT_PACKAGE internal table, data will get aggregate based on Target aggregation behaviour and key fields.
Before execution of end routine RESULT_PACKAGE data will be as follows:
|
Sales Order |
Service Material No. |
Quantity |
|
1001 |
|
540 |
Service material no. is blank. So three rows get aggregate and only last row (per DSO property) remain in RESULT_PACKAGE internal table.
After execution of end routine RESULT_PACKAGE data will be as follows:
|
Sales Order |
Service Material No. |
Quantity |
|
1001 |
Bucket |
540 |
This value will go to target and store.
Case 2: Lookup through field routine
When we write lookup code in field routine target DSO will store data like below table:
Target table data (when using field routine lookup)
|
Sales Order |
Service Material No. |
Quantity |
|
1001 |
Table |
125 |
|
1001 |
Window |
320 |
|
1001 |
Bucket |
540 |
Field routine will get execute for every records. It will execute before data moves to RESULT_PACKAGE so RESULT_PACKAGE will be having sales order along with service material no.
Conclusion: When it require to have routine for key fields. It is better to write in field routine. For performance perspective, we can push select statement to start routine and keep necessary data to global internal table then we can refer this global internal table at field routine.
Hi Kuldeep,
How about 3 appending records in the end-routine for the single record that comes into the end-routine?
Are there any advantages in doing it this way than doing it in the end-routine? A little more insight on execution times between both methods would help anyone reading this.
Benedict
Hi Benedict,
If you see my example data, There will not be three records in end routine if you choose case-1. Because before execution of end routine data come to RESULT_PACKAGE table, here service material no is blank. So Uniqueness of records maintained at sales order level. And as per aggregation behaviour of DSO last record will retain in RESULT_PACKAGE.
Hope it is clear to you know.
Performance wise definitely field routine will not be good idea. But we can optimize by using start routine and field routine combination as mentioned in above document.
Isn't this possible by simply using the "Read from DSO" ruletype in the transformation?
It is possible
But there are cases when we can not opt for this options and we need to write logic to fetch record in routine.
Hi Kuldeep,
Nice Explaination for Routine.
Thanks,
Swapna Jain
Thanks Swapna Jain
Hi KD ,
Very Nice explanation . I have a little doubt here . If source field Material no. is of Char and the target is of char as well i.e. Service Mat No. then we can try with direct mapping of Material No. with Service Material No. and in End routine update the Service material Number again with a lookup of the external table.
Regards,
Suraj Grewal.