How to implement CRM hierarchy in BI 7

When integrating CRM with BI 7 one can notice that Organization structure hierarchy cannot be easily uploaded to BI. Unfortunately, Business Content does not provide a standard solution for integrating CRM organization hierarchy. In this post I outline steps on how to implement a solution for loading organization hierarchy from CRM to BI. This approach has been implemented and tested with SAP CRM 5.2 and SAP BI 7.0.

As an introduction to hierarchy table structures and related concepts I suggest first looking at an earlier post on the topic: “Use master data Update Rules for Generating Hierarchies Automatically”. The approach described below is not limited to CRM only. It can be used in generating hierarchies dynamically for various data sources.

1. Clarify requirements

Let us imagine we have a business case where organization hierarchy is structured as follows: Total Organization – Region – Unit – Position – Employee (business partner). See an example from CRM (transaction code PPOMA_CRM):

In order to keep the task simple we intend to design a solution that displays the hierarchy exactly as it is in the source system. If there are any changes in the organization structure we want to see the latest organization structure hierarchy in BI. That is, if an employee moves to another department his/her sales will move with them and will be reported under the new department. Another requirement is to process only English versions of node names.

Note: It is possible to slightly adjust code’s logic described below in order to 
         accommodate time dependant hierarchies.

One more requirement is related to the fact that some reports have to display Region and Unit next to employee’s code. In order to provide this capability we will add Region and Unit as attributes of the Employee infoobject, and populate them with appropriate values from the organization structure.

2. Create extractors

As business content does not provide a solution for extracting organization hierarchy, we have to build generic extractors. They have to be based on the following tables: HRP_1001 (nodes) and HRP_1000 (texts).

It is recommended to design database views on top of the tables and use them for building extractors: Z_HRP_1001 and Z_HRP_1000. If organization structure does not contain vast amount of nodes we can refresh hierarchy from CRM using a daily full refresh.

3. Create infoobjects in BI

Next we have to build objects in BI that would be updated by the extractors. We can use two infoobjects: one for organization structure (employee) attributes and hierarchy (ZORGSTR), and another one for texts (ZORGTXT).

Infoobject ZORGSTR has to have navigation attributes ZREGION and ZUNIT in order to link employees with corresponding region and units. I suggest using the following data flow:

4. Implement attribute transformation to construct the hierarchy

The magic happens on the way from the extractor to the infoobject attributes (ZORGSTR). First we have to set both of the newly created infoobjects (ZORGSTR and ZORGTXT) as data targets in BI. The next step is to create a transformation for ZORGSTR and add some ABAP code in the start routine of the transformation.

In the transformation mapping screen three objects have to be mapped to the target fields of ZORGSTR:

• SOBID -> ZORGSTR
• ADATANR -> ZREGION
• SHORT -> ZUNIT

The idea is to process all nodes coming from CRM with the extractor Z_HRP1001 and generate appropriate hierarchy nodes together with assigning proper attributes for Region and Unit. Below you will find guidelines and some ABAP code that can be used in the transformation start routine of the ZORGSTR infoobject:

* Select existing root node in the hierarchy

SELECT SINGLE * FROM /BIC/HZORGSTR INTO node

WHERE TLEVEL = '01'.

* Completely refresh hierarchy table

DELETE FROM /BIC/ HZORGSTR.

* Leave only current Plan entries in the hierarchy

DELETE SOURCE_PACKAGE WHERE PLVAR <> '01'. " OR ENDDA <> '99991231'.

* Populate texts from characteristic ZORGTXT

LOOP AT SOURCE_PACKAGE INTO sp.

  curid = sp-OBJID.

  SELECT SINGLE txtsh txtlg INTO (sp-mc_short, sp-stext)

  FROM /BIC/TZORGTXT WHERE /BIC/ZORGTXT = curid. " AND LANGU = 'EN'.

  IF sy-subrc = 0.

    MODIFY SOURCE_PACKAGE from sp.

  ENDIF.

ENDLOOP.

* Root node for internal organization is called 'ROOT'

READ TABLE SOURCE_PACKAGE

WITH KEY mc_short = 'ROOT' INTO sp.

curnode = '1'.

* Prepare internal table for BP codes

CLEAR resp. rwa = sp.

* Form Level 1 of the hierarchy -----------------------------

node-NODENAME = sp-STEXT.

TRANSLATE node-NODENAME TO UPPER CASE.

curnode = curnode + 1.

node-CHILDID = curnode.

INSERT INTO /BIC/ HZORGSTR VALUES node.

* Form Level 2 of the hierarchy ---- Region ----------------

*Browse through all nodes belonging to current root ----------

lastnode2 = '0'.

LOOP AT SOURCE_PACKAGE INTO sp2

      WHERE OBJID = sp-OBJID AND SUBTY(1) = 'B' AND SCLAS = 'O'.

*Find child record

  childnode = sp2-SOBID.

  READ TABLE SOURCE_PACKAGE WITH KEY OBJID = childnode INTO sp3a.

                        <… ABAP code …-  assign fields for the new hierarchy node >

  lastnode2 = curnode.

  curnode = curnode + 1.

  node-CHILDID = '0'.

  INSERT INTO /BIC/HZTVFKORG VALUES node.

* Form Level 3 of the hierarchy ---- Unit ----------------

<… ABAP code …>

* Form Level 4 of the hierarchy ---- Position ----------------

<… ABAP code …>

* Form Level 5 of the hierarchy ---- Employee ----------------

  lastnode6 = '0'.

  LOOP AT SOURCE_PACKAGE INTO sp6

      WHERE OBJID = sp6a-OBJID AND SCLAS = 'CP'.

*Find child record

    childnode = sp6-SOBID.

    READ TABLE SOURCE_PACKAGE WITH KEY OBJID = childnode

    OTYPE = 'CP' SCLAS = 'BP' INTO sp7a.

   IF sy-subrc = 0.

*Add new BP record if it is found in the structure

<… ABAP code …>

* Add BP code & attributes

      rwa-sobid = node-NODENAME.

      rwa-ADATANR = sp3a-mc_short. " Region

      rwa-SHORT = sp4a-mc_short. " Unit

      INSERT rwa INTO TABLE resp.

<… ABAP code …>

   ENDIF.

        ENDLOOP. " Level 5--------

      ENDLOOP. " Level 4--------

  ENDLOOP. " Level 3------

ENDLOOP. " Level 2-----

* Populate source package from resp

DELETE SOURCE_PACKAGE WHERE PLVAR = '01'.

APPEND LINES OF resp TO SOURCE_PACKAGE.

* Remove duplicates

SORT SOURCE_PACKAGE BY SOBID.

DELETE ADJACENT DUPLICATES FROM SOURCE_PACKAGE COMPARING SOBID.

5. Outcome

Data loads should be done in the following order:

• Run extractor Z_HRP1000 to update characteristic ZORGTXT with texts for hierarchy nodes and attributes.
• Run extractor Z_HRP1001 to update attributes of ZORGSTR.

When the transformation script is executed a new organization hierarchy is generated based on current organization model in CRM. At the same time ZORGSTR is populated with employee codes together with Region and Unit attributes.

Below is an example of the hierarchy generated in SAP BI dynamically: