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.
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 using SQL
The figure, Aggregating Values in a Hierarchy using SQL, 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.