Creating Time Series Calculations in Analytic Views

View All Tutorials Login to Run Tutorial

Tutorial Creating Time Series Calculations in Analytic Views
Description This tutorial provides examples for creating time series calculations such as prior periods, year ago and period-to-dates. These types of calculations can be used with any ordered list of hierarchy values (for example calendar or fiscal hierarchies, sequential tests or steps in a manufacturing process).
Tags analytic view, time series, LEAD, LAG, SHARE_OF
Area SQL General
Contributor William Endress (Oracle)
Created Wednesday January 04, 2017
Modules 12

Prerequisite SQL

CREATE OR REPLACE ATTRIBUTE DIMENSION time_attr_dim
USING av.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)
LEVEL month
  KEY month_id
  MEMBER NAME month_name
  MEMBER CAPTION month_name
  MEMBER DESCRIPTION month_long_name
  ORDER BY month_end_date
  DETERMINES (month_end_date,
    quarter_id)
LEVEL quarter
  KEY quarter_id
  MEMBER NAME quarter_name
  MEMBER CAPTION quarter_name
  MEMBER DESCRIPTION quarter_name
  ORDER BY quarter_end_date
  DETERMINES (quarter_end_date,
    year_id)
LEVEL year
  KEY year_id
  MEMBER NAME year_name
  MEMBER CAPTION year_name
  MEMBER DESCRIPTION year_name
  ORDER BY year_end_date
  DETERMINES (year_end_date)
ALL MEMBER NAME 'All';

CREATE OR REPLACE HIERARCHY time_hier
USING time_attr_dim
 (month CHILD OF
  quarter CHILD OF
  year);

CREATE OR REPLACE ATTRIBUTE DIMENSION product_attr_dim
USING av.product_dim 
ATTRIBUTES
 (department_id,
  department_name,
  category_id,
  category_name)
LEVEL DEPARTMENT
  KEY department_id
  MEMBER NAME department_name
  MEMBER CAPTION department_name
  ORDER BY department_name
LEVEL CATEGORY
  KEY category_id
  MEMBER NAME category_name
  MEMBER CAPTION category_name
  ORDER BY category_name
  DETERMINES(department_id)
ALL MEMBER NAME 'ALL PRODUCTS';

CREATE OR REPLACE HIERARCHY product_hier
USING product_attr_dim
 (CATEGORY
  CHILD OF department);

CREATE OR REPLACE ATTRIBUTE DIMENSION geography_attr_dim
USING av.geography_dim
ATTRIBUTES
 (region_id,
  region_name,
  country_id,
  country_name, 
  state_province_id,
  state_province_name)
LEVEL REGION
  KEY region_id
  MEMBER NAME region_name
  MEMBER CAPTION region_name
  ORDER BY region_name
LEVEL COUNTRY
  KEY country_id
  MEMBER NAME country_name
  MEMBER CAPTION country_name
  ORDER BY country_name
  DETERMINES(region_id)
LEVEL STATE_PROVINCE
  KEY state_province_id
  MEMBER NAME state_province_name
  MEMBER CAPTION state_province_name
  ORDER BY state_province_name
  DETERMINES(country_id)
ALL MEMBER NAME 'ALL CUSTOMERS';

CREATE OR REPLACE HIERARCHY geography_hier
USING geography_attr_dim
 (state_province
  CHILD OF country
  CHILD OF region);

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

Analytic views provide a variety of expressions that can be used to define time series calculations. Time series calculations allow you to compare the measure values between different time periods or aggregate values across time periods. Common examples include:
- Year ago
- Change from quarter ago
- Percent change from month ago
- Year-to-date
- Percent change this year-to-date compared to the same period last year
The primary building blocks for time series calculations include the following expressions:
- LAG
- LAG_DIFF
- LAG_DIFF_PERCENT
- LEAD
- LEAD_DIFF
- LEAD_DIFF_PERCENT
- QUALIFY
- An aggregation operator and a windowing clause.
Module 2
How Time Series Calculations Work
Most time series calculations are created using the LEAD and LAG functions or an aggregation operator and a windowing clause. Time series calculations use the order of values in a hierarchy (as determined by the ORDER BY property of a level) to locate prior or feature values. That is, LEAD and LAG expressions locate other hierarchy values based on the relative position in the hierarchy. For example, a prior period calculation would use an expression such as LAG 1 ... WITHIN LEVEL which locates the hierarchy value 1 position back within the same level.

A year ago calculation would use an expression such as LAG 1 ... ACROSS ANCESTOR AT LEVEL YEAR which locates the ancestor value at YEAR level, then the YEAR value 1 position back and then the value at the current level which has the same position relative to the year. For example to locate the year ago value for August 2015 the expression would first locate the year (2015), the year value one position back (2014) and then the 8th month of 2014 (August 2015).

A 12 period moving average calculation would use an expression such as AVG(sales) ... BETWEEN 11 PRECEDING AND CURRENT MEMBER which locates each of the values back to the 11th value and averages these values plus the current value.

Because the LAG and LEAD expressions and the windowing clause use the relative order by value rather than actual dates these expressions can be used with any hierarchy.

The order of values in the TIMES_DIM table can be viewed with the following query. The DENSE_RANK function to return the order number of hierarchy values within the level, a parent or an ancestor.
```
SELECT year_name,
  dense_rank() over (ORDER BY year_name) AS year_num,
  quarter_name,
  dense_rank() over (PARTITION BY year_name ORDER BY quarter_name) AS quarter_num,
  month_name,
  month_end_date,
  dense_rank() over (partition BY quarter_name ORDER BY month_end_date) AS month_of_quarter,
  dense_rank() over (partition BY year_name ORDER BY month_end_date)    AS month_of_year
FROM av.time_dim
ORDER BY year_name,
  quarter_name,
  month_end_date;
```
To return the year ago period for Aug-15 find the year of Aug-15 (CY2015, year_num = 5), the previous year (CY2014, year_num = 4) and the 8th month of that year (month_of_year = 8). Using this approach, the year ago value for Aug-15 is Aug-14.

What if there was a gap in the calendar and Jul-14 did not exist? The year ago value for Aug-15 would be Sep-14 because that would be the 8th month of 2014. This can be seen using the following query which filters out 'Jul-14' in the WITH clause.
```
WITH time_view AS
  ( SELECT * FROM av.time_dim WHERE MONTH_NAME != 'Jul-14'
  )
SELECT year_name,
  dense_rank() over (ORDER BY year_name
) AS year_num,
  quarter_name,
  dense_rank() over (PARTITION BY year_name ORDER BY quarter_name) AS quarter_num,
  month_name,
  month_end_date,
  dense_rank() over (partition BY quarter_name ORDER BY month_end_date) AS month_of_quarter,
  dense_rank() over (partition BY year_name ORDER BY month_end_date)    AS month_of_year
FROM time_view
ORDER BY year_name,
  quarter_name,
  month_end_date;
```
Time series calculations automatically 'reach out' to get data that is outside the WHERE clause of the query. For example if a query selects data WHERE year_name = '2015', a calculation such as Sales Change from Year Ago will automatically access Sales data for 2014 to calculate the difference between 2014 and 2015.

Module 3

Prior and Future Periods Within Level

You can return prior or future values using the LAG and LEAD expressions. LAG_DIFF, LAG_DIFF_PERCENT, LEAD_DIFF and LEAD_DIFF_PERCENT expressions returns the difference and percent difference between the current member and the prior or future members.

The following example returns sales, change in sales and percent change in sales. OFFSET indicates the number of prior or future periods.

CREATE OR REPLACE ANALYTIC VIEW sales_av
USING av.sales_fact
DIMENSION BY  ( 
   time_attr_dim KEY month_id REFERENCES month_id HIERARCHIES (time_hier DEFAULT),
   product_attr_dim  KEY category_id REFERENCES category_id HIERARCHIES (product_hier DEFAULT),
   geography_attr_dim KEY state_province_id REFERENCES state_province_id HIERARCHIES (geography_hier DEFAULT)
   )
MEASURES
 (sales FACT sales,
  sales_prior_period AS (LAG(sales) OVER (HIERARCHY time_hier OFFSET 1 WITHIN LEVEL)),
  sales_change_prior_period AS (LAG_DIFF(sales) OVER (HIERARCHY time_hier OFFSET 1 WITHIN LEVEL)),
  sales_percent_change_prior_period AS (LAG_DIFF_PERCENT(sales) OVER (HIERARCHY time_hier OFFSET 1 WITHIN LEVEL))
  )
DEFAULT MEASURE SALES;

Prior and future periods 'within level' means that the LAG or LAG function returns the prior or future value within the same level as the current hierarchy value regardless of whether a value crosses the boundary of a parent or ancestor. For example, the prior period of Jan 2016 is Dec 2015. The LAG and LEAD expressions return values at any level of the hierarchy. For example, the same expression would return CY2015 as the prior period to year CY2016.

The following query selects sales and prior period measures at the Year level.

SELECT
  time_hier.member_name      AS time_hier,
  geography_hier.member_name AS geography_hier,
  sales,
  sales_prior_period,
  sales_change_prior_period,
  ROUND(sales_percent_change_prior_period,2) AS sales_percent_change_prior_period
FROM
  sales_av HIERARCHIES (
    time_hier,
    geography_hier)
WHERE
  time_hier.level_name = 'YEAR'
  AND geography_hier.member_name = 'South America'
ORDER BY
  geography_hier.hier_order,
  time_hier.hier_order;

The next query select sales and prior period measures at the Quarter level. Note that the prior periods for 4th quarters are the 1st quarter of the prior year.

SELECT
  time_hier.member_name      AS time_hier,
  geography_hier.member_name AS geography_hier,
  sales,
  sales_prior_period,
  sales_change_prior_period,
  ROUND(sales_percent_change_prior_period,2) AS sales_percent_change_prior_period
FROM
  sales_av HIERARCHIES (
    time_hier,
    geography_hier)
WHERE
  time_hier.level_name = 'QUARTER'
  AND geography_hier.member_name = 'South America'
ORDER BY
  geography_hier.hier_order,
  time_hier.hier_order;

The next query selects data for both the Quarter and Year levels.

SELECT
  time_hier.member_name      AS time_hier,
  geography_hier.member_name AS geography_hier,
  sales,
  sales_prior_period,
  sales_change_prior_period,
  ROUND(sales_percent_change_prior_period,2) AS sales_percent_change_prior_period
FROM
  sales_av HIERARCHIES (
    time_hier,
    geography_hier)
WHERE
  time_hier.level_name in ('QUARTER','YEAR')
  AND geography_hier.member_name = 'South America'
ORDER BY
  geography_hier.hier_order,
  time_hier.hier_order;

If WITHIN LEVEL is omitted, LEAD and LAG default to WITHIN LEVEL. This can be seen in the following example.

CREATE OR REPLACE ANALYTIC VIEW sales_av
USING av.sales_fact
DIMENSION BY  ( 
   time_attr_dim KEY month_id REFERENCES month_id HIERARCHIES (time_hier DEFAULT),
   product_attr_dim  KEY category_id REFERENCES category_id HIERARCHIES (product_hier DEFAULT),
   geography_attr_dim KEY state_province_id REFERENCES state_province_id HIERARCHIES (geography_hier DEFAULT)
   )
MEASURES
 (sales FACT sales,
  sales_prior_period_1 AS (LAG(sales) OVER (HIERARCHY time_hier OFFSET 1 WITHIN LEVEL)),
  sales_prior_period_2 AS (LAG(sales) OVER (HIERARCHY time_hier OFFSET 1))
  )
DEFAULT MEASURE SALES;

Note that SALES_PRIOR_PERIOD_1 and SALES_PRIOR_PERIOD_2 return the same values.

SELECT
  time_hier.member_name      AS time_hier,
  geography_hier.member_name AS geography_hier,
  sales,
  sales_prior_period_1,
  sales_prior_period_2
FROM
  sales_av HIERARCHIES (
    time_hier,
    geography_hier)
WHERE
  time_hier.level_name = 'YEAR'
  AND geography_hier.member_name = 'South America'
ORDER BY
  geography_hier.hier_order,
  time_hier.hier_order;

Module 4

Prior and Future Values Within Parent

The WITHIN PARENT keyword restricts the hierarchy values considered by LEAD and LAG to values that share the same parent value. For example the LAG ... WITHIN PARENT of Feb-16 will return Jan-16 because both Feb-16 and Jan-16 have the parent Q1CY2016. LAG ... WITHIN PARENT of Jan-16 will return NULL because Jan-16 is the first month of Q1CY2016.

Create an analytic view with sales prior period, change from prior period and percent change from period parent WITHIN PARENT.

CREATE OR REPLACE ANALYTIC VIEW sales_av
USING av.sales_fact
DIMENSION BY  ( 
   time_attr_dim KEY month_id REFERENCES month_id HIERARCHIES (time_hier DEFAULT),
   product_attr_dim  KEY category_id REFERENCES category_id HIERARCHIES (product_hier DEFAULT),
   geography_attr_dim KEY state_province_id REFERENCES state_province_id HIERARCHIES (geography_hier DEFAULT)
   )
MEASURES
 (sales FACT sales,
  sales_prior_period AS (LAG(sales) OVER (HIERARCHY time_hier OFFSET 1 WITHIN PARENT)),
  sales_change_prior_period AS (LAG_DIFF(sales) OVER (HIERARCHY time_hier OFFSET 1 WITHIN PARENT)),
  sales_percent_change_prior_period AS (LAG_DIFF_PERCENT(sales) OVER (HIERARCHY time_hier OFFSET 1 WITHIN PARENT))
  )
DEFAULT MEASURE SALES;

Run the following query and note that each of the Q1 periods return null values.

SELECT
  time_hier.year_name,
  time_hier.member_name      AS time_hier,
  geography_hier.member_name AS geography_hier,
  sales,
  sales_change_prior_period,
  ROUND(sales_percent_change_prior_period,2)
FROM
  sales_av HIERARCHIES (
    time_hier,
    geography_hier)
WHERE
  time_hier.level_name = 'QUARTER'
  AND geography_hier.member_name = 'Europe'
ORDER BY
  geography_hier.hier_order,
  time_hier.hier_order;

Create the analytic view with LAG ...WITHIN LEVEL and LAG ... WITHIN PARENT.

CREATE OR REPLACE ANALYTIC VIEW sales_av
USING av.sales_fact
DIMENSION BY  ( 
   time_attr_dim KEY month_id REFERENCES month_id HIERARCHIES (time_hier DEFAULT),
   product_attr_dim  KEY category_id REFERENCES category_id HIERARCHIES (product_hier DEFAULT),
   geography_attr_dim KEY state_province_id REFERENCES state_province_id HIERARCHIES (geography_hier DEFAULT)
   )
MEASURES
 (sales FACT sales,
  sales_lag_level AS (LAG(sales) OVER (HIERARCHY time_hier OFFSET 1 WITHIN LEVEL)),
  sales_lag_parent AS (LAG(sales) OVER (HIERARCHY time_hier OFFSET 1 WITHIN PARENT))
  )
DEFAULT MEASURE SALES;

Query both SALES_LAG_LEVEL and SALES_LAG_PARENT and observe the difference.

SELECT
  time_hier.year_name,
  time_hier.member_name      AS time_hier,
  geography_hier.member_name AS geography_hier,
  sales,
  sales_lag_level,
  sales_lag_parent
FROM
  sales_av HIERARCHIES (
    time_hier,
    geography_hier)
WHERE
  time_hier.level_name = 'QUARTER'
  AND geography_hier.member_name = 'Europe'
ORDER BY
  geography_hier.hier_order,
  time_hier.hier_order;

Create the analytic view with both LEAD and LAG.

CREATE OR REPLACE ANALYTIC VIEW sales_av
USING av.sales_fact
DIMENSION BY  ( 
   time_attr_dim KEY month_id REFERENCES month_id HIERARCHIES (time_hier DEFAULT),
   product_attr_dim  KEY category_id REFERENCES category_id HIERARCHIES (product_hier DEFAULT),
   geography_attr_dim KEY state_province_id REFERENCES state_province_id HIERARCHIES (geography_hier DEFAULT)
   )
MEASURES
 (sales FACT sales,
  sales_prior_period AS (LAG(sales) OVER (HIERARCHY time_hier OFFSET 1 WITHIN PARENT)),
  sales_next_period AS (LEAD(sales) OVER (HIERARCHY time_hier OFFSET 1 WITHIN PARENT))
  )
DEFAULT MEASURE SALES;

Note that the last quarter of a year returns NULL for the SALES_NEXT_PERIOD measure.

SELECT
  time_hier.member_name      AS time_hier,
  geography_hier.member_name AS geography_hier,
  sales,
  sales_prior_period,
  sales_next_period
FROM
  sales_av HIERARCHIES (
    time_hier,
    geography_hier)
WHERE
  time_hier.level_name = 'QUARTER'
  AND geography_hier.member_name = 'North America'
ORDER BY
  geography_hier.hier_order,
  time_hier.hier_order;

Module 5

Lead and Lag within Ancestor (e.g., Year Ago)

LEAD and LAG within ancestor returns values for the same period within a future or prior ancestor value. This type of calculation is use to create measures such as Sales Year Ago. This calculation works by first finding the lead or lag of the ancestor value and then finding the value within that ancestor that has the same position as the current member. For example the same period year ago for February 2001 is February 2000.

The following analytic view includes Sales Year Ago, Sales Change Year Ago and Sales Percent Change Year Ago.

CREATE OR REPLACE ANALYTIC VIEW sales_av
USING av.sales_fact
DIMENSION BY  ( 
   time_attr_dim KEY month_id REFERENCES month_id HIERARCHIES (time_hier DEFAULT),
   product_attr_dim  KEY category_id REFERENCES category_id HIERARCHIES (product_hier DEFAULT),
   geography_attr_dim KEY state_province_id REFERENCES state_province_id HIERARCHIES (geography_hier DEFAULT)
   )
MEASURES
 (sales FACT sales,
  sales_year_ago AS (LAG(sales) OVER (HIERARCHY time_hier OFFSET 1 ACROSS ANCESTOR AT LEVEL year)),
  sales_change_year_ago AS (LAG_DIFF(sales) OVER (HIERARCHY time_hier OFFSET 1 ACROSS ANCESTOR AT LEVEL year)),
  sales_percent_change_year_ago AS (LAG_DIFF_PERCENT(sales) OVER (HIERARCHY time_hier OFFSET 1 ACROSS ANCESTOR AT LEVEL year))
  )
DEFAULT MEASURE SALES;

The following query returns Sales Year Ago measures at the Quarter level.

SELECT
  time_hier.year_name,
  time_hier.member_name      AS time_hier,
  geography_hier.member_name AS geography_hier,
  sales,
  sales_year_ago,
  sales_change_year_ago,
  ROUND(sales_percent_change_year_ago,2)
FROM
  sales_av HIERARCHIES (
    time_hier,
    geography_hier)
WHERE
  time_hier.level_name = 'QUARTER'
  AND geography_hier.member_name = 'South America'
ORDER BY
  geography_hier.hier_order,
  time_hier.hier_order;

The same period ancestor ago calculations can return data for any hierarchy value at or below the ancestor level. In this example, Month level data.

SELECT
  time_hier.year_name,
  time_hier.member_name      AS time_hier,
  geography_hier.member_name AS geography_hier,
  sales,
  sales_year_ago,
  sales_change_year_ago,
  ROUND(sales_percent_change_year_ago,2)
FROM
  sales_av HIERARCHIES (
    time_hier,
    geography_hier)
WHERE
  time_hier.level_name = 'MONTH'
  AND geography_hier.member_name = 'South America'
ORDER BY
  geography_hier.hier_order,
  time_hier.hier_order;

The following example adds Quarter Ago measures

CREATE OR REPLACE ANALYTIC VIEW sales_av
USING av.sales_fact
DIMENSION BY  ( 
   time_attr_dim KEY month_id REFERENCES month_id HIERARCHIES (time_hier DEFAULT),
   product_attr_dim  KEY category_id REFERENCES category_id HIERARCHIES (product_hier DEFAULT),
   geography_attr_dim KEY state_province_id REFERENCES state_province_id HIERARCHIES (geography_hier DEFAULT)
   )
MEASURES
 (sales FACT sales,
  sales_year_ago AS (LAG(sales) OVER (HIERARCHY time_hier OFFSET 1 ACROSS ANCESTOR AT LEVEL year)),
  sales_change_year_ago AS (LAG_DIFF(sales) OVER (HIERARCHY time_hier OFFSET 1 ACROSS ANCESTOR AT LEVEL year)),
  sales_percent_change_year_ago AS (LAG_DIFF_PERCENT(sales) OVER (HIERARCHY time_hier OFFSET 1 ACROSS ANCESTOR AT LEVEL year)),
  sales_qtr_ago AS (LAG(sales) OVER (HIERARCHY time_hier OFFSET 1 ACROSS ANCESTOR AT LEVEL quarter)),
  sales_change_qtr_ago AS (LAG_DIFF(sales) OVER (HIERARCHY time_hier OFFSET 1 ACROSS ANCESTOR AT LEVEL quarter)),
  sales_percent_change_qtr_ago AS (LAG_DIFF_PERCENT(sales) OVER (HIERARCHY time_hier OFFSET 1 ACROSS ANCESTOR AT LEVEL quarter))
  )
DEFAULT MEASURE SALES;

The following query selects all measures each level in CY2014 and CY2015.

SELECT
  time_hier.member_name      AS time_hier,
  geography_hier.member_name AS geography_hier,
  sales,
  sales_qtr_ago,
  sales_change_qtr_ago,
  ROUND(sales_percent_change_qtr_ago,2)
  sales_year_ago,
  sales_change_year_ago,
  ROUND(sales_percent_change_year_ago,2)
FROM
  sales_av HIERARCHIES (
    time_hier,
    geography_hier)
WHERE
  time_hier.level_name in ('MONTH','QUARTER','YEAR')
  AND year_name in ('CY2014','CY2015')
  AND geography_hier.member_name = 'South America'
ORDER BY
  geography_hier.hier_order,
  time_hier.hier_order;

Module 6

Period-to-Date (e.g,, Sales Year-to-Date)

Period-to-date calculations aggregate data from the beginning or ending value of a parent or ancestor to the current value. For example Sales Year-to-Date and Sales Quarter-to-Date. Period-to-date calculations are created using a aggregation operator and a windowing clause

The following example creates a Sales Year-to-Date measure.

CREATE OR REPLACE ANALYTIC VIEW sales_av
USING av.sales_fact
DIMENSION BY  ( 
   time_attr_dim KEY month_id REFERENCES month_id HIERARCHIES (time_hier DEFAULT),
   product_attr_dim  KEY category_id REFERENCES category_id HIERARCHIES (product_hier DEFAULT),
   geography_attr_dim KEY state_province_id REFERENCES state_province_id HIERARCHIES (geography_hier DEFAULT)
   )
MEASURES
 (sales FACT sales,
  sales_year_to_date AS (SUM(sales) OVER (HIERARCHY time_hier BETWEEN UNBOUNDED PRECEDING AND CURRENT MEMBER WITHIN ANCESTOR AT LEVEL YEAR))
  )
DEFAULT MEASURE SALES;

Select Sales and Sales Year-to-Date.

SELECT
  time_hier.year_name        AS year_name,
  time_hier.member_name      AS time_hier,
  geography_hier.member_name AS geography_hier,
  sales,
  sales_year_to_date
FROM
  sales_av HIERARCHIES (
    time_hier,
    geography_hier)
WHERE
  time_hier.level_name in ('QUARTER')
  AND geography_hier.member_name = 'North America'
ORDER BY
  geography_hier.hier_order,
  time_hier.hier_order;

To create Sales Quarter-to-Date, just change the the level from Year to Quarter.

CREATE OR REPLACE ANALYTIC VIEW sales_av
USING av.sales_fact
DIMENSION BY  ( 
   time_attr_dim KEY month_id REFERENCES month_id HIERARCHIES (time_hier DEFAULT),
   product_attr_dim  KEY category_id REFERENCES category_id HIERARCHIES (product_hier DEFAULT),
   geography_attr_dim KEY state_province_id REFERENCES state_province_id HIERARCHIES (geography_hier DEFAULT)
   )
MEASURES
 (sales FACT sales,
  sales_quarter_to_date AS (SUM(sales) OVER (HIERARCHY time_hier BETWEEN UNBOUNDED PRECEDING AND CURRENT MEMBER WITHIN ANCESTOR AT LEVEL quarter)), 
  sales_year_to_date AS (SUM(sales) OVER (HIERARCHY time_hier BETWEEN UNBOUNDED PRECEDING AND CURRENT MEMBER WITHIN ANCESTOR AT LEVEL year)) 
  )
DEFAULT MEASURE SALES;

Select Sales, Sales Quarter-to-Date and Sales Year-to-Date.

SELECT
  time_hier.year_name        AS year_name,
  time_hier.member_name      AS time_hier,
  geography_hier.member_name AS geography_hier,
  sales,
  sales_quarter_to_date,
  sales_year_to_date
FROM
  sales_av HIERARCHIES (
    time_hier,
    geography_hier)
WHERE
  time_hier.level_name in ('MONTH')
  and time_hier.year_name in ('CY2014','CY2015')
  AND geography_hier.member_name = 'North America'
ORDER BY
  geography_hier.hier_order,
  time_hier.hier_order;

Module 7

Period-to-Date, Ancestor Ago (E.g., Year-to-Date, Year Ago)

By nesting a period-to-date expression within a ancestor ago expression you can create a calculation such as Sales Year-to-Date, Year Ago.

Calculations can be nested by defining the inner calculation as a measure and referencing that calculation in another calculation or be nesting the inner expression in the outer expression.

In the following example, SALES_YEAR_TO_DATE is defined as a measure and the SALES_YEAR_TO_DATE measure is used within the SALES_YTD_YEAR_AGO measure.

CREATE OR REPLACE ANALYTIC VIEW sales_av
USING av.sales_fact
DIMENSION BY  ( 
   time_attr_dim KEY month_id REFERENCES month_id HIERARCHIES (time_hier DEFAULT),
   product_attr_dim  KEY category_id REFERENCES category_id HIERARCHIES (product_hier DEFAULT),
   geography_attr_dim KEY state_province_id REFERENCES state_province_id HIERARCHIES (geography_hier DEFAULT)
   )
MEASURES
 (sales FACT sales,
  sales_year_to_date AS (SUM(sales) OVER (HIERARCHY time_hier BETWEEN UNBOUNDED PRECEDING AND CURRENT MEMBER WITHIN ANCESTOR AT LEVEL YEAR)),
  sales_ytd_year_ago AS (LAG(sales_year_to_date) OVER (HIERARCHY time_hier OFFSET 1 ACROSS ANCESTOR AT LEVEL year))
  )
DEFAULT MEASURE SALES;

Select each of the measures at the Quarter level.

SELECT
  time_hier.year_name        AS year_name,
  time_hier.member_name      AS time_hier,
  geography_hier.member_name AS geography_hier,
  sales,
  sales_year_to_date,
  sales_ytd_year_ago
FROM
  sales_av HIERARCHIES (
    time_hier,
    geography_hier)
WHERE
  time_hier.level_name in ('QUARTER')
  AND geography_hier.member_name = 'North America'
ORDER BY
  geography_hier.hier_order,
  time_hier.hier_order;

The next example creates the Sales YTD Year Ago measure by nesting the Sales Year-to-Date expression in the Year Ago expression.

CREATE OR REPLACE ANALYTIC VIEW sales_av
USING av.sales_fact
DIMENSION BY  ( 
   time_attr_dim KEY month_id REFERENCES month_id HIERARCHIES (time_hier DEFAULT),
   product_attr_dim  KEY category_id REFERENCES category_id HIERARCHIES (product_hier DEFAULT),
   geography_attr_dim KEY state_province_id REFERENCES state_province_id HIERARCHIES (geography_hier DEFAULT)
   )
MEASURES
 (sales FACT sales,
  sales_year_ago AS (LAG(sales) OVER (HIERARCHY time_hier OFFSET 1 ACROSS ANCESTOR AT LEVEL year)),
  sales_ytd_year_ago AS (LAG(
     SUM(sales) OVER (HIERARCHY time_hier BETWEEN UNBOUNDED PRECEDING AND CURRENT MEMBER WITHIN ANCESTOR AT LEVEL YEAR))
   OVER (HIERARCHY time_hier OFFSET 1 ACROSS ANCESTOR AT LEVEL year))
  )
DEFAULT MEASURE SALES;

Select each of the measures at the Quarter level.

SELECT
  time_hier.year_name        AS year_name,
  time_hier.member_name      AS time_hier,
  geography_hier.member_name AS geography_hier,
  sales,
  sales_ytd_year_ago
FROM
  sales_av HIERARCHIES (
    time_hier,
    geography_hier)
WHERE
  time_hier.level_name in ('QUARTER')
  AND geography_hier.member_name = 'North America'
ORDER BY
  geography_hier.hier_order,
  time_hier.hier_order;

Module 8

Period-to-Go (E.g., Remaining Sales Forecast)

Period-to-go calculations can be created by using a windowing clause that aggregates following periods. For example if SALES in the SALES_FACT table is a sales forecast, the remaining forecasted amount could be calculated using BETWEEN CURRENT MEMBER and UNBOUNDED FOLLOWING.

CREATE OR REPLACE ANALYTIC VIEW sales_av
USING av.sales_fact
DIMENSION BY  ( 
   time_attr_dim KEY month_id REFERENCES month_id HIERARCHIES (time_hier DEFAULT),
   product_attr_dim  KEY category_id REFERENCES category_id HIERARCHIES (product_hier DEFAULT),
   geography_attr_dim KEY state_province_id REFERENCES state_province_id HIERARCHIES (geography_hier DEFAULT)
   )
MEASURES
 (sales_forecast FACT sales,
  sales_forcast_to_go_in_quarter AS (SUM(sales_forecast) OVER (HIERARCHY time_hier BETWEEN CURRENT MEMBER AND UNBOUNDED FOLLOWING WITHIN ANCESTOR AT LEVEL quarter)), 
  sales_forcast_to_go_in_year AS (SUM(sales_forecast) OVER (HIERARCHY time_hier BETWEEN CURRENT MEMBER AND UNBOUNDED FOLLOWING WITHIN ANCESTOR AT LEVEL year))
    )
DEFAULT MEASURE sales_forecast;

View the remaining sales forecast for the quarter and year.

SELECT
  time_hier.year_name        AS year_name,
  time_hier.member_name      AS time_hier,
  geography_hier.member_name AS geography_hier,
  sales_forecast,
  sales_forcast_to_go_in_quarter,
  sales_forcast_to_go_in_year
FROM
  sales_av HIERARCHIES (
    time_hier,
    geography_hier)
WHERE
  time_hier.level_name in ('MONTH')
  and time_hier.year_name in ('CY2014','CY2015')
  AND geography_hier.member_name = 'North America'
ORDER BY
  geography_hier.hier_order,
  time_hier.hier_order;

Module 9

Moving Averages and Totals (E.g., Trailing 12 Periods)

Moving averages and totals are calculated with an aggregation operator and windowing clause. The windowing clause can be within level, within parent or within ancestor.

Within Level

The following example creates a moving average and total over the trailing 11 periods plus the current period (for a total of 12 periods). This example is within level.

CREATE OR REPLACE ANALYTIC VIEW sales_av
USING av.sales_fact
DIMENSION BY  ( 
   time_attr_dim KEY month_id REFERENCES month_id HIERARCHIES (time_hier DEFAULT),
   product_attr_dim  KEY category_id REFERENCES category_id HIERARCHIES (product_hier DEFAULT),
   geography_attr_dim KEY state_province_id REFERENCES state_province_id HIERARCHIES (geography_hier DEFAULT)
   )
MEASURES
 (sales FACT sales,
  sales_12_period_moving_total AS (SUM(sales) OVER (HIERARCHY time_hier BETWEEN 11 PRECEDING AND CURRENT MEMBER WITHIN LEVEL)), 
  sales_12_period_moving_avg AS (AVG(sales) OVER (HIERARCHY time_hier BETWEEN 11 PRECEDING AND CURRENT MEMBER WITHIN LEVEL))
    )
DEFAULT MEASURE sales;

If the number of preceding or following values is less than the number of available values, the expression will return the aggregate of the available values (rather than return NULL). For example for the 11 months of CY2011, where 11 preceding periods are not available, the expression is calculated using the available periods.

SELECT
  time_hier.year_name        AS year_name,
  time_hier.member_name      AS time_hier,
  geography_hier.member_name AS geography_hier,
  sales,
  sales_12_period_moving_total,
  ROUND(sales_12_period_moving_avg,2)  AS sales_12_period_moving_avg
FROM
  sales_av HIERARCHIES (
    time_hier,
    geography_hier)
WHERE
  time_hier.level_name in ('MONTH')
  AND geography_hier.member_name = 'North America'
ORDER BY
  geography_hier.hier_order,
  time_hier.hier_order;

Within Parent

A moving aggregate within parent restarts aggregation with each change in parent. For example months restarts with each change of quarter and quarter restarts with each change of year. The following example creates a 3 period moving total and moving average within parent.

CREATE OR REPLACE ANALYTIC VIEW sales_av
USING av.sales_fact
DIMENSION BY  ( 
   time_attr_dim KEY month_id REFERENCES month_id HIERARCHIES (time_hier DEFAULT),
   product_attr_dim  KEY category_id REFERENCES category_id HIERARCHIES (product_hier DEFAULT),
   geography_attr_dim KEY state_province_id REFERENCES state_province_id HIERARCHIES (geography_hier DEFAULT)
   )
MEASURES
 (sales FACT sales,
  sales_3_period_mvg_total_within_parent AS (SUM(sales) OVER (HIERARCHY time_hier BETWEEN 2 PRECEDING AND CURRENT MEMBER WITHIN PARENT)), 
  sales_3_period_mvg_avg_within_parent AS (AVG(sales) OVER (HIERARCHY time_hier BETWEEN 2 PRECEDING AND CURRENT MEMBER WITHIN PARENT))
  )
DEFAULT MEASURE sales;

The following query selects data at the month level. Note that the moving aggregates restarts with each new quarter.

SELECT
  time_hier.year_name        AS year_name,
  time_hier.quarter_name     AS quarter_name,
  time_hier.member_name      AS time_hier,
  geography_hier.member_name AS geography_hier,
  sales,
  sales_3_period_mvg_total_within_parent,
  ROUND(sales_3_period_mvg_avg_within_parent,2) AS sales_3_period_mvg_avg_within_parent
FROM
  sales_av HIERARCHIES (
    time_hier,
    geography_hier)
WHERE
  time_hier.level_name in ('MONTH')
  AND time_hier.year_name in ('CY2014','CY2015')
  AND geography_hier.member_name = 'North America'
ORDER BY
  geography_hier.hier_order,
  time_hier.hier_order;

The next query selects data at the quarter level. Note that the moving aggregates restarts at each year.

SELECT
  time_hier.year_name        AS year_name,
  time_hier.quarter_name     AS quarter_name,
  time_hier.member_name      AS time_hier,
  geography_hier.member_name AS geography_hier,
  sales,
  sales_3_period_mvg_total_within_parent,
  ROUND(sales_3_period_mvg_avg_within_parent,2) AS sales_3_period_mvg_avg_within_parent
FROM
  sales_av HIERARCHIES (
    time_hier,
    geography_hier)
WHERE
  time_hier.level_name in ('QUARTER')
  AND time_hier.year_name in ('CY2013','CY2014','CY2015')
  AND geography_hier.member_name = 'North America'
ORDER BY
  geography_hier.hier_order,
  time_hier.hier_order;

Within Ancestor

A moving aggregate within ancestor restarts aggregates at each change of an ancestor value. For example, aggregation of months or quarters restarts with each new year. The following example creates 3 period moving totals and averages of sales within quarters and years.

CREATE OR REPLACE ANALYTIC VIEW sales_av
USING av.sales_fact
DIMENSION BY  ( 
   time_attr_dim KEY month_id REFERENCES month_id HIERARCHIES (time_hier DEFAULT),
   product_attr_dim  KEY category_id REFERENCES category_id HIERARCHIES (product_hier DEFAULT),
   geography_attr_dim KEY state_province_id REFERENCES state_province_id HIERARCHIES (geography_hier DEFAULT)
   )
MEASURES
 (sales FACT sales,
  sales_3_period_mvg_total_within_quarter AS (SUM(sales) OVER (HIERARCHY time_hier BETWEEN 2 PRECEDING AND CURRENT MEMBER WITHIN ANCESTOR AT LEVEL quarter)), 
  sales_3_period_mvg_avg_within_quarter AS (AVG(sales) OVER (HIERARCHY time_hier BETWEEN 2 PRECEDING AND CURRENT MEMBER WITHIN ANCESTOR AT LEVEL quarter)),
  sales_3_period_mvg_total_within_year AS (SUM(sales) OVER (HIERARCHY time_hier BETWEEN 2 PRECEDING AND CURRENT MEMBER WITHIN ANCESTOR AT LEVEL year)), 
  sales_3_period_mvg_avg_within_year AS (AVG(sales) OVER (HIERARCHY time_hier BETWEEN 2 PRECEDING AND CURRENT MEMBER WITHIN ANCESTOR AT LEVEL year))
    )
DEFAULT MEASURE sales;

The following query selects data at the month level. Notice where aggregation restarts.

SELECT
  time_hier.year_name        AS year_name,
  time_hier.quarter_name     AS quarter_name,
  time_hier.member_name      AS time_hier,
  geography_hier.member_name AS geography_hier,
  sales,
  sales_3_period_mvg_total_within_quarter,
  ROUND(sales_3_period_mvg_avg_within_quarter,0) AS sales_3_period_mvg_avg_within_quarter,
  sales_3_period_mvg_total_within_year,
  ROUND(sales_3_period_mvg_avg_within_year,2) AS sales_3_period_mvg_avg_within_year
FROM
  sales_av HIERARCHIES (
    time_hier,
    geography_hier)
WHERE
  time_hier.level_name in ('MONTH')
  AND time_hier.year_name in ('CY2014','CY2015')
  AND geography_hier.member_name = 'North America'
ORDER BY
  geography_hier.hier_order,
  time_hier.hier_order;

Module 10

Aggregation Operators and Windowing Expressions

Expressions that aggregate using windowing functions (e.g., period-to-date and moving average) support most SQL aggregation operators that accept a single expression as an argument. These aggregation operators include:

APPROX_COUNT_DISTINCT
AVG
COUNT
MAX
MIN
STATS_MODE
STDDEV
SUM
VARIANCE

The following example creates sales year-to_date measures using the SUM, AVG, MIN, MAX and STDDEV aggregation operators.

CREATE OR REPLACE ANALYTIC VIEW sales_av
USING av.sales_fact
DIMENSION BY  ( 
   time_attr_dim KEY month_id REFERENCES month_id HIERARCHIES (time_hier DEFAULT),
   product_attr_dim  KEY category_id REFERENCES category_id HIERARCHIES (product_hier DEFAULT),
   geography_attr_dim KEY state_province_id REFERENCES state_province_id HIERARCHIES (geography_hier DEFAULT)
   )
MEASURES
 (sales FACT sales,
  sum_sales_year_to_date AS (SUM(sales) OVER (HIERARCHY time_hier BETWEEN UNBOUNDED PRECEDING AND CURRENT MEMBER WITHIN ANCESTOR AT LEVEL year)), 
  avg_sales_year_to_date AS (AVG(sales) OVER (HIERARCHY time_hier BETWEEN UNBOUNDED PRECEDING AND CURRENT MEMBER WITHIN ANCESTOR AT LEVEL year)), 
  min_sales_year_to_date AS (MIN(sales) OVER (HIERARCHY time_hier BETWEEN UNBOUNDED PRECEDING AND CURRENT MEMBER WITHIN ANCESTOR AT LEVEL year)), 
  max_sales_year_to_date AS (MAX(sales) OVER (HIERARCHY time_hier BETWEEN UNBOUNDED PRECEDING AND CURRENT MEMBER WITHIN ANCESTOR AT LEVEL year)), 
  stddev_sales_year_to_date AS (STDDEV(sales) OVER (HIERARCHY time_hier BETWEEN UNBOUNDED PRECEDING AND CURRENT MEMBER WITHIN ANCESTOR AT LEVEL year))
    )
DEFAULT MEASURE sales;

Query the data at the month level.

SELECT
  time_hier.year_name        AS year_name,
  time_hier.quarter_name     AS quarter_name,
  time_hier.member_name      AS time_hier,
  geography_hier.member_name AS geography_hier,
  sales,
  sum_sales_year_to_date,
  ROUND(avg_sales_year_to_date,0)  AS avg_sales_year_to_date,
  min_sales_year_to_date,
  max_sales_year_to_date,
  ROUND(stddev_sales_year_to_date,0) AS  stddev_sales_year_to_date
FROM
  sales_av HIERARCHIES (
    time_hier,
    geography_hier)
WHERE
  time_hier.level_name in ('MONTH')
  AND time_hier.year_name in ('CY2014','CY2015')
  AND geography_hier.member_name = 'North America'
ORDER BY
  geography_hier.hier_order,
  time_hier.hier_order;

Another query at the quarter level.

SELECT
  time_hier.year_name        AS year_name,
  time_hier.quarter_name     AS quarter_name,
  time_hier.member_name      AS time_hier,
  geography_hier.member_name AS geography_hier,
  sales,
  sum_sales_year_to_date,
  ROUND(avg_sales_year_to_date,0)  AS avg_sales_year_to_date,
  min_sales_year_to_date,
  max_sales_year_to_date,
  ROUND(stddev_sales_year_to_date,0) AS  stddev_sales_year_to_date
FROM
  sales_av HIERARCHIES (
    time_hier,
    geography_hier)
WHERE
  time_hier.level_name in ('QUARTER')
  AND time_hier.year_name in ('CY2014','CY2015')
  AND geography_hier.member_name = 'North America'
ORDER BY
  geography_hier.hier_order,
  time_hier.hier_order;

Module 11

Index to a Benchmark Time Period (e.g., Index to Year 2011)

Measures that calculate an index (ratio) of a time period to a specific time period can be created using SHARE_OF and the QUALIFY expressions. For example, a measure can be created that calculates the index (ratio) of any time period to the value of Sales for CY2011.

Both expressions reference a specific time period using the key attribute value. Run the following query to find the key attribute value of CY2011.

SELECT DISTINCT year_name, year_id
FROM time_hier
WHERE year_name is not null;

The key attribute value for CY2011 is '11'. The following examples uses both the SHARE_OF and QUALIFY expressions to create an index of sales to CY2011 measure.

CREATE OR REPLACE ANALYTIC VIEW sales_av
USING av.sales_fact
DIMENSION BY  ( 
   time_attr_dim KEY month_id REFERENCES month_id HIERARCHIES (time_hier DEFAULT),
   product_attr_dim  KEY category_id REFERENCES category_id HIERARCHIES (product_hier DEFAULT),
   geography_attr_dim KEY state_province_id REFERENCES state_province_id HIERARCHIES (geography_hier DEFAULT)
   )
MEASURES
 (sales FACT sales,
  share_of_sales_to_cy2011 AS (SHARE_OF(sales HIERARCHY time_hier MEMBER year['11'])),
  sales_qualify_to_cy2011 AS (sales / (QUALIFY(sales, time_hier = year['11'])))
  )
DEFAULT MEASURE sales;

Select the measures by Year in North America.

SELECT
  time_hier.member_name      AS time_hier,
  geography_hier.member_name AS geography_hier,
  sales,
  ROUND(share_of_sales_to_cy2011,2)  AS share_of_sales_to_cy2011,
  ROUND(sales_qualify_to_cy2011,2)   AS sales_qualify_to_cy2011
FROM
  sales_av HIERARCHIES (
  time_hier,
  geography_hier)
WHERE
  time_hier.level_name in ('YEAR')
  AND geography_hier.member_name = 'North America'
ORDER BY
  geography_hier.hier_order,
  time_hier.hier_order;

The percent change is easily calculated by subtracting 1 from either of these expressions.

CREATE OR REPLACE ANALYTIC VIEW sales_av
USING av.sales_fact
DIMENSION BY  ( 
   time_attr_dim KEY month_id REFERENCES month_id HIERARCHIES (time_hier DEFAULT),
   product_attr_dim  KEY category_id REFERENCES category_id HIERARCHIES (product_hier DEFAULT),
   geography_attr_dim KEY state_province_id REFERENCES state_province_id HIERARCHIES (geography_hier DEFAULT)
   )
MEASURES
 (sales FACT sales,
  share_of_sales_to_cy2011_pct_change AS (SHARE_OF(sales HIERARCHY time_hier MEMBER year['11']) -1),
  sales_qualify_to_cy2011_pct_change AS (sales / (QUALIFY(sales, time_hier = year['11'])) - 1)
  )
DEFAULT MEASURE sales;

A query of the percent change measures.

SELECT
  time_hier.member_name      AS time_hier,
  geography_hier.member_name AS geography_hier,
  sales,
  ROUND(share_of_sales_to_cy2011_pct_change,2)  AS share_of_sales_pct_change_from_cy2011,
  ROUND(sales_qualify_to_cy2011_pct_change,2)   AS sales_qualify_percent_change_from_cy2011
FROM
  sales_av HIERARCHIES (
  time_hier,
  geography_hier)
WHERE
  time_hier.level_name in ('YEAR')
  AND geography_hier.member_name = 'North America'
ORDER BY
  geography_hier.hier_order,
  time_hier.hier_order;

Module 12

Comparing Table and Analytic View Queries

As with any other calculated measure, applications merely need to include the calculated measure in the select list of a query selecting from an analytic view. The analytic view will generate SQL needed to execute the query.

In the case of a query selecting from tables, it is up to the application to generate SQL necessary to resolve calculations. A query selecting time series calculations such as sales year ago and sales percent change year ago will require:

Expanding time filters to include the previous year(s).
Partitioned outer joins to account for sparse data.
Aggregating data.
Calculating sales year-to-date and sales year-to-date percent change year ago.
Finally filtering for the desired time periods (that is, eliminating years added to filters for prior years.

Select year, department, region, sales, sales_year_ago, sales_chg_year_ago and sales_pct_chg_year_ago from tables.


WITH
  fact_dense AS
      --
      -- Step 1:  Get the aggregated value of sales for year 2015 and 2014.
      --
      (
        SELECT
          b.year_name,
          b.year_end_date,
          a.department_name,
          a.region_name,
          a.sales
        FROM
          (
            SELECT
              d1.year_name,
              d1.year_end_date,
              d2.department_name,
              d3.region_name,
              SUM(f.sales) sales
            FROM
              av.time_dim d1,
              av.product_dim d2,
              av.geography_dim d3,
              av.sales_fact f
            WHERE
              d1.month_id            = f.month_id
            AND d2.category_id       = f.category_id
            AND d3.state_province_id = f.state_province_id
            --
            -- Filter to years 2015 and 2014.  Find the prior year by subtracting
            -- 12 months from the last day of the 2015.
            --
            AND year_end_date       IN (add_months('31-DEC-15', - 12),'31-DEC-15')
            GROUP BY
              d1.year_name,
              d1.year_end_date,
              d2.department_name,
              d3.region_name
          )
          a
          --
          -- Join to the dimension table using a right outer join to cover the
          -- case where there are where no sales for a particular department and
          -- region in the previous year (else the query is at risk for returning
          -- wrong results).
          --
          PARTITION BY (a.department_name, a.region_name)
        RIGHT OUTER JOIN
          (
            SELECT DISTINCT
              year_name,
              year_end_date
            FROM
              av.time_dim
            WHERE
            --
            -- Filter to years 2015 and 2014.  Find the prior year by subtracting
            -- 12 months from the last day of the 2015.
            --
              year_end_date IN (add_months('31-DEC-15', - 12), '31-DEC-15')
          )
          b
        ON
          (
            a.year_name = b.year_name
          )
      )
--
-- Step 2:  Calculate sales change year ago and sales percentage change year ago.
--
SELECT
  c.region_name,
  c.department_name,
  c.year_name,
  c.sales,
  -- Last year.
  d.sales sales_year_ago,
  -- This year minus last year.
  (c.sales        - d.sales) sales_chg_year_ago,
  -- Pecent change this year vs last year.
  ROUND(((c.sales - d.sales) / d.sales), 3) AS sales_pct_chg_year_ago
FROM
  fact_dense c
  --
  -- Cover the sparse data case with an outer join.
  --
LEFT OUTER JOIN fact_dense d
ON
  (
    add_months(c.year_end_date, - 12)    = d.year_end_date
  AND c.department_name                  = d.department_name
  AND c.region_name                      = d.region_name
  )
WHERE
  -- Filter out CY2014.
  c.year_name      = 'CY2015'
ORDER BY
  c.region_name,
  c.department_name,
  c.year_name;

Create an analytic view with sales, sales_year_ago, sales_chg_year_ago and sales_pct_chg_year_ago measures


CREATE OR REPLACE ANALYTIC VIEW sales_av
USING av.sales_fact
DIMENSION BY  ( 
   time_attr_dim KEY month_id REFERENCES month_id HIERARCHIES (time_hier DEFAULT),
   product_attr_dim  KEY category_id REFERENCES category_id HIERARCHIES (product_hier DEFAULT),
   geography_attr_dim KEY state_province_id REFERENCES state_province_id HIERARCHIES (geography_hier DEFAULT)
   )
MEASURES
 (sales FACT sales,
  sales_year_ago AS (LAG(sales) OVER (HIERARCHY time_hier OFFSET 1 ACROSS ANCESTOR AT LEVEL year)),
  sales_chg_year_ago AS (LAG_DIFF(sales) OVER (HIERARCHY time_hier OFFSET 1 ACROSS ANCESTOR AT LEVEL year)),
  sales_pct_chg_year_ago AS (LAG_DIFF_PERCENT(sales) OVER (HIERARCHY time_hier OFFSET 1 ACROSS ANCESTOR AT LEVEL year))
  )
DEFAULT MEASURE SALES;