Creating SHARE_OF (Hierarchical Ratio) Calculations in Analytic Views

View All Tutorials Login to Run Tutorial

Tutorial Creating SHARE_OF (Hierarchical Ratio) Calculations in Analytic Views
Description SHARE_OF expressions calculate the ratio of a hierarchy value to the value of a parent or an ancestor. For example, the share (ratio) of sales for a store within a sales district or the share of sales for a month within a quarter or year.
Tags analytic view, SHARE_OF, ratio, QUALIFY
Area SQL General
Contributor William Endress (Oracle)
Created Wednesday January 04, 2017
Modules 7

Prerequisite SQL

CREATE OR REPLACE ATTRIBUTE DIMENSION time_attr_dim
USING av.time_dim
ATTRIBUTES
 (year_id
   CLASSIFICATION caption VALUE 'YEAR_ID'
   CLASSIFICATION description VALUE 'YEAR ID',
  year_name
    CLASSIFICATION caption VALUE 'YEAR_NAME'
    CLASSIFICATION description VALUE 'Year',
  year_end_date
    CLASSIFICATION caption VALUE 'YEAR_END_DATE'
    CLASSIFICATION description VALUE 'Year End Date',
  quarter_id
    CLASSIFICATION caption VALUE 'QUARTER_ID'
    CLASSIFICATION description VALUE 'QUARTER ID',
  quarter_name
    CLASSIFICATION caption VALUE 'QUARTER_NAME'
    CLASSIFICATION description VALUE 'Quarter',
  quarter_end_date
    CLASSIFICATION caption VALUE 'QUARTER_END_DATE'
    CLASSIFICATION description VALUE 'Quarter End Date',
  month_id
    CLASSIFICATION caption VALUE 'MONTH_ID'
    CLASSIFICATION description VALUE 'MONTH ID',
  month_name
    CLASSIFICATION caption VALUE 'MONTH_NAME'
    CLASSIFICATION description VALUE 'Month',
  month_long_name
    CLASSIFICATION caption VALUE 'MONTH_LONG_NAME'
    CLASSIFICATION description VALUE 'Month Long Name',
  month_end_date
    CLASSIFICATION caption VALUE 'MONTH_END_DATE'
    CLASSIFICATION description VALUE 'Month End Date')
LEVEL month
  CLASSIFICATION caption VALUE 'MONTH'
  CLASSIFICATION description VALUE 'Month'
  KEY month_id
  MEMBER NAME month_name
  MEMBER CAPTION month_name
  MEMBER DESCRIPTION month_long_name
  ORDER BY month_end_date
  DETERMINES (month_end_date,
    quarter_id)
LEVEL quarter
  CLASSIFICATION caption VALUE 'QUARTER'
  CLASSIFICATION description VALUE 'Quarter'
  KEY quarter_id
  MEMBER NAME quarter_name
  MEMBER CAPTION quarter_name
  MEMBER DESCRIPTION quarter_name
  ORDER BY quarter_end_date
  DETERMINES (quarter_end_date,
    year_id)
LEVEL year
  CLASSIFICATION caption VALUE 'YEAR'
  CLASSIFICATION description VALUE 'Year'
  KEY year_id
  MEMBER NAME year_name
  MEMBER CAPTION year_name
  MEMBER DESCRIPTION year_name
  ORDER BY year_end_date
  DETERMINES (year_end_date)
ALL MEMBER NAME 'All';

CREATE OR REPLACE HIERARCHY time_hier
  CLASSIFICATION caption VALUE 'CALENDAR'
  CLASSIFICATION description VALUE 'CALENDAR'
USING time_attr_dim
 (month CHILD OF
 quarter CHILD OF
 year);

CREATE OR REPLACE ATTRIBUTE DIMENSION product_attr_dim
USING av.product_dim 
ATTRIBUTES
 (department_id
    CLASSIFICATION caption VALUE 'DEPARTMENT_ID'
    CLASSIFICATION description VALUE 'DEPARTMENT ID',
  department_name
    CLASSIFICATION caption VALUE 'DEPARTMENT_NAME'
    CLASSIFICATION description VALUE 'Department',
  category_id
    CLASSIFICATION caption VALUE 'CATEGORY_ID'
    CLASSIFICATION description VALUE 'CATEGORY ID',
  category_name
    CLASSIFICATION caption VALUE 'CATEGORY_NAME'
    CLASSIFICATION description VALUE 'Category')
LEVEL DEPARTMENT
  CLASSIFICATION caption VALUE 'DEPARTMENT'
  CLASSIFICATION description VALUE 'Department'
  KEY department_id
  MEMBER NAME department_name
  MEMBER CAPTION department_name
  ORDER BY department_name
LEVEL CATEGORY
  CLASSIFICATION caption VALUE 'CATEGORY'
  CLASSIFICATION description VALUE 'Category'
  KEY category_id
  MEMBER NAME category_name
  MEMBER CAPTION category_name
  ORDER BY category_name
  DETERMINES(department_id)
ALL MEMBER NAME 'ALL PRODUCTS';

CREATE OR REPLACE HIERARCHY product_hier
  CLASSIFICATION caption VALUE 'PRODUCT'
  CLASSIFICATION description VALUE 'Product'
USING product_attr_dim
 (CATEGORY
  CHILD OF department);

CREATE OR REPLACE ATTRIBUTE DIMENSION geography_attr_dim
USING av.geography_dim
ATTRIBUTES
 (region_id
    CLASSIFICATION caption VALUE 'REGION_ID'
    CLASSIFICATION description VALUE 'REGION ID',
  region_name
    CLASSIFICATION caption VALUE 'REGION_NAME'
    CLASSIFICATION description VALUE 'Region',
  country_id
    CLASSIFICATION caption VALUE 'COUNTRY_ID'
    CLASSIFICATION description VALUE 'COUNTRY ID',
  country_name
    CLASSIFICATION caption VALUE 'COUNTRY_NAME'
    CLASSIFICATION description VALUE 'Country',
  state_province_id
    CLASSIFICATION caption VALUE 'STATE_PROVINCE_ID'
    CLASSIFICATION description VALUE 'STATE-PROVINCE ID',
  state_province_name
    CLASSIFICATION caption VALUE 'STATE_PROVINCE_NAME'
    CLASSIFICATION description VALUE 'State-Province')
LEVEL REGION
  CLASSIFICATION caption VALUE 'REGION'
  CLASSIFICATION description VALUE 'Region'
  KEY region_id
  MEMBER NAME region_name
  MEMBER CAPTION region_name
  ORDER BY region_name
LEVEL COUNTRY
  CLASSIFICATION caption VALUE 'COUNTRY'
  CLASSIFICATION description VALUE 'Country'
  KEY country_id
  MEMBER NAME country_name
  MEMBER CAPTION country_name
  ORDER BY country_name
  DETERMINES(region_id)
LEVEL STATE_PROVINCE
  CLASSIFICATION caption VALUE 'STATE_PROVINCE'
  CLASSIFICATION description VALUE 'State-Province'
  KEY state_province_id
  MEMBER NAME state_province_name
  MEMBER CAPTION state_province_name
  ORDER BY state_province_name
  DETERMINES(country_id)
ALL MEMBER NAME 'ALL CUSTOMERS';

CREATE OR REPLACE HIERARCHY geography_hier
  CLASSIFICATION caption VALUE 'GEOGRAPHY'
  CLASSIFICATION description VALUE 'Geography'
USING geography_attr_dim
 (state_province
  CHILD OF country
  CHILD OF region);

Module 1
Introduction
You will create several different versions of an analytic view, each with different calculations. These analytic views require 3 attribute dimensions and 3 hierarchies. Before continuing, create these objects clicking on "Execute the SQL required by this tutorial" which precedes the list of modules.

SHARE_OF expressions calculate the ratio of a hierarchy value to the parent value, an ancestor value or to a specific hierarchy value. For example, the ratio of sales for a month to the parent of that month or to the year of that month. In same cases you might also define certain ratios using the QUALIFY expression.

The SHARE_OF expression is similar to the SQL RATIO_TO_REPORT expression in that it calculates the ratio of a row to the aggregate (e.g., SUM) of a grouping of rows. RATIO_TO_REPORT requires that you explicitly express the aggregate groupings across all columns using the query partition clause. The SHARE_OF expression only requires that you express the aggregate within a single hierarchy. Aggregates across all other hierarchies are calculated automatically.
Module 2
How SHARE_OF Calculations Work
The SHARE_OF expression works by computing the ratio of a measure of a hierarchy value to the measure of all hierarchy values in the same level, to the parent of the hierarchy value or to an ancestor of a hierarchy value.

The analytic view will automatically expand the SQL to access hierarchy values that are outside the scope of a WHERE clause. For example if the query selects SALES and SALES_SHARE_OF_PARENT at the month level, the analytic view will automatically expand the query to access the quarter level data needed for the calculation.

Module 3

SHARE_OF Parent

SHARE_OF parent calculates the ratio of the current member to the parent of the current member in the same hierarchy. The following example creates measure for Sales Share of Parent in the time, product and geography hierarchies.

CREATE OR REPLACE ANALYTIC VIEW sales_av
USING av.sales_fact
DIMENSION BY  ( 
   time_attr_dim KEY month_id REFERENCES month_id HIERARCHIES (time_hier DEFAULT),
   product_attr_dim  KEY category_id REFERENCES category_id HIERARCHIES (product_hier DEFAULT),
   geography_attr_dim KEY state_province_id REFERENCES state_province_id HIERARCHIES (geography_hier DEFAULT)
   )
MEASURES
 (sales FACT sales,
  sales_share_of_time_parent AS (SHARE_OF(sales HIERARCHY time_hier PARENT)),
  sales_share_of_product_parent AS (SHARE_OF(sales HIERARCHY product_hier PARENT)),
  sales_share_of_geog_parent AS (SHARE_OF(sales HIERARCHY geography_hier PARENT))
  )
DEFAULT MEASURE SALES;

The following query selects time members, sales and the share of sales in the time hierarchy.


SELECT
  product_hier.member_name    AS product_member,
  time_hier.member_name       AS time_member,
  time_hier.level_name        AS time_level,
  sales,
  ROUND(sales_share_of_time_parent,2)    AS sales_share_of_time_parent
FROM
  sales_av HIERARCHIES (product_hier, time_hier)
WHERE
  time_hier.level_name in ('YEAR','QUARTER','MONTH')
  AND product_hier.member_name = 'Cameras and Accessories'
ORDER BY
  time_hier.hier_order;

The following query selects product members, sales and the share of sales in the product hierarchy.

SELECT
  time_hier.member_name          AS time_member,
  product_hier.member_name       AS product_member,
  product_hier.level_name        AS product_level,
  sales,
  ROUND(sales_share_of_product_parent,2)    AS sales_share_of_product_parent
FROM
  sales_av HIERARCHIES (time_hier, product_hier)
WHERE
  product_hier.level_name in ('ALL','DEPARTMENT','CATEGORY')
  AND time_hier.member_name = 'CY2011'
ORDER BY
  product_hier.hier_order;

Module 4

SHARE_OF Ancestor

SHARE_OF ANCESTOR calculates the ratio of the current hierarchy value to an ancestor value. For example, the ratio of Sales at a Day, a Month or a Quarter to a Year. The following example creates measures for:

Sales Share of Year.
Sales Share of Department.
Sales Share of Region.

CREATE OR REPLACE ANALYTIC VIEW sales_av
USING av.sales_fact
DIMENSION BY  ( 
   time_attr_dim KEY month_id REFERENCES month_id HIERARCHIES (time_hier DEFAULT),
   product_attr_dim  KEY category_id REFERENCES category_id HIERARCHIES (product_hier DEFAULT),
   geography_attr_dim KEY state_province_id REFERENCES state_province_id HIERARCHIES (geography_hier DEFAULT)
   )
MEASURES
 (sales FACT sales,
  sales_share_of_year AS (SHARE_OF(sales HIERARCHY time_hier LEVEL year)),
   sales_share_of_department AS (SHARE_OF(sales HIERARCHY product_hier LEVEL department)),
   sales_share_of_region AS (SHARE_OF(sales HIERARCHY geography_hier LEVEL region))
  )
DEFAULT MEASURE SALES;

Query the Time hierarchy. Note that data at each level is a ratio to the ancestor at the Year level.

SELECT
  time_hier.member_name       AS time_member,
  time_hier.level_name        as time_level,
  sales,
  ROUND(sales_share_of_year,2)    AS sales_share_of_time_year
FROM
  sales_av HIERARCHIES (time_hier)
WHERE
  time_hier.level_name in ('YEAR','QUARTER','MONTH')
ORDER BY
  time_hier.hier_order;

This might be a little easier to see by querying only the Year and one descendant level at a time. First with Year and Quarter.

SELECT
  time_hier.member_name       AS time_member,
  time_hier.level_name        as time_level,
  sales,
  ROUND(sales_share_of_year,2)    AS sales_share_of_time_year
FROM
  sales_av HIERARCHIES (time_hier)
WHERE
  time_hier.level_name in ('YEAR','QUARTER')
ORDER BY
  time_hier.hier_order;

Next by Year and Month.

SELECT
  time_hier.member_name       AS time_member,
  time_hier.level_name        as time_level,
  sales,
  ROUND(sales_share_of_year,2)    AS sales_share_of_time_year
FROM
  sales_av HIERARCHIES (time_hier)
WHERE
  time_hier.level_name in ('YEAR','MONTH')
ORDER BY
  time_hier.hier_order;

The next query illustrates that Year level data does not need to selected for the calculations to return data. The query will be automatically expanded to fetch Year level data for the SHARE_OF expression.

SELECT
  time_hier.member_name       AS time_member,
  time_hier.level_name        as time_level,
  sales,
  ROUND(sales_share_of_year,2)    AS sales_share_of_time_year
FROM
  sales_av HIERARCHIES (time_hier)
WHERE
  time_hier.level_name = 'MONTH'
ORDER BY
  time_hier.hier_order;

Query the Product hierarchy. Note that data at each level is a ratio of Sales of the current hierarchy value to Sales of the ancestor at the Department level.

SELECT
  product_hier.member_name       AS product_member,
  product_hier.level_name        as product_level,
  sales,
  ROUND(sales_share_of_department,2)    AS sales_share_of_department
FROM
  sales_av HIERARCHIES (product_hier)
WHERE
  time_hier.level_name in ('ALL','DEPARTMENT','CATEGORY')
ORDER BY
  product_hier.hier_order;

Module 5

SHARE_OF Within Level

To calculate the ratio of a hierarchy value to all values in the same level create a SHARE_OF measure that calculates the ratio of the current value to the top most aggregate value. The can be done using the MEMBER and ALL keywords.

For example:

CREATE OR REPLACE ANALYTIC VIEW sales_av
USING av.sales_fact
DIMENSION BY  ( 
   time_attr_dim KEY month_id REFERENCES month_id HIERARCHIES (time_hier DEFAULT),
   product_attr_dim  KEY category_id REFERENCES category_id HIERARCHIES (product_hier DEFAULT),
   geography_attr_dim KEY state_province_id REFERENCES state_province_id HIERARCHIES (geography_hier DEFAULT)
   )
MEASURES
 (sales FACT sales,
  sales_share_of_all_time AS (SHARE_OF(sales HIERARCHY time_hier MEMBER ALL)),
  sales_share_of_all_product AS (SHARE_OF(sales HIERARCHY product_hier MEMBER ALL)),
  sales_share_of_all_geography AS (SHARE_OF(sales HIERARCHY geography_hier MEMBER ALL))
  )
DEFAULT MEASURE SALES;

Both MEMBER and ALL are keywords and thus are not quoted and case insensitive. The MEMBER keyword indicates that a specific hierarchy value will be specified. The ALL keyword is a shortcut to referencing the ALL MEMBER NAME value which is the top most hierarchy value.

Select the SALES_SHARE_OF_ALL_PRODUCT measure at the Department level.

SELECT
  time_hier.member_name                            AS time,
  product_hier.member_name                         AS product,
  product_hier.level_name                          AS product_level,
  TO_CHAR(sales,'999,999,999,999')                 AS sales,
  ROUND(sales_share_of_all_product,3) * 100 || '%' AS sales_share_of_all_product
FROM
  sales_av HIERARCHIES (product_hier, time_hier)
WHERE
  product_hier.level_name = 'DEPARTMENT'
  AND time_hier.member_name = 'CY2015'
ORDER BY
  product_hier.hier_order;

Select the SALES_SHARE_OF_ALL_PRODUCT measure at the Category level.

SELECT
  time_hier.member_name                            AS time,
  product_hier.member_name                         AS product,
  product_hier.level_name                          AS product_level,
  TO_CHAR(sales,'999,999,999,999')                 AS sales,
  ROUND(sales_share_of_all_product,3) * 100 || '%' AS sales_share_of_all_product
FROM
  sales_av HIERARCHIES (product_hier, time_hier)
WHERE
  product_hier.level_name = 'CATEGORY'
  AND time_hier.member_name = 'CY2015'
ORDER BY
  product_hier.hier_order;

Module 6
Ratio to a Specific Hierarchy Value Using SHARE_OF ... MEMBER
In each of the previous examples the SHARE_OF expression was used to create a measure that was the ratio of a hierarchy value to a parent or ancestor value. You can use the MEMBER keyword to create a measure that is the ratio of a hierarchy value to a specific hierarchy value.

In the previous module you saw how MEMBER ALL can be used to reference the top most value of a hierarchy. The MEMBER keyword can also be use to reference a specific value. For example, if you would like to create a measure that is the ratio of any hierarchy value to the value for year 2001 you could use SHARE_OF ... MEMBER .. VALUE.

The value used by the MEMBER keyword is the key attribute value of the level preceded by the level name. In the TIME_HIER hierarchy the key attribute value for year is in the YEAR_ID attribute. The key attribute value can also be seen in the last element of the MEMBER_UNIQUE_NAME attribute. This can be seen in the following query.
```
SELECT member_name, year_id, member_unique_name FROM time_hier WHERE level_name = 'YEAR' ORDER BY hier_order;
```
The following example creates a ratio of sales to the value of sales for year 2001.
```
CREATE OR REPLACE ANALYTIC VIEW sales_av
USING av.sales_fact
DIMENSION BY  ( 
   time_attr_dim KEY month_id REFERENCES month_id HIERARCHIES (time_hier DEFAULT),
   product_attr_dim  KEY category_id REFERENCES category_id HIERARCHIES (product_hier DEFAULT),
   geography_attr_dim KEY state_province_id REFERENCES state_province_id HIERARCHIES (geography_hier DEFAULT)
   )
MEASURES
 (sales FACT sales,
  sales_ratio_to_year_2011 AS (SHARE_OF(sales HIERARCHY time_hier MEMBER year ['11']))
  )
DEFAULT MEASURE SALES;
```
The following query selects at the year and department levels.
```
SELECT
  time_hier.member_name                            AS time,
  product_hier.member_name                         AS product,
  TO_CHAR(sales,'999,999,999,999')                 AS sales,
  ROUND(sales_ratio_to_year_2011,3) * 100 || '%'   AS sales_ration_to_year_2011
FROM
  sales_av HIERARCHIES (product_hier, time_hier)
WHERE
  product_hier.level_name = 'DEPARTMENT'
  AND time_hier.level_name = 'YEAR'
ORDER BY
  product_hier.hier_order,
  time_hier.hier_order;
```

Module 7

Comparing Table and Analytic View Queries

One of the benefits of using an analytic view is simplified SQL generation and avoidance of data errors. The following examples compare ratio to parent queries using tables and an analytic view.

Create an analytic view with share to parent calculations.


CREATE OR REPLACE ANALYTIC VIEW sales_av
USING av.sales_fact
DIMENSION BY  ( 
   time_attr_dim KEY month_id REFERENCES month_id HIERARCHIES (time_hier DEFAULT),
   product_attr_dim  KEY category_id REFERENCES category_id HIERARCHIES (product_hier DEFAULT),
   geography_attr_dim KEY state_province_id REFERENCES state_province_id HIERARCHIES (geography_hier DEFAULT)
   )
MEASURES
 (sales FACT sales,
  sales_share_of_time_parent AS (SHARE_OF(sales HIERARCHY time_hier PARENT)),
  sales_share_of_product_parent AS (SHARE_OF(sales HIERARCHY product_hier PARENT)),
  sales_share_of_geog_parent AS (SHARE_OF(sales HIERARCHY geography_hier PARENT))
  )
DEFAULT MEASURE SALES;

The first example looks for the share of quarters to years.

The table example uses RATIO_TO_REPORT to calculate the ratio of a sales values at the quarter level to its' parent (years). The RATIO_TO_REPORT function doesn't understand the relationship between years and quarters, so it is up to the application to understand this relationship as well as relationships to any other columns in the select list. This is seen in PARTITION BY of RATIO_TO_REPORT.


SELECT
  p.category_name,
  t.year_name,
  t.quarter_name,
  SUM(f.sales),
  ROUND(RATIO_TO_REPORT(SUM(f.sales)) OVER (PARTITION BY t.year_name, p.category_name),2) AS sales_ratio_to_report
FROM
  av.time_dim t,
  av.product_dim p,
  av.sales_fact f
WHERE
  t.month_id = f.month_id
  AND p.category_id = f.category_id
GROUP BY
  p.category_name,
  t.year_name,
  t.quarter_name;

With the analytic view the application just selects from the SALES_SHARE_OF_TIME_PARENT column. The application doesn't need to understand the relationship between year and quarter or any other column in the select list. Those relationships are embedded in the analytic view (as well as joins and aggregation).


SELECT
  product_hier.member_name    AS product_member,
  time_hier.year_name         AS year_name,
  time_hier.member_name       AS time_member,
  sales,
  ROUND(sales_share_of_time_parent,2)    AS sales_share_of_time_parent
FROM
  sales_av HIERARCHIES (product_hier, time_hier)
WHERE
  time_hier.level_name = 'QUARTER'
  AND product_hier.level_name = 'CATEGORY'
ORDER BY
  product_hier.member_name,
  time_hier.hier_order;

If the table query changes to month level data and DEPARTMENT_NAME is added to the SELECT list, QUARTER_NAME must also be added to the SELECT list the RATIO_TO_REPORT calculation must be updated to PARTITION BY those columns. Failure to do so might result in unexpected results.


SELECT
  p.department_name,
  p.category_name,
  t.year_name,
  t.quarter_name,
  t.month_name,
  SUM(f.sales),
  ROUND(RATIO_TO_REPORT(SUM(f.sales)) OVER (PARTITION BY t.year_name, t.quarter_name, p.department_name, p.category_name),2) AS sales_share_of_quarter
FROM
  av.time_dim t,
  av.product_dim p,
  av.sales_fact f
WHERE
  t.month_id = f.month_id
  AND p.category_id = f.category_id
GROUP BY
  p.department_name,
  p.category_name,
  t.year_name,
  t.quarter_name,
  t.month_name;

If we 'forget' to PARTITION BY quarter_name, the query returned incorrect values for time share to parent.


SELECT
  p.department_name,
  p.category_name,
  t.year_name,
  t.quarter_name,
  t.month_name,
  SUM(f.sales),
  ROUND(RATIO_TO_REPORT(SUM(f.sales)) OVER (PARTITION BY t.year_name, 
 p.department_name, p.category_name),2) AS sales_share_of_quarter
FROM
  av.time_dim t,
  av.product_dim p,
  av.sales_fact f
WHERE
  t.month_id = f.month_id
  AND p.category_id = f.category_id
GROUP BY
  p.department_name,
  p.category_name,
  t.year_name,
  t.quarter_name,
  t.month_name;

When querying the analytic view, all that is needed is changing the TIME_HIER.LEVEL_NAME filter to 'MONTH'. DEPARTMENT_NAME and CATEGORY_NAME are added to be consistent with the table query.


SELECT
  product_hier.department_name AS department_name,
  product_hier.category_name   AS category_name,
  product_hier.member_name     AS product_member,
  time_hier.year_name          AS year_name,
  time_hier.quarter_name       AS quarter_name,
  time_hier.member_name        AS time_member,
  sales,
  ROUND(sales_share_of_time_parent,2)    AS sales_share_of_time_parent
FROM
  sales_av HIERARCHIES (product_hier, time_hier)
WHERE
  time_hier.level_name = 'MONTH'
  AND product_hier.level_name = 'CATEGORY'
ORDER BY
  product_hier.hier_order,
  time_hier.hier_order;

Omitting DEPARTMENT_NAME, YEAR_NAME AND QUARTER_NAME from the analytic_view query does not change the SALES_SHARE_OF_TIME_PARENT calculation because the analytic view knows that QUARTER is the parent of MONTH and the relationships between TIME and PRODUCT hierarchies.


SELECT
  --product_hier.department_name  AS department_name,
  product_hier.member_name        AS product_member,
  -- time_hier.year_name          AS year_name,
  -- time_hier.quarter_name       AS quarter_name,
  time_hier.member_name           AS time_member,
  sales,
  ROUND(sales_share_of_time_parent,2)    AS sales_share_of_time_parent
FROM
  sales_av HIERARCHIES (product_hier, time_hier)
WHERE
  time_hier.level_name = 'MONTH'
  AND product_hier.level_name = 'CATEGORY'
ORDER BY
  product_hier.hier_order,
  time_hier.hier_order;

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database