Skip Headers
Previous
Previous
 
Next
Next

Creating Hierarchical Queries

Drilling is an important capability in business analysis. In a dashboard or an application, users click a dimension key to change the selection of data. Decision makers frequently want to drill down to see the contributors to a data value, or drill up to see how a particular data value contributes to the whole. For example, the Boston regional sales manager might start at total Boston sales, drill down to see the contributions of each sales representative, then drill up to see how the Boston region contributes to the New England sales total.

The hierarchy views include a PARENT column that identifies the parent of every dimension key. This column encapsulates all of the hierarchical information of the dimension: If you know the parent of every key, then you can derive the ancestors, the children, and the descendants.

For level-based hierarchies, the LEVEL_NAME column supplements this information by providing a convenient way to identify all the keys at the same depth in the hierarchy, from the top to the base. For value-based hierarchies, the PARENT column provides all the information about the hierarchy.

Drilling Down to Children

You can use the PARENT column of a hierarchy view to select only the children of a particular value. The following WHERE clause selects the children of calendar year 2005.

/* Select children of calendar year 2005 */
WHERE  t.parent = 'CY2005'
   AND p.dim_key = 'TOTAL'
   AND cu.dim_key = 'TOTAL'
   AND ch.dim_key = 'TOTAL'

The query drills down from Year to Quarter. The four quarters Q1-05 to Q4-05 are the children of year CY2005 in the Calendar hierarchy.

TIME          SALES
-------- ----------
Q1.05      31381338
Q2.05      37642741
Q3.05      32617249
Q4.05      35345244

Drilling Up to Parents

The PARENT column of a hierarchy view identifies the parent of each dimension key. Columns of level keys identify the full heritage. The following WHERE clause selects the parent of a Time key based on its LONG_DESCRIPTION attribute.

/* Select the parent of a Time key*/
WHERE t.dim_key = 
   (SELECT DISTINCT parent
      FROM time_calendar_view
      WHERE long_description='JAN-05')
   AND p.dim_key= 'TOTAL'
   AND cu.dim_key = 'TOTAL'
   AND ch.dim_key = 'TOTAL'

The query drills up from Month to Quarter. The parent of month JAN-05 is the quarter Q1-05 in the Calendar hierarchy.

TIME          SALES
-------- ----------
Q1.05      31381338

Drilling Down to Descendants

The following WHERE clause selects the descendants of calendar year 2005 by selecting the rows with a LEVEL_NAME of MONTH and a CALENDAR_YEAR of CY2005.

/* Select Time level and ancestor */
WHERE t.level_name = 'MONTH'
    AND t.calendar_year = 'CY2005'
    AND p.dim_key = 'TOTAL'
    AND cu.dim_key = 'TOTAL'
    AND ch.dim_key = 'TOTAL'

The query drills down two levels, from year to quarter to month. The 12 months Jan-05 to Dec-05 are the descendants of year 2005 in the Calendar hierarchy.

TIME          SALES
-------- ----------
JAN-05     12093518
FEB-05     10103162
MAR-05      9184658
APR-05      9185964
MAY-05     11640216
JUN-05     16816561
JUL-05     11110903
AUG-05      9475807
SEP-05     12030538
OCT-05     11135032
NOV-05     11067754
DEC-05     13142459

Drilling Up to Ancestors

The hierarchy views provide the full ancestry of each dimension key, as shown in "Displaying the Contents of a Hierarchy View". The following WHERE clause uses the CALENDAR_YEAR level key column to identify the ancestor of a MONTH dimension key.

/* Select the ancestor of a Time key based on its Long Description attribute */
WHERE t.dim_key =
   (SELECT calendar_year
      FROM time_calendar_view
      WHERE long_description = 'JAN-05')
   AND p.dim_key = 'TOTAL'
   AND cu.dim_key = 'TOTAL'
   AND ch.dim_key = 'TOTAL'

The query drills up two levels from month to quarter to year. The ancestor of month Jan-05 is the year 2005 in the Calendar hierarchy.

TIME          SALES
-------- ----------
2005      136986572