Creating Time Series Calculations in Analytic Views

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

  • Additional Information