1. Introduction:
Lately, I was asked to design a solution that generates a custom hierarchy for an InfoObject without a standard DataSource. InfoObject to be used is 0CUSTOMER and 0CUSTOMER has an attribute – ZCCST_GRP (Customer Group). The hierarchy must use various customer groups as text/root 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:
Right. So this is it, structurally. Moving on to the code which is the heart of this solution. (And exhibit A of my belief that you can never be a complete BW consultant until you can write ABAP. Do you agree?)
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.
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.
6. Test Cases:
I have tested this solution excessively and found it working for all the cases mentioned below:
A hierarchy with around 25,000 nodes and the DTP loads within
4 seconds.
This brings us to the end of the blog. Let’s summarize:
Do share your thoughts if you made it so far. Thanks.
Okumaya devam et...
Lately, I was asked to design a solution that generates a custom hierarchy for an InfoObject without a standard DataSource. InfoObject to be used is 0CUSTOMER and 0CUSTOMER has an attribute – ZCCST_GRP (Customer Group). The hierarchy must use various customer groups as text/root 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:
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.
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:
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.
Right. So this is it, structurally. Moving on to the code which is the heart of this solution. (And exhibit A of my belief that you can never be a complete BW consultant until you can write ABAP. Do you agree?)
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.
Kod:
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.
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.
6. Test Cases:
I have tested this solution excessively 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.
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.
Okumaya devam et...