Column to Row Transpose using BW Expert Routine

[Elangovan]

This blog post will explain a method to implement expert routine for column to row transpose on a table with huge number of columns. In a recent project I had a requirement to load a excel placed in SharePoint location to BW on HANA system. The data from excel will be used for reporting in WebI and Lumira. The issue with the excel is, the data is entered by the business in below format.

Visitor’s Category	Jan-2021	Feb-2021	Mar-2021	Apr-2021	……………..	Dec-2025
Type 1
Type 2
………..
Type N

i.e., the number of columns will keep increasing in future and its bit difficult to model the data for reporting if each member of a time dimension is a separate column.

The desired format for reporting would be

Visitor’s Category	CalMonth	Value
Type 1
Type 2
………..
Type N

While creating a data source using SDI based source system a virtual table will be created against the remote source. So, we had to freeze the number of columns (created columns till Dec-2025) to avoid changing the virtual table structure in future.

As BW is on top of HANA, I had the option to perform the transpose in two layers. Either transpose the data in the application layer using BW transformation or load the data in a staging ADSO and perform the transpose in database layer using native HANA modelling.

Transpose in database layer

Transposing using HANA modelling would be better since the operations are carried out in database layer. But the main drawback here is the number of columns in the source table. Implementing transpose using the native HANA modelling would require creating a calculated column or a projection for each column in the table (60 in this case). Below blogs explain in detail about implementing transpose using native HANA modelling.

https://blogs.sap.com/2014/01/02/table-transpose-in-sap-hana-modeling/

https://answers.sap.com/questions/12929729/transpose-columns-to-rows-in-sap-bw.html

https://archive.sap.com/documents/docs/DOC-50541

Transpose in application layer

Transpose can be done using routines or rule group in the BW transformation. In case of rule group, we need to create one rule for each column which is a tedious process for the number of columns are high. This approach is explained clearly in below document.

https://archive.sap.com/documents/docs/DOC-11537

So decided to perform the transpose using expert routine. The below code is short and simple which is not dependent on the number of columns to be transposed and easier to maintain that the above explained approaches.

Transpose in application layer using Expert routine

***Source Structure - System Defined***

TYPES:

      BEGIN OF _ty_s_SC_1,

*      Field: FIELDNAME Field Name.

        FIELDNAME           TYPE C LENGTH 200,

*      Field: JUL16 Jul-16.

        JUL16           TYPE C LENGTH 17,

*      Field: AUG16 Aug-16.

        AUG16           TYPE C LENGTH 17,

*

*****Other Months*****

*

*      Field: NOV27 Nov-27.

        NOV27           TYPE C LENGTH 17,

*      Field: DEC27 Dec-27.

        DEC27           TYPE C LENGTH 17,

*      Field: RECORD Record Number.

        RECORD           TYPE RSARECORD,

      END   OF _ty_s_SC_1.

    TYPES:

      _ty_t_SC_1        TYPE STANDARD TABLE OF _ty_s_SC_1

                        WITH NON-UNIQUE DEFAULT KEY.

***Source Structure - System Defined***



***Target Structure - System Defined***

    TYPES:

      BEGIN OF _ty_s_TG_1,

*      InfoObject: 0REQTSN Request Transaction Sequence Number.

        REQTSN           TYPE RSPM_REQUEST_TSN,

*      InfoObject: 0DATAPAKID Data Package.

        DATAPAKID           TYPE /BI0/OIDATAPAKID,

*      InfoObject: 0RECORD Data Record Number.

        RECORD           TYPE /BI0/OIRECORD,

*      Field: TYPE Type.

        TYPE           TYPE C LENGTH 200,

*      Field: ZCALMON ZCALMON.

        ZCALMON           TYPE C LENGTH 30,

*      InfoObject: 0CALMONTH Calendar Year/Month.

        CALMONTH           TYPE /BI0/OICALMONTH,

*      InfoObject: 0RECORDMODE BW Delta Process: Update Mode.

        RECORDMODE           TYPE RODMUPDMOD,

*      Field: VALUE VALUE.

        VALUE           TYPE P LENGTH 9 DECIMALS 3,

      END   OF _ty_s_TG_1.

    TYPES:

      _ty_t_TG_1        TYPE STANDARD TABLE OF _ty_s_TG_1

                        WITH NON-UNIQUE DEFAULT KEY.

***Target Structure - System Defined***



***Data Declaration - Custom Code***

DATA: SRC_STRUC        TYPE TABLE OF _TY_S_SC_1,

          L_TABLEDESCR_REF TYPE REF TO CL_ABAP_TABLEDESCR,

          L_DESCR_REF      TYPE REF TO CL_ABAP_STRUCTDESCR,

          FIELD_LIST       TYPE ABAP_COMPDESCR,

          TRG_DATA         TYPE _TY_S_TG_1.

***Data Declaration - Custom Code***



***Routine Code - Custom Code***

L_TABLEDESCR_REF ?= CL_ABAP_TYPEDESCR=>DESCRIBE_BY_DATA( SRC_STRUC )

    .

    L_DESCR_REF ?= L_TABLEDESCR_REF->GET_TABLE_LINE_TYPE( ).



    FIELD-SYMBOLS: <FS> TYPE ANY.

    DATA FIELD_NAME(5) TYPE C.



    BREAK-POINT.

    LOOP AT SOURCE_PACKAGE ASSIGNING <SOURCE_FIELDS>.

      CLEAR TRG_DATA.

      LOOP AT L_DESCR_REF->COMPONENTS INTO FIELD_LIST .

        IF FIELD_LIST-NAME = 'FIELDNAME'.

          TRG_DATA-TYPE = <SOURCE_FIELDS>-FIELDNAME.

        ELSEIF FIELD_LIST-NAME = 'RECORD'.

          CONTINUE.

        ELSE.

          CLEAR: FIELD_NAME.

          TRG_DATA-ZCALMON = FIELD_LIST-NAME.

          CASE FIELD_LIST-NAME+0(3).

            WHEN 'JAN'.

              CONCATENATE '20' FIELD_LIST-NAME+3(2) '01' INTO

              TRG_DATA-CALMONTH.

            WHEN 'FEB'.

              CONCATENATE '20' FIELD_LIST-NAME+3(2) '02' INTO

              TRG_DATA-CALMONTH.

            WHEN 'MAR'.

              CONCATENATE '20' FIELD_LIST-NAME+3(2) '03' INTO

              TRG_DATA-CALMONTH.

            WHEN 'APR'.

              CONCATENATE '20' FIELD_LIST-NAME+3(2) '04' INTO

              TRG_DATA-CALMONTH.

            WHEN 'MAY'.

              CONCATENATE '20' FIELD_LIST-NAME+3(2) '05' INTO

              TRG_DATA-CALMONTH.

            WHEN 'JUN'.

              CONCATENATE '20' FIELD_LIST-NAME+3(2) '06' INTO

              TRG_DATA-CALMONTH.

            WHEN 'JUL'.

              CONCATENATE '20' FIELD_LIST-NAME+3(2) '07' INTO

              TRG_DATA-CALMONTH.

            WHEN 'AUG'.

              CONCATENATE '20' FIELD_LIST-NAME+3(2) '08' INTO

              TRG_DATA-CALMONTH.

            WHEN 'SEP'.

              CONCATENATE '20' FIELD_LIST-NAME+3(2) '09' INTO

              TRG_DATA-CALMONTH.

            WHEN 'OCT'.

              CONCATENATE '20' FIELD_LIST-NAME+3(2) '10' INTO

              TRG_DATA-CALMONTH.

            WHEN 'NOV'.

              CONCATENATE '20' FIELD_LIST-NAME+3(2) '11' INTO

              TRG_DATA-CALMONTH.

            WHEN 'DEC'.

              CONCATENATE '20' FIELD_LIST-NAME+3(2) '12' INTO

              TRG_DATA-CALMONTH.

            WHEN OTHERS.

              EXIT.

          ENDCASE.



          FIELD_NAME = FIELD_LIST-NAME.

          ASSIGN COMPONENT FIELD_NAME OF STRUCTURE <SOURCE_FIELDS> TO

          <FS>.

          IF SY-SUBRC = 0.

            TRG_DATA-VALUE = <FS>.

            APPEND TRG_DATA TO RESULT_PACKAGE.

          ELSE.

            EXIT.

          ENDIF.

        ENDIF.

      ENDLOOP.

    ENDLOOP.

***Routine Code - Custom Code***

With few modifications the code can be used to transpose CALWEEK and CALQUARTER data.