Hierarchical Queries in Oracle – Bullet proofing your dimension builds in Essbase

In this blog, I would be talking about hierarchical queries in Oracle. Most of us, at some point or the other, might have used Oracle as a data source for building metadata. Now the most famous data extract format used for building dimension build rules file is always the parent-child format. Now suppose that I have a table that has metadata for thousands of rows. As a sanity check when building the rules file extract, I would want to ensure that my parent dimension member is built before any of its children dimension members are built. So the question now is how to achieve that…

I have created a table in Oracle called as “Period_metadata” whose structure is as shown in the below snapshot.

For sake of simplicity, it has three self-explanatory columns, Parent, Child and Alias.

Now I load the monthly values as shown in the below snapshot.

 

Once the monthly parent child references are inserted, I insert the quarterly parent child references as shown in the below snapshot.

The contents of the table is as shown in the below snapshot after the data inserts. As you can see, the monthly values come first and this is followed by the quarterly values.

I now want to write a query in such a way that my quarter values are selected first and this is followed by the monthly values… For doing this, I would be using the hierarchical query in Oracle.

Hierarchical queries are used for getting data in a hierarchical order (no surprises there…) Its output is similar to a tree structure and can be used as such…

The structure of the query is as shown in the below snapshot.

START with specifies the root node with which the query should start as a reference. In this case it is “Period”.

CONNECT BY specifies the relation between the parent rows and the child rows of the hierarchy.

PRIOR is a unary operator that evaluates immediately following expression for the parent row of the current row in a hierarchical query…

The query can be read as follows:-

Select those rows where the value of the parent first comes in the child column and start the selection from rows where parent=Period

The output of the query is as shown in the below snapshots.

The extract in a parent child format is as shown in the below snapshot.

Now, let us mess around a little with the hierarchical query…

If instead of using CHILD=PARENT, if I use the query the other way round as shown in the below example.

The next query shows the use of the Level pseudocolumn. For each row returned in a hierarchical query, level will return 1 for ROOT row, 2 for child of root and so on.

The concept of level is not to be confused with the Essbase concept of Level. In hierarchical query, Level is logically equivalent to the “Generation concept” in Essbase.

Output of the query with level is as shown in the below snapshot.

A cleaner output, where the data is ordered by level is as shown in the below snapshots.