A hierarchy that is defined in a calculation view can also be accessed directly by SQL statements. 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(days) with a where hier_node = ‘service’ clause, could 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 defined in a dimension calculation view, which then must be consumed in the star join node of a cube calculation view. This is because only shared hierarchies can be read by SQL.
You must allow the shared hierarchy to be exposed to SQL by setting a parameter in the Properties tab of the semantics node.
In the SQL Access section of the hierarchy tab of the semantic node, you should find 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 as if it were a regular column in the data source.