End Routine Vs Field Routine on Target DSO Key fields

[KulDeepJain]

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.

1. Sales order and Service Material No. is key field of target DSO.
2. 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.