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 may need to return the total number of absence days for the Service line of business that can only be found by rolling up many departments and sub-departments in a company hierarchy that is modeled in a calculation view.
A simple SQL statement such as : SELECT sum(no_show_days) WHERE hier_node = ‘Service’ clause, would provide this value.
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.
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.
Reading hierarchies using SQL is supported for both level and also parent-child types.
Here you will see, for each shared hierarchy, the node column name that is proposed. This name can be overwritten so that a more meaningful name can be supplied.
Aggregating Values in a Hierarchy Via SQL
The figure, Aggregating Values in a Hierarchy Via SQL, shows 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.