26 Analytic View Objects

Example 26-1 Creating a Simple Analytic View

This analytic view uses the TIME_HIER hierarchy and the SALE_FACT table. It contains a single measure, SALES.

CREATE OR REPLACE ANALYTIC VIEW sales_av
USING sales_fact                      -- Refers to the SALES_FACT table
DIMENSION BY                          -- List of attribute dimensions
  (time_attr_dim                      -- TIME_ATTR_DIM attribute dimension
    KEY month_id REFERENCES month_id  -- Dimension KEY joins to fact column
    HIERARCHIES (                     -- List of hierarchies that use 
      time_hier DEFAULT))             -- the attribute dimension
MEASURES                              -- List of measures
 (sales FACT sales)                   -- SALES measure references SALES column
DEFAULT MEASURE SALES;                -- Default measure of the analytic view

A query that selects from an analytic view that does not include filters has the potential of returning large numbers of rows. However, in this query, the SALES_AV analytic view includes a single hierarchy that returns only returns 86 rows.

SELECT *
  FROM sales_av HIERARCHIES(time_hier)
  ORDER BY time_hier.hier_order;

This is a excerpt of the returned values.

Example 26-2 Adding a Base Measure to an Analytic View

CREATE OR REPLACE ANALYTIC VIEW sales_av
USING sales_fact
DIMENSION BY
  (time_attr_dim
    KEY month_id REFERENCES month_id
    HIERARCHIES (
      time_hier DEFAULT))
MEASURES
 (sales FACT sales,
  units FACT units)                   -- Add the UNITS base measure
DEFAULT MEASURE SALES;

Because a query of the analytic view could return a great many rows, a query typically uses filters to limit the results. In the WHERE clause, this query filters the time periods to those in the YEAR level, so it returns only SALES and UNITS data at that level..

SELECT time_hier.member_name as TIME,
 sales,
 units
FROM
 sales_av HIERARCHIES(time_hier)
WHERE time_hier.level_name = 'YEAR'
ORDER BY time_hier.hier_order;

These are the returned values.

Example 26-3 Adding Hierarchies to an Analytic View

This example adds attribute dimensions and hierarchies to the DIMENSION BY list of the analytic view.

CREATE OR REPLACE ANALYTIC VIEW sales_av
USING 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,
  units FACT units
  )
DEFAULT MEASURE SALES;

The following query adds the PRODUCT_HIER and GEOGRAPHY_HIER hierarchies to the HIERARCHIES phrase of the FROM clause.

SELECT time_hier.member_name AS Time,
 product_hier.member_name AS Product,
 geography_hier.member_name AS Geography,
 sales,
 units
FROM
 sales_av HIERARCHIES (time_hier, product_hier, geography_hier)
WHERE time_hier.level_name in ('YEAR')
  AND product_hier.level_name in ('DEPARTMENT')
  AND geography_hier.level_name in ('REGION')
ORDER BY time_hier.hier_order,
  product_hier.hier_order,
  geography_hier.hier_order;

The query returns 50 rows. The following image shows only the first 20 rows.

Example 26-4 Adding a LAG Expression

CREATE OR REPLACE ANALYTIC VIEW sales_av
USING 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,
  units FACT units,
  sales_prior_period AS       -- Add a calculated measure.
    (LAG(sales) OVER (HIERARCHY time_hier OFFSET 1))
  )
DEFAULT MEASURE SALES;

Select the SALES and SALES_PRIOR_PERIOD measures at the YEAR and QUARTER levels.

SELECT time_hier.member_name as TIME,
 sales,
 sales_prior_period
FROM
 sales_av HIERARCHIES(time_hier)
WHERE time_hier.level_name IN ('YEAR','QUARTER')
ORDER BY time_hier.hier_order;

In this excerpt from the query results, note that the LAG expression returns prior periods within the same level.

Example 26-5 Using SHARE OF Expressions

This example adds calculated measures that use SHARE OF operations to the SALES_AV analytic view.

CREATE OR REPLACE ANALYTIC VIEW sales_av
USING 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,
  units FACT units,
  -- Share of calculations
 sales_shr_parent_prod AS
   (SHARE_OF(sales HIERARCHY product_hier PARENT)),
 sales_shr_parent_geog AS
   (SHARE_OF(sales HIERARCHY geography_hier PARENT)),
 sales_shr_region AS
   (SHARE_OF(sales HIERARCHY geography_hier LEVEL REGION)) 
  )
DEFAULT MEASURE SALES;

The SALES_SHR_PARENT_PROD measure calculates the ratio of a SALES value at the CATEGORY or DEPARTMENT level to SALES of the parent in the PRODUCT_HIER hierarchy, such as the ratio of SALES for Total Server Computers to Computers.

This query selects SALES and SALES_SHR_PARENT_PROD measure for CY2014 at each level of the PRODUCT_HIER hierarchy.

SELECT time_hier.member_name AS Time,
 product_hier.member_name    AS Product,
 product_hier.level_name     AS Prod_Level,
 sales,
 ROUND(sales_shr_parent_prod,2) AS sales_shr_parent_prod 
FROM
 sales_av HIERARCHIES (time_hier, product_hier)
WHERE time_hier.year_name = 'CY2014'
AND time_hier.level_name = 'YEAR'
ORDER BY product_hier.hier_order;

The results of the query are:

The SALE_SHR_REGION measure calculates the share of SALES at the STATE or COUNTRY levels to SALES at the REGION level, for example, the ratio of SALES for California – US to SALES for North America.

This query returns the values for the SALES and SALES_SHR_REGION measures for year CY2014 and states in the United States.

SELECT time_hier.member_name AS Time,
 geography_hier.member_name  AS Geography,
 geography_hier.level_name   AS Geog_Level,
 sales,
 ROUND(sales_shr_region,2)   AS sales_shr_region 
FROM
 sales_av HIERARCHIES (time_hier, geography_hier)
WHERE time_hier.year_name = 'CY2014'
AND time_hier.level_name = 'YEAR'
AND geography_hier.country_name = 'United States'
AND geography_hier.level_name = 'STATE_PROVINCE'
ORDER BY geography_hier.hier_order;

This is the result of the query.

Example 26-6 Using QUALIFY Expressions

Create the SALES_AV analytic view with the SALES_2011 and SALES_PCT_CHG_2011 measures.

CREATE OR REPLACE ANALYTIC VIEW sales_av
USING 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,
  units FACT units,
 -- Sales for CY2011
 sales_2011 AS
    (QUALIFY (sales, time_hier = year['11'])),
  -- Sales percent change from 2011.
  sales_pct_chg_2011 AS
    ((sales - (QUALIFY (sales, time_hier = year['11']))) /
    (QUALIFY (sales, time_hier = year['11'])))
  )
DEFAULT MEASURE SALES;

Regardless of filters in the query, the SALES_2011 measure always returns data for the year CY2011. The SALES_PCT_CHG_2011 measure calculates the percent difference between the current time period and CY2011.

This query selects SALES, SALES_2011 and SALES_PCT_CHG_2011 at the YEAR and REGION levels.

SELECT time_hier.member_name   AS Time,
 geography_hier.member_name    AS Geography,
 sales,
 sales_2011,
 ROUND(sales_pct_chg_2011,2) as sales_pct_chg_2011
FROM
 sales_av HIERARCHIES (time_hier, geography_hier)
WHERE time_hier.level_name = 'YEAR'
AND geography_hier.level_name = 'REGION'
ORDER BY geography_hier.hier_order,
 time_hier.hier_order;

This is an excerpt from the query results. Note that for each row SALES_2011 returns SALES for CY2011.

Example 26-7 Using the SEASON Attribute

This example first creates an attribute dimension that has SEASON and SEASON_ORDER as attributes. This allows a hierarchy and an analytic view to reuse some metadata of those attributes and to relate the attributes to other levels. For example, SEASON is determined by MONTH values.

-- Create a time attribute dimension with a SEASON attribute.
CREATE OR REPLACE ATTRIBUTE DIMENSION time_attr_dim
DIMENSION TYPE TIME
USING time_dim
ATTRIBUTES
 (year_id,
  year_name,
  year_end_date,
  quarter_id,
  quarter_name,
  quarter_end_date,
  month_id,
  month_name,
  month_long_name,
  month_end_date,
  season,
  season_order)
LEVEL month
  LEVEL TYPE MONTHS
  KEY month_id
  MEMBER NAME month_name
  MEMBER CAPTION month_name
  MEMBER DESCRIPTION month_long_name
  ORDER BY month_end_date
  DETERMINES (quarter_id, season, season_order)
LEVEL quarter
  LEVEL TYPE QUARTERS
  KEY quarter_id
  MEMBER NAME quarter_name
  MEMBER CAPTION quarter_name
  MEMBER DESCRIPTION quarter_name
  ORDER BY quarter_end_date
  DETERMINES (year_id)
LEVEL year
  LEVEL TYPE YEARS
  KEY year_id
  MEMBER NAME year_name
  MEMBER CAPTION year_name
  MEMBER DESCRIPTION year_name
  ORDER BY year_end_date
LEVEL season
  LEVEL TYPE QUARTERS
  KEY season
  MEMBER NAME season
  MEMBER CAPTION season
  MEMBER DESCRIPTION season
  ORDER BY season_order;

Create a hierarchy in which MONTH is a child of SEASON.

CREATE OR REPLACE HIERARCHY time_season_hier
USING time_attr_dim 
  (month CHILD OF
   season);

Select data from the TIME_SEASON_HIER hierarchy.

SELECT member_name,
  member_unique_name,
  level_name,
  hier_order
FROM time_season_hier
ORDER BY hier_order;

In the results of the query, the TIME_SEASON_HIER hierarchy returns rows for the ALL level, SEASONS, and MONTHS. This image captures the first twenty of the rows returned.

The example next creates an analytic view that provides aggregate data for SEASON.

CREATE OR REPLACE ANALYTIC VIEW sales_av
USING sales_fact
DIMENSION BY
  (time_attr_dim
    KEY month_id REFERENCES month_id
    HIERARCHIES (
      time_hier DEFAULT,
      time_season_hier),
   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,
  units FACT units
  )
DEFAULT MEASURE SALES;

You can now select SALES by YEAR and SEASON directly from the analytic view. This query selects from the TIME_HIER and TIME_SEASON_HIER hierarchies at the YEAR and SEASON levels.

SELECT  time_hier.member_name      AS Time, 
 time_season_hier.member_name      AS Season,
 ROUND(sales)                      AS Sales
FROM sales_av HIERARCHIES (time_hier, time_season_hier)
WHERE time_hier.level_name = 'YEAR'
  AND time_season_hier.level_name = 'SEASON'
ORDER BY time_hier.hier_order, 
  time_season_hier.hier_order;

This excerpt from the query results shows the first twelve rows returned.