MichaelB.
7/29/2018 - 6:48 PM

Read a hierarchy structure with SQL

Need to be a star join with dimensions with hierarchies (Shared Hierarchy from a Shared Dimension) Need to enable Hierarchies for SQL access in Semantics of CVCS Can only access Shared Hierarchies Node Column in the SQL Access section in the Hierarchies tab in Semantics is used in SQL statement Hierarchy Expression parameter is not used at all Can use this to roll up quantities to a certain hierarchy level. Displays all hierachy levels above as well. Node column contains path. Root node is also displayed if not restricted by WHERE column.

sql
content_copyfile_download
SELECT "Node_Column", SUM/AGG_EXPRESSION("column")
FROM table
[WHERE "Node_Column" = 'hierarchy_value']
GROUP BY "Node_Column";