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.

Step1: Create dimensional calculation view with PC_EMPLOYEE table as shown below

Step2: 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.

Step3: 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

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

Step6: 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.