Skip to Content
Author's profile photo Former Member

Unassigned Member Null Handling Hierarchy in Calculation View

Not Assigned Members enabled hierarchies with SAP HANA Calculation Views


Often occurs that there will be entries in fact table for which corresponding master data doesn’t exist, for instance sales records for unregistered customers. In such cases when the two tables joined together with referential integrity, sales data for unregistered customers will be lost. Using the feature Unassigned Member Null Handling in hierarchy, we can see the whole data in a hierarchical fashion without any loss of data wherein the not assigned member/unregistered customer details will be grouped under a node.

This can be achieved by using SAP HANA Modeler studio and MDX client.

I am going to use below data tables PC_EMPLOYEE and PC_SALES. PC_EMPLOYEE has records in a parent child relationship. And there are values in PC_SALES table for which there is no corresponding master data exists in PC_EMPLOYEE table ex: FOO.

Now let us see it in a hierarchy with grouping all not assigned members together.


Create dimensional calculation view with PC_EMPLOYEE table as shown below


Create a parent child hierarchy with PERSON as child and MANAGER as parent as shown below

If your data is in levels, level hierarchy can be created. The feature is applicable for both level and parent child hierarchies.


Enable Not Assigned Members by giving a name for grouping all unassigned members as highlighted below

Step4: Once dimensional calculation view is activated, it generates a columnview[ “_SYS_BIC”.”shireesha/CAL_DIM_PC_EMP/hier/NotAssigned_PC”] for hierarchy under _SYS_BIC where not assigned members will be ignored as below. (Person FOO is ignored in the hierarchy)


To achieve the Unassigned member handling dimensional calculation view should be consumed in a star calculation view


Consume above dimensional calculation view in a star calculation view by using PC_SALES as fact table



Once star calculation view is activated there will be two columnviews generated for hierarchy under _SYS_BIC columnviews folder, one with hierarchy name and another with ending $NA.

The second column view contains only the details of Not assigned members  as below

The first columnview consists the whole data along with the not assigned members

This is how not assigned members can be handled. To see the output in hierarchy the star calculation view should be consumed in any HANA client tools as MDX, Analysis Office or Lumira, etc

Null Values also can be handled by enabling Null Processing and to denote null values specially in the hierarchy can be represented by mentioning under Null Member Settings.

Hope my blog helped to learn about Unassigned Member and Null handling in parent child hierarchy


Assigned Tags

      1 Comment
      You must be Logged on to comment or reply to a post.
      Author's profile photo Mel Waldner
      Mel Waldner


      Great instructions, thank you for preparing them.

      Can you tell me what Ver & SP of HANA you were using for these steps?  I tried this on our 1.0 SP9 HANA system and the 2nd view for the unassigned values was not created, and I am wondering if this is a bug with the version we are on.

      Thanks,  Mel