To consume HANA Hierarchy of Calculated View

[aj_shaik]

Hi,

Can some body help me to what I am currently trying to do is possible in HANA ?

My end requirement is something similar to the post

http://scn.sap.com/docs/DOC-40409

http://scn.sap.com/message/13986059#13986059

http://scn.sap.com/thread/3417970

Followed some of existing post; but could not solve the error which I am currently getting.

http://scn.sap.com/message/13797032

http://scn.sap.com/thread/3317284

Below is what I tried to do explained in a best way I can.

I would like to know If I can use the HANA Hierarchy inside the Calculated View as below  ?

Calculated View 2: (Top) CALL_H_TEST2

/********* Begin Procedure Script ************/

BEGIN

var_out = 

SELECT

  QUERY_NODE,

  RESULT_NODE,

  LEVEL,

  LEVEL_NAME,

  ORDINAL,

  IS_LEAF,

  QUERY_NODE_NAME,

  RESULT_NODE_NAME,

  PATH,

  PARENTS,

  CHILDREN

  FROM

  "_SYS_BIC"."CII_AS/CALL_H_TEST1/TEST_H1/hier/TEST_H1"

  ( PLACEHOLDER."$$LOCALIDCONTEXTITEM$$" => :LOCALIDCONTEXTITEM,

    PLACEHOLDER."$$LOCALIDAUTHORITY$$" => :LOCALIDAUTHORITY

  );

END /********* End Procedure Script ************/

Calculated View 1: (Lower) CALL_H_TEST1

The Hierarchy "_SYS_BIC"."CII_AS/CALL_H_TEST1/TEST_H1/hier/TEST_H1" is created over Calculated View 1

which generates Parent Child hierarchical Table type data output.

When I view the Calculated View 2: providing default values for :LOCALIDCONTEXTITEM and :LOCALIDAUTHORITY

The Error is as below

Same is the case when I directly execute the sql script by providing values

SELECT

  *

  FROM

"_SYS_BIC"."CII_AS/CALL_H_TEST1/TEST_H1/hier/TEST_H1"

  ( PLACEHOLDER."$$LOCALIDCONTEXTITEM$$" => 80000,

    PLACEHOLDER."$$LOCALIDAUTHORITY$$" => 90000

  );

ERROR:

Could not execute 'SELECT * FROM "_SYS_BIC"."CII_AS/CALL_H_TEST1/TEST_H1/hier/TEST_H1" ( ...' in 72 ms 188 µs .

SAP DBTech JDBC: [2048]: column store error: search table error:  [6929] Hierarchy source data: Format error;PopHierarchyCreateSelect pop1,SQL: column store error: search table error:  [34092] search on calculation model requires parameters;Required variable $$LOCALIDCONTEXTITEM$$ is not set. Dimension [TEST_H1] hierarchy [TEST_H1].[TEST_H1]

Can somebody clarify to me if its possible to use HANA Hierarchy created over Calculated View with PLACEHOLDERS and use then inside Calculated View  ?

Thank You, I appreciate 

Shaik

Message was edited by: Aj Shaik

[Former Member]

Why are you using the PLACEHOLDER syntax? I'd expect that the following would work.

My advice when creating calc views is always to create a SQLScript procedure that works, first, then to port that code into a Calc View. It makes debugging much easier.

John

/********* Begin Procedure Script ************/

BEGIN

var_out =

SELECT

  QUERY_NODE,

  RESULT_NODE,

  LEVEL,

  LEVEL_NAME,

  ORDINAL,

  IS_LEAF,

  QUERY_NODE_NAME,

  RESULT_NODE_NAME,

  PATH,

  PARENTS,

  CHILDREN

  FROM

  "_SYS_BIC"."AS/CALL_H_TEST1/AS_HEI_TEST1/hier/AS_HEI_TEST1" WHERE CONTEXTITEM = :contextitem and AUTHORITY = :authority;

END /********* End Procedure Script ************/

[aj_shaik]

Hi John,

Sorry, actually I edited my question for better understanding of what I need dont know why the changes are not reflected.

Let me show what I tried to do.

I have:

1) CALL_H_TEST1 calculated view which generates table suitable for creating hierarchy by calling a stored procedure TEST_H2 which consumes parameters CONTEXTITEM and AUTHORITY

CALL "_SYS_BIC"."AS/TEST_H2" (:CONTEXTITEM, :AUTHORITY, HIERARHCYTBL) WITH OVERVIEW;

On CALL_H_TEST1  calculated view i have created HANA Heirarchy.

as shown below

As you suggested I tried to execute the script which is present in Calculated View 2 separately

SELECT

  *

  FROM

  "_SYS_BIC"."AS/CALL_H_TEST1/TEST_H1/hier/TEST_H1"

  ( PLACEHOLDER."$$CONTEXTITEM$$" => 80000,

    PLACEHOLDER."$$AUTHORITY$$" => 90000

  );

Following error:

Could not execute 'SELECT * FROM "_SYS_BIC"."AS/CALL_H_TEST1/TEST_H1/hier/TEST_H1" ( ...' in 89 ms 573 µs .

SAP DBTech JDBC: [2048]: column store error: search table error:  [6929] Hierarchy source data: Format error;PopHierarchyCreateSelect pop1,SQL: column store error: search table error:  [34092] search on calculation model requires parameters;Required variable $$CONTEXTITEM$$ is not set. Dimension [TEST_H1] hierarchy [TEST_H1].[TEST_H1]

Wondering if the HANA Hierarchy can be used along with internal PARAMETERS ?

If it is can you please provide the syntax ?

Thank You,

shaik

[Former Member]

Yikes, I don't know. This is the syntax, if it is possible:

SELECT * FROM ProcView WITH PARAMETERS ('placeholder' = ('$$id$$', '5')); 

SELECT

  *

  FROM

  "_SYS_BIC"."AS/CALL_H_TEST1/TEST_H1/hier/TEST_H1"

  WITH PARAMETERS ( 'PLACEHOLDER' = ('$$CONTEXTITEM$$','80000'));

John

[aj_shaik]

No Luck same Error

SELECT

*

FROM

"_SYS_BIC"."AS/CALL_H_TEST1/TEST_H1/hier/TEST_H1"

WITH PARAMETERS

( 'PLACEHOLDER' = ('$$CONTEXTITEM$$', '80000'),

  'PLACEHOLDER' = ('$$AUTHORITY$$', '90000')

)

Shaik

[Former Member]

Why don't you just select the hierarchy out of the calc view, rather than specifying the hierarchy node?

John

[aj_shaik]

The thing is hierarchy node generates information of

  QUERY_NODE,

  RESULT_NODE,

  LEVEL,

  LEVEL_NAME,

  ORDINAL,

  IS_LEAF,

  QUERY_NODE_NAME,

  RESULT_NODE_NAME,

  PATH,

  PARENTS,

  CHILDREN

over the PRED/SUCC hierachy table.

If I select on Calculated View I can only get the information of PRED/SUCC. But what i need is information which is generated by HANA Hierarchy node.

I currently dont know the syntax of how to get Hierarchy node information by calling Calculated View directly .

Please let me know if you have any idea.

Thanks for following up, I appreciate

Regards,

Shaik

[Former Member]

Hi Shaik,

While this won't help you much for now, I did want to mention that lots of hierarchy-specific SQL extensions *should* become available in SP7 (can't promise) which should give access to useful hierarchy functions as well as the capacity to pass input parameters. (As for the specific syntax for passing parameters, unfortunately I don't recall at the moment - but it should be documented in SP7. Fingers crossed.)

Cheers,

Jody

[aj_shaik]

Hi Jody,

Thank You,

Regards,

Shaik