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.
In an organization, all employees report to their superior employee. Let take a scenario where:
The pictorial representation of this hierarchy is as below:
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,

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

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

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.

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.This will take you to the selection screen which displays the location of the Flat File. Now, click on the execute button.

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.This opens the list of flat files under this directory. Double click on the file name.

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

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.

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

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

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

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

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.)

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".

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

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

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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
| User | Count |
|---|---|
| 524 | |
| 263 | |
| 238 | |
| 234 | |
| 167 | |
| 157 | |
| 152 |