Loading Hierarchy Data from CSV to BW using BODS

[former_member223526]

Hi All,

I have described the steps for loading Hierarchy from CSV to BW using BODS.

The Structure of the CSV file will be as shown below :

Plant is the Leaf nodes for which we are going to load the Hierarchy.  Also we need to have the text for each element which will be another CSV as below.

The object in the above text file structure will represent the info Object names in BW.

The File output will be like below

The overall job will look like below with 3 dataflow objects each performing a different role.

DataFlow 1 : CreatingNodeID

Query : (Remove the blanks value with updating the previous value)

The first Column country and state has blank values as shown above so we need to repeat the value till a new value in the column has encountered like in case of State AP should be repeated twice till new value KERALA has occurred in the data.

We do this using a custom function

The Custom Function DF_SAVESTR(Plant.Field1) will look like

IF input is not null the value in the column country will be stored in $Country. When the next value is null in the column the $input1 is not assigned i.e. null value is not assigned and the previous value in the $country returns nothing but the same value as INDIA for column country in our case.

The country column get the value in $country when the value in the file is null as below

Similar way we create the values for State column and the output of Query will look like

Then in order to get all the nodes in one single column we split the Query into four others Query Transforms as Country, State, City and Plant as shown in the data flow screenshot and also add a group number to each query transform to arrange the nodes in sequence.

Perform distinct selection to ignore the duplicates

Then perform a merge of all the individual transforms mentioned above to get all the values in a single column.

Merge:

Then perform order by based on GrpNum and generate the NodeID in the Final Query transform NodeID

The output of NodeID will look like below and save this in a table Master.

Master :

To get the Parent Child relation between the node we have another flow from Query which is split into 3 other transforms as Parent_child relation transforms like Country_state, State_city and City_plant.

The main purpose of these transforms is to generate the parent ID for each node of the File.

After we perform a merge on these three transforms the output will look like

Save the output in another table like Parent_child as shown above.

DataFlow 2 : CreatingMaster

This data flow works on retrieving the NodeID for parent nodes in the Parent_child table with lookup from Master table.

LookupNodeID :

We perform a lookup into master table referring the Parent Node with the NodeName of master the get the NodeId from the master table as shown in above screenshot using Lookup_ext().

The output  looks like: stored in table Master_Temp.

DataFlow 3 : ParentID and text (To add the parentID and text for each Node)

Get_ParentID:

We retrieve the parentID from Maser_temp table referring the NodeName from master table in above query transform with the Child in Maser_temp table and get the corresponding parentID for all the nodes in master.

Root_NodeID Transform : (Set the root node ParentID to ‘0’)

Here as we retrieved parentID for all the nodes in master table in the previous query transform, but the root node i.e. INDIA in our case will not have any parent and the value will be set to null in previous query transform.

To set to “0” we use this query transform.

Getting_NodeID :

We create the NodeID for the text item coming from the text CSV with reference to Master table and get the node , object name and text from the file.

Set_Object : Join the text and master data using NodeID.

The below two sets are joined

Output from Root_NodeID query transform:

Output from Getting_NodeID query transform:

The final output that we get is as below:

This can be mapped to the BW data source and loaded to BW.

Thanks,

Madhusudhan K.