SAP HANA: Parent-Child Hierarchy

Create Parent-Child Hierarchies

In parent-child hierarchies, you use a parent attribute that determines the relationship among the view attributes. Parent-child hierarchies have elements of the same type and do not contain named levels.

Context

Parent-child hierarchies are value-based hierarchies, and you create a parent-child hierarchy from a single parent attribute. You can also define multiple parent-child pairs to support the compound node IDs.

For example, you can create a compound parent-child hierarchy that uniquely identifies cost centers with the following two parent-child pairs:

·         CostCenter and ParentCostCenter and

·         ControllingArea and ParentControllingArea,

A parent-child hierarchy is always based on two table columns and these columns define the hierarchical relationships amongst its elements.

Others examples of parent-child hierarchies are bill of materials hierarchy (parent and child) or employee master (employee and manager) hierarchy.

Example:

            Here I used two tables named as PC_HIER_EMP_DETAILS and PC_HIER_MANAGER to create a parent-child hierarchy.

Procedure:

Step 1: Create an Attribute view for PC_HIER_MANAGER by right click the Package and select New and choose Attribute View.

Step 2: And Provide Name and Label Details which are required and Click Finish.

Step 3: Drag and Drop the required tables and select required field.

Step 4: Click semantics and select Hierarchy tab and select Create Icon. A pop-up will appear and provide details as shown as below:

                        Hierarchy type : Parent-Child Hierarchy

                        Child Node     : Parent_Id

                        Parent Node    : Reporting_To

Step 5: Click OK. And validate and Activate the view.

Step 6: Then create an Analytic view by right click the Package and select New and choose Attribute View.

Step 7: And Provide Name and Label Details which are required and Click Finish.

Step 8: Drag and Drop the required tables and select required field. Here I used the above-created attribute view and one fact table named as PC_HIER_EMP_DETAILS.

Drag a Projection Node: Drag and Drop PC_HIER_EMP_DETAILS table and select required fields.

Star Join Node:

Step 9: And make a link between the Attribute view and the fact table in a Star Join based on their relationship. In a Semantic, hierarchy tab we can see the Hierarchy which we created in an Attribute view.

Step 10: Then validate and Activate the view.

In an Excel:

Step 1: Open Excel and Click “Data” tab and select “From Other Sources” and Choose “From Data Connection Wizard”.

Step 2: A Pop-up will appear. And choose “Other/Advanced” and Click “Next”.

Step 3: A Pop-up will appear “Data Link properties” select “SAP HANA MDX Provider” and Click Ok. A Pop-up window will appear as shown as below and provide required details. And click “Ok”.

Step 4: A pop-up window will appear from this select your view which contains hierarchy. Here I choose “AN_PC_HIER_EMP_MANAGER”. And click “Next”.

Step 5: Click “Finish”.

Step 6: Another “Import Data” pop-up will appear to select how we want to view our data from this select “Pivot table Report” and click “Ok”.

Result:

From the Pivot table Fields: select the field based on your requirement:

Here I choose following fields:

• PC_HIER_STRUCTURE
• First_Name
• Salary

SAP HANA: Level Hierarchy

What is Hierarchy?

Hierarchies help business to analyze their data in a tree structure through different levels/layers with drill-down capability. Each hierarchy comprises a set of levels having many-to-one relationships between each other and collectively these levels make up the hierarchical structure.

For example, a time hierarchy comprises of levels such as Fiscal Year, Fiscal Quarter, Fiscal Month, and so on.

We can create two types of hierarchies in SAP HANA, they are

1.      Level Hierarchy

2.      Parent-Child Hierarchy

Level Hierarchy:

In level hierarchies, each level represents a position in the hierarchy. For example, a time dimension can have a hierarchy that represents data at the month, quarter, and year levels.

Context

Level hierarchies consist of one or more levels of aggregation. Attributes roll up to the next higher level in a many-to-one relationship and members at this higher level roll up into the next higher level, and so on until they reach the highest level.

A hierarchy typically comprises of several levels, and you can include a single level in more than one hierarchy. A level hierarchy is rigid in nature, and you can access the root and child node in a defined order only.

Use Case:

Customer wants to analyze the sales revenue by customer country, state, and city. Now we will create a level hierarchy in our attribute view and access that using ‘MS Excel’ to analyze the sales revenue.

Example:

            Here I’m going to analyze the sales amount by Country.

To create an Attribute View:

Step 1: As we already know how to create attribute view. Simply Right Click your package and select Attribute View. A pop-up window will appear and provide the details and click ok. Here I named it as “AT_CUSTOMER_GEOGRAPHY”

Step 2: Drag and Drop the required tables in a “Data Foundation” and make the link between them based on a relationship. Here, I used two tables “Dim_Customer” and “Dim_Geograpghy”. And join it by using “Geography_Key” field.

Step 3: And now we create Level Hierarchy based on Geography location like country, state, city. To Create a Hierarchy, Click Semantics, from the Details pane select Hierarchies. A Pop-up window will appear. And provide details as Shown as Below: and click “OK”

Step 4: Then Validate and Activate the view.

Step 5: And create another Attribute view for Products. Do the same thing which we have done the above. Here I used “Product” table alone.

To Create an Analytic View:

Step 1: Simply Right Click your package and select Analytic View. A pop-up window will appear and provide the details and click ok. Here I named it as “AN_ADW_FACT_INTERNET_SALES”.

Step 2: Drag and Drop the required tables in a “Data Foundation” and make the link between them based on a relationship. Here, I used two Attribute views which we are created above.

Step 3: Drag and Drop the Fact table which contains measures in another Data Foundation. Here, I used “ADW_FACT_INTERNET_SALES”. Then join this to the attributes based on the relationship.

Step 4: Then Validate and Activate the view.

Excel:

Step 1: Open Excel and Click “Data” tab and select “From Other Sources” and Choose “From Data Connection Wizard”. 

Step 2: A Pop-up will appear. And choose “Other/Advanced” and Click “Next”.

Step 3: A Pop-up will appear “Data Link properties” select “SAP HANA MDX Provider” and Click Ok. A Pop-up window will appear as shown as below and provide required details. And click “ok”.

Step 4: A pop-up window will appear from this select your view which contains hierarchy. Here I choose “AN_ADW_FACT_INTERNET_SALES”. And click “Next”.

Step 5: Click “Finish”.

Step 6: Another “Import Data” pop-up will appear to select how we want to view our data from this select “Pivot table Report” and click “Ok”.

Result:

From the Pivot table Fields: select the field based on your requirement:

Here I choose following fields:

1.      HI_CUS_GEO

2.      OrderQuanity

3.      SalesAmount

Thak you...

Share your Comments...