A hierarchy that is defined in a calculation view can also be accessed directly by SQL statements. The benefit of this is to provide access to the node values at any level of the hierarchy in your SQL code. You read the nodes as if they were columns in a data source.
For example, you want to calculate the total number of absence days for the Service line of business. The service line of business sits within the organization hierarchy and has many levels below it that represent all departments. If you roll up all the lower levels we can calculate the total for the service line of business.
A simple SQL statement such as : SELECT sum (absence_days) WHERE hier_node = ‘Service’ clause, would provide this value. We do not need to read all the lower levels separately to reach the total. The lower levels are automatically rolled up.
But before you can write the SQL query, you need to work on the following setup:
The hierarchy you wish to query must be created in a dimension calculation view. The dimension calculation view is then consumed in the star join node of a cube with star join calculation view. This is because only shared hierarchies can be read by SQL.
You must then enable the shared hierarchy to be exposed to SQL by setting a parameter in the Properties tab of the semantics node of the cube with star join calculation view.
.png "Screenshot of a Calculation View properties. The Enable Hierarchies for SQL access property is selected.")
In the SQL Access section of the Hierarchy tab of the semantic node, you should locate the name that is given to the node column, because you will need to refer to this in the SQL statement.
This name can be overwritten so that a more meaningful name can be supplied.
.png "Screenshot of the Hierarchies tab. In the SQL Access section you can find the Node Column name. The default name for H_GEO hierarchy is H_GEOnode.")
Reading hierarchies using SQL is supported for both level and also parent-child types.
Note
The Hierarchy Expression Parameter is not used in the current release of SAP HANA Cloud. Ignore this setting.Aggregating Values in a Hierarchy using SQL
The following figure shows you how the SQL is written and how the result would appear. Simply refer to the generated hierarchy node column name in the SELECT statement as if it were a regular column in the data source.
The node column can be used in the GROUP BY clause. The result then shows the aggregated measure for each node of the hierarchy.
