Creating a Column View with Hierarchy, using SQL Editor

[Former Member]

I am trying to create a column view with a parent-child hierarchy, using the SQL editor rather than the graphical editor.     I was given the following SQL from someone, and if I put my parent-child SELECT query in the "...etc" (below) it creates the view successfully.  However, the hierarchy data involves multiple parents (for the same node), and SELECTing from the view complains about this ("multiple parents not allowed for hierarchy").  I know there is a 'Multiple Parents' option on hierarchies, as the graphical editor includes a checkbox for it, but I cannot find any documentation for doing it via SQL. ie. what parameter is required?   In fact, I cannot find any documentation on creating column views with hierarchies using SQL.

CREATE COLUMN VIEW "MD_HIER"

  TYPE hierarchy WITH PARAMETERS (

  'hierarchyDefinitionType'='select',

  'hierarchyDefinition'='{"NODESTYLE":"nameOnly","NODETYPE":"integer","RUNTIMEOBJECTTYPE":"tree",

  "SOURCEQUERY":"select ... etc",

  "SOURCEQUERYSCHEMA":"MYSCHEMA","SOURCETYPE":"RECURSIVE","VIEWLEVELS":10,"rootNode":""}');

[Former Member · Accepted solution]

Might I ask why you're wanting to do this in the SQL editor as opposed to graphically?

In any case, you might find an answer by doing one of two things:

1) When you activate views, you can double click on the Job Log for details. You should find a DDL specific to the hierarchy view by combing through that.

2) Alternately, enable SQL trace to view the DDL.

Nonetheless, I'd recommend doing this graphically for a handful of reasons - maintainability, reusability (in repo as opposed to catalog object) and others.

[Former Member · Accepted solution]

many thanks Jody,

I explored the Job Log, and as you said, buried within it was the DDL (below), which showed the parameter I was looking for:  "multiParent":true.

(btw. I'm currently using DDL rather than the graphical editor because I am keeping our scripts (for table creation, views, procedure creation, etc) elsewhere, not within Hana repository. This is while we are experimenting with Hana on cloudshare.   But I'll look at doing more with the graphical editor.)

Create hierarchy view: CREATE COLUMN VIEW "_SYS_BIC"."resdet/MD_HIER/hier/MD_HIER" TYPE hierarchy WITH PARAMETERS ( 'hierarchyDefinitionType' = 'select','hierarchyDefinition' = '{ "runtimeObjectType":"blob","nodeType":"string","multiParent":true,"sourceType":"recursive","sourceQuery":" SELECT \"PARENT_MEASUREDEF_UID\" AS \"PRED\",\"CHILD_MEASUREDEF_UID\" AS \"SUCC\" FROM \"_SYS_BIC\".\"resdet/MD_HIER\" ORDER BY \"SUCC\"" }') timestamp: 2014-01-03,02:42:23.270

David

[Former Member · Accepted solution]

Cool, glad you got it worked out

[zpatsarun · Accepted solution]

Awesome, thanks for informations.