Creating Analytic View Calculated Measures Dynamically in SQL Queries (Great for APEX!)

View All Tutorials Login to Run Tutorial

Tutorial Creating Analytic View Calculated Measures Dynamically in SQL Queries (Great for APEX!)
Description Calculated measures can be defined in the analytic view, where they are accessible to any application by selecting the calculation as a column. Calculations can also be defined on-the-fly in a SQL query using the ADD MEASURES clause. This tutorial provides examples for using the ADD MEASURES clause, tips, and a link to a live APEX application that uses ADD_MEASURES.
Tags analytic views, calculated measures, apex
Area SQL Analytics
Contributor William Endress (Oracle)
Created Wednesday February 15, 2023
Modules 8

Prerequisite SQL

CREATE OR REPLACE ATTRIBUTE DIMENSION time_attr_dim
USING av.time_dim
ATTRIBUTES
 (year_id
   CLASSIFICATION caption VALUE 'YEAR_ID'
   CLASSIFICATION description VALUE 'YEAR ID',
  year_name
    CLASSIFICATION caption VALUE 'YEAR_NAME'
    CLASSIFICATION description VALUE 'Year',
  year_end_date
    CLASSIFICATION caption VALUE 'YEAR_END_DATE'
    CLASSIFICATION description VALUE 'Year End Date',
  quarter_id
    CLASSIFICATION caption VALUE 'QUARTER_ID'
    CLASSIFICATION description VALUE 'QUARTER ID',
  quarter_name
    CLASSIFICATION caption VALUE 'QUARTER_NAME'
    CLASSIFICATION description VALUE 'Quarter',
  quarter_end_date
    CLASSIFICATION caption VALUE 'QUARTER_END_DATE'
    CLASSIFICATION description VALUE 'Quarter End Date',
  month_id
    CLASSIFICATION caption VALUE 'MONTH_ID'
    CLASSIFICATION description VALUE 'MONTH ID',
  month_name
    CLASSIFICATION caption VALUE 'MONTH_NAME'
    CLASSIFICATION description VALUE 'Month',
  month_long_name
    CLASSIFICATION caption VALUE 'MONTH_LONG_NAME'
    CLASSIFICATION description VALUE 'Month Long Name',
  month_end_date
    CLASSIFICATION caption VALUE 'MONTH_END_DATE'
    CLASSIFICATION description VALUE 'Month End Date')
LEVEL month
  CLASSIFICATION caption VALUE 'MONTH'
  CLASSIFICATION description VALUE '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
  CLASSIFICATION caption VALUE 'QUARTER'
  CLASSIFICATION description VALUE '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
  CLASSIFICATION caption VALUE 'YEAR'
  CLASSIFICATION description VALUE '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 TIMES';

CREATE OR REPLACE HIERARCHY time_hier
  CLASSIFICATION caption VALUE 'CALENDAR'
  CLASSIFICATION description VALUE 'CALENDAR'
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
    CLASSIFICATION caption VALUE 'DEPARTMENT_ID'
    CLASSIFICATION description VALUE 'DEPARTMENT ID',
  department_name
    CLASSIFICATION caption VALUE 'DEPARTMENT_NAME'
    CLASSIFICATION description VALUE 'Department',
  category_id
    CLASSIFICATION caption VALUE 'CATEGORY_ID'
    CLASSIFICATION description VALUE 'CATEGORY ID',
  category_name
    CLASSIFICATION caption VALUE 'CATEGORY_NAME'
    CLASSIFICATION description VALUE 'Category')
LEVEL DEPARTMENT
  CLASSIFICATION caption VALUE 'DEPARTMENT'
  CLASSIFICATION description VALUE 'Department'
  KEY department_id
  MEMBER NAME department_name
  MEMBER CAPTION department_name
  ORDER BY department_name
LEVEL CATEGORY
  CLASSIFICATION caption VALUE 'CATEGORY'
  CLASSIFICATION description VALUE '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
  CLASSIFICATION caption VALUE 'PRODUCT'
  CLASSIFICATION description VALUE 'Product'
USING product_attr_dim
 (CATEGORY
  CHILD OF department);

CREATE OR REPLACE ATTRIBUTE DIMENSION geography_attr_dim
USING av.geography_dim
ATTRIBUTES
 (region_id
    CLASSIFICATION caption VALUE 'REGION_ID'
    CLASSIFICATION description VALUE 'REGION ID',
  region_name
    CLASSIFICATION caption VALUE 'REGION_NAME'
    CLASSIFICATION description VALUE 'Region',
  country_id
    CLASSIFICATION caption VALUE 'COUNTRY_ID'
    CLASSIFICATION description VALUE 'COUNTRY ID',
  country_name
    CLASSIFICATION caption VALUE 'COUNTRY_NAME'
    CLASSIFICATION description VALUE 'Country',
  state_province_id
    CLASSIFICATION caption VALUE 'STATE_PROVINCE_ID'
    CLASSIFICATION description VALUE 'STATE-PROVINCE ID',
  state_province_name
    CLASSIFICATION caption VALUE 'STATE_PROVINCE_NAME'
    CLASSIFICATION description VALUE 'State-Province')
LEVEL REGION
  CLASSIFICATION caption VALUE 'REGION'
  CLASSIFICATION description VALUE 'Region'
  KEY region_id
  MEMBER NAME region_name
  MEMBER CAPTION region_name
  ORDER BY region_name
LEVEL COUNTRY
  CLASSIFICATION caption VALUE 'COUNTRY'
  CLASSIFICATION description VALUE 'Country'
  KEY country_id
  MEMBER NAME country_name
  MEMBER CAPTION country_name
  ORDER BY country_name
  DETERMINES(region_id)
LEVEL STATE_PROVINCE
  CLASSIFICATION caption VALUE 'STATE_PROVINCE'
  CLASSIFICATION description VALUE 'State-Province'
  KEY state_province_id
  MEMBER NAME state_province_name
  MEMBER CAPTION state_province_name
  ORDER BY state_province_name
  DETERMINES(country_id)
ALL MEMBER NAME 'ALL CUSTOMERS';

CREATE OR REPLACE HIERARCHY geography_hier
  CLASSIFICATION caption VALUE 'GEOGRAPHY'
  CLASSIFICATION description VALUE 'Geography'
USING geography_attr_dim
 (state_province
  CHILD OF country
  CHILD OF region);

CREATE OR REPLACE ANALYTIC VIEW sales_av
  CLASSIFICATION caption VALUE 'Sales AV'
  CLASSIFICATION description VALUE 'Sales Analytic View'
  CLASSIFICATION created_by VALUE 'George Jones'
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
    CLASSIFICATION caption VALUE 'Sales'
    CLASSIFICATION description VALUE 'Sales'
    CLASSIFICATION format_string VALUE '$999,999,999,999.99',
  units FACT units
    CLASSIFICATION caption VALUE 'Units'
    CLASSIFICATION description VALUE 'Units Sold'
    CLASSIFICATION format_string VALUE '999,999,999,999',
  sales_prior_period AS
    (LAG(SALES) OVER (HIERARCHY time_hier OFFSET 1))
      CLASSIFICATION caption VALUE 'Sales Prior Period'
      CLASSIFICATION description VALUE 'Sales Prior_Period'
      CLASSIFICATION format_string VALUE '$999,999,999,999.99',
  sales_share_prod_parent AS
   (SHARE_OF(sales HIERARCHY product_hier PARENT))
      CLASSIFICATION caption VALUE 'Sales Share of Product Parent'
      CLASSIFICATION description VALUE 'Sales Share of Product Parent'
      CLASSIFICATION format_string VALUE '999.99',
  sales_share_geog_parent AS
   (SHARE_OF(sales HIERARCHY geography_hier PARENT))
      CLASSIFICATION caption VALUE 'Sales Share of Geography Parent'
      CLASSIFICATION description VALUE 'Sales Share of Geography Parent'
      CLASSIFICATION format_string VALUE '999.99'
  )
DEFAULT MEASURE SALES;

Module 1
Introduction
An Analytic View is a type of view in the Oracle Database that allows users to perform complex queries and calculations on data stored in one or more tables. These views provide a higher level of abstraction over the underlying data, allowing users to access and analyze the data in a more meaningful way. They are typically used in business intelligence and data warehousing applications, and can be based on a single table or multiple tables joined together.

Analytic views include fact measures, which are accessed or derived from columns in a fact table, and calculated measures, which are measures created using analytic view expressions and SQL single-row functions. Common examples of calculated measures include lead, lags, same period year ago, ranking, and shares. To get started with calculated measures, review the following tutorials:
- Creating Analytic Views - Getting Started
- Creating Time Series Calculations in Analytic Views
Creating calculated measures within a query provides application developers the ability to provide large numbers of calculations without overloading the analytic view with calculations that satisfy every possible requirement. By allowing users of the application to choose a calculation type and set parameters for those calculation, the application can easily support hundreds or even thousands of calculations.

Consider a modest application with just a few fact measures such as Sales, Units Sold, Units Returns, and Dollar Value of Units Returned. Let's say there are requirements for the base measures plus:
- Sum of each measure quarter to date.
- Sum of each measure year to date
So far, there are 8 measures (four base * two calculations). Additional calculations might be required for each of the 8 measures:
- Prior period
- Change from prior period
- Percent change from prior period
- Same period one year ago
- Change from same period one year ago
- Percent change from same period one year ago
There now 48 measures, and this is a very small model. If we started out with 20 base measures, 240 predefined measures would be required in this scenario. And every time new fact measures are added, new calculated measures would also need to be added.

As another example, what if there was a requirement for user defined moving aggregates (for example, a 6 month moving average) where the use could control the number of trailing and leading periods and the aggregation operators? The only way to satisfy that requirement would be on-the-fly calculations.

Because Oracle Application Express (APEX) developer provide SQL used in APEX application, APEX is well suited for defining calculations on-the-fly.

Module 2

Sample Hierarchies and Analytic View

The prerequisite SQL for this tutorial created three hierarchies and a simple analytic view. The following queries can be used to get aquatinted with these objects.

The TIME_HIER hierarchy has three levels: Month, Quarter and Year.


SELECT * FROM time_hier;


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

The GEOGRAPHY_HIER hierarchy also has three levels: State/Province, Country, and Region:


SELECT * FROM geography_hier

SELECT
    member_name,
    member_unique_name,
    level_name
FROM
    geography_hier
ORDER BY
    hier_order

The PRODUCT_HIER hierarchy as two levels: Category and Department.


SELECT * FROM product_hier;


SELECT
    member_name,
    member_unique_name,
    level_name
FROM
    product_hier
ORDER BY
    hier_order

The analytic view has two fact measures:


SELECT * FROM user_analytic_view_base_meas;

There are two calculated measures.


SELECT *  FROM user_analytic_view_calc_meas

Module 3

Selecting Fact and Predefined Calculated Measures

Calculated measures defined in the analytic view can be selected as columns using this form of the FROM clause. This query selects MEMBER_CAPTION columns, fact measures and a calculated measure.


  SELECT
    time_hier.member_caption,
    product_hier.member_caption,
    geography_hier.member_caption,
    sales,
    units,
    sales_prior_period
FROM
    sales_av
        HIERARCHIES (
            time_hier,
            product_hier,
            geography_hier
        )
WHERE
    time_hier.level_name = 'YEAR'
    AND product_hier.level_name = 'CATEGORY'
    AND geography_hier.level_name = 'COUNTRY'
ORDER BY
    product_hier.hier_order,
    geography_hier.hier_order,
    time_hier.hier_order;

Module 4

The USING form of the FROM clause

The USING form of the FROM clause allows the use of the ADD MEASURES and FILTER FACT clauses in the SELECT statement. ADD MEASURES extends the content of the analytic view with calculated measures. To learn about FILTER FACT, see Using FILTER FACT to Extend Analytic View Aggregation in SELECT.

The USING form of the FROM clause. It is structured as:

FROM ANALYTIC VIEW USING (analytic view name(HIERARCHIES(hier1, hier2))

The following query uses the USING form of the FROM clause.


SELECT 
    time_hier.member_caption, 
    product_hier.member_caption, 
    geography_hier.member_caption, 
    sales, 
    units, 
    sales_prior_period 
FROM ANALYTIC VIEW ( 
    USING sales_av 
        HIERARCHIES ( 
            time_hier, 
            product_hier, 
            geography_hier 
        ) 
) 
WHERE 
    time_hier.level_name = 'YEAR' 
    AND product_hier.level_name = 'CATEGORY' 
    AND geography_hier.level_name = 'COUNTRY' 
ORDER BY 
    product_hier.hier_order, 
    geography_hier.hier_order, 
    time_hier.hier_order

This form of the query can always be used. With the exception of ADD MEASURES and FILTER FACT it is functionally equivalent the non-USING form.

This query can also be written as:


WITH my_av ANALYTIC VIEW AS (
     USING sales_av 
        HIERARCHIES ( 
            time_hier, 
            product_hier, 
            geography_hier 
        ) 
) 
SELECT 
    time_hier.member_caption, 
    product_hier.member_caption, 
    geography_hier.member_caption, 
    sales, 
    units, 
    sales_prior_period 
FROM
     my_av
WHERE 
    time_hier.level_name = 'YEAR' 
    AND product_hier.level_name = 'CATEGORY' 
    AND geography_hier.level_name = 'COUNTRY'
ORDER BY 
    product_hier.hier_order, 
    geography_hier.hier_order, 
    time_hier.hier_order;

Module 5

Add a Calculated Measures to the Query

To add a calculated measure to the query, use the ADD MEASURES clause. Any measure that can be defined in analytic view DDL can be included in ADD MEASURES.

Several LAG parallel period calculations are added to the query.


SELECT 
    time_hier.member_caption, 
    product_hier.member_caption, 
    geography_hier.member_caption, 
    sales, 
    units, 
     sales_quarter_ago,
    sales_chg_quarter_ago,
    ROUND(sales_pct_chg_quarter_ago,2) AS sales_pct_chg_quarter_ago
FROM ANALYTIC VIEW ( 
    USING sales_av 
        HIERARCHIES ( 
            time_hier, 
            product_hier, 
            geography_hier 
        )
    ADD MEASURES (
        -- Sales 1 quarter ago
        sales_quarter_ago AS
            (LAG(sales) OVER (HIERARCHY time_hier OFFSET 1 ACROSS ANCESTOR AT LEVEL quarter)),
        -- Change in sales from 1 quarter ago
        sales_chg_quarter_ago AS
            (LAG_DIFF(sales) OVER (HIERARCHY time_hier OFFSET 1 ACROSS ANCESTOR AT LEVEL quarter)),
        -- Percent change in sales one quarter ago
        sales_pct_chg_quarter_ago AS
            (LAG_DIFF_PERCENT(sales) OVER (HIERARCHY time_hier OFFSET 1 ACROSS ANCESTOR AT LEVEL quarter))
    )
) 
WHERE 
    time_hier.level_name = 'MONTH' 
    AND product_hier.level_name = 'CATEGORY' 
    AND geography_hier.level_name = 'COUNTRY' 
ORDER BY 
    product_hier.hier_order, 
    geography_hier.hier_order, 
    time_hier.hier_order;

Module 6

Usage Notes

Empty ADD MEASURES Clause

Applications that sometimes need define calculations can standardize on the USING form of the FROM clause to simplify SQL generation code. Note that ADD MEASURES cannot be empty. There must be at least one measure in ADD MEASURES.

The following query will fail.


SELECT 
    time_hier.member_caption, 
    product_hier.member_caption, 
    geography_hier.member_caption, 
    sales, 
    units
FROM ANALYTIC VIEW ( 
    USING sales_av 
        HIERARCHIES ( 
            time_hier, 
            product_hier, 
            geography_hier 
        )
    ADD MEASURES ( )
) 
WHERE 
    time_hier.level_name = 'MONTH' 
    AND product_hier.level_name = 'CATEGORY' 
    AND geography_hier.level_name = 'COUNTRY' 
ORDER BY 
    product_hier.hier_order, 
    geography_hier.hier_order, 
    time_hier.hier_order;

Including one 'dummy' calculation in ADD MEASURES will solve this problem. It is not necessary to include the measure in the SELECT list. For example:


SELECT 
    time_hier.member_caption, 
    product_hier.member_caption, 
    geography_hier.member_caption, 
    sales, 
    units
FROM ANALYTIC VIEW ( 
    USING sales_av 
        HIERARCHIES ( 
            time_hier, 
            product_hier, 
            geography_hier 
        )
    ADD MEASURES (
  dummy_measures AS (null)
  )
) 
WHERE 
    time_hier.level_name = 'MONTH' 
    AND product_hier.level_name = 'CATEGORY' 
    AND geography_hier.level_name = 'COUNTRY' 
ORDER BY 
    product_hier.hier_order, 
    geography_hier.hier_order, 
    time_hier.hier_order;

Duplicate Measures

Measure names must be unique within the analytic view. A measure defined in ADD MEASURES is considered to be part of the analytic view for the duration of the query. In the following query SALES_CHG_PRIOR_PERIOD is defined in the analytic view and in the query. This query will fail with:

ORA-18345: Duplicate metadata object "SALES_PRIOR_PERIOD".


SELECT 
    time_hier.member_caption, 
    product_hier.member_caption, 
    geography_hier.member_caption, 
    sales, 
    units
FROM ANALYTIC VIEW ( 
    USING sales_av 
        HIERARCHIES ( 
            time_hier, 
            product_hier, 
            geography_hier 
        )
    ADD MEASURES (
      sales_prior_period AS (LAG(sales) OVER (HIERARCHY time_hier OFFSET 1))
  )
) 
WHERE 
    time_hier.level_name = 'MONTH' 
    AND product_hier.level_name = 'CATEGORY' 
    AND geography_hier.level_name = 'COUNTRY' 
ORDER BY 
    product_hier.hier_order, 
    geography_hier.hier_order, 
    time_hier.hier_order;

Unselected Calculations

All calculations that are included in ADD MEASURES are checked for correct syntax, adding a very small amount of parsing time. Calculations are only executed if they are in the SELECT list.

Module 7
Sample APEX Application using ADD MEASURE
You can view a sample APEX (Oracle Application Expression) application that uses ADD MEASURES that allows users to apply time series transformation. The application shows the generated SQL.
Wells Drilled in the State of Massachusetts
Module 8
Questions or Comments?
Get in touch at william.endress@oracle.com.

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database