Access or expose Hierarchy Node column of HANA Calculation View

[fcolombo]

Dear experts,

I've created a HANA graphical calculation view with a star-join schema and a shared hierarchy. Basically my modeling is very similar to the example described in this blog:

https://blogs.sap.com/2017/01/26/advance-hierarchy-modeling-with-sap-hana/

Everything works fine and, as explained also in the HANA Modeling Guide, I can use the generated Hierarchy Node column into SQL statements such as:

select "Category_ID", sum("Revenue") FROM "_SYS_BIC"."my_view"
where "HierarchyNodeColumn" = 'ID_1'
group by "Category_ID";

My final use-case however is a bit different and I was wondering if it is possible to access the "HierarchyNodeColumn" in the same or in another Calculation View, associating it to an input parameter or variable. This would have the same effect of the "where" statement, but without having explicit SQL statements.

Many thanks in advance for any input,

Fabio

[richiew03]

Hi Fabio,

Thanks for your reply. I am also using a workaround, instead of using the hierarchy variable, I am using an input parameter with a hierarchy selection. The IP then holds a value which can represent a value from any level of the hierarchy. I apply this filter to all levels within the hierarchy using an OR statement in the projection node. This only works when all hierarchy levels have been flattened out so they can be restricted on.

Using Deodutt's doesn't work for me - if I select a hierarchy node which isn't at the bottom, it doesn't return all the child nodes.

[deodutt_dwivedi]

Hi,

Kindly select the child node in the attribute, you will be able to see the hierarchy.

Regards,

Deo

[richiew03]

Hi Fabio - where did you get to with exposing the SQL column to the view to create a variable? any joy?

[fcolombo]

Hi Richard, sorry for the late answer: I did not get any notification about your comment.
Well, after asking the question on here I kept investigating a bit on the topic and finally, while following an online course on HANA Modelling, I came across the following statement: "this column is only accessible via SQL. It is not exposed to the graphical information models that consume the view".

So I guess that at the moment what I was trying to do is not supported.

A "workaround" that I found was to "pack" the above SQL statement into a HANA table function and then use the table function as input for another Calculation View.

[former_member210482]

Hello Fabio,

You can create a variable in the same HANA view. When you choose the field on which the variable has to be applied in the attribute section, calculation view editor will automatically recognise the heirarchy and enable the heirarchy option. This way you create a variable on top of heirarchy. On data preview, you will get a popup to choose the heirarchy value.

Note: Dont forget to activate view first, close and reopen in order to see the heirarchy option enabled in variable editor.

Cheers,

Safiyu

[fcolombo]

Hi Safiyu,

thanks a lot for the answer.
The problem is that in the attribute section, when I have to choose the field on which to apply the variable, the "HierarchyNodeColumn" field is not visible and can not be selected. Only the other fields of the calculation view are visible.

However this is exactly the field on which I want to apply my selection, so that at the end I have a result identical to the SQL statement:

select "Category_ID", sum("Revenue") FROM "_SYS_BIC"."my_view"
where "HierarchyNodeColumn" = 'my_test_variable'
group by "Category_ID"<br>

Cheers,

Fabio