Skip to Content

Generating Custom Hierarchies in SAP BI BW using Custom DataSource

Applies to:

SAP BI 2004s or SAP BI 7.0


Summary

Standard Business Content extractors support main hierarchies but it does not support all the hierarchies. SAP does not provide a standard DataSource to extract Employee-Supervisor Hierarchies to BW. This article explains the procedure of how to generate custom Employee-Supervisor hierarchy in SAP BI / BW for HR Module (SAP Business Warehouse).

Author: Raj Kumar Rai  

Company: Infosys Limited

Created on:  19th June 2012

Author Bio

/wp-content/uploads/2012/06/a_112084.jpg

Raj Kumar Rai have experience of over 4 Years SAP BI & ABAP.

Table of Contents

  1. Introduction
  2. Scenario
  3. Enhancing 0EMPLOYEE to bring supervisor information
    1. Code to bring supervisor / super-supervisor information
  4. Storing Employee-Supervisor information in a DSO
    1. DTP Filters
  5. Custom Hierarchies – Steps & Workaround
    1. DSO Containing Hierarchy Data
    2. InfoObject for activating hierarchy
    3. Activating hierarchy by creating a dummy node
    4. Generating Export DataSource
    5. Creating an InfoPackage
    6. Enhancing the Export DataSource
    7. Code to enhance the hierarchy DataSource
    8. Loading the Hierarchy and structure generated

Introduction

Standard Business Content extractors support main hierarchies but it does not support all the hierarchies. SAP does not provide a standard DataSource to extract Employee-Supervisor Hierarchies from ECC to BW. This becomes even more complicated when this hierarchy is not existent at ECC side & you have to generate the hierarchical structure of all the employees in an organization at BW side. This article explains the procedure of how to generate custom Employee-Supervisor hierarchy in SAP BI / BW (SAP Business Warehouse).

Scenario

For a reporting requirement we need all the employees and its supervisor information in a hierarchical structure. InfoObject 0employee is a master data object with all the employee related information present in its master data table. But the twist was that those employees who do not have any active immediate supervisor, should be assigned to their respective super-supervisors (supervisor of supervisor). Let’s take a simple example to understand the relation –

  1. P Employee Id
  2. S Position in an Organization

  1. P1 S1: Every employee (P1) assigned to a position (S1)
  2. S1 S2: Every position (S1) would have a reporting parent position (S2)
  3. S2 P2: Every parent position (S2) will have an active employee (P2) assigned to it, which in turn would be the supervisor (P2) for employee (P1)

Case1: There is no valid employee (P2) assigned to position (S2), we need to find the super supervisor with following logic:

  1. S2 P2: No active employee (P2)
  2. S2 S3: super supervisor position (S3) of supervisor position (S2)
  3. S3 P3: P3 is the active super supervisor assigned to super supervisor position S3

Above steps are to be repeated as many times until we get an active employee (P) assigned to the supervisor position (S).

Important HR relations to be noted:

HRP1001 is standard sap HR table which have diff Sub Types connecting OBJID & SOBID fields signifying different relations. Few important relations we would require are explained below –


SUB Type –      Relation

     A002      –   Reports To    

     A004      –   Is Sub-Ordinate To

     A008      –   Holder

     B005      –   Is Supervisor Of

Now we have employee supervisor relation data in place. But to create the hierarchy structure we require the employee-supervisor information in a specific format which requires following information for each entry.

  1. Node Id
  2. Node Name
  3. Infoobject Name
  4. TLevel
  5. Parent Id

So this is what we intend to achieve and understand by the detailed stepsin this document.


Enhancing 0EMPLOYEE to bring supervisor information:


  Go to transaction RSA2 in ECC side. Enter the datasource name and click Display.

/wp-content/uploads/2012/06/a1_112106.png

Go to extraction tab and double click the extract structure

/wp-content/uploads/2012/06/a2_112107.png

Click on append structure option and add the new InfoObject ZZXXXXXX to the extract structure

/wp-content/uploads/2012/06/a3_112108.png

Go to transaction CMOD in R/3 side and enhance the extractor 0employee_attr for filling the ZZXXXXXX field which was a custom field added to the original extract structure.

/wp-content/uploads/2012/06/a4_112112.png

Enhance the code for 0employee_attr in the above highlighted SAP Exit

Code to bring supervisor / super-supervisor information


TYPES: BEGIN OF s_pa0001,
pernr
TYPE pa0001-pernr,
plans
TYPE pa0001-plans,
begda
TYPE pa0001-begda,
endda
TYPE pa0001-endda,
END OF s_pa0001.

DATA: l_s_hrms_biw_io_occupancy LIKE hrms_biw_io_occupancy.

DATA: it_pa0001 TYPE STANDARD TABLE OF s_pa0001.

DATA: lit_lead_pos TYPE STANDARD TABLE OF hrobject,
lwa_lead_pos
TYPE hrobject.

DATA: var_pos TYPE hrp1001-sobid.

REFRESH lit_lead_pos[].

  SELECT pernr plans begda endda FROM pa0001 INTO TABLE it_pa0001
WHERE begda <= sy-datum
AND endda >= sy-datum.

IF sy-subrc EQ 0.
SORT it_pa0001 BY pernr plans.
DELETE ADJACENT DUPLICATES FROM it_pa0001 COMPARING ALL FIELDS.
ENDIF.


var_pos = l_s_hrms_biw_io_occupancy-plans.

CALL FUNCTION ‘RH_GET_LEADING_POSITION’
EXPORTING
plvar             =
’01’
otype             =
‘S’
sobid             = var_pos
date              = sy-datum
auth              =
‘X’
buffer_mode       =
‘ ‘
consider_vac_pos  =
‘ ‘
TABLES
leading_pos       = lit_lead_pos
EXCEPTIONS
no_lead_pos_found =
1
OTHERS            = 2.

IF sy-subrc EQ 0.
CLEAR: lwa_lead_pos.

READ TABLE lit_lead_pos INTO lwa_lead_pos INDEX 1.

READ TABLE it_pa0001 ASSIGNING <fs_pa0001> WITH KEY plans = lwa_lead_pos-objid.

IF sy-subrc EQ 0.

l_s_hrms_biw_io_occupancy-zzxxxxxx = <fs_pa0001>-pernr.
*      Start of code for calculating super supervisor (Raj Kumar Rai), Infosys Pune           

    ELSE.   ” This portion of code executes when there is no active supervior for sup pos
v_flag =
0.

WHILE ( v_flag = 0 ).

CLEAR var_pos.
CLEAR lit_lead_pos.
var_pos = lwa_lead_pos-objid.

CALL FUNCTION ‘RH_GET_LEADING_POSITION’
EXPORTING
plvar             =
’01’
otype             =
‘S’
sobid             = var_pos
date              = sy-datum
auth              =
‘X’
buffer_mode       =
‘ ‘
consider_vac_pos  =
‘ ‘
TABLES
leading_pos       = lit_lead_pos
EXCEPTIONS
no_lead_pos_found =
1
OTHERS            = 2.

IF sy-subrc EQ 0.
CLEAR: lwa_lead_pos.

READ TABLE lit_lead_pos INTO lwa_lead_pos INDEX 1.

READ TABLE it_pa0001 ASSIGNING <fs_pa0001> WITH KEY plans = lwa_lead_pos-objid.

IF sy-subrc EQ 0.

l_s_hrms_biw_io_occupancy-zzxxxxxx = <fs_pa0001>-pernr.
v_flag = 1” Setting flag to 1 in case there exist an active supervisor for supervisor position.

ENDIF.

ELSE.
EXIT.

ENDIF.

ENDWHILE.

          ENDIF.

      ENDIF.

Function Module RH_GET_LEADING_POSITION is used to arrive to position of supervisor.

  1. FM RH_GET_LEADING_POSITION takes position of an employee as input (import parameter).
  2. Table lit_lead_pos is filled with supervisor position after completion of this FM.
  3. Supervisor position returned by this standard FM is then used to calculate supervisor employee id from PA0001 table.

Storing Employee-Supervisor information in a DSO

Creating hierarchy structure was a challenging requirement, so we decided to have the most critical Employee-Supervisor information in a write-optimized DSO. This was required to facilitate the dynamic access of this WDSO to create Supervisor hierarchy structure rather than focusing on establishing employee-supervisor relation at run time.

/wp-content/uploads/2012/06/a5_112114.png

In WDSO we needed Employee & Supervisor information. Since Supervisor is a time dependent attribute of 0employee master data so VALIDFROM & VALIDTO dates becomes critical and was thus included in the WDSO.

Direct mapping was done between Attribute of 0employee and WDSO ZWDSO08.

/wp-content/uploads/2012/06/a6_112121.png

DTP Filters

Employment Status and Employee Group are two important attributes in term of reducing the no of records loaded to DSO ZWDSO08.


1.     Employee Group = 5 signifies retired employees & they could be excluded to reduce the record count to this DSO.

2.     Employment Status = 1(inactive) & 3(active) employees. We can restrict Employment Status to either 1 or 2, as these are the two employment status we         are interested in.

3.     VALIFROM <= sy-datum

       A dynamic DTP filter routine is written to implement this.

4.     VALIDTO >= sy-datum

       A dynamic DTP filter routine is written to implement this.

/wp-content/uploads/2012/06/a7_112122.png

Now we have employee-supervisor information in active table of a DSO and we can utilize it to establish the hierarchy structure.

Custom Hierarchies – Steps & Workaround

When you have to load non-standard SAP hierarchies or hierarchies from other source systems, you come across certain bottlenecks. As a standard practice SAP BW only allows standard hierarchy datasource or flat file based hierarchy datasources. To load any kind of hierarchy we require a datasource of type hierarchy to insert data into a desired Infoobject hierarchy table. So to conclude we do not have a standard way to load custom hierarchy from a source system. Next few steps are work around this problem to create and load custom hierarchies.


DSO Containing Hierarchy Data

In the initial stages we already learnt that how we are enhancing the 0employee extractor to bring the additional employee-supervisor information and storing it in a DSO ZWDSO08 for deriving the hierarchy structure. For a similar requirement you can use a master or transaction datasource and get data in a DSO of your choice. Data can be loaded to this DSO from a simple flat file as well.

/wp-content/uploads/2012/06/a8_112123.png

A dummy data set to show the kind of data this DSO ZWDSO08 holds

/wp-content/uploads/2012/06/a9_112127.png

InfoObject for activating hierarchy

/wp-content/uploads/2012/06/a10_112128.png

Properties of this InfoObject needs to be edited to suit for this particular requirement. Check for following settings:-


  • Maintain the InfoObject as an ‘InfoSource with Direct Update’.
  • Choose an application component after enabling corresponding check box.
  • Mark the InfoObject as an InfoProvider and assign it to an InfoArea. Now the InfoObject would be visible in InfoProvider tree in Data Warehousing Workbench.
  • Enable the InfoObject as an export data source. This allows the extraction of the InfoObject data using data mart interface.
  • Check the hierarchy’s check box to specify that InfoObject contains hierarchies.

/wp-content/uploads/2012/06/a11_112129.png

Activating hierarchy by creating a dummy node

InfoObject settings are correctly maintained and activated. To activate the hierarchy we need to create a dummy hierarchy node which would be the first step towards generating custom hierarchy. Creation of dummy node would have to be done only once manually.

Right Click on the hierarchies and choose the Maintain Hierarchy option

/wp-content/uploads/2012/06/a12_112130.png

Click on create button to create a dummy hierarchy with requisite description.

/wp-content/uploads/2012/06/a13_112131.png

Create a simple text node by choosing text node out of available options at the top.

/wp-content/uploads/2012/06/a14_112132.png

A dummy hierarchy is created and it looks like this

/wp-content/uploads/2012/06/a15_112133.png

Generating Export DataSource


Next step is to generate an export datasource for this InfoObject. Go to the ‘Data Warehousing Workbench’ – RSA1 and right-click on the InfoObject. In the menu, choose ‘Additional Functions’ à ‘Generate Export DataSource’ option.

An export datasource typically gets a technical name ‘8<InfoObject Name><H, T, M>’ depending on whether the datasource is generated for hierarchy, Text or Attribute. This new export datasource can be found in application component Data Marts Master Data (DM-IO), after we choose source system as BW and replicate this application component. In case the above application component is not active this DataSource would by default go to ‘Unassigned Nodes’.

Now we have a hierarchy DataSource and we can link it to the InfoObject hierarchy via transfer rules. Hierarchy datasources are not yet supported in SAP BW 7.0 version and therefore we still need to use a SAP BW 3.5 datasource and transfer rules to build the connection. To create transfer rules, right-click on the hierarchy and use the context menu to go to ‘Additional Functions’ à ‘Create transfer rules’. Select the correct source system (SAP BI i.e. BW in this case and not ECC) & choose the Export DataSource we created from the list displayed. The transfer rules are generated and they link the Export DataSource and the InfoObject hierarchy named Employee-Supervisor.

Creating an InfoPackage


  Create a new InfoPackage for the hierarchy datasource by right-clicking on the datasource and selecting ‘Create InfoPackage’ option. Refresh the ‘available hierarchies from OLTP’. This will load all hierarchies that are available in the source. As we only created a dummy hierarchy, this will be the only hierarchy we can extract. Select this dummy hierarchy and save the InfoPackage

/wp-content/uploads/2012/06/a16_112134.png

Enhancing the Export DataSource


  Till now we have built a data flow that allows the extraction of a dummy hierarchy into a new hierarchy. Now we still need to insert the hierarchy data that is currently residing in the DSO ZWDSO08. This can be done in a user exit, an enhancement of the generated hierarchy datasource.

/wp-content/uploads/2012/06/a17_112135.png

Go to transaction CMOD, give your project name and choose the ‘Components’ radio button and click on Display

/wp-content/uploads/2012/06/a18_112139.png

Hierarchy datasources can be enhanced like any other type of datasource. Enhancement “RSAP0001 – Customer function calls in the service API” is called automatically during extraction. We have different exits for Transaction, Master &Text, Hierarchies.

  • EXIT_SAPLRSAP_001: Transactional data
  • EXIT_SAPLRSAP_002: Master data attributes or texts
  • EXIT_SAPLRSAP_004: Hierarchies

If enhancement RSAP0001 is not yet active in your system, it can be activated and assigned to a project.

/wp-content/uploads/2012/06/a19_112140.png

The function module ‘EXIT_SAPLRSAP_004′ is called every time a hierarchy datasource is executed. This function module contains an include  ‘ZXRSAU04′. This include can be enhanced using ABAP code to fetch the data from the DSO we loaded earlier. The most important table in this exit is table C_T_HIENODE which should be filled with the actual hierarchy data. Do not forget to refresh the C_T_HIENODE before inserting any data to delete the text node created for activating the Hierarchy.

Assumptions:

  • An employee with neither a parent nor a child shall be put under Unassigned Nodes.
  • An employee with no immediate supervisor active on immediate supervisor position, the super supervisor be assigned as its parent.

Code to enhance the hierarchy DataSource:

*&———————————————————————*
*&  Include           ZXRSAU04
*&———————————————————————*

* Global variable declaration


data: wa_hienode TYPE ROSHIENODE.
data : it_emp_super TYPE STANDARD TABLE OF /BIC/AZWDSO0800,“table to select nodes
it_temp
TYPE STANDARD TABLE OF /BIC/AZWDSO0800,” table to select child
it_emp_unassg
TYPE STANDARD TABLE OF /BIC/AZWDSO0800,
wa_emp_super
TYPE /BIC/AZWDSO0800 ,
v_count
TYPE n LENGTH 8 VALUE 1 ,
v_level
TYPE n LENGTH 8 VALUE 1 ,
v_flag
type n LENGTH 1 VALUE 1.

FIELD-SYMBOLS :
<fs_emp_super>
TYPE /BIC/AZWDSO0800 .

CASE i_datasource.

WHEN ’80EMPLOYEEH’.

REFRESH c_t_hienode.

“Select all level 1 nodes
select * from /BIC/AZWDSO0800
into table it_emp_super
where /bic/zsupvsr =  ‘  ‘
or /bic/zsupvsr =  0.

“Select all level 1 node who have at least 1 child
select * from /BIC/AZWDSO0800
into table it_emp_unassg
where /bic/zsupvsr
INselect EMPLOYEE from /BIC/AZWDSO0800
where /bic/zsupvsr =  ‘  ‘
or /bic/zsupvsr =  0 ).

“Appending UNASSIGNED NODE
wa_hienode-nodename =
‘UNASSIGN’ .
wa_hienode-nodeid =
99999999.
wa_hienode-iobjnm =
‘0EMPLOYEE’.
wa_hienode-tlevel = v_level.
wa_hienode-parentid = 
‘  ‘ .


append wa_hienode to c_t_hienode .
v_count = v_count +
1 .
clear : wa_hienode .

” For level 1 Nodes & Nodes with no child or parent


LOOP at it_emp_super into wa_emp_super.

wa_hienode-nodename = wa_emp_super-employee .
wa_hienode-nodeid = wa_emp_super-employee.
wa_hienode-iobjnm = ‘0EMPLOYEE’.
wa_hienode-tlevel = v_level.
wa_hienode-parentid = 
‘  ‘ .

read table it_emp_unassg assigning <fs_emp_super>
with key /bic/zsupvsr = wa_emp_super-employee.

if sy-subrc <> 0.
     
“If NO CHILD NODE
      wa_hienode-tlevel =
2 .
      wa_hienode-parentid =
99999999.
endif.


append wa_hienode to c_t_hienode .
v_count = v_count +
1 .
clear : wa_hienode .

ENDLOOP.

Refresh : it_emp_unassg .

While ( v_flag ne 0 ).

      refresh : it_temp .
     
select * from /BIC/AZWDSO0800
     
into table it_temp
     
for all entries in it_emp_super
     
where /bic/zsupvsr =  it_emp_super-employee  .

      If sy-subrc = 0.
           v_level = v_level +
1.
          
refresh : it_emp_super .
           it_emp_super[] = it_temp[] .

           Loop at it_emp_super into wa_emp_super.

           wa_hienode-nodename = wa_emp_super-employee .
           wa_hienode-nodeid = wa_emp_super-employee.
           wa_hienode-iobjnm = ‘0EMPLOYEE’.
           wa_hienode-tlevel = v_level.
           wa_hienode-parentid = wa_emp_super-/bic/zsupvsr .
          
append wa_hienode to c_t_hienode .
           v_count = v_count +
1 .
          
clear : wa_hienode .

           Endloop.
      E
lse.


      v_flag =
0.


      E
ndif.

Endwhile.

ENDCASE.

Loading the Hierarchy and structure generated

/wp-content/uploads/2012/06/a20_112141.png

Once the InfoPackage completes successfully following hierarchy tree structure would be generated

/wp-content/uploads/2012/06/a21_112145.png


28 Comments
You must be Logged on to comment or reply to a post.
  • Hi,

    Thank you for this useful post, i have a question about debugging the exit, after i wrote the coding in exit EXIT_SAPLRSAP_004, i am trying to debug it in RSA3, but i am not able to do it, it's hard to write the right code without debugging, do you have any idea?

    I also tried to simulate the update in infopackage monitoring, but it fails, it throws the error 'simulated hierarchies can not be edited'.

    Thank you very much.

    Zeynep

    • Zeynep,

      That's the problem i also faced while writing this code for extracting custom hierarchies. There is no way to debug the code for custom hiererachy datasource or export datasource. What you can do is , you can copy the entire CMOD code and create a report program using that. Print the C_T_HIENODE data using ALV and then check out for incorrect data records. This report can be debugged to find out any logical errors that might have got introduced.

      Regards,

      Raj

        • Hi,

          This probably isn't relevant anymore, but may be relevant to other people who see this:

          There is another way to debug the enhancement code.

          In order to do that you need to add code to the enhancement that will make it run in an infinite loop, and then open the process via sm50 and change the value for the loop condition.

          The code that needs to be added (anywhere before where you want to start debugging):

          Data i type i.

          do.

          if i = 1.

          exit.

          endif.

          enddo.

          After activating the enhancement go to sm50 and choose the line with the report name "SAPLXRSA". 

          Click on Program/Session->Program->Debugging.

          Then double click "i" in the code and then double click the variable in the data window.

          Click on the change icon for the value and change its value to 1.

          Press enter and then go back to Desktop 1 and continue debugging from there.

          Once you've found the problem you can remove the additional code.

          Shai

      • Hi Raj,

        Great post. I had a similar requirement and it works.

        Something that may be relevant to people who see this post - I had a problem with the enhancement running in an endless loop. This happens when the data in the DSO has the employee "0", which was obviously a junk record in my system. This can be solved by adding a filter to the DTP or removing the junk record at the source (0employee).

        Thanks again for the useful post.

        Shai

  • Hello,

    I've done all this steps but faced a problem:

    I can see that there is two lines in the hierarchy list - one in the active and one in the modified state.

    Also I can see in a infopackage log that 3 records were managed and it's a second step in the update.

    How to activate this herarchy automatically? (checkbox about automatic activation is set)

    • Are you referrign to the main hierarchy node which inturn contains the leaf nodes ?? did you cleared the C_T_HIENODE data before inserting any leaf nodes as u remember we need to create a dummy text (simplest of the availabke ones) node to activate the hierarchy, refer the heading "Activating the hierarchy by creating dummy node"

      Thanks,

      Raj

  • Hello Raj,

    Thanks for this useful post, helped a lot, and saved my time. Few remarks:

    1. as stated above no debug available in this cmod, copy-pasted the code to se38 and tested there.

    2. In  my case an organizational hier. needed to be generated from master data attributes, so no DSO was required, data (child-parent relationships) was sourced from master data tables.

    3. My source data proved to be inconsitent, with records where ID=0 and where parent id = id.

    To avoid endless loops I needed to delete those records, by adding these lines:

    ...

    DELETE it_emp_super WHERE <ID> = 0.

    ...

    LOOP at it_emp_super into wa_emp_super.

    DELETE it_emp_unassg WHERE <ID> = wa_emp_super-<ID>.

    ENDLOOP.


    ....

    After this your hier. gen. code worked like a charm, thanks again!


    Regards, Balazs

  • How would we create a heirarchy from a flatfile which is part of an ETL extract with transaction data as well

    Employee Number Earnings Deductions AVP  RD   DD Consultant Valid From Valid To

    1000                     10             5            2000 3000 4000       1000    01.1000    31.12.9999

    2000                     20             5            2000 

    3000                     30             5            2000 3000

    4000                     40             5            2000 3000

    5000                     50             5            2000 3000 4000        5000

    Thanks    

  • Hi Raj,

    Very nice document!

    I have similar requirement, but in our case we are implementing custom hierarchy on 0EMPLOYEE, we are reading data from 0EMPLOYEE and 0ORGUNIT data, and we are generating hierarchy nodes dynamically using Classes, FMs, etc., and then placing a file in to App. server. After that we are reading the App. server file to load 0EMPLOYEE hierarchy table.

    We are on 7.2v BW, so we don't have option to migrate 3.x hierarchy to 7.x.

    Issue we are facing is, when i transport to BW quality, it is falling with the below screenshot. I have collected all the objects but still it fails. It is expecting Data source to be present in Source system flat file. When we try to activate 3.x data source, in general it will take us though Infosource, so DS will not be collected in TR.  I tried it many ways but still i could not get it through.


    image1.JPG

    /wp-content/uploads/2016/03/image2_898619.jpg

    Can you please help?

    Regards,

    Kiran