Creating an Analytic View for the Sales History (SH) Sample Schema

View All Tutorials Login to Run Tutorial

Tutorial Creating an Analytic View for the Sales History (SH) Sample Schema
Description This tutorial will create an analytic view and supporting objects using data in the Sales History (SH) sample schema. It offers a quick overview of the process of creating an analytic view.
Tags analytic view, SH, sales history
Area SQL General
Contributor William Endress (Oracle)
Created Wednesday January 04, 2017
Modules 9

Module 1
Introduction
This tutorial creates an analytic view and supporting objects using data in the Sales History (SH) schema. The analytic view uses 6 hierarchies, 2 measures from the fact table and includes a number of calculated measures.

Module 2

Time Attribute Dimension and Hierarchies

The TIMES table contains data that can suport both a calendar year and fiscal year hierarchies.

View the data in the TIMES table.

SELECT * FROM sh.times WHERE rownum <= 30;

Columns selected in the following query can be used to build the calendar year hierarchy. Note that:

TIME_ID contains unique values within CALENDAR_MONTH_DESC.
CALENDAR_MONTH_DESC contains unique values within CALENDAR_QUARTER_DESC.
CALENDAR_QUARTER_DESC contains unique values within CALENDAR_YEAR.
The columns in the ORDER BY clause can be used to sort values in chronological order.

SELECT
  time_id,
  calendar_month_desc,
  end_of_cal_month,
  calendar_quarter_desc,
  end_of_cal_quarter,
  calendar_year
 FROM
  sh.times
 ORDER BY
  calendar_year,
  end_of_cal_quarter,
  end_of_cal_month,
  time_id;

Columns in the following query can be used to create a fiscal year hierarchy.

SELECT
  time_id,
  fiscal_month_desc,
  end_of_fis_month,
  fiscal_quarter_desc,
  end_of_fis_quarter,
  fiscal_year
 FROM
  sh.times
 ORDER BY
  fiscal_year,
  end_of_fis_quarter,
  end_of_fis_month,
  time_id;

Attribute Dimension for Time

An attribute dimension is a metadata object that contains attributes and levels created from columns in a dimension table. Hierarchy objects reference attribute dimensions and inherit attributes and levels from them. The attribute dimension allows you to create attributes and levels once and reuse them in any number of hierarchies.

An attribute dimension must have at least one attribute and one level.

An attribute references a column in a table (or view) and includes metadata which may be useful to applications. An attribute may assume the name of a column or be aliased to create a different attribute name.

A level has the following properties:

A name. E.g., LEVEL day or LEVEL calendar_month.
A KEY which continues unique values in the level.
MEMBER NAME, MEMBER CAPTION and MEMBER DESCRIPTION, all of which are typically used for text descriptors of the KEY value.
ORDER BY, which is used for sorting within the level.
DETERMINES, which may include one or more attributes that are determined by the KEY attribute of the level. For example, for each value in TIME_ID, there are only one value in the CALENDAR_MONTH_DESC and FISCAL_MONTH_DESC attributes. In most cases, the parent attribute of the level is listed in DETERMINES.

CREATE OR REPLACE ATTRIBUTE DIMENSION sh_times_attr_dim
USING sh.times
ATTRIBUTES
 (time_id,
  calendar_month_desc,
  end_of_cal_month, 
  calendar_quarter_desc,
  end_of_cal_quarter, 
  calendar_year,
  end_of_cal_year,
  fiscal_month_desc,
  end_of_fis_month, 
  fiscal_quarter_desc,
  end_of_fis_quarter,
  fiscal_year,
  end_of_fis_year
    )
  LEVEL day
     KEY time_id
     MEMBER NAME to_char(time_id)
     MEMBER CAPTION to_char(time_id)
     MEMBER DESCRIPTION to_char(time_id)
     ORDER BY time_id
     DETERMINES(calendar_month_desc,fiscal_month_desc)
  LEVEL calendar_month
    KEY calendar_month_desc
    MEMBER NAME calendar_month_desc
    MEMBER CAPTION calendar_month_desc
    MEMBER DESCRIPTION calendar_month_desc
    ORDER BY end_of_cal_month
    DETERMINES(end_of_cal_month,calendar_quarter_desc)
  LEVEL calendar_quarter
    KEY calendar_quarter_desc
    MEMBER NAME calendar_quarter_desc
    MEMBER CAPTION calendar_quarter_desc
    MEMBER DESCRIPTION calendar_quarter_desc
    ORDER BY end_of_cal_quarter
    DETERMINES(end_of_cal_quarter,calendar_year)
  LEVEL calendar_year
    KEY calendar_year
    MEMBER NAME TO_CHAR(calendar_year)
    MEMBER CAPTION TO_CHAR(calendar_year)
    MEMBER DESCRIPTION TO_CHAR(calendar_year)
    ORDER BY end_of_cal_year
    DETERMINES (end_of_cal_year)
  LEVEL fiscal_month
    KEY fiscal_month_desc
    MEMBER NAME fiscal_month_desc
    MEMBER CAPTION fiscal_month_desc
    MEMBER DESCRIPTION fiscal_month_desc
    ORDER BY end_of_fis_month
    DETERMINES(end_of_fis_month,fiscal_quarter_desc)
  LEVEL fiscal_quarter
    KEY fiscal_quarter_desc
    MEMBER NAME fiscal_quarter_desc
    MEMBER CAPTION fiscal_quarter_desc
    MEMBER DESCRIPTION fiscal_quarter_desc
    ORDER BY end_of_fis_quarter
    DETERMINES(end_of_fis_quarter,fiscal_year)
  LEVEL fiscal_year
    KEY fiscal_year
    MEMBER NAME TO_CHAR(fiscal_year)
    MEMBER CAPTION TO_CHAR(fiscal_year)
    MEMBER DESCRIPTION TO_CHAR(fiscal_year)
    ORDER BY end_of_fis_year
    DETERMINES (end_of_fis_year)
  ALL MEMBER NAME 'ALL YEARS';

The previous attribute dimension is fully functional, but it does not contain metadata to describe the elements of the attributes or levels. The following CREATE ATTRIBUTE DIMENSION statement adds classifications.

CLASSIFICATION caption< and CLASSIFICATION description are typically used by applications as descriptive labels (rather than displaying the object name). For example, the TIME_ID column is created as an attribute with the caption and description 'Day'.

CREATE OR REPLACE ATTRIBUTE DIMENSION sh_times_attr_dim
  CLASSIFICATION caption VALUE 'Time'
  CLASSIFICATION description VALUE 'Time'
USING sh.times
ATTRIBUTES
 (time_id
    CLASSIFICATION caption VALUE 'Day'
    CLASSIFICATION description VALUE 'Day',
  calendar_month_desc
    CLASSIFICATION caption VALUE 'Calendar Month'
    CLASSIFICATION description VALUE 'Calendar Month',
  end_of_cal_month
    CLASSIFICATION caption VALUE 'End of Calendar Month'
    CLASSIFICATION description VALUE 'End of Calendar Month', 
  calendar_quarter_desc
    CLASSIFICATION caption VALUE 'Calendar Quarter'
    CLASSIFICATION description VALUE 'Calendar Quarter',
  end_of_cal_quarter
    CLASSIFICATION caption VALUE 'End of Calendar Quarter'
    CLASSIFICATION description VALUE 'End of Calendar Quarter', 
  calendar_year
    CLASSIFICATION caption VALUE 'Calendar Year'
    CLASSIFICATION description VALUE 'Calendar Year',
  end_of_cal_year
    CLASSIFICATION caption VALUE 'End of Calendar Year'
    CLASSIFICATION description VALUE 'End of Calendar Year', 
  fiscal_month_desc
    CLASSIFICATION caption VALUE 'Fiscal Month'
    CLASSIFICATION description VALUE 'Fiscal Month',
  end_of_fis_month
    CLASSIFICATION caption VALUE 'End of Fiscal Month'
    CLASSIFICATION description VALUE 'End of Fiscal Month', 
  fiscal_quarter_desc
    CLASSIFICATION caption VALUE 'Fiscal Quarter'
    CLASSIFICATION description VALUE 'Calendar Quarter',
  end_of_fis_quarter
    CLASSIFICATION caption VALUE 'End of Fiscal Quarter'
    CLASSIFICATION description VALUE 'End of Fiscal Quarter', 
  fiscal_year
    CLASSIFICATION caption VALUE 'Fiscal Year'
    CLASSIFICATION description VALUE 'Fiscal Year',
  end_of_fis_year
    CLASSIFICATION caption VALUE 'End of Fiscal Year'
    CLASSIFICATION description VALUE 'End of Fiscal Year' 
    )
  LEVEL day
    CLASSIFICATION caption VALUE 'Day'
    CLASSIFICATION description VALUE 'Day'
     KEY time_id
     MEMBER NAME to_char(time_id)
     MEMBER CAPTION to_char(time_id)
     MEMBER DESCRIPTION to_char(time_id)
     ORDER BY time_id
     DETERMINES(calendar_month_desc,fiscal_month_desc)
  LEVEL calendar_month
    CLASSIFICATION caption VALUE 'Calendar Month'
    CLASSIFICATION description VALUE 'Calendar Month'
    KEY calendar_month_desc
    MEMBER NAME calendar_month_desc
    MEMBER CAPTION calendar_month_desc
    MEMBER DESCRIPTION calendar_month_desc
    ORDER BY end_of_cal_month
    DETERMINES(end_of_cal_month,calendar_quarter_desc)
  LEVEL calendar_quarter
    CLASSIFICATION caption VALUE 'Calendar Quarter'
    CLASSIFICATION description VALUE 'Calendar Quarter'
    KEY calendar_quarter_desc
    MEMBER NAME calendar_quarter_desc
    MEMBER CAPTION calendar_quarter_desc
    MEMBER DESCRIPTION calendar_quarter_desc
    ORDER BY end_of_cal_quarter
    DETERMINES(end_of_cal_quarter,calendar_year)
  LEVEL calendar_year
    CLASSIFICATION caption VALUE 'Calendar Year'
    CLASSIFICATION description VALUE 'Calendar Year'
    KEY calendar_year
    MEMBER NAME TO_CHAR(calendar_year)
    MEMBER CAPTION TO_CHAR(calendar_year)
    MEMBER DESCRIPTION TO_CHAR(calendar_year)
    ORDER BY end_of_cal_year
    DETERMINES (end_of_cal_year)
  LEVEL fiscal_month
    CLASSIFICATION caption VALUE 'Fiscal Month'
    CLASSIFICATION description VALUE 'Fiscal Month'
    KEY fiscal_month_desc
    MEMBER NAME fiscal_month_desc
    MEMBER CAPTION fiscal_month_desc
    MEMBER DESCRIPTION fiscal_month_desc
    ORDER BY end_of_fis_month
    DETERMINES(end_of_fis_month,fiscal_quarter_desc)
  LEVEL fiscal_quarter
    CLASSIFICATION caption VALUE 'Fiscal Quarter'
    CLASSIFICATION description VALUE 'Fiscal Quarter'
    KEY fiscal_quarter_desc
    MEMBER NAME fiscal_quarter_desc
    MEMBER CAPTION fiscal_quarter_desc
    MEMBER DESCRIPTION fiscal_quarter_desc
    ORDER BY end_of_fis_quarter
    DETERMINES(end_of_fis_quarter,fiscal_year)
  LEVEL fiscal_year
    CLASSIFICATION caption VALUE 'Fiscal Year'
    CLASSIFICATION description VALUE 'Fiscal Year'
    KEY fiscal_year
    MEMBER NAME TO_CHAR(fiscal_year)
    MEMBER CAPTION TO_CHAR(fiscal_year)
    MEMBER DESCRIPTION TO_CHAR(fiscal_year)
    ORDER BY end_of_fis_year
    DETERMINES (end_of_fis_year)
  ALL MEMBER NAME 'ALL YEARS';

Attribute values are queried from hierarchies. A hierarchy references an attribute dimension, inheriting metadata from it. As a result, the CREATE ATTRIBUTE statement is relatively simple.

Hierarchies for Time

The calendar year hierarchy.

CREATE OR REPLACE HIERARCHY sh_times_calendar_hier
  CLASSIFICATION caption VALUE 'Calendar Year'
  CLASSIFICATION description VALUE 'Calendar Year'
  USING sh_times_attr_dim
    (day CHILD OF
     calendar_month CHILD OF
     calendar_quarter CHILD OF
     calendar_year);

The fiscal year hierarchy.

CREATE OR REPLACE HIERARCHY sh_times_fiscal_hier
  CLASSIFICATION caption VALUE 'Fiscal Year'
  CLASSIFICATION description VALUE 'Fiscal Year'
  USING sh_times_attr_dim
    (day CHILD OF
     fiscal_month CHILD OF
     fiscal_quarter CHILD OF
     fiscal_year);

Query the TIMES_CALENDAR_HIER hierarchy.

SELECT * FROM sh_times_calendar_hier WHERE calendar_year = '2001';

Query the TIMES_FISCAL_HIER hierarchy.

SELECT * FROM sh_times_fiscal_hier WHERE fiscal_year = '2001';

Module 3

Product Attribute Dimension and Hierarchy

The PRODUCTS table supports a hierarchy of levels Product > Subcategory > Category.

SELECT all columns from PRODUCTS.

SELECT * FROM sh.products;

A hierarchy can be created from the following columns.

SELECT
  prod_id,
  prod_name,
  prod_subcategory,
  prod_category
FROM
  sh.products
ORDER BY
  prod_category,
  prod_subcategory,
  prod_name;

Create the attribute dimension for product.

CREATE OR REPLACE ATTRIBUTE DIMENSION sh_products_attr_dim
USING sh.products
ATTRIBUTES (
  prod_id
    CLASSIFICATION caption VALUE 'Product'
    CLASSIFICATION description VALUE 'Product',
  prod_name
    CLASSIFICATION caption VALUE 'Product'
    CLASSIFICATION description VALUE 'Product',
  prod_subcategory
    CLASSIFICATION caption VALUE 'Subcategory'
    CLASSIFICATION description VALUE 'Subcategory',
  prod_category
    CLASSIFICATION caption VALUE 'Category'
    CLASSIFICATION description VALUE 'Category'
    )
  LEVEL PRODUCT
    CLASSIFICATION caption VALUE 'Product'
    CLASSIFICATION description VALUE 'Product'
    KEY prod_id
    MEMBER NAME prod_name
    MEMBER CAPTION prod_name
    MEMBER DESCRIPTION prod_name
    ORDER BY prod_name
    DETERMINES (prod_subcategory)
  LEVEL SUBCATEGORY
    CLASSIFICATION caption VALUE 'Subcategory'
    CLASSIFICATION description VALUE 'Subcategory'
    KEY prod_subcategory
    MEMBER NAME prod_subcategory
    MEMBER CAPTION prod_subcategory
    MEMBER DESCRIPTION prod_subcategory
    ORDER BY prod_subcategory
    DETERMINES (prod_category)
  LEVEL CATEGORY
    CLASSIFICATION caption VALUE 'Category'
    CLASSIFICATION description VALUE 'Category'
    KEY prod_category
    MEMBER NAME prod_category
    MEMBER CAPTION prod_category
    MEMBER DESCRIPTION prod_category
    ORDER BY prod_category
  ALL MEMBER NAME 'ALL PRODUCTS';

Create a hierarchy for products.

CREATE OR REPLACE HIERARCHY sh_products_hier
  CLASSIFICATION caption VALUE 'Products'
  CLASSIFICATION description VALUE 'Products'
USING sh_products_attr_dim
 (product CHILD OF
  subcategory CHILD OF
  category);

SELECT * FROM sh_products_hier;

Module 4

Customer Attribute Dimension and Hierarchy

Customer is a little more complicated than time and product because:

Customer data is in two tables (CUSTOMERS and COUNTRIES) rather than one.
Cities might not always be unique within states and states might not always be unique within countries. There could, for example be a Miami in Florida and a Miami in Ohio. (With this small sample data set that condition does not actually exist, but you will create the hierarchy to cover this case.)

Examine data in the CUSTOMERS table.

SELECT * FROM sh.customers WHERE rownum <= 30;

Examine data in the COUNTRIES table.

SELECT * FROM sh.countries;

Create a view that joins the tables and creates unique values for customer_name, city_id, and state_province_id.

CREATE OR REPLACE VIEW sh_customers_dim_view AS
SELECT
  a.cust_id,
  a.cust_last_name || ', ' || a.cust_first_name as customer_name,
  a.cust_city || ', ' || a.cust_state_province || ', ' || a.country_id as city_id,
  a.cust_city as city_name,
  a.cust_state_province || ', ' || a.country_id as state_province_id,
  a.cust_state_province as state_province_name,
  b.country_id,
  b.country_name,
  b.country_subregion as subregion,
  b.country_region as region
FROM sh.customers a, sh.countries b
where a.country_id = b.country_id;

Query the view.

SELECT *
FROM sh_customers_dim_view
WHERE rownum <= 50
ORDER BY region,
  subregion,
  country_name,
  state_province_id,
  city_id;

Create the attribute dimension for customers.

CREATE OR REPLACE ATTRIBUTE DIMENSION sh_customers_attr_dim
USING sh_customers_dim_view
ATTRIBUTES (
 cust_id
    CLASSIFICATION caption VALUE 'Customer'
    CLASSIFICATION description VALUE 'Customer',
  customer_name
    CLASSIFICATION caption VALUE 'Customer'
    CLASSIFICATION description VALUE 'Customer',
  city_id
    CLASSIFICATION caption VALUE 'City'
    CLASSIFICATION description VALUE 'City',
  city_name
    CLASSIFICATION caption VALUE 'City'
    CLASSIFICATION description VALUE 'City',
  state_province_id
    CLASSIFICATION caption VALUE 'State Province'
    CLASSIFICATION description VALUE 'State Province',
  state_province_name
    CLASSIFICATION caption VALUE 'State Province'
    CLASSIFICATION description VALUE 'State Province',
  country_id
    CLASSIFICATION caption VALUE 'Country'
    CLASSIFICATION description VALUE 'Country',
   country_name
    CLASSIFICATION caption VALUE 'Country'
    CLASSIFICATION description VALUE 'Country',
   subregion
    CLASSIFICATION caption VALUE 'Subregion'
    CLASSIFICATION description VALUE 'Subregion',
    region
    CLASSIFICATION caption VALUE 'Region'
    CLASSIFICATION description VALUE 'Region'
    )
  LEVEL CUSTOMER
    CLASSIFICATION caption VALUE 'Customer'
    CLASSIFICATION description VALUE 'Customer'
    KEY cust_id
    MEMBER NAME customer_name
    MEMBER CAPTION customer_name
    MEMBER DESCRIPTION customer_name
    ORDER BY customer_name
    DETERMINES (city_id)
  LEVEL CITY
    CLASSIFICATION caption VALUE 'City'
    CLASSIFICATION description VALUE 'City'
    KEY city_id
    MEMBER NAME city_name
    MEMBER CAPTION city_name
    MEMBER DESCRIPTION city_name
    ORDER BY city_name
    DETERMINES (state_province_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
    MEMBER DESCRIPTION state_province_name
    ORDER BY state_province_name
    DETERMINES (country_id)
  LEVEL COUNTRY
    CLASSIFICATION caption VALUE 'Country'
    CLASSIFICATION description VALUE 'Country'
    KEY country_id
    MEMBER NAME country_name
    MEMBER CAPTION country_name
    MEMBER DESCRIPTION country_name
    ORDER BY country_name
    DETERMINES (subregion)
 LEVEL SUBREGION
    CLASSIFICATION caption VALUE 'Subregion'
    CLASSIFICATION description VALUE 'Subregion'
    KEY subregion
    MEMBER NAME subregion
    MEMBER CAPTION subregion
    MEMBER DESCRIPTION subregion
    ORDER BY subregion
    DETERMINES (region)
  LEVEL REGION
    CLASSIFICATION caption VALUE 'Region'
    CLASSIFICATION description VALUE 'Region'
    KEY region
    MEMBER NAME region
    MEMBER CAPTION region
    MEMBER DESCRIPTION region
    ORDER BY region
  ALL MEMBER NAME 'ALL CUSTOMERS';

Create the hierarchy for customers.

CREATE OR REPLACE HIERARCHY sh_customers_hier
  CLASSIFICATION caption VALUE 'Customers'
  CLASSIFICATION description VALUE 'Customers'
USING sh_customers_attr_dim
 (customer CHILD OF
  city CHILD OF
  state_province CHILD OF
  country CHILD OF
  subregion CHILD OF
  region);

Query the customer hierarchy.

SELECT * FROM sh_customers_hier WHERE rownum <= 50;

With 5 levels, notice that this hierarchy returns quite a few columns. Some columns are hierarchical attribute columns and other columns are simply attribute columns.

Hierarchical columns are created by the database. Some hierarchical columns combine unpivot attributes into a single column, creating rows for aggregate level attributes. For example, each of the MEMBER NAME attributes are combined into a single MEMBER_NAME column. Other hierarchical columns are calculated, for example the HIER_ORDER column.

Query the hierarchical attributes in the SH_CUSTOMERS_HIER hierarchy.

SELECT member_name,
  member_unique_name,
  member_caption,
  member_description,
  level_name,
  hier_order,
  depth,
  parent_level_name,
  parent_unique_name
FROM sh_customers_hier
WHERE rownum <= 50;

These hierarchical attribute columns will exist in every hierarchy.

Note that the hierarchy view returns data at each level. That is, at both detail and aggregate levels. Also, the HIER_ORDER column sorts children within parents.

SELECT member_name,
  member_unique_name,
  level_name,
  hier_order
FROM sh_customers_hier
WHERE rownum <= 50
ORDER BY hier_order;

Attribute columns contain data from each attribute in the ATTRIBUTES list of the attribute dimension.

SELECT cust_id,
  customer_name,
  city_id,
  city_name,
  state_province_id,
  state_province_name,
  country_id,
  country_name,
  subregion,
  region
FROM sh_customers_hier
WHERE rownum <= 50;

Module 5

Channels and Promotions Attribute Dimensions and Hierarchies

There no new concepts with these attribute dimensions and hierarchies for Channels and Promotions, so just go ahead and create them.

View data in the CHANNELS table.

SELECT * from sh.channels;

Create the attribute dimension for channels.

CREATE OR REPLACE ATTRIBUTE DIMENSION sh_channels_attr_dim
  CLASSIFICATION caption VALUE 'Channels'
  CLASSIFICATION description VALUE 'Channels'
USING sh.channels
ATTRIBUTES (
  channel_id
    CLASSIFICATION caption VALUE 'Channel'
    CLASSIFICATION description VALUE 'Channel',
  channel_desc
    CLASSIFICATION caption VALUE 'Channel'
    CLASSIFICATION description VALUE 'Channel',
  channel_class
    CLASSIFICATION caption VALUE 'Channel Class'
    CLASSIFICATION description VALUE 'Channel Class'
  )   
  LEVEL CHANNEL
    CLASSIFICATION caption VALUE 'Channel'
    CLASSIFICATION description VALUE 'Channel'
    KEY channel_id
    MEMBER NAME channel_desc
    MEMBER CAPTION channel_desc
    ORDER BY channel_desc
    DETERMINES (channel_class)
  LEVEL CHANNEL_CLASS
    CLASSIFICATION caption VALUE 'Channel_Class'
    CLASSIFICATION description VALUE 'Channel Class'
    KEY channel_class
    MEMBER NAME channel_class
    MEMBER CAPTION channel_class
    ORDER BY channel_class 
  ALL MEMBER NAME 'ALL CHANNELS';

Create the hierarchy for channels.

CREATE OR REPLACE HIERARCHY sh_channels_hier
  CLASSIFICATION caption VALUE 'Channels'
  CLASSIFICATION description VALUE 'Channels'
USING sh_channels_attr_dim
 (channel CHILD OF
  channel_class);

View the SH_CHANNELS_HIER hierarchy.

SELECT * FROM sh_channels_hier;

View the PROMOTIONS table.

SELECT * from sh.promotions;

Create the attribute dimension for promotions.

CREATE OR REPLACE ATTRIBUTE DIMENSION sh_promotions_attr_dim
  CLASSIFICATION caption VALUE 'Promotions'
  CLASSIFICATION description VALUE 'Promotions'
USING sh.promotions
ATTRIBUTES (
  promo_id
    CLASSIFICATION caption VALUE 'promotion'
    CLASSIFICATION description VALUE 'promotion',
  promo_name
    CLASSIFICATION caption VALUE 'promotion'
    CLASSIFICATION description VALUE 'promotion',
  promo_subcategory
    CLASSIFICATION caption VALUE 'Subcategory'
    CLASSIFICATION description VALUE 'Subcategory',
  promo_category
    CLASSIFICATION caption VALUE 'Category'
    CLASSIFICATION description VALUE 'Category'  
  )
  LEVEL PROMOTION
    CLASSIFICATION caption VALUE 'promotion'
    CLASSIFICATION description VALUE 'promotion'
    KEY promo_id
    MEMBER NAME promo_name
    MEMBER CAPTION promo_name
    ORDER BY promo_name
    DETERMINES (promo_subcategory)
  LEVEL SUBCATEGORY
    CLASSIFICATION caption VALUE 'Subcategory'
    CLASSIFICATION description VALUE 'Subcategory'
    KEY promo_subcategory
    MEMBER NAME promo_subcategory
    MEMBER CAPTION promo_subcategory
    ORDER BY promo_subcategory
    DETERMINES (promo_category)
  LEVEL CATEGORY
    CLASSIFICATION caption VALUE 'Category'
    CLASSIFICATION description VALUE 'Category'
    KEY promo_category
    MEMBER NAME promo_category
    MEMBER CAPTION promo_category
    ORDER BY promo_category
  ALL MEMBER NAME 'ALL PROMOTIONS';

Create the promotions hierarchy.

CREATE OR REPLACE HIERARCHY sh_promotions_hier
  CLASSIFICATION caption VALUE 'Promotions'
  CLASSIFICATION description VALUE 'Promotions'
USING sh_promotions_attr_dim
 (promotion CHILD OF
  subcategory CHILD OF
  category);

View data in the SH_PROMOTIONS_HIER hierarchy.

SELECT * FROM SH_PROMOTIONS_HIER;

Module 6

Creating the Analytic View for Sales Data

The analytic view joins the hierarchies to the fact table to present fact data as measures. A measure may come directly from the fact table or it may be calculated using an expression.

The fact table contains keys for product, customer, time, channel and promotions and the measures QUANTITY_SOLD and AMOUNT_SOLD.

View data in the SALES table.

SELECT * FROM sh.sales WHERE rownum <= 50;

Create the analytic view. Note the comments in the CREATE statement.

CREATE OR REPLACE ANALYTIC VIEW sh_sales_history_av
 CLASSIFICATION caption VALUE 'Sales History (SH Sample Schema)'
 CLASSIFICATION description VALUE 'Sales History by Time, Product, Customer, Channel and Promotion'
 -- This AV references the SALES fact table.
USING sh.sales 
-- This is where hierarchies are joined into the analytic view.
DIMENSION BY
 (
  sh_times_attr_dim KEY time_id REFERENCES time_id HIERARCHIES (sh_times_calendar_hier DEFAULT, sh_times_fiscal_hier),
  sh_products_attr_dim KEY prod_id REFERENCES prod_id
 HIERARCHIES (sh_products_hier DEFAULT),
  sh_customers_attr_dim KEY cust_id REFERENCES cust_id HIERARCHIES (sh_customers_hier DEFAULT),
  sh_channels_attr_dim KEY channel_id REFERENCES channel_id HIERARCHIES (sh_channels_hier DEFAULT),
  sh_promotions_attr_dim KEY promo_id REFERENCES promo_id HIERARCHIES (sh_promotions_hier DEFAULT)  
  )
MEASURES (
  -- Amount sold maps to the fact table.
  amount_sold FACT amount_sold
    CLASSIFICATION caption VALUE 'Amount Sold'
    CLASSIFICATION description VALUE 'Amount Sold'
    CLASSIFICATION format_string VALUE '999,999,999,999.99',
  -- Quantity sold maps to the fact table.
  quantity_sold FACT quantity_sold
    CLASSIFICATION caption VALUE 'Quantity Sold'
    CLASSIFICATION description VALUE 'Quantity Sold'
    CLASSIFICATION format_string VALUE '999,999,999,999'
  )
DEFAULT MEASURE amount_sold;

The following query returns data at the calendar year, category and region levels. Note that a query of an analytic view does not need to select from all hierarchies (The SH_CHANNELS_HIER hierarchy is not used in this query).

SELECT
  sh_times_calendar_hier.member_name AS TIMES_CALENDAR_HIER,
  sh_products_hier.member_name AS PRODUCTS_HIER,
  sh_customers_hier.member_name AS CUSTOMERS_HIER,
  amount_sold,
  quantity_sold
FROM
   sh_sales_history_av HIERARCHIES (
   sh_times_calendar_hier,
   sh_products_hier,
   sh_customers_hier)
WHERE
  sh_times_calendar_hier.level_name = 'CALENDAR_YEAR'
  AND sh_products_hier.level_name = 'CATEGORY'
  AND sh_customers_hier.level_name = 'REGION'
ORDER BY
  sh_times_calendar_hier.hier_order,
  sh_products_hier.hier_order,
  sh_customers_hier.hier_order;

The next query includes all hierarchies.

SELECT
  sh_times_calendar_hier.member_name AS TIMES_CALENDAR_HIER,
  sh_products_hier.member_name AS PRODUCTS_HIER,
  sh_customers_hier.member_name AS CUSTOMERS_HIER,
  sh_channels_hier.member_name AS CHANNELS_HIER,
  sh_promotions_hier.member_name AS PROMOTIONS_HIER,
  amount_sold,
  quantity_sold
FROM
  sh_sales_history_av HIERARCHIES (
  sh_times_calendar_hier,
  sh_products_hier,
  sh_customers_hier,
  sh_channels_hier,
  sh_promotions_hier)
WHERE
  sh_times_calendar_hier.level_name = 'CALENDAR_YEAR'
  AND sh_products_hier.level_name = 'CATEGORY'
  AND sh_customers_hier.level_name = 'REGION'
  AND sh_channels_hier.level_name = 'CHANNEL_CLASS'
  AND sh_promotions_hier.level_name = 'CATEGORY'
ORDER BY
  sh_times_calendar_hier.hier_order,
  sh_products_hier.hier_order,
  sh_customers_hier.hier_order,
  sh_channels_hier.hier_order,
  sh_promotions_hier.hier_order;

Module 7

Adding Calculated Measures

Calculated measures are added to the analytic views using expressions that reference attributes in hierarchies, hierarchy values and other measures. The following analytic view includes a variety of examples.

Create the analytic view with calculated measures.

CREATE OR REPLACE ANALYTIC VIEW sh_sales_history_av
 CLASSIFICATION caption VALUE 'Sales History (SH Sample Schema)'
 CLASSIFICATION description VALUE 'Sales History by Time, Product, Customer, Channel and Promotion'
USING sh.sales 
DIMENSION BY
 (
  sh_times_attr_dim KEY time_id REFERENCES time_id HIERARCHIES (sh_times_calendar_hier DEFAULT, sh_times_fiscal_hier),
  sh_products_attr_dim KEY prod_id REFERENCES prod_id HIERARCHIES (sh_products_hier DEFAULT),
  sh_customers_attr_dim KEY cust_id REFERENCES cust_id HIERARCHIES (sh_customers_hier DEFAULT),
  sh_channels_attr_dim KEY channel_id REFERENCES channel_id HIERARCHIES (sh_channels_hier DEFAULT),
  sh_promotions_attr_dim KEY promo_id REFERENCES promo_id HIERARCHIES (sh_promotions_hier DEFAULT)  
  )
MEASURES (
  -- Amount sold maps to the fact table.
  amount_sold FACT amount_sold
    CLASSIFICATION caption VALUE 'Amount Sold'
    CLASSIFICATION description VALUE 'Amount Sold'
    CLASSIFICATION format_string VALUE '999,999,999,999.99',
  -- Quantity sold maps to the fact table.
  quantity_sold FACT quantity_sold
    CLASSIFICATION caption VALUE 'Quantity Sold'
    CLASSIFICATION description VALUE 'Quantity Sold'
    CLASSIFICATION format_string VALUE '999,999,999,999',
  -- Ratio of amount sold for the current value to the parent product value. 
  amt_sold_shr_parent_prod AS (SHARE_OF(amount_sold HIERARCHY sh_products_hier PARENT))
    CLASSIFICATION caption VALUE 'Sales Product Share of Parent'
    CLASSIFICATION description VALUE 'Sales Product Share of Parent'
    CLASSIFICATION format_string VALUE '999.99',
  -- Ratio of amount sold for the current value to the parent customer value. 
  sales_shr_parent_cust AS (SHARE_OF(amount_sold HIERARCHY sh_customers_hier PARENT))
    CLASSIFICATION caption VALUE 'Sales Customer Share of Parent'
    CLASSIFICATION description VALUE 'Sales Customer Share of Parent'
    CLASSIFICATION format_string VALUE '999,999,999,999.99',
  --
  -- Calendar Year measures
  --
  -- Sales Calendar Year to Date  
  sales_cal_ytd AS (SUM(amount_sold) OVER (HIERARCHY sh_times_calendar_hier BETWEEN UNBOUNDED PRECEDING AND CURRENT MEMBER WITHIN ANCESTOR AT LEVEL calendar_year))
    CLASSIFICATION caption VALUE 'Sales Calendar YTD'
    CLASSIFICATION description VALUE 'Sales Calendar YTD'
    CLASSIFICATION format_string VALUE '999,999,999,999.99',
   -- Sales same period 1 year ago.
  sales_cal_year_ago as (LAG(amount_sold) OVER (HIERARCHY sh_times_calendar_hier OFFSET 1 ACROSS ANCESTOR AT LEVEL calendar_year))
    CLASSIFICATION caption VALUE 'Sales Calendar Year Ago'
    CLASSIFICATION description VALUE 'Sales Year Ago'
    CLASSIFICATION format_string VALUE '$999,999,999,999.99',
   -- Change in sales for the current period as compared to the same period 1 year ago.
  sales_chg_cal_year_ago as (LAG_DIFF(amount_sold) OVER (HIERARCHY sh_times_calendar_hier OFFSET 1 ACROSS ANCESTOR AT LEVEL calendar_year))
    CLASSIFICATION caption VALUE 'Sales Change Calendar Year Ago'
    CLASSIFICATION description VALUE 'Sales Change Calendar Year Ago'
    CLASSIFICATION format_string VALUE '$999,999,999,999.99',
   -- Percent change in sales for the current period as compared to the same period 1 year ago. 
  sales_pctchg_cal_year_ago as (LAG_DIFF_PERCENT(amount_sold) OVER (HIERARCHY sh_times_calendar_hier OFFSET 1 ACROSS ANCESTOR AT LEVEL calendar_year))
    CLASSIFICATION caption VALUE 'Sales Percent Change Calendar Year Ago'
    CLASSIFICATION description VALUE 'Sales Percent Change Calendar Year Ago'
    CLASSIFICATION format_string VALUE '999.99',
  --
  -- Fiscal Year measures
  --
  sales_fis_ytd AS (SUM(amount_sold) OVER (HIERARCHY sh_times_fiscal_hier BETWEEN UNBOUNDED PRECEDING AND CURRENT MEMBER WITHIN ANCESTOR AT LEVEL fiscal_year))
    CLASSIFICATION caption VALUE 'Sales Fiscal YTD'
    CLASSIFICATION description VALUE 'Sales Fiscal YTD'
    CLASSIFICATION format_string VALUE '999,999,999,999.99',
  sales_fis_year_ago as (LAG(amount_sold) OVER (HIERARCHY sh_times_fiscal_hier OFFSET 1 ACROSS ANCESTOR AT LEVEL fiscal_year))
    CLASSIFICATION caption VALUE 'Sales Fiscal Year Ago'
    CLASSIFICATION description VALUE 'Sales Fiscal Year Ago'
    CLASSIFICATION format_string VALUE '$999,999,999,999.99',
   -- Change in sales for the current period as compared to the same period 1 year ago.
  sales_chg_fis_year_ago as (LAG_DIFF(amount_sold) OVER (HIERARCHY sh_times_fiscal_hier OFFSET 1 ACROSS ANCESTOR AT LEVEL fiscal_year))
    CLASSIFICATION caption VALUE 'Sales Change Fiscal Year Ago'
    CLASSIFICATION description VALUE 'Sales Change Fiscal Year Ago'
    CLASSIFICATION format_string VALUE '$999,999,999,999.99',
   -- Percent change in sales for the current period as compared to the same period 1 year ago. 
  sales_pctchg_fis_year_ago as (LAG_DIFF_PERCENT(amount_sold) OVER (HIERARCHY sh_times_fiscal_hier OFFSET 1 ACROSS ANCESTOR AT LEVEL fiscal_year))
    CLASSIFICATION caption VALUE 'Sales Percent Change Fiscal Year Ago'
    CLASSIFICATION description VALUE 'Sales Percent Change Fiscal Year Ago'
    CLASSIFICATION format_string VALUE '999.99'
  )
DEFAULT MEASURE amount_sold;

Module 8

Sample Queries

Let's look at some data using the analytic view.

Amount sold by Calendar Year, Category and Region.

SELECT sh_times_calendar_hier.member_name AS time,
  sh_products_hier.member_name            AS product,
  sh_customers_hier.member_name           AS customer,
  amount_sold
FROM sh_sales_history_av HIERARCHIES (sh_times_calendar_hier, sh_products_hier, sh_customers_hier)
WHERE sh_times_calendar_hier.level_name  = 'CALENDAR_YEAR'
AND sh_products_hier.level_name  = 'CATEGORY'
AND sh_customers_hier.level_name = 'REGION'
ORDER BY sh_times_calendar_hier.hier_order,
  sh_products_hier.hier_order,
  sh_customers_hier.hier_order;

Add Sales Percent Change Calendar Year Ago. Note that the sort order is changed to make it easier to view year over year changes.

SELECT sh_times_calendar_hier.member_name AS time,
  sh_products_hier.member_name            AS product,
  sh_customers_hier.member_name           AS customer,
  amount_sold,
  sales_cal_year_ago,
  ROUND(sales_pctchg_cal_year_ago,2) AS sales_pctchg_cal_year_ago
FROM sh_sales_history_av HIERARCHIES (sh_times_calendar_hier, sh_products_hier, sh_customers_hier)
WHERE sh_times_calendar_hier.level_name  = 'CALENDAR_YEAR'
AND sh_products_hier.level_name  = 'CATEGORY'
AND sh_customers_hier.level_name = 'REGION'
ORDER BY sh_products_hier.hier_order,
  sh_customers_hier.hier_order,
  sh_times_calendar_hier.hier_order;

Sales Calendar Year to Date at the Calendar Month level in calendar year 2001 for Electronics in Europe.

SELECT sh_times_calendar_hier.member_name AS time,
  sh_products_hier.member_name            AS product,
  sh_customers_hier.member_name           AS customer,
  amount_sold,
  sales_cal_ytd
FROM sh_sales_history_av HIERARCHIES (sh_times_calendar_hier, sh_products_hier, sh_customers_hier)
WHERE sh_times_calendar_hier.level_name  = 'CALENDAR_MONTH'
AND sh_products_hier.member_name  = 'Electronics'
AND sh_customers_hier.member_name = 'Europe'
ORDER BY sh_times_calendar_hier.hier_order,
  sh_products_hier.hier_order,
  sh_customers_hier.hier_order;

The Share of Sales for each region to sales of all customers in calendar year 2001 for Electronics.

SELECT sh_times_calendar_hier.member_name AS time,
  sh_products_hier.member_name            AS product,
  sh_customers_hier.member_name           AS customer,
  amount_sold,
  ROUND(sales_shr_parent_cust,2) * 100 || '%' AS sales_shr_parent_cust
FROM sh_sales_history_av HIERARCHIES (sh_times_calendar_hier, sh_products_hier, sh_customers_hier)
WHERE sh_times_calendar_hier.member_name  = '2001'
AND sh_products_hier.member_name  = 'Electronics'
AND sh_customers_hier.level_name = 'REGION'
ORDER BY amount_sold desc;

Add all Calendar Years to the query and note that the Share of Sales automatically breaks out by year.

SELECT sh_times_calendar_hier.member_name AS time,
  sh_products_hier.member_name            AS product,
  sh_customers_hier.member_name           AS customer,
  amount_sold,
  ROUND(sales_shr_parent_cust,2) * 100 || '%' AS sales_shr_parent_cust
FROM sh_sales_history_av HIERARCHIES (sh_times_calendar_hier, sh_products_hier, sh_customers_hier)
WHERE sh_times_calendar_hier.level_name = 'CALENDAR_YEAR'
AND sh_products_hier.member_name  = 'Electronics'
AND sh_customers_hier.level_name = 'REGION'
ORDER BY sh_times_calendar_hier.hier_order,
  amount_sold desc;

Selecting hierarchical columns such as MEMBER_NAME simplifies SQL generation because the hierarchical columns do not need to change depending on the hierarchy or level of aggregation. It is, however, perfectly ok to select from 'regular' attribute columns.

SELECT sh_times_calendar_hier.calendar_year,
  sh_products_hier.prod_category,
  sh_customers_hier.region,
  amount_sold,
  ROUND(sales_shr_parent_cust,2) * 100 || '%' AS sales_shr_parent_cust
FROM sh_sales_history_av HIERARCHIES (sh_times_calendar_hier, sh_products_hier, sh_customers_hier)
WHERE sh_times_calendar_hier.level_name = 'CALENDAR_YEAR'
AND sh_products_hier.member_name  = 'Electronics'
AND sh_customers_hier.level_name = 'REGION'
ORDER BY sh_times_calendar_hier.hier_order,
  amount_sold desc;

Module 9
Questions or Comments?
Get in touch at william.endress@oracle.com.

Additional Information

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