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.

6 Comments

You must be Logged on to comment or reply to a post.

Benedict Venmani Felix September 19, 2015 at 8:45 pm

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

like (0)
1. KD Jain Post authorSeptember 20, 2015 at 2:35 am
  
  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.
  
  like (0)
Suhas Karnik September 20, 2015 at 5:15 am

Isn’t this possible by simply using the “Read from DSO” ruletype in the transformation?

like (0)
1. KD Jain Post authorSeptember 20, 2015 at 9:18 am
  It is possible
  
  Read from DSO If Lookup table is DSO and you are reading based on key field
  
  Read from MD if lookup table is Characteristics and reading based on primary key(IO)
  
  But there are cases when we can not opt for this options and we need to write logic to fetch record in routine.
  like (0)
Swapna Jain September 21, 2015 at 11:21 am

Hi Kuldeep,

Nice Explaination for Routine.

Thanks,

Swapna Jain

like (0)
1. KD Jain Post authorSeptember 21, 2015 at 1:24 pm
  
  Thanks Swapna Jain
  
  like (0)