Transposing the Fields in SAP BW Transformation

Scenario

The business records the daily exposure and positions into one record for a term. In my example, a term is for 10 days. So the record looks like this:

<> 

Transaction ID	Date	Day 1	Day 2	Day 3	Day 4	Day 5
646578	4/17/2011	432.00	606.00	2011.00	2169.00	379.00

This means that I will have to come up with 10 key figures in SAP BW InfoProvider design which might not be a good design.

Target Design

I wanted to show this as 1 key figure and with another field that shows what day it is.

Transaction ID	Date	Day Ending	Price Per day
646578	4/17/2011	01	432.00
646578	4/17/2011	02	606.00
646578	4/17/2011	03	2011.00
646578	4/17/2011	04	2169.00
646578	4/17/2011	05	379.00

Resolution

The solution presented in here is following this Dataflow: Source System -> Write-Optimized DSO -> Standard DSO. The solution given below can be applied between the Transformation that exists between Write-Optimized and Standard DSO. This can be applied in any Transformations.

Fig 1: Dataflow

This can be extended up to 25 fields. After 15 fields, it might give you an error.

First Solution:

One way to do was to write Start Routine and End Routine in Transformation.

Fig 2: Transformation - Start and End Routine

The code might look like this but you will need to modify this depending on your needs:

Start Routine:

DATA: wa_source_package TYPE _ty_s_SC_1.

    LOOP AT SOURCE_PACKAGE INTO wa_SOURCE_PACKAGE.
      wa_sp-/BIC/ZTRAN_ID = wa_source_package-/BIC/ZTRAN_ID.
      wa_sp-/BIC/ZDATE = wa_source_package-/BIC/ZDATE.
      wa_sp-/bic/ZPDP_DY1 = wa_source_package-/bic/ZPPD_DY1.
      wa_sp-/bic/ZPDP_DY2 = wa_source_package-/bic/ZPPD_DY2.
      wa_sp-/bic/ZPDP_DY3 = wa_source_package-/bic/ZPPD_DY3.
      wa_sp-/bic/ZPDP_DY3 = wa_source_package-/bic/ZPPD_DY3.
      wa_sp-/bic/ZPDP_DY4 = wa_source_package-/bic/ZPPD_DY4.
      wa_sp-/bic/ZPDP_DY5 = wa_source_package-/bic/ZPPD_DY5.

End Routine:


DATA: rem TYPE I.
    DATA: lt_rp TYPE TABLE OF _ty_s_TG_1,
          wa_rp TYPE _ty_s_TG_1.

    lt_rp[] = RESULT_PACKAGE[].
    REFRESH RESULT_PACKAGE.

    LOOP AT lt_rp INTO wa_rp.
      READ TABLE it_sp INTO wa_sp WITH KEY
      /BIC/ZTRAN_ID  = wa_rp-/BIC/ZTRAN_ID
      /BIC/ZDATE = wa_rp-/BIC/ZDATE
  
      IF  sy-subrc = 0.
        wa_rp-/BIC/ZDAY_END = 'Day 1'.
        wa_rp-/BIC/ZPPD_PR = wa_sp-/bic/ZPPD_DY1.
        APPEND wa_rp TO RESULT_PACKAGE.

        wa_rp-/BIC/ZDAY_END = 'Day 2'.
        wa_rp-/BIC/ZPPD_PR = wa_sp-/bic/ZPPD_DY2.
        APPEND wa_rp TO RESULT_PACKAGE.

        wa_rp-/BIC/ZDAY_END = 'Day 3'.
        wa_rp-/BIC/ZPPD_PR = wa_sp-/bic/ZPPD_DY3.
        APPEND wa_rp TO RESULT_PACKAGE.

        wa_rp-/BIC/ZDAY_END = 'Day 4'.
        wa_rp-/BIC/ZPPD_PR = wa_sp-/bic/ZPPD_DY4.
        APPEND wa_rp TO RESULT_PACKAGE.

        wa_rp-/BIC/ZDAY_END = '0ay 5'.
        wa_rp-/BIC/ZPPD_PR = wa_sp-/bic/ZPPD_DY5.
        APPEND wa_rp TO RESULT_PACKAGE.

Transformation looks like this:

Fig 3 : Transformation with Start and End Routine

Second Solution:

By using the Rule Groups
We can use the Rule Groups functionality to achieve the same what I did with Start and End routine.

1. Create two new InfoObjects: Day Ending (Characteristics type and type CHAR with Length as 6 and Price Per Day (Key figure as Amount)
2. Insert these InfoObjects Standard DSO
3. In the Transformation between, Write-Optimized DSO and Standard DSO, click on Rule Groups and create one.

• For day 1, give the name of the Rule group as Day 1, Map the Price Per Day 1 to Price Per Day and Hard code Day Ending as Day 1
• For day 2, give the name of the Rule group as Day 2, Map the Price Per Day 2 to Price Per Day and Hard code Day Ending as Day 2

Follow the same steps for all other days.

Fig 4: Rule Groups

The data that looked like this earlier

Fig 5: Data in Write-Optimized DSO

Will look like this

Fig 6: Data in Standard DSO

I hope this will be helpful to somebody.

*
**
***
****
*****