-- Create a view with 25 years to days
CREATE OR REPLACE VIEW time_dim AS
SELECT
currdate AS day_id,
INITCAP(RTRIM(TO_CHAR(currdate,'MONTH'))) ||' ' || TO_CHAR(currdate,'DD') || ', ' || RTRIM(TO_CHAR(currdate,'YYYY')) AS day,
initcap(TO_CHAR(currdate,'Mon') || '-' || TO_CHAR(currdate,'YY')) AS month,
'Q' || upper(TO_CHAR(currdate,'Q') || '-' || TO_CHAR(currdate,'YYYY')) AS quarter,
TO_CHAR(currdate,'YYYY') AS year
FROM
(
SELECT
level n,
-- Calendar starts at the day after this date.
TO_DATE('31/12/2000','DD/MM/YYYY') + numtodsinterval(level,'DAY') currdate
FROM
dual
-- Change for the number of days to be added to the table.
CONNECT BY
level <= 9131);Hierarchies typically contain multiple levels. Data representing hierarchy members in the MEMBER_NAME or MEMBER_CAPTON columns might or might not be suitable for sorting hiearchy members. Often there is the need to sort hierachy members using an alternative value or to sort hierarchy members as nested values within parent and ancestors, as in a pivot table or tree control.
The HIER_ORDER column, which is present in every hierarchy, can be used to sort hierarchy members in nested order within parents and ancestors, independany of MEMBER_NAME, MEMBER_CAPTION or other attributes
The prerequisite (setup) SQL of this tutorial creates a view with 25 years of time data at the day, month, quarter and year levels.
View the sample data.
SELECT * FROM time_dim;
Note that data does not sort according to the calendar when ordering by DAY, MONTH or QUARTER.
Order by DAY.
SELECT
day
, month
, quarter
, year
FROM
time_dim
ORDER BY
day;
Order by MONTH.
SELECT
DISTINCT
month
, quarter
, year
FROM
time_dim
ORDER BY
month;
Order by QUARTER.
SELECT
DISTINCT
quarter
, year
FROM
time_dim
ORDER BY
quarter;
Non-leaf columns can be sorted by DAY_ID, a date data type.
SELECT
month
, quarter
, year
, MAX(day_id)
FROM
time_dim
GROUP BY
month
, quarter
, year
ORDER BY
MAX(day_id);
SELECT
quarter
, year
, MAX(day_id)
FROM
time_dim
GROUP BY
quarter
, year
ORDER BY
MAX(day_id);Create an attribute dimension and hierarchy using the TIME_DIM view.
Note that all levels can ORDER BY day_id. The hierarchy will automatically ORDER BY MAX(day_id).
CREATE OR REPLACE ATTRIBUTE DIMENSION time_ad
USING time_dim
ATTRIBUTES (
day_id
, day
, month
, quarter
, year)
LEVEL year
KEY year
MEMBER NAME year
ORDER BY day_id
LEVEL quarter
KEY quarter
MEMBER NAME quarter
ORDER BY day_id
DETERMINES (year)
LEVEL month
KEY month
MEMBER NAME month
ORDER BY day_id
DETERMINES (quarter)
LEVEL day
KEY day_id
MEMBER NAME day
ORDER BY day_id
DETERMINES (day, month);
Create the TIME_HIER hierarchy.
CREATE OR REPLACE HIERARCHY time_hier
USING time_ad (
day CHILD OF
MONTH CHILD OF
QUARTER CHILD OF
YEAR);
View data in the hierarchy.
SELECT * FROM time_hier;Time hierarchies are typically sorted in calendar order. If multiple levels are selected, children or often positioned before or after parents and ancestors (as in a pivot table or tree control)
The DAY, MONTH, QUARTER, and YEAR columns were used as MEMBER NAME in each of the levels. Each level used DAY_ID in ORDER BY.
SELECT and ORDER BY DAY.
SELECT
member_name
, day
, month
, quarter
, year
FROM
time_hier
WHERE
level_name = 'DAY'
ORDER BY
day;
Most likely, this is not what a user would expect. They would expect days to be sorted in calendar order. This can be done by sorting by HIER_ORDER, which uses DAY_ID, a date data type.
SELECT
member_name
, day
, month
, quarter
, year
FROM
time_hier
WHERE
level_name = 'DAY'
ORDER BY
hier_order;
The same can be seen at the month, quarter, and year levels
Select month level data ORDER BY month.
SELECT
member_name
, day
, month
, quarter
, year
FROM
time_hier
WHERE
level_name = 'MONTH'
ORDER BY
month;
Select month level data ORDER BY hier_order.
SELECT
member_name
, day
, month
, quarter
, year
FROM
time_hier
WHERE
level_name = 'MONTH'
ORDER BY
hier_order;
Select month level data ORDER BY month.
SELECT
member_name
, day
, month
, quarter
, year
FROM
time_hier
WHERE
level_name = 'QUARTER'
ORDER BY
quarter;
Select month level data ORDER BY hier_order.
SELECT
member_name
, day
, month
, quarter
, year
FROM
time_hier
WHERE
level_name = 'QUARTER'
ORDER BY
hier_order;
The nested sorting can be seen by selecting multiple levels in a query.
SELECT
member_name
, day
, month
, quarter
, year
FROM
time_hier
WHERE
level_name IN ('MONTH','QUARTER','YEAR')
ORDER BY
hier_order;As seen in previous queries, HIER_ORDER can be used in the ORDER BY clause of a SELECT statement without including HIER_ORDER in the SELECT list.
How HIER_ORDER is calculated in the SQL differs depending on whether it is included in the SELECT list. This can have a significant impact on query performance, pending on the size of the hierarchy.
If HIER_ORDER is used in ORDER BY but not the SELECT list, HIER_ORDER is calculated after filters are applied. This provides the expected sorted behavior and the best query performance. For example.
SELECT
member_name
, day
, month
, quarter
, year
FROM
time_hier
WHERE
level_name IN ('MONTH','QUARTER','YEAR')
AND year = '2024'
ORDER BY
hier_order;
Best practice is to not include HIER_ORDER in the SELECT list.
If HIER_ORDER is included in the SELECT list, HIER_ORDER returns the absolute order within the hierarchy, This means the HIER_ORDER needs to be calculated for all hierarchy members, not just those members that are returned by the query
Note that in the following query, which is not filtered by year, HIER_ORDER begins at 1.
SELECT
hier_order
, member_name
, day
, month
, quarter
, year
FROM
time_hier
WHERE
level_name IN ('MONTH','QUARTER','YEAR')
ORDER BY
hier_order;
With the YEAR = '2024' filter, HIER_ORDER begins at 8792
SELECT
hier_order
, member_name
, day
, month
, quarter
, year
FROM
time_hier
WHERE
level_name IN ('MONTH','QUARTER','YEAR')
AND year = '2024'
ORDER BY
hier_order;
While the difference in query performance is probably not noticeable with this small hierarchy, in the context of querying the hierarchy directly rather than through an anaytic view, including HIER_ORDER in the SELECT list can cause a noticeable different in query performance in other contexts.