-- william.endress@oracle.com

-- This script requires Oracle 12.2.



CREATE OR REPLACE ATTRIBUTE DIMENSION sh_times_attr_dim
  -- Classifications are metadata that can be used by applications.  They are
  -- not required by the database to create or query an analytic view object.
  CLASSIFICATION caption VALUE 'Time'
  CLASSIFICATION description VALUE 'Time'
-- The attribute dimension references the sh.times table.
USING sh.times
-- In this example, a list of columns from the sh.times table that will be used
-- by hierarchies and analytic views.
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' 
    )
  -- A grouping of values at the same level of aggregation.
  LEVEL day
    CLASSIFICATION caption VALUE 'Day'
    CLASSIFICATION description VALUE 'Day'
     -- Attribute with unique values for days.
     KEY time_id
     -- Can be used as a text description of the KEY attribute.
     MEMBER NAME to_char(time_id)
     -- Another text description.
     MEMBER CAPTION to_char(time_id)
     -- Another text description.
     MEMBER DESCRIPTION to_char(time_id)
     -- Default sort order of the level.
     ORDER BY time_id
     -- A list of attributes that are uniquely determined by the KEY attribute.  That is,
     -- for each value of the KEY attribute there is only one value of the determined 
     -- attribute.
     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(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(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)
  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(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(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
  -- The value of a single, top most hierarchy value. 
  ALL MEMBER NAME 'ALL YEARS'

CREATE OR REPLACE HIERARCHY sh_times_calendar_hier
  CLASSIFICATION caption VALUE 'Calendar Year'
  CLASSIFICATION description VALUE 'Calendar Year'
  -- This hierarchy references the sh_times_attr_dim attribute dimension.
  USING sh_times_attr_dim
     -- A list of levels that make up an aggregation path.
    (day CHILD OF
     calendar_month CHILD OF
     calendar_quarter CHILD OF
     calendar_year)

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)

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 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)

CREATE TABLE sh_customers_dim 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

CREATE OR REPLACE ATTRIBUTE DIMENSION sh_customers_attr_dim
USING sh_customers_dim
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 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)

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 OR REPLACE HIERARCHY sh_channels_hier
  CLASSIFICATION caption VALUE 'Channels'
  CLASSIFICATION description VALUE 'Channels'
USING sh_channels_attr_dim
 (channel CHILD OF
  channel_class)

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)

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 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)

CREATE OR REPLACE ANALYTIC VIEW sh_sales_history_av
 CLASSIFICATION caption VALUE 'Sales History (SH Sample Schema) with CACHE'
 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 FACT amount_sold,
  quantity_sold FACT quantity_sold

  )
DEFAULT MEASURE amount_sold
    CACHE
      -- The list of measures in the MV.
      MEASURE GROUP (
          amount_sold,
          quantity_sold)
        -- Levels that match the GROUP BY clause of the materialized view.
        LEVELS (
          sh_times_calendar_hier.calendar_year,
          sh_products_hier.category,
          sh_customers_hier.country,
          sh_channels_hier.channel_class,
          sh_promotions_hier.category)
        MATERIALIZED

CREATE materialized VIEW sh_sales_history_mv
AS
  SELECT t.calendar_year,
    p1.prod_category,
    g.country_id,
    c.channel_class,
    p2.promo_category,
    SUM(f.amount_sold)   AS measures#amount_sold,
    SUM(f.quantity_sold) AS measures#quantity_sold
  FROM times t,
    products p1,
    sh_customers_dim g,
    channels c,
    promotions p2,
    sales f
  WHERE t.time_id  = f.time_id
  AND p1.prod_id   = f.prod_id
  AND g.cust_id    = f.cust_id
  AND c.channel_id = f.channel_id
  AND p2.promo_id  = f.promo_id
  -- GROUP BY should include columns used as KEY attributes of levels in the
  -- CACHE clause of the anlaytic view
  GROUP BY t.calendar_year,
    p1.prod_category,
    g.country_id,
    c.channel_class,
    p2.promo_category

ALTER MATERIALIZED VIEW sh_sales_history_mv ENABLE QUERY REWRITE

SELECT mview_name, rewrite_enabled, rewrite_capability, staleness FROM user_mviews

ALTER SESSION SET query_rewrite_integrity = trusted

-- This query will rewrite to the MV

SELECT sh_times_calendar_hier.member_name AS SH_TIMES_CALENDAR_HIER,
  sh_products_hier.member_name            AS SH_PRODUCTS_HIER,
  sh_customers_hier.member_name           AS SH_CUSTOMERS_HIER,
  sh_channels_hier.member_name            AS SH_CHANNELS_HIER,
  sh_promotions_hier.member_name          AS SH_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

DROP hierarchy sh_channels_hier

DROP hierarchy sh_customers_hier

DROP hierarchy sh_products_hier

DROP hierarchy sh_promotions_hier

DROP hierarchy sh_times_calendar_hier

DROP hierarchy sh_times_fiscal_hier

DROP attribute dimension sh_channels_attr_dim

DROP attribute dimension sh_customers_attr_dim

DROP attribute dimension sh_products_attr_dim

DROP attribute dimension sh_promotions_attr_dim

DROP attribute dimension sh_times_attr_dim

DROP analytic view sh_sales_history_av

DROP materialized view sh_sales_history_mv

DROP TABLE sh_customers_dim