In the current example, there is no NODEID, PARENTID, CHILDID or NEXTID available. The data is a flat table with each CMDTY_CD assigned under up to 5 levels. This data needs to be converted into a proper format required for loading of hierarchy.
Source Data -
We start with creating a dummy hierarchy that we will use in the transformations.
Some of the parameters are hard coded within the transformations. In this example, the transformation is used to load only one hierarchy. Hence the hierarchy header information is constant. If you want to load multiple hierarchies, you can modify them through your source data or code it in your end routine.
The challenge is to populate the hierarchy structure with the proper node, parent, child and next ID’s.
We start by finding the parent nodes at each level and storing them in individual tables. The parent nodes for lowers levels will be children for upper nodes. We will take that into consideration when we populate the parent and child ID. In this example, all the ‘parent’ nodes are external characteristics. Their children might be CMDTY_CD or lower group level code.
The parent nodes for the top most level are populated.
We then add level 2 objects to the hierarchy. We make one pass for all the external characteristics and then another for all the CMDTY_CD that might be present. For finding the CMDTY_CD, we do a search within the entire table and not on the individual levels.
We repeat the process for all 5 levels. Since level 5 will not have any children from the external characteristic, we only look for CMDTY_CD for that level.
The code can be modified to fit a hierarchy with no external characteristic or with different number of levels. The link node would be needed if you have the same object used multiple times in the hierarchy.
The next step is to create a DTP for loading the hierarchy. We will use a full load info package to load data into PSA and then subsequently into the hierarchy.
You can see the hierarchy loaded once the DTP is run –