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

  • 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;

  • Additional Information