Home > Querying Dimensional Objects > Creating Hierarchical Queries
![]() Previous |
![]() Next |
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.
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
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
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
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