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.

To report this post you need to login first.

6 Comments

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

  1. Benedict Venmani Felix

    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

    (0) 
    1. KD Jain Post author

      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.

      (0) 
    1. KD Jain Post author

      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.

      (0) 

Leave a Reply