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.
3. Enter the DataSource hierarchy name, select the source system as FILE, select the DataSource Data Type as Hierarchies and press Enter.
4. Enter the description and Application component details where the DataSource will be placed.
5. Now in the Extraction tab, specify the Target InfoObject in which you wish to associate the Work Center Hierarchy into.
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.
7. Activate the DataSource.
Configuring ECC:
The Work Center Hierarchy is of the below mentioned structure.
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.
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.
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:
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 TEXT-004 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 sy-langu,
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 rlgrap-filename,
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 sy-datum.
lv_date = sy-datum.
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 sy-subrc 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_hrp9002-yy_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_hierarchy-nodename
gw_hierarchy-nodid.
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 sy-tabix.
SORT gt_hierarchy1 BY iobjnm nodename nodid ASCENDING.
LOOP AT gt_hierarchy1 INTO gw_hierarchy.
lv_tabix = sy-tabix + 1.
READ TABLE gt_hierarchy1 ASSIGNING <gfs_hierarchy> INDEX lv_tabix.
IF sy-subrc EQ 0.
IF gw_hierarchy-iobjnm = <gfs_hierarchy>-iobjnm
AND gw_hierarchy-nodename = <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_hierarchy-nodid = gv_count.
lw_hierarchy-iobjnm = p_iobjnm.
lw_hierarchy-nodename = p_nodename.
lw_hierarchy-tlevel = p_level.
lw_hierarchy-parentid = p_parentid.
lw_hierarchy-langu = 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_hrp9002-YY_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_hrp9002-YY_REGION
parentid = lv_parentid.
IF sy-subrc NE 0.
gv_nodename = gw_hrp9002-YY_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_hierarchy1-nodid.
ENDIF.
ENDIF.
*Update District Node at 5th Level
IF gw_hrp9002-YY_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_hrp9002-YY_DISTRICT
parentid = lv_parentid.
IF sy-subrc NE 0.
gv_nodename = gw_hrp9002-YY_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_hierarchy1-nodid.
ENDIF.
ENDIF.
*Update work center Node at 6th Level
IF gw_hrp9002-objid IS NOT INITIAL.
*Check if the workcenter is already present
READ TABLE gt_hierarchy1 INTO lw_hierarchy1
WITH KEY iobjnm = gc_workcen
nodename = gw_hrp9002-objid
parentid = lv_parentid.
IF sy-subrc NE 0.
gv_nodename = gw_hrp9002-objid.
PERFORM f_fill_final_table USING gc_workcen
gv_nodename
gc_level05
lv_parentid.
CLEAR: lv_parentid, gv_nodename.
ELSE.
lv_parentid = lw_hierarchy1-nodid.
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 sy-subrc <> 0.
MESSAGE text-001 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 sy-subrc EQ 0.
LOOP AT gt_hierarchy1 INTO gw_hierarchy.
CONCATENATE gw_hierarchy-nodid
gw_hierarchy-iobjnm
gw_hierarchy-nodename
gw_hierarchy-link
gw_hierarchy-parentid
gw_hierarchy-langu
gw_hierarchy-txtsh
gw_hierarchy-txtmd
gw_hierarchy-txtlg
gw_hierarchy-tlevel
gw_hierarchy-childid
gw_hierarchy-nextid 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 sy-subrc EQ 0.
MESSAGE text-003 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_hrp9002-yy_DIVISION.
IF sy-subrc 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_hrp9002-YY_DISTRICT.
IF sy-subrc 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_hrp9002-YY_REGION.
IF sy-subrc 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 sy-subrc EQ 0.
<gfs_hierarchy>-txtsh = lw_div_text-yy_DIVISION_text.
<gfs_hierarchy>-txtmd = lw_div_text-yy_DIVISION_text.
<gfs_hierarchy>-txtlg = lw_div_text-yy_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 sy-subrc EQ 0.
<gfs_hierarchy>-txtsh = lw_dist_text-YY_DISTRICT_text.
<gfs_hierarchy>-txtmd = lw_dist_text-YY_DISTRICT_text.
<gfs_hierarchy>-txtlg = lw_dist_text-YY_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 sy-subrc EQ 0.
<gfs_hierarchy>-txtsh = lw_reg_text-YY_REGION_text.
<gfs_hierarchy>-txtmd = lw_reg_text-YY_REGION_text.
<gfs_hierarchy>-txtlg = lw_reg_text-YY_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.
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.
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.
Now create a transformation with the following rule groups:
a. Hierarchy Header
b. Hierarchy Description
c. Hierarchy Structure
Write a routine on the Workcenter InfoObject as following:
d. Descriptions for Text Nodes
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.
To see the hierarchy, right click on the Workcenter (Hierarchies) section and select Maintain Hierarchies.
This will open the initial screen of the hierarchy maintenance.
Double click on the Workcenter hierarchy to get the Active version of the Hierarchy ‘Workcenter’ display.
- SAP Managed Tags:
