Update 12/12/2020: Code for following advanced cases added:
- Multiple Hierarchies
- Time Dependent Hierarchies
- Multilevel Hierarchies
- Duplicate Leaf Nodes
Prerequisites: You need some understanding of both, ABAP and how hierarchy in BW works, to be able to write/alter the code involved.
1. Introduction:
Lately, I was asked to design a solution that generates a custom hierarchy for an InfoObject. InfoObject to be used is 0CUSTOMER and 0CUSTOMER has an attribute - ZCCST_GRP (Customer Group). The hierarchy must use various customer groups as text nodes and under those nodes must lay our customers. Essentially a tree structure where all the customers are clubbed under their parent customer groups.
The only object that we will need to achieve this is a self-transformation on 0CUSTOMER (from ATTR to HIERARCHY) (and an expert routine on that transformation). No hierarchy data source is required!
The picture below shows the required result:
Expected Output - ABC, TEST1, NE*****, TEST2, TEST3, and TEXT
are customer groups and they have their respective customers
underneath them.All the existing solutions tell you to generate an export DataSource on 0CUSTOMER and write a Customer Exit/BAdI for that DataSource and whatnot. That certainly isn't required anymore with the new Hierarchy framework (BW 7.3 & above) and this solution seems to be the neatest.
2. System Details & Requirements:
I'm working on BW 7.5 but this should work on any system above 7.3. (Irrespective of this blog, my deepest sympathies if you're below 7.3).
3. Transformation & Routine:
Create a transformation for the hierarchy subtype of 0CUSTOMER.
In source, select Object Type = InfoObject, Name = 0CUSTOMER, and
Subtype = Attributes.
Once the transformation is created, create an expert routine for it.
From menu bar select: Edit -> Routines -> Expert Routine.Now, all we need to do is write the code in the expert routine and our solution will be complete. But before that, one must understand that the transformations for hierarchies are segmented by default. These segments will be utilized in the expert routine and hence, we need a brief understanding of them. If you already do understand this, skip step 4, and jump to the code.
4. Segments in Hierarchy Transformations:
There are 5 segments in total. You can view them just next to the rule group button.
See the picture below for reference:
Segments in a Hierarchy Transformation. Highlighted are the ones that
are mandatory.Out of the 5 segments, only two are mandatory - Hierarchy Header & Hierarchy Structure. Hierarchy Header is mandatory only if you want to generate multiple hierarchies. It can be excluded for a single hierarchy. In this example, I'm only showcasing a single hierarchy but still have used Hierarchy Header for future-proofing the code. I would recommend using it even for a single hierarchy. I have also used Texts for Text Node to enter the text for the text nodes. This isn't mandatory at all.
Now, just like in a normal transformation we have a result_package in the end routine; here, all 5 target segments have their own result_package called, respectively, result_package_1, result_package_2, result_package_3, result_package_4 & result_package_6 (yes, there is no result_package_5).
So we'll need to fill these result_packages manually as it is an expert routine and since we want to use three segments, we'll have to populate the result packages associated with all three of them. They are listed as below:
- Hierarchy Header - result_package_1 <- Mandatory for multiple hierarchies. Recommended to be used always.
- Hierarchy Structure - result_package_3 <- Mandatory. This is where all the magic happens.
- Texts for Text Node - result_package_4 <- Not mandatory. Used for giving texts to text nodes.
5. The Code:
Right. So this is it, structurally. Moving on to the code which is the heart of this solution.
Below is the complete ABAP code that goes into the expert routine and that you can use directly as long as you have used the same InfoObject(0CUSTOMER) & attribute(ZCCST_GRP). Else, just appropriately replace them in the code with your InfoObject and attribute and the rest of the code can be used as-is.
I've added comments throughout to give an understanding of every logic. Although if you need any further help, let me know in the comments.
IF SOURCE_PACKAGE IS NOT INITIAL.
" Constants Declaration
CONSTANTS: lc_hier TYPE rshienm VALUE 'HIER_CUST_GRP',
lc_act TYPE rsobjvers VALUE 'A',
lc_hier_node TYPE rsnodename VALUE '0HIER_NODE',
lc_customer TYPE rsiobjnm VALUE '0CUSTOMER',
lc_en TYPE c LENGTH 2 VALUE 'EN'.
" Data Declaration
DATA: lv_prnt_id TYPE rshienodid,
lv_max_nodeid TYPE rshienodid.
" Macro to increment a number by 1.
DEFINE increment.
&1 = &1 + 1.
END-OF-DEFINITION.
" Get technical ID (hieid) of customer group hierarchy
SELECT SINGLE hieid
FROM rshiedir
INTO @DATA(lv_hieid_hier_cust_grp)
WHERE hienm = @lc_hier.
" Get the MAX node id. This is important. Simply put,
" always take the max node id and start creating
" your nodes by adding 1 to it to make sure you
" don't end up using a node id that is already
" being used.
" Also, never use the "WHERE hieid =" clause here
" as we want maximum node ID across all hierarchies
" for 0CUSTOMER.
SELECT SINGLE MAX( nodeid )
FROM /bi0/hcustomer
INTO lv_max_nodeid.
" Get parents that already exist. This includes
" everything - the root node & the leaves/leaf.
" This needs to be selected only from our custom
" hierarchy. So, use the "WHERE hieid =" clause.
" In the case of multiple hierarchies, add all of
" them here, separated by "AND".
SELECT hieid, nodeid, nodename
FROM /bi0/hcustomer
INTO TABLE @DATA(lt_existing_parents)
WHERE hieid = @lv_hieid_hier_cust_grp
AND objvers = @lc_act.
SORT lt_existing_parents BY hieid nodename.
SORT SOURCE_PACKAGE BY customer.
" lv_index will be used to increment the node ID.
DATA(lv_index) = 00000000.
LOOP AT SOURCE_PACKAGE ASSIGNING <source_fields>.
*--------------------------------------------------------------------*
*~~~~~~~~~~~~~~~~~~Process Hierarchy - HIER_CUST_GRP~~~~~~~~~~~~~~~~~*
*--------------------------------------------------------------------*
IF <source_fields>-/bic/zccst_grp <> space.
CLEAR RESULT_FIELDS_3.
" Fill result_package_1 with the technical name
" for the hierarchy.
RESULT_PACKAGE_1 = VALUE #( BASE RESULT_PACKAGE_1 (
objectid = lv_hieid_hier_cust_grp
h_hienm = CONV char30( lc_hier )
) ).
" Reading result_package_3 to check if we already have a
" root node (customer group) added. If we have it already,
" then it's a case of multiple customers per customer group.
" So skip adding customer group again and keep adding the
" customers for it.
" ***(Don't use binary search here.)***
" Sy-subrc = 0 means customer group already exists.
READ TABLE RESULT_PACKAGE_3 INTO RESULT_FIELDS_3
WITH KEY h_hiernode = <source_fields>-/bic/zccst_grp.
IF sy-subrc <> 0.
"See if we already have this customer group in hierarchy.
READ TABLE lt_existing_parents ASSIGNING
FIELD-SYMBOL(<ep_cust_grp>)
WITH KEY hieid = lv_hieid_hier_cust_grp
nodename = <source_fields>-/bic/zccst_grp
BINARY SEARCH.
IF sy-subrc = 0.
" We have the customer group already. So write its node_id
" from lt_existing_parents table to result_package_3.
" T_level will be 1 as it's root node.
RESULT_PACKAGE_3 = VALUE #( BASE RESULT_PACKAGE_3 (
objectid = lv_hieid_hier_cust_grp
h_nodeid = <ep_cust_grp>-nodeid
h_hiernode = <source_fields>-/bic/zccst_grp
h_iobjnm = lc_hier_node
h_tlevel = 01 "Always 01
) ).
" Store parent_id. Will be used when adding customers.
lv_prnt_id = <ep_cust_grp>-nodeid.
ELSE.
" Increment lv_index before (manually) adding any node.
increment lv_index.
" Customer grp neither already added to result_package_3,
" nor exists in backend. So, it's a new customer group.
RESULT_PACKAGE_3 = VALUE #( BASE RESULT_PACKAGE_3 (
objectid = lv_hieid_hier_cust_grp
h_nodeid = lv_max_nodeid + lv_index
h_hiernode = <source_fields>-/bic/zccst_grp
h_iobjnm = lc_hier_node
h_tlevel = 01 "Always 01
) ).
" Store parent_id. Will be used when adding customers.
lv_prnt_id = lv_max_nodeid + lv_index.
ENDIF.
ELSE.
" Store parent_id. Will be used when adding customers.
lv_prnt_id = RESULT_FIELDS_3-h_nodeid.
ENDIF.
" Increment lv_index before (manually) adding any node.
increment lv_index.
" Check if customer already exists.
" If it does, copy the node_id from lt_existing_parents to
" result_package_3.
" *** But do not copy the parent_id. ***
" Parent_ID must be entered from lv_prnt_id var.
" This will take care of the case where a customer is moved
" from one customer group to another customer group.
READ TABLE lt_existing_parents ASSIGNING
FIELD-SYMBOL(<ep_cust_grp_1>)
WITH KEY hieid = lv_hieid_hier_cust_grp
nodename = <source_fields>-customer
BINARY SEARCH.
IF sy-subrc = 0.
RESULT_PACKAGE_3 = VALUE #( BASE RESULT_PACKAGE_3 (
objectid = lv_hieid_hier_cust_grp
h_nodeid = <ep_cust_grp_1>-nodeid
h_iobjnm = lc_customer
customer = <source_fields>-customer
h_parentid = lv_prnt_id "IMP!
h_tlevel = 02 "Always 02
) ).
ELSE.
" New customer. Add to result_package_3.
RESULT_PACKAGE_3 = VALUE #( BASE RESULT_PACKAGE_3 (
objectid = lv_hieid_hier_cust_grp
h_nodeid = lv_max_nodeid + lv_index
h_iobjnm = lc_customer
customer = <source_fields>-customer
h_parentid = lv_prnt_id
h_tlevel = 02 "Always 02
) ).
ENDIF.
" Fill the Text in result_package_4 table.
" Key is used as text here.
" Always give value for langu. Texts only work
" if a language is supplied.
RESULT_PACKAGE_4 = VALUE #( BASE RESULT_PACKAGE_4 (
objectid = lv_hieid_hier_cust_grp
langu = lc_en
h_hiernode = <source_fields>-/bic/zccst_grp
txtlg = <source_fields>-/bic/zccst_grp
txtmd = <source_fields>-/bic/zccst_grp
txtsh = <source_fields>-/bic/zccst_grp
) ).
ENDIF.
ENDLOOP.
ENDIF.The code above simply takes all the customer groups in the source_package and creates a text node for each of them. Then it takes all the customers and adds them underneath their respective customer group nodes. result_package_3 is responsible for doing both.
lv_max_nodeid - this variable holds the maximum node id across all the hierarchies for our InfoObject. This will be used to ensure that we never overlap the node IDs of two different nodes.
lt_existing_parents - this internal table holds all the nodes that already exist for this hierarchy. So, for example, if node - ABC is already present, it will be held in this table. This is true for both - customer groups and customers. If you're running this code for the first time, the internal table will be blank. Using this we can take care of the case when a customer moves from one customer group to another.
Note: Always give value for langu when populating result_package_4. Texts only work if a language is supplied.
6. Test Cases:
I have tested this solution extensively and found it working for all the cases mentioned below:
- Hierarchy Tampering - If someone manually deletes any text node or an inner node or anything really, just reload the DTP and all will be good.
- Data Update - If master data is updated in any way - a customer is deleted, added, or moved to another customer group, or a customer group is deleted or renamed or anything, reload the DTP and we will have an updated hierarchy as per the updated master data.
- Different Levels - I have used a simpler example to keep things straight - but you can change the T_Levels and create different text nodes and levels as per your requirement. Just add them all to result_package_3.
- Multiple Hierarchies - I originally wrote this blog showcasing generation of 2 different hierarchies but decided to go with one to keep things simple. I have used and tested 2 hierarchies but you can essentially generate any number of hierarchies using the code given. The code will need to be altered, yes. But once you understand how it works for a single hierarchy, it becomes rather simple to alter it for any further hierarchies.
- Performance - I have tested it thoroughly and performance won't be a problem at all.
A hierarchy with around 25,000 nodes and the DTP loads within
4 seconds.
7. Multiple Hierarchies plus Time Dependent Hierarchies:
This section explains how to create multiple hierarchies and making them time dependent as well.
The concept remains the same. Just few settings need to be altered and few things need to be remembered.
For Multiple Hierarchies:
- Always make sure you have a unique hier ID for every hierarchy created.
- If you are creating a hierarchy for the first time, lv_hierid_* variable/s would be blank. In such a case always assign a unique value to the variables manually. I have assigned 1,2 and 3 to respective variables of the 3 hierarchies I'm generating.
For Time Dependent Hierarchies (structure level):
- Hierarchy must first be set as Time Dependent. Go to Hierarchy tab and select radio button named - "Time-Dependent Hierarchy Structure".
- Dateto and Datefrom fields must be supplied in result_package_3 for every node you wish to be time-dependent.
Combined code showing both multiple hierarchies and time dependency:
https://mega.nz/file/tF0SVBSK#x9Yv8TMP4fMD4t1CNkOlpuE8UvMoQvTOEaiFvApS1Cs
8. Multi Level Hierarchies:
Points to be remembered:
- Duplicate Nodes are only allowed for leaves (the last node).
- Most of the errors you'll get will be due to duplicate (non-leaf) nodes. Check if same name is being repeated at any level (non-leaf).
Code for multilevel hierarchies:
https://mega.nz/file/gV8SxDjC#J4XRnWkmJ10aT9yRc6YgQ5aT1WA17F2pMYy3-c4y674
9. Duplicate Leaf Nodes:
Points to be remembered:
Duplicate nodes are allowed in hierarchies only at leaf/lowest level. We can achieve this by reading result_package_3 before processing the leaf node and checking if it already exists for the combination of object_id, parent_id and value of the leaf (customer, in this case).
Also, if you are clubbing this with multiple hierarchies, remember to created a local variable for every hierarchy that stores the object id which will be used in READ statement at leaf level.
Variable used in this example:
lv_objectid_hier_zcustomer TYPE /bi0/oiobjectid.READ statement:
READ TABLE RESULT_PACKAGE_3 INTO RESULT_FIELDS_3
WITH KEY objectid = lv_objectid_hier_zcustomer
/bic/zcustomer = <source_fields>-/bic/zcustomer
h_parentid = lv_prnt_id.This is an example of a duplicate leaf node.
Code to allow duplicate leaf nodes:
https://mega.nz/file/wZ8SQZCa#fBnQYU_ziJeolSo76HoCgktjdy-YygpH-WLXC8BGghw
This brings us to the end of the blog. Let's summarize:
- Create a transformation from ATTR to HIERARCHY and create an expert routine on it.
- Copy the code given above into the expert routine and alter it as per your requirements.
- ???
- Profit.
Do share your thoughts if you made it so far. Thanks.
- SAP Managed Tags:
67 Comments
