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
Raj Kumar Rai have experience of over 4 Years SAP BI & ABAP.
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).
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 –
Case1: There is no valid employee (P2) assigned to position (S2), we need to find the super supervisor with following logic:
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.
So this is what we intend to achieve and understand by the detailed stepsin this document.
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
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.
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.
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.
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.
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
Properties of this InfoObject needs to be edited to suit for this particular requirement. Check for following settings:-
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
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.
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
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.
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:
*&---------------------------------------------------------------------*
*& 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.
Once the InfoPackage completes successfully following hierarchy tree structure would be generated
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
10 | |
9 | |
5 | |
4 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 |