Multilevel Categorization in SAP BW reporting (CRM)

[arun_purohit]

Requirement

It is required to have a BW report on complaints data with 10 additional fields in free characteristics (other than standard content available fields). These 10 fields are 10 levels (level 1 through level 10) of multilevel categorization. Multiple records in BW report output should aggregate based on same text of category id's (where ID of category can be different but text remains same). In multilevel categorization hierarchy, each leaf category id can be at max at level 10 or less. Length  of each parallel strand of multilevel categorization hierarchy may vary vary from 2 to 10. Following is the descriptive diagram for it-

Additional Information

In complaints transaction data in SAP CRM, each complaint stores 10th level of category id (i.e. leaf node of multilevel categorization hierarchy)

BW BEx report on top of Complaints ETL-

ETL for Complaints

1) 0CRM_COMPLAINTS_I (CRM Complaint Item)

2) 0CRM_COMP (CRM Complaints (Items))

3) 0CSAL_C09 (Complaints)

There is a standard master data datasource 0CRM_CAT_CATEGORY_ATTR (Category - Attributes (Multilevel Categorization)). This datasource is supposed to bring in 10 levels of category ID's as attributes.

Challenge

These 10 levels won't populate if hierarchy set up done in CRM side is done in hierarchical way. There are 2 ways of setting up Multilevel categorization hierarchy in CRM-

1) Attributive Way

2) Hierarchical Way

If hierarchy set up done in CRM side is done using attributive way, then those 10 fields of datasource should be populated.

Actual Case

1) Multilevel Categorization Hierarchy setup in CRM is done using Hierarchical way.

2) In BW report output it is expected that multiple records should aggregate based on same text description of levels (level 1 -10 of multilevel categorization), irrespective of different category id's.

Solution

0CRM_CAT_CATEGORY_ATTR datasource is enhanced with 10 additional fields and enhancement code is written to populate those 10 fields.

Logic-

This master data source brings all categories regardless of their level in hierarchy (number of records of this data source equal to number of categories). Following are steps of dummy code-

1) Check the main category in each record of master data source

2) If the category checked in first step is leaf node [check crmc_erms_cat_hi-node_leaf = 'X'], then only process further with following logic.

3) DO loop 11 times (as it is known that there can be maximum 10 levels in hierarchy), in each pass of loop Table- crmc_erms_cat_hi is read

4) At the end of DO loop we will know, maximum number of levels of the hierarchy are utilized out of 10 [maximum levels utilized could be 5 or 7 or 8 or 10].

5) Now again DO loop 11 times, in it for every record of datasource, where category is leaf node, it will read [Table- crmc_erms_cat_hi] and update different levels of category over in in level 1 to level-n fields. Where level-n (maximum levels utilized out of 10) is derived in step 3-4.

Code is Attached-

Result

As can be seen below, report aggregated the records with same Category Text (though there are multiple category id's with same text)-