Hana Hierarchy based on MCSHIERB

[kohesco]

Hi,

The great advantange is when you create this hierarchy on MCSHIERB, is that avery hierarchy can be used defined in that table.  You just can copy-paste this dimension-calculation view and change the filters and within a few minutes you created a new hierarchy in Hana.

MCSHIERB is a parent-child hierarchy, so in Hana we will create a dimension with a parent-child hierarchy.

In my example the hierarchy is called"MTP Hierarchy" (ZMTP), based on Materialgroup.

1. Create a calculation view with date category: DIMENSION, "DIM_MAT_GRP"  If you set the execute in SQL Engine, you will improve perfomancy, but note that you have to set your cardinalities in your view correctly!
2. Create projection based on MCSHIERB, set filter for Hierarchy Name: "ZMTP"                          data looks like:
3. Create another Projection, again based on MCSHIERB.  Exactly the same as the first one.  Why?  The ECC-hierarchy table is based on ID's not on the Material groups we want to combine.  ID in the table is child and PARENT is parent of the hierarchy.  NAME holds the materialgroups, because we want to have the name of the parent and the child we need to add the name via the join.  You will grasp it during the process.
4. Create a left outer join to map the Parent of the first projection to the child (ie ID) of the second, NAME is the materialgroup of the child and NAME_1 (from projection 2) is the name of the parent.data looks like:
5. Link the join to the projection as last step under the semantics.  In this step we have to create a calculated column.  The Hana hierarchy in parent-child mode has to have a rootnode equally to space, ''.  So create a "new parent"-column. I called it: MATKL_PARENT.  I created a MATKL_CHILD as well but this was not necessary (fomula is just: NAME, so it figures You just can rename NAME)
6. The view should look like this
7. In the semantics we can create the hierarchy easily
8. Activate your model and preview it, rawdate looks like this
9. Now we want to have this dimension in our Starschema, so let's do this.  Add the dimension to the star join and map the MATKL that comes from your "transactional/Fact-table" projection with the MATKL_CHILD.
10. In the semantics of our CUBE (starschema) the Hierarchy will be autmatically be generated.  So let's check out the result of our view in Analysis for Office. 
11. Some remarks, I played with the advanced features of the hierarchy, but with Analysis for Office as reporting tool, the outcome was not as set in Hana.  So Sap will have some work on that.