Introduction
This document will explain how to convert a flat hierarchy into true hierarchy.
Scope
We will be referring to Product Hierarchy for the scope of our documentation. More often than not, product hierarchy is maintained as separate attributes for each level in BW.
Assumptions
The different hierarchy levels in a product hierarchy will follow a basic rule which can help identify the desired level based on the length of the string.
Software Components
- SAP BPC 10.0 on SAP NW BW(7.31)
- EPM Addin - Version 10 SP21 .NET4
Business Case
The product hierarchy is maintained as string - generally 18 characters in length - field PRDHA in Basic Data1 view of Material Master in ECC. The level details can be understood using table T179 and T179T in ECC. The levels have been extracted in BW but instead of storing them as a true hierarchy, each level is updated in BW as a separate InfoObject and these InfoObjects are then added into BW Material Master as its attributes. BPC needs true hierarchy so that reporting can be easier as well as it is easy to get description for each node whenever required.
Approach
Let us say that there are three levels to the hierarchy for document purpose and only those three have been maintained in BW.
Level1: First 4 Characters
Level2: First 6 Characters
Level3: First 8 Characters
The hierarchy string we have is ABCDEFGHIJKLMNOPQR.
Level1 will be ABCD
Level2 will be ABCDEF
Level3 will be ABCDEFGH
Level 1 is the highest node with no Parent.
Here is how the Material Master looks
We also have 0PRODH2, 0PRODH3 and 0PRODH4 as the objects that are storing ID and text for each level shown above.
We have BPC dimension BPC_MATERIAL which has a property called PROD_HIER and a hierarchy PARENTH1. First we will load all the base level members and parent nodes.
We will create 4 packages, each of which will be loading into the BPC_MATERIAL.
- Load Material ID and Text
- Load 0PRODH2 ID and Text
- Load 0PRODH3 ID and Text
- Load 0PRODH4 ID and Text
The transformations will be as follows:
Load from BW Material Master
ID=ID
PROD_HIER=0PRODH4
Load from 0PRODH4
ID=ID
PROD_HIER=ID(1:6)
Load from 0PRODH3
ID=ID
PROD_HIER=ID(1:4)
Load from 0PRODH2
ID=ID
In case, 0PRODH2, 0PRODH3 and 0PRODH4 reference to the standard InfoObject 0PRO_HIER, you will have to skip the records of undesired length. You can do that using conversion file. Here is how Conversion will look for 0PRODH2:
You might have to make other adjustments based on your data.
Once all the 4 packages are executed, here is how the dimension members will look like.
Now we need to copy these values in the property to PARENTH1.
Create a flat file in CSV format and upload it to BPC server. The file will have just a dummy record and a header row with column name ID.
We cannot load hierarchy from BW InfoObject as there is no hierarchy there and an empty selection is not accepted by the system. Hence this one record will help us to trigger the End Routine BADI. You can either maintain this dummy member in your dimension or if you do not want, adjust the BADI code to delete the record before passing to the result table. In this case, dummy member TEST is present in the BPC_MATERIAL. Here is the transformation file:
The BADI implementation is written in UJD_ROUTINE. You will need to adjust as per your dimension name and the property name in your landscape.
Create a Data Manager Package with Data Collection process chain /CPMB/IMPORT_MASTER - BPC: Import Master Data Attributes/Hier from Flat File.
Execute the DMP by calling the previously uploaded file. The log will show that 1 record was accepted and a submit count of 5 - A record for dummy member and 4 that we loaded.
The hierarchy is now updated in the dimension.
The flat hierarchy is now available as true hierarchy in BPC.
- SAP Managed Tags:
3 Comments
