Generating Custom Hierarchies in SAP BI BW using Custom DataSource

[Former Member]

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:  19^th June 2012

Author Bio

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.

Go to extraction tab and double click the extract structure

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

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.

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.

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.

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.

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.

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

InfoObject for activating hierarchy

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.

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

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

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

A dummy hierarchy is created and it looks like this

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

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.

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

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.

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
IN (  select 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.
      Else.

      v_flag = 0.

      Endif.

Endwhile.


ENDCASE.

Loading the Hierarchy and structure generated

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