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!0000Capture.PNG
  2. Create projection based on MCSHIERB, set filter for Hierarchy Name: “ZMTP”                          0002Capture.PNGdata looks like: 0000Capture.PNG
  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.0004Capture.pngdata looks like: 0000Capture.PNG
  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)0000Capture.PNG
  6. The view should look like this0001Capture.PNG
  7. In the semantics we can create the hierarchy easily0000Capture.PNG
  8. Activate your model and preview it, rawdate looks like this0000Capture.PNG
  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.0000Capture.PNG
  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.  0000Capture.PNG
  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.0000Capture.PNG
To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

  1. karthik kumar

    Hi Koen Hesters,

    We are facing issues with percentage calculations in Analysis for Office.\

    Pre-requisites :-

    HANA SP9

    AFO 2.0 32 bit

    firstly we created level hierarchy in the backend,data and hierarchy where working fine but when it comes to subtotals it is throwing an error in AFO withing hierarchy.

    We moved to Parent-Child hierarchy with SAP suggestion but to no avail we are unable to the + mark but also the hierarchy level 1 and Level 2 are clubbed and showing in the report.

    as shown below seperated by “/” sign.


    We are using calc views for the following.

    Thanks and Regards

    Karthik Kumar C V


Leave a Reply