Skip to Content

  Business Scenario: 

         In any Organization, all Employees report to their Supervisors. Supervisors report to the Manager of their corresponding departments. All Managers report to the Delivery Manager of their Unit, who in turn reports to the Director. The Directors report  to CEO. CEO reports to Chairman who is at the Top level management. So, the Organization is taken as his higher level of management.

We need to build a Hierarchy in SAP-BW based on this Information. Since there would be large data, creating a hierarchy manually is a tough job to perform. So, one of the solutions is to generate a flat file which will load the hierarchy based on the information provided.

Technical Scenario:

In an organization, all employees report to their superior employee. Let take a scenario where:

  1. Employees E1, E2, E3, E4, E5 report to PM1.
  2. E6, E7, E8, E9, E10 report to TL1.
  3. E11, E12, E13, E14, E15 report to TL2.
  4. TL1 and TL2 report to PM2.
  5. PM1 and PM2 report to CEO.
  6. CEO is at the top level management. So, there is no Superior Person to CEO. CEO reports to the Organization.

The pictorial representation of this hierarchy is as below:

Hierarchy  in Organization

This Information is provided to us in a table which stores, the Employee ID and his Supervisor’s Employee ID.

The input table is as below:

Employee ID

Parent Employee ID

E1

PM1

E2

PM1

E3

PM1

E4

PM1

E5

PM1

E6

TL1

E7

TL1

E8

TL1

E9

TL1

E10

TL1

E11

TL2

E12

TL2

E13

TL2

E14

TL2

E15

TL2

TL1

PM2

TL2

PM2

PM1

CEO

PM2

CEO

CEO

Organization

Organization

Our task is to generate a flat file in the Application Server using an ABAP program which will then be used to load the hierarchy. The flat file structure would be as below:

Node ID

InfoObject

NodeName

Parent ID

Date To

Date From

Language

00000001

YHIEROBJ

Organization

00000000

99991231

10000101

E

00000002

YHIEROBJ

CEO

00000001

99991231

10000101

E

00000003

YHIEROBJ

PM1

00000002

99991231

10000101

E

00000004

YHIEROBJ

PM2

00000002

99991231

10000101

E

00000005

YHIEROBJ

E1

00000003

99991231

10000101

E

00000006

YHIEROBJ

E2

00000003

99991231

10000101

E

00000007

YHIEROBJ

E3

00000003

99991231

10000101

E

00000008

YHIEROBJ

E4

00000003

99991231

10000101

E

00000009

YHIEROBJ

E5

00000003

99991231

10000101

E

00000010

YHIEROBJ

TL1

00000004

99991231

10000101

E

00000011

YHIEROBJ

TL2

00000004

99991231

10000101

E

00000012

YHIEROBJ

E6

00000010

99991231

10000101

E

00000013

YHIEROBJ

E7

00000010

99991231

10000101

E

00000014

YHIEROBJ

E8

00000010

99991231

10000101

E

00000015

YHIEROBJ

E9

00000010

99991231

10000101

E

00000016

YHIEROBJ

E10

00000010

99991231

10000101

E

00000017

YHIEROBJ

E11

00000011

99991231

10000101

E

00000018

YHIEROBJ

E12

00000011

99991231

10000101

E

00000019

YHIEROBJ

E13

00000011

99991231

10000101

E

00000020

YHIEROBJ

E14

00000011

99991231

10000101

E

00000021

YHIEROBJ

E15

00000011

99991231

10000101

E

In the above flat file,

  • Node ID indicates the unique number which defines the Node.
  • InfoObject “gives the name of the InfoObject which is assigned to this Node ID.
  • NodeName is the name you specify to that Node.
  • Parent ID is the Node ID to which the Current Node ID reports to.
  • Date To and “Date From are taken for time references (You can set them constant if in case the hierarchy is time-independent).
  • Language” is set to English (E).

Step by Step Procedure:

  1. Go to Transaction SE38. In the Program Field, enter the name of the source code as YTEST_HIER and click on the “Create” button.

2a.JPG

      2. Give suitable Title and select the Attribute types as in the screen shot below and then click on the Save Button.

3a.JPG

       3. This takes you to the screen below to write the ABAP code to generate the flat file.

/wp-content/uploads/2009/08/4a_122694.jpg

Pre-requisites: We can use a DSO which stores the information of all the Employees and their corresponding Supervisors.

YTESTBOK is a DSO which has two fields. One is to store the Employee and the second field is to store their Supervisor. The table of the DSO which stores this information is: /BIC/AYTESTBOK00.

Below is the data contained in the table /BIC/AYTESTBOK00.

  20.JPG

      4. ABAP Code has to be written in the above screen to generate a flat file in application server.

      5. Once the coding is done without any errors, execute the program by clicking on the button.

6.JPG

      6.This will take you to the selection screen which displays the location of the Flat File. Now, click on the execute button.

7.JPG

The flat file would be generated in the Application server now.

      7.To view the File, Go to Transaction AL11. Search the directory /tmp. Double click on this directory.

8.JPG

       8.This opens the list of flat files under this directory. Double click on the file name.

9.JPG

Now, you will be able to see the flat file which is generated after executing the program.

10.JPG

      9. To create an InfoSource, Goto TX: RSA1OLD. Right click on the Application Component and click on Create InfoSource. Select Direct Update of Master Data. Give the Master data object name and click on Continue Button.

11.JPG

Now, right click on the InfoSource and click on Assign DataSource. Give the Source System name and press the Continue button.

12.JPG

      10. This will open the InfoSource. You should select the data source as YHIEROBJ_HIER and then activate the InfoSource.

13.JPG

      11.You should create an InfoPackage to load the Hierarchy. Right click on the PC Files (Source System) and click on “Create InfoPackage”.

14.JPG

Under External Data tab, select the Application Server. Also, give the Name of the file as the name present in Application Server.

15.JPG

Select the hierarchy which you want to load. Also, you can rename the Hierarchy after loading it. The update method is selected as “Full Update”. (See the below screen shot.)

16.JPG

     12. Schedule the InfoPackage to load the data into the Hierarchy. This willload the hierarchy. If you have selected “Only PSA” under Processing tab in InfoPackage, you need to update the data from PSA.

      13. To view the hierarchy, go to Tx: RSA1 and search for the Master Data YHIEROBJ in Info Providers. Right Click on the Hierarchy and then click on the “Maintain Hierarchies”.

17.JPG

        14. All the hierarchies built on YHIEROBJ will be listed now.

18.JPG

       15.     Double click on the Hierarchy to view the hierarchy generated. Check for the correctness of the hierarchy built once loaded.

19.JPG

Limitations:

Data has to be present in any of the database tables in SAP BW in order to use this method of generation of flat file.

Appendix:

Pseudo code of Program for generating flat file in application server to load a hierarchy.

\  \

*&———————————————————————*
\ *& Report  YTEST_HIER
\ *&
\ *&———————————————————————*
\ *& This program reads the Employee and Corresponding Supervisor data
\ *& from DSO YTESTBOK and creates a Flat File in the Application Server.
\ *&
\ *& Input : The program reads the table of DSO YTESTBOK as input
\ *& Output: The program generates a flat file in the Application Server.
\ *&         This program is used to load the Hierarchy of master data YHIEROBJ.
\ *&———————————————————————*
\
\ REPORT YTEST_HIER.
\
\
\ *************************************************************************
\ ******Start of Data Declaration******************************************
\ *************************************************************************
\
\

\  \

** Declaration of the Hierarchy Structure.
\ TYPES : BEGIN OF T_OUTPUT_DATASET,
\                NODEID(8)           TYPE N,
\                INFOOBJECT(30)      TYPE C,
\                NODENAME(32)        TYPE C,
\                LINK(1)             TYPE C,
\                PARENTID(8)         TYPE N,
\                DATETO              LIKE SY-DATUM,
\                DATEFROM            LIKE SY-DATUM,
\                LANGU(1)            TYPE C,
\                TXTSH(20)           TYPE C,
\                TXTMD(40)           TYPE C,
\                TXTLG(60)           TYPE C,
\            END OF T_OUTPUT_DATASET.
\
\ ** Declare a table with structure same as Active table of DSO YTESTBOK**
\ TYPES: GTT_EMP TYPE  HASHED TABLE OF /BIC/AYTESTBOK00
\                         WITH  UNIQUE KEY /BIC/YEMP,
\
\            BEGIN OF GTS_LOOKUP,
\               NODE TYPE /BIC/OIZCOERESP,
\               ID   TYPE RSHIENODID,
\            END   OF GTS_LOOKUP,
\
\            GT_LOOKUP TYPE HASHED TABLE OF GTS_LOOKUP
\                             WITH UNIQUE KEY NODE,
\
\
\ ** Create an Structure with five fields i.e. NodeID,
\ *** InfoObject, NodeName, Link and Parent NodeID *****
           BEGIN OF GTS_EXTRACT,
\                NODEID     TYPE RSHIENODID,
\                INFOOBJECT TYPE RSIOBJNM,
\                NODENAME   TYPE RSNODENAME,
\                LINK       TYPE RSLINK,
\                PARENTID   TYPE RSPARENT,
\            END OF GTS_EXTRACT,
  \

*** Declare an table with the structure “gts_extract” ****
          GTT_EXTRACT TYPE TABLE OF GTS_EXTRACT.
\
\ DATA: G_LOOKUP TYPE GT_LOOKUP.
\
\ ** Declaration of Internal Variables ****
\ DATA:
\      GTH_HIEREMP    TYPE GTT_EMP,
\      GS_EXTRACT      TYPE GTS_EXTRACT,
\      GT_EXTRACT      TYPE GTT_EXTRACT,
\      FINAL_WA          TYPE T_OUTPUT_DATASET,
\      MSG(250),
\      L_UPPERNODENAME   TYPE RSNODENAME,
\      GS_ID              LIKE LINE OF G_LOOKUP.
\
\
\ ** Creating the Structure Of Internal Table “itab1” with the Structure of “gts_extract”. ***
\ DATA: ITAB1 TYPE STANDARD TABLE OF GTS_EXTRACT
\                   WITH HEADER LINE
\                   WITH NON-UNIQUE DEFAULT KEY INITIAL SIZE 0.
\
\ ** Declaration of the Work Area for Internal table “itab1” with the structure of “gts_extract”.***
\ DATA: WA_ITAB1 TYPE GTS_EXTRACT.
\
\
\ FIELD-SYMBOLS:
\  <EMP>          LIKE LINE OF GTH_HIEREMP,
\  <NODEID>     LIKE LINE OF G_LOOKUP,
\  <EXTRACT>   LIKE LINE OF GT_EXTRACT.
\
\
\ *************************************************************************
\ ******End of Data Declaration********************************************
\ *************************************************************************
\
\

\  \

** Start of the Selection Screen ***
\ START-OF-SELECTION.
\
\ ****The selection screen displays the default file name as ‘/tmp/YTEST_FILE’*****
\   PARAMETER: SSFILE1 LIKE  AUTHB-FILENAME DEFAULT ‘/tmp/YTEST_FILE’.
\
\
\ ** Get all the records from DSO ZM2K_O01 into an table “GTH_HIEREMP”.****
\   SELECT * FROM /BIC/AYTESTBOK00
\     INTO TABLE GTH_HIEREMP.
\
\   CHECK SY-SUBRC = 0.
\
\ ** Delete records in “GTH_HIEREMP” whose EMP value is blank.****
\   DELETE GTH_HIEREMP WHERE /BIC/YEMP IS INITIAL.
\
\ ** Begin building extract structure.****
\
\ ** The infoObject for which the hierarchy built is YHIEROBJ.****
\ ** Assign the YHIEROBJ as the Infoobject in  GS_EXTRACT*****
\   GS_EXTRACT-INFOOBJECT = ‘YHIEROBJ’.
\
\ ** Read all the EMP values into NodeName Field of the table “gt_extract”. ****
\   LOOP AT GTH_HIEREMP ASSIGNING <EMP>.
\
\     GS_EXTRACT-NODENAME   = <EMP>-/BIC/YEMP.
\     APPEND GS_EXTRACT TO GT_EXTRACT.
\
\   ENDLOOP.
\
\ ** Build lookup table for Parent Node ID ****
\   SORT GT_EXTRACT BY NODENAME.
\
\   LOOP AT GT_EXTRACT ASSIGNING <EXTRACT>.
\
\     GS_ID-NODE = <EXTRACT>-NODENAME.
\     GS_ID-ID = <EXTRACT>-NODEID   = SY-TABIX.
\     INSERT GS_ID INTO TABLE G_LOOKUP.
\
\   ENDLOOP.
\
\
\ ** Fill Parent Node ID field in extract ****
\   LOOP AT GT_EXTRACT ASSIGNING <EXTRACT>.
\
\     READ TABLE GTH_HIEREMP ASSIGNING <EMP>
\        WITH TABLE KEY /BIC/YEMP = <EXTRACT>-NODENAME.
\     CHECK SY-SUBRC = 0.
\
\     READ TABLE G_LOOKUP ASSIGNING <NODEID>
\        WITH TABLE KEY NODE = <EMP>-/BIC/YSUPEMP.
\     CHECK SY-SUBRC = 0.
\     <EXTRACT>-PARENTID = <NODEID>-ID.
\
\   ENDLOOP.
\
\ ** Append the extract data from GT_EXTRACT to work area WA_ITAB1 and then into table ITAB1****
\   LOOP AT GT_EXTRACT ASSIGNING <EXTRACT>.
\
\     WA_ITAB1-INFOOBJECT = ‘YHIEROBJ’.
\     WA_ITAB1-NODEID = <EXTRACT>-NODEID.
\     WA_ITAB1-NODENAME = <EXTRACT>-NODENAME.
\     WA_ITAB1-PARENTID = <EXTRACT>-PARENTID.
\
\     APPEND WA_ITAB1 TO ITAB1.
\
\   ENDLOOP.
\
\ *************************************************************************************
\   CLEAR: FINAL_WA.
\   OPEN DATASET SSFILE1 FOR OUTPUT IN TEXT MODE ENCODING DEFAULT
\      MESSAGE MSG.
\   IF SY-SUBRC NE 0.
\     MESSAGE E008(ZBW1) WITH MSG.
\   ENDIF.
\
\ ** Populate the output from table “itab1” to “final_wa”. ****
\   LOOP AT ITAB1.
\
\     FINAL_WA-NODEID = ITAB1-NODEID.
\     FINAL_WA-INFOOBJECT = ITAB1-INFOOBJECT.
\     FINAL_WA-NODENAME = ITAB1-NODENAME.
\     FINAL_WA-LINK = ITAB1-LINK.
\     FINAL_WA-PARENTID = ITAB1-PARENTID.
\     FINAL_WA-DATETO = ‘99991231’.
\     FINAL_WA-DATEFROM = ‘10000101’.
\     FINAL_WA-LANGU = ‘E’.
\     FINAL_WA-TXTSH = ”.
\     FINAL_WA-TXTMD = ”.
\     FINAL_WA-TXTLG = ”.
\
\ ** Transfer this final_wa to the ssfile1 which is the desired output file. ****
\     TRANSFER FINAL_WA TO SSFILE1.
\     CLEAR: FINAL_WA.
\
\   ENDLOOP.
\
\ ** CLOSE DATASET****
\ ***********************************************************************
\   CLOSE DATASET SSFILE1.

To report this post you need to login first.

5 Comments

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

  1. Mohammad Ali
    This is excellent way of loading hierarchy via flat file, especially for large amount of data. Thanks for adding the code along. This gives me the opportunity to test your work out.

    Regards

    (0) 
    1. Anjum S N Post author

      Hello Ekaterina,

      I am in process of correcting this article with right attachments. Request your patience till then.

      Regards,

      Anjum.

      (0) 

Leave a Reply