Creating Parent Child Hierarchy in SAP Data Warehouse Cloud

[avinash_eppar]

Today we will walk through how to create Parent child hierarchy in SAP DWC.

Introduction 

A Parent child hierarchy is a hierarchy in a dimension that is based on two table columns . Together this  columns defines the hierarchical relationship among the members of the dimension .

The limitation of parent-child hierarchy is that each node is must have the single parent  and also a dimension containing a parent child hierarchy can not include any other hierarchies.

 

Steps to create the Parent-Child Hierarchy :

Step 1 : 

Go to Data Builder and select on which dataset we need to create a hierarchy.

 

Here I have created a dataset for showing parent child hierarchy i.e. Parent_child_Ex which  contains the columns Sales Country and Sales State.

 

Step 2 :

The next step is to change the semantic usage of your table(the dataset) as a dimension . In DWC it has to be set as dimension only then you can able to create the hierarchy in the  same .

 

1. 

 

In above figure  you can see the semantic usage is not yet set to dimension so that the hierarchy option is not enabled (Marked in a red circle)

 

Step 3 : 

We can now change the semantic type to Dimension and see the option will get enabled for us .

 

 

Step 4 :

Click on the hierarchy icon to create the hierarchy and there you will get the option for Parent_Child hierarchy as below.

Click on + sing to see the option .

 

Step 5 :

5.You can now define business name , technical name and also the parent and child in your parent child hierarchy. in this example the parent column is Sales Country and child  column is Sales State .

Click on close once done .

 

You can see the number  at hierarchy icon that we have created one Hierarchy for this  dataset as below.

 

 

Step 6 :

Save and deploy the dataset once done .

 

Share your thoughts and feedback in comments section below for your use cases.  You can try SAP DWC free account Here.

Happy Learning !!

Thanks & Regards,

Avinash

Image credits: All images in this blog are created as screenshots while creating this use case by the author.

[Pavan_Golesar]

Well documented Avinash!

Br,

Pavan G

[former_member800477]

Nice and clear explanation.

[former_member98559]

This is really helpful...!!! 🙂

[johnfeigs]

Hi Avinash,

I guess a time dependant hierarchy is needed in most databases.

Can you explain how to implement that in the SAP DWC?

Thanks a lot in advance!

John

[avinash_eppar]

Hello John,

 

Thanks for your query !

 

in SAP DWC you must import the Time Dimension views and Tables as below :

Time Dimension Views in SAP DWC

if you open this views the time dependent hierarchy is already maintained on top of this views as their semantic type is "Dimension"

Default Hierarchy

 

You need to associate this particular time dimension view to your analytical dataset for hierarchy implementation as below:

 

Association Mapping

 

now when you consume the analytical dataset into SAC you are able to see the hierarchy set to the order date dimension .

 

 

You can change the hierarchy level on the fly in SAC as per your requirement.

Thanks & Regards,

Avinash

Happy Learning !!

[florenciaborgarelli]

Hi Avinash!

 

I guess if we create hierarchies following these steps, you are able to use them in Data Builder, but not in Business Builder right?

I need to create parent child hierarchies but using Business Builder in DWC.

Could you explain how to implement that in the SAP DWC?

Thanks a lot in advance!

[mikehoekstra1]

Hi--Thanks for this, well done.

When we use this hierarchy in SAC, can we drill down into a hierarchy and see the children?

For example, if we have a location hierarchy with the following:

Parent Regions: Europe

Children Countries: France, Germany, Poland, Austria, etc.

Can it be set up in SAC so that in the filters show these relationships?

For example, in the filters, the end user chooses Europe and then sees the countries in Europe below.  Or chooses another region and sees those countries within that region.

Or are the filters listed separately?

Hope this is clear--thank you.