Skip to Content

According to business requirement, the hierarchy is being created on the BI side with the help of a custom program is executed on the ECC side to extract the data from the tables present in the ECC. We are creating a program in source system i.e. ECC to create flat file (.CSV format) in hierarchy format (as required by BW7.3 hierarchy DataSource), which will be uploaded at application server. The program will directly upload the file to the Application server, and the generic flat data source in BW 7.3 will pick up the file from the application server and load it into our InfoObject.

Business Scenario:

The standard business content supports number of main hierarchies such as Cost Center hierarchy, Material, Plant, Work Center, GL Account, etc. We might come across some custom requests which require us to extract the data on the basis of database tables and generate a custom hierarchy based on that in BW. In current scenario, let us consider a generic Work Center Hierarchy. Work centers belonging to different region, department and division can be grouped into a work center hierarchy. Apart from the top of the hierarchy – the hierarchy root – every work center in the hierarchy is subordinate to another work center. In our requests, for this, we try to use customized generic hierarchies.

Applies to: SAP ECC 6.0, SAP BI 7.3.

Configure DataSource in BI:

To create a generic work center hierarchy, we need to create a generic data source in BI with type ‘hierarchies’. For this, we need to login to BI and go to RSA1 T-code.

To create a hierarchy data source, we need to follow the below steps:

1.       Select DataSources from the Modeling section.

2.       Select the DataSource icon from the top menu or press Ctrl+F8.

/wp-content/uploads/2013/10/1_294947.jpg

3.       Enter the DataSource hierarchy name, select the source system as FILE, select the DataSource Data Type as Hierarchies and press Enter.

/wp-content/uploads/2013/10/2_294948.png

4.       Enter the description and Application component details where the DataSource will be placed.

/wp-content/uploads/2013/10/3_294949.png

5.       Now in the Extraction tab, specify the Target InfoObject in which you wish to associate the Work Center Hierarchy into.

/wp-content/uploads/2013/10/4_294950.png

6.       In the File Structure tab, we will be able to see the structure of the hierarchy automatically created. This is same as the hierarchy attributes.

/wp-content/uploads/2013/10/5_294951.png

7.       Activate the DataSource.

Configuring ECC:

The Work Center Hierarchy is of the below mentioned structure.

/wp-content/uploads/2013/10/6_294952.png

To get the data in the attributes associated in the hierarchy, follow the steps:

1.       Go to SE38 T-code to create a custom program.

  /wp-content/uploads/2013/10/7_294956.png

2.       In this, specify a Program name and the SubObjects as Source Code and click on create.

3.       Enter the title for the program and within the attributes section, select the Attribute type as Executable program and status as SAP Standard Production Program.  You can also specify the package for all your BW requests. Press Enter.

/wp-content/uploads/2013/10/8_294957.png

4.       The ABAP Editor opens up which allows us to write a program. The following program is extracting the work center data from the HRP9002 table.

Prerequisites:

Create a custom infotype hrp9002 to get the data for location, region, district and division.The InfoType hrp9002 is shown as the following:

/wp-content/uploads/2013/10/9_294958.png

       

REPORT  <Program Name>.
INCLUDE
zi_workcenter_top.
INCLUDE
zi_workcenter_sub.
START-OF-SELECTION.
*Perform to fetch data from HRP9002
  PERFORM
f_fetch_data_hrp9002.
*Perform to fill YDIVISION node in internal table
  PERFORM
f_fill_table.
*Perform to fill Hierarchy in internal table
  PERFORM
f_create_hierarchy.
  PERFORM
f_final_hierarchy_table.
  IF gt_hierarchy1 IS NOT INITIAL.
    PERFORM
f_get_text.
    PERFORM
f_file_application_server.
  ELSE.
    MESSAGE TEXT004 TYPE ‘E’. “error message
    LEAVE LIST-PROCESSING.
  ENDIF.

5.       The custom Include zi_workcenter_top is created in order to provide all the declarations related to the hierarchy. While creating an include program, select the Attribute type as “Include”. The following code explains the declarations made within zi_workcenter_top.

TABLES: hrp9002.

TYPES: BEGIN OF ty_hrp9002,
        yy_DIVISION       TYPE ze_division,
        plvar             TYPE plvar,
        otype             TYPE otype,
        objid             TYPE hrobjid,
        subty             TYPE subtyp,
        begda             TYPE begdatum,
        endda             TYPE enddatum,
        YY_REGION         TYPE ze_region,
        YY_DISTRICT       TYPE ze_district,
       END OF ty_hrp9002,

       BEGIN OF ty_hierarchy,
         nodid    TYPE rsshnodeid,
         iobjnm   TYPE rsiobjnm,
         nodename TYPE rsshnodename,
         link     TYPE rslink,
         parentid TYPE rsparent,
         langu    TYPE sylangu,
         txtsh    TYPE text20,
         txtmd    TYPE text40,
         txtlg    TYPE text60,
         tlevel   TYPE rstlevel,
         childid  TYPE rschild,
         nextid   TYPE rsnext,
       END OF ty_hierarchy.

DATA: gt_hrp9002    TYPE STANDARD TABLE OF ty_hrp9002,
      gt_hierarchy  TYPE STANDARD TABLE OF ty_hierarchy,
      gt_hierarchy1 TYPE STANDARD TABLE OF ty_hierarchy,
      gw_hrp9002    TYPE ty_hrp9002,
      gw_hierarchy  TYPE ty_hierarchy.

DATA: gv_count        TYPE char03,
      gv_nodename     TYPE rsshnodename,
      gv_filepath     TYPE rlgrapfilename,
      gv_error        TYPE string,
      gv_exception    TYPE REF TO cx_root.

FIELD-SYMBOLS: <gfs_hierarchy>  TYPE ty_hierarchy.

CONSTANTS: gc_plvar    TYPE plvar         VALUE ’01’,
           gc_otype    TYPE otype         VALUE ‘A’,
           gc_subty    TYPE subtyp        VALUE ‘0001’,
           gc_division TYPE rsiobjnm      VALUE ‘YDIVISION’,
           gc_district TYPE rsiobjnm      VALUE ‘YDISTRICT’,
           gc_region   TYPE rsiobjnm      VALUE ‘YREGION’,
           gc_workcen  TYPE rsiobjnm      VALUE ‘YWORKCENTER’,
           gc_loctype  TYPE rsiobjnm      VALUE ‘YLOCATION’,
           gc_nodename TYPE rsshnodename  VALUE ‘ROOT’,
           gc_one      TYPE rsparent      VALUE ‘1’,
           gc_level01  TYPE rstlevel      VALUE ‘1’,
           gc_level02  TYPE rstlevel      VALUE ‘2’,
           gc_level03  TYPE rstlevel      VALUE ‘3’,
           gc_level04  TYPE rstlevel      VALUE ‘4’,
           gc_level05  TYPE rstlevel      VALUE ‘5’,
           gc_level06  TYPE rstlevel      VALUE ‘6’,
           gc_lang     TYPE sylangu       VALUE ‘E’,
           gc_err      TYPE c             VALUE ‘E’.

6.       The custom Include zi_workcenter_sub is created in order to write all the perform codes (functions) related to the hierarchy. While creating an include program, select the Attribute type as “Include”. The following code explains the declarations made within zi_workcenter_sub.

FORM f_fetch_data_hrp9002 .

  DATA: lv_date TYPE sydatum.
  lv_date = sydatum.

  SELECT plvar otype objid subty begda endda
         YY_REGION
         YY_DISTRICT yy_DIVISION
    FROM hrp9002
    INTO CORRESPONDING FIELDS OF TABLE gt_hrp9002
    WHERE plvar = gc_plvar
    AND   otype = gc_otype
    AND   subty = gc_subty
    AND   begda =< lv_date
    AND   endda >= lv_date.
  IF sysubrc NE 0.
    REFRESH gt_hrp9002.
  ELSE.
    SORT gt_hrp9002 BY plvar otype objid subty begda endda
                       YY_REGION YY_DISTRICT yy_DIVISION.
    DELETE ADJACENT DUPLICATES FROM gt_hrp9002 COMPARING ALL FIELDS.
    SORT gt_hrp9002 BY plvar otype objid subty endda DESCENDING
                                               begda DESCENDING.
    DELETE ADJACENT DUPLICATES FROM gt_hrp9002 COMPARING plvar otype
                                                         objid subty.
  ENDIF.
ENDFORM.                    ” F_FETCH_DATA_HRP9002
*&———————————————————————*
*&      Form  F_FILL_TABLE
*&———————————————————————*
FORM f_fill_table .

  DATA: lc_node     TYPE rsiobjnm VALUE ‘0HIER_NODE’,
        lc_parentid TYPE rsparent VALUE ‘0’.

  CLEAR gv_count.
  SORT gt_hrp9002 BY yy_DIVISION.

  PERFORM f_fill_final_table USING lc_node
                                   gc_nodename
                                   gc_level01
                                   lc_parentid.

  LOOP AT gt_hrp9002 INTO gw_hrp9002 WHERE yy_DIVISION IS NOT INITIAL.
*Append YDIVISION Node to the final table
    AT NEW yy_DIVISION.
      gv_nodename = gw_hrp9002yy_DIVISION.
      PERFORM f_fill_final_table USING gc_division
                                       gv_nodename
                                       gc_level02
                                       gc_one.
      CLEAR gv_nodename.
    ENDAT.
  ENDLOOP.

ENDFORM.                    ” F_FILL_TABLE
*&———————————————————————*
*&      Form  F_CREATE_HIERARCHY
*&———————————————————————*
FORM f_create_hierarchy.

  DATA: lv_blank TYPE rsshnodename.

  CLEAR: gw_hierarchy, gw_hrp9002.
  SORT   gt_hrp9002 BY yy_DIVISION “yy_location_type
                       YY_REGION YY_DISTRICT objid.

  IF gt_hierarchy1 IS NOT INITIAL.
    gt_hierarchy[] = gt_hierarchy1[].
*–When Division is present
    LOOP AT gt_hierarchy INTO gw_hierarchy.
      PERFORM f_set_hierarchy USING gw_hierarchynodename
                                    gw_hierarchynodid.
    ENDLOOP.
  ENDIF.

*when division is not present
  PERFORM f_set_hierarchy USING lv_blank
                                gc_one.

ENDFORM.                    ” F_CREATE_HIERARCHY
*&———————————————————————*
*&      Form  F_FINAL_HIERARCHY_TABLE
*&———————————————————————*
*       text
*———————————————————————-*
*  –>  p1        text
*  <–  p2        text
*———————————————————————-*
FORM f_final_hierarchy_table .

  DATA: lv_tabix TYPE sytabix.

  SORT gt_hierarchy1 BY iobjnm nodename nodid ASCENDING.
  LOOP AT gt_hierarchy1 INTO gw_hierarchy.
    lv_tabix = sytabix + 1.
    READ TABLE gt_hierarchy1 ASSIGNING <gfs_hierarchy> INDEX lv_tabix.
    IF sysubrc EQ 0.
      IF  gw_hierarchyiobjnm   = <gfs_hierarchy>iobjnm
      AND gw_hierarchynodename = <gfs_hierarchy>nodename.
        <gfs_hierarchy>link = ‘X’.
      ENDIF.
    ENDIF.
  ENDLOOP.

  SORT gt_hierarchy1 BY nodid.
  CLEAR: lv_tabix, gw_hierarchy.
  UNASSIGN <gfs_hierarchy>.

ENDFORM.                    ” F_FINAL_HIERARCHY_TABLE
*&———————————————————————*
*&      Form  F_FILL_FINAL_TABLE
*&———————————————————————*
FORM f_fill_final_table  USING p_iobjnm   TYPE rsiobjnm
                               p_nodename TYPE rsshnodename
                               p_level    TYPE rstlevel
                               p_parentid TYPE rsparent.

  DATA: lw_hierarchy TYPE ty_hierarchy.

  gv_count = gv_count + 1.
  lw_hierarchynodid    = gv_count.
  lw_hierarchyiobjnm   = p_iobjnm.
  lw_hierarchynodename = p_nodename.
  lw_hierarchytlevel   = p_level.
  lw_hierarchyparentid = p_parentid.
  lw_hierarchylangu    = gc_lang.

  APPEND lw_hierarchy TO gt_hierarchy1.
  CLEAR: lw_hierarchy.

ENDFORM.                    ” F_FILL_FINAL_TABLE
*&———————————————————————*
*&      Form  F_SET_HIERARCHY
*&———————————————————————*
FORM f_set_hierarchy  USING p_nodname TYPE rsshnodename
                            p_nodid   TYPE rsshnodeid.

  DATA: lv_parentid      TYPE rsparent,
        lw_hierarchy1    TYPE ty_hierarchy,
        lv_location_type TYPE ye_location_type.

  LOOP AT gt_hrp9002 INTO gw_hrp9002 WHERE yy_DIVISION = p_nodname.
    lv_parentid = p_nodid.

*Update Region Node at 4th Level
    IF gw_hrp9002YY_REGION IS NOT INITIAL.
*Check if the region is already present
      READ TABLE gt_hierarchy1 INTO lw_hierarchy1
      WITH KEY iobjnm   = gc_region
               nodename = gw_hrp9002YY_REGION
               parentid = lv_parentid.
      IF sysubrc NE 0.
        gv_nodename = gw_hrp9002YY_REGION.
        PERFORM f_fill_final_table USING gc_region
                                         gv_nodename
                                         gc_level03
                                         lv_parentid.
        CLEAR: lv_parentid, gv_nodename.
        lv_parentid = gv_count.
      ELSE.
        lv_parentid = lw_hierarchy1nodid.
      ENDIF.
    ENDIF.

*Update District Node at 5th Level
    IF gw_hrp9002YY_DISTRICT IS NOT INITIAL.
*Check if the district is already present
      READ TABLE gt_hierarchy1 INTO lw_hierarchy1
      WITH KEY iobjnm   = gc_district
               nodename = gw_hrp9002YY_DISTRICT
               parentid = lv_parentid.
      IF sysubrc NE 0.
        gv_nodename = gw_hrp9002YY_DISTRICT.

        PERFORM f_fill_final_table USING gc_district
                                         gv_nodename
                                         gc_level04
                                         lv_parentid.
        CLEAR: lv_parentid, gv_nodename.
        lv_parentid = gv_count.
      ELSE.
        lv_parentid = lw_hierarchy1nodid.
      ENDIF.
    ENDIF.

*Update work center Node at 6th Level
    IF gw_hrp9002objid IS NOT INITIAL.
*Check if the workcenter is already present
      READ TABLE gt_hierarchy1 INTO lw_hierarchy1
      WITH KEY iobjnm   = gc_workcen
               nodename = gw_hrp9002objid
               parentid = lv_parentid.
      IF sysubrc NE 0.
        gv_nodename = gw_hrp9002objid.
        PERFORM f_fill_final_table USING gc_workcen
                                         gv_nodename
                                         gc_level05
                                         lv_parentid.
        CLEAR: lv_parentid, gv_nodename.
      ELSE.
        lv_parentid = lw_hierarchy1nodid.
      ENDIF.
    ENDIF.
  ENDLOOP.

ENDFORM.                    ” F_SET_HIERARCHY
*&———————————————————————*
*&      Form  F_FILE_APPLICATION_SERVER
*&———————————————————————*
FORM f_file_application_server .

  DATA: lv_logical_filename   TYPE fileintern,
        lv_physical_filename  TYPE string,
        lv_output             TYPE string,
        lv_sysid_uc           TYPE sysid,
        lv_sysid_lc           TYPE sysid.

  CONSTANTS: lc_semicolon TYPE c VALUE ‘;’.

  lv_logical_filename = ‘<LOGICAL_PATH_NAME’.
*get the file from logical path
  CALL FUNCTION ‘FILE_GET_NAME’
    EXPORTING
      logical_filename        = lv_logical_filename
      use_presentation_server = ‘X’
    IMPORTING
      file_name               = lv_physical_filename
    EXCEPTIONS
      file_not_found          = 1
      OTHERS                  = 2.
  .
  IF sysubrc <> 0.
    MESSAGE text001 TYPE ‘E’. “DISPLAY LIKE gc_err. “folder path is not maintained
    LEAVE LIST-PROCESSING.
  ELSE.

*Open the filepath / dataset on the application server for writing
    TRY.
        OPEN DATASET lv_physical_filename FOR OUTPUT IN TEXT MODE ENCODING DEFAULT.
      CATCH cx_dynamic_check INTO gv_exception.
        gv_error = gv_exception->get_text( ).
        MESSAGE gv_error TYPE ‘E’.
    ENDTRY.
*Check if the OPEN has been successful
    IF sysubrc EQ 0.
      LOOP AT gt_hierarchy1 INTO gw_hierarchy.
        CONCATENATE gw_hierarchynodid
         gw_hierarchyiobjnm
         gw_hierarchynodename
         gw_hierarchylink
         gw_hierarchyparentid
         gw_hierarchylangu
         gw_hierarchytxtsh
         gw_hierarchytxtmd
         gw_hierarchytxtlg
         gw_hierarchytlevel
         gw_hierarchychildid
         gw_hierarchynextid  INTO lv_output SEPARATED BY lc_semicolon.
        TRANSFER lv_output TO lv_physical_filename.
        CLEAR: lv_output, gw_hierarchy.
      ENDLOOP.
    ENDIF.

    TRY.
        CLOSE DATASET lv_physical_filename.
      CATCH cx_dynamic_check INTO gv_exception.
        gv_error = gv_exception->get_text( ).
        MESSAGE gv_error TYPE ‘E’.
    ENDTRY.
    IF sysubrc EQ 0.
      MESSAGE text003 TYPE ‘S’.
    ENDIF.

  ENDIF.

ENDFORM.                    ” F_FILE_APPLICATION_SERVER
*&———————————————————————*
*&      Form  F_GET_TEXT
*&———————————————————————*
FORM f_get_text .

  DATA: lt_div_text  TYPE STANDARD TABLE OF zthrom_division,
        lt_reg_text  TYPE STANDARD TABLE OF zthrom_region,
        lt_dist_text TYPE STANDARD TABLE OF zthrom_district,
        lw_div_text  TYPE zthrom_division,
        lw_reg_text  TYPE zthrom_region,
        lw_dist_text TYPE zthrom_district.

*get the text of division
  SELECT * FROM zthrom_division
    INTO TABLE lt_div_text
    FOR ALL ENTRIES IN gt_hrp9002
    WHERE yy_DIVISION EQ gt_hrp9002yy_DIVISION.
  IF sysubrc EQ 0.
    SORT lt_div_text BY yy_DIVISION.
  ELSE.
    REFRESH lt_div_text.
  ENDIF.

*get the text of district
  SELECT * FROM zthrom_district
    INTO TABLE lt_dist_text
    FOR ALL ENTRIES IN gt_hrp9002
    WHERE YY_DISTRICT EQ gt_hrp9002YY_DISTRICT.
  IF sysubrc EQ 0.
    SORT lt_dist_text BY YY_DISTRICT.
  ELSE.
    REFRESH lt_dist_text.
  ENDIF.

*get the text of region
  SELECT * FROM zthrom_region
    INTO TABLE lt_reg_text
    FOR ALL ENTRIES IN gt_hrp9002
    WHERE YY_REGION EQ gt_hrp9002YY_REGION.
  IF sysubrc EQ 0.
    SORT lt_reg_text BY YY_REGION.
  ELSE.
    REFRESH lt_reg_text.
  ENDIF.

  LOOP AT gt_hierarchy1 ASSIGNING <gfs_hierarchy>.
    CASE <gfs_hierarchy>iobjnm.
      WHEN gc_division.
        READ TABLE lt_div_text INTO lw_div_text
        WITH KEY yy_DIVISION = <gfs_hierarchy>nodename.
        IF sysubrc EQ 0.
          <gfs_hierarchy>txtsh = lw_div_textyy_DIVISION_text.
          <gfs_hierarchy>txtmd = lw_div_textyy_DIVISION_text.
          <gfs_hierarchy>txtlg = lw_div_textyy_DIVISION_text.
        ENDIF.
        <gfs_hierarchy>iobjnm = ‘0HIER_NODE’.
      WHEN gc_district.
        READ TABLE lt_dist_text INTO lw_dist_text
        WITH KEY YY_DISTRICT = <gfs_hierarchy>nodename.
        IF sysubrc EQ 0.
          <gfs_hierarchy>txtsh = lw_dist_textYY_DISTRICT_text.
          <gfs_hierarchy>txtmd = lw_dist_textYY_DISTRICT_text.
          <gfs_hierarchy>txtlg = lw_dist_textYY_DISTRICT_text.
        ENDIF.
        <gfs_hierarchy>iobjnm = ‘0HIER_NODE’.
      WHEN gc_region.
        READ TABLE lt_reg_text INTO lw_reg_text
        WITH KEY YY_REGION = <gfs_hierarchy>nodename.
        IF sysubrc EQ 0.
          <gfs_hierarchy>txtsh = lw_reg_textYY_REGION_text.
          <gfs_hierarchy>txtmd = lw_reg_textYY_REGION_text.
          <gfs_hierarchy>txtlg = lw_reg_textYY_REGION_text.
        ENDIF.
        <gfs_hierarchy>iobjnm = ‘0HIER_NODE’.
        <gfs_hierarchy>iobjnm = ‘0HIER_NODE’.
    ENDCASE.

  ENDLOOP.

  UNASSIGN <gfs_hierarchy>.
  REFRESH: lt_div_text, lt_dist_text, lt_reg_text. “lt_loc_text,
  CLEAR:   lw_div_text, lw_dist_text, lw_reg_text. “lw_loc_text,

ENDFORM.                    ” F_GET_TEXT

Configuring Logical File Path:

Create a logical path to physical path mapping in FILE T-Code. Below is the configuration for setting the physical path of the Hierarchy.

/wp-content/uploads/2013/10/10_294959.png

Data Extraction Settings:

               

To extract the data from the physical file stored on the application server into BI, we need to make changes to the Hierarchy DataSource of the Work Center. For this, go to RSA1 T-code in the BI Development server.

/wp-content/uploads/2013/10/11_294960.png

Select the Work Center Hierarchy DataSource and go to extraction tab. Change the Adapter settings to “Load Test-Type File from Application Server”. Set the File Name by selecting the appropriate logical file name present on the application server.

Once the File Name is selected, activate the DataSource and create an InfoPackage corresponding to the Hierarchy DataSource.

/wp-content/uploads/2013/10/12_294961.png

Now create a transformation with the following rule groups:

a.       Hierarchy Header

                    /wp-content/uploads/2013/10/13_294967.png

b.      Hierarchy Description

                     /wp-content/uploads/2013/10/14_294968.png

c.       Hierarchy Structure

                     /wp-content/uploads/2013/10/15_294969.png

                    Write a routine on the Workcenter InfoObject as following:

                        /wp-content/uploads/2013/10/16_294970.png

d.      Descriptions for Text Nodes

                     /wp-content/uploads/2013/10/17_294971.png

Now, create a DTP for the corresponding Hierarchy DataSource and execute it to move the contents to the Work Center InfoObject.

Note: Please check the Activate Hierarchy check box in the Update tab to activate the hierarchy whenever the DTP is executed.

/wp-content/uploads/2013/10/18_294972.png

To see the hierarchy, right click on the Workcenter (Hierarchies) section and select Maintain Hierarchies.

/wp-content/uploads/2013/10/19_294973.png

This will open the initial screen of the hierarchy maintenance.

/wp-content/uploads/2013/10/20_294974.png

Double click on the Workcenter hierarchy to get the Active version of the Hierarchy ‘Workcenter’ display.

/wp-content/uploads/2013/10/21_294975.png

To report this post you need to login first.

Be the first to leave a comment

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

Leave a Reply