Introduction

Business intelligence tools such as Microsoft Power BI and Tableau query relational tables using the familiar SELECT .. SUM .. FROM .. GROUP BY pattern. These tools can query Oracle analytic views using the same pattern.

Oracle always presents a hierarchical representation of hierarchies and the analytic view. These are the views found in the USER_HIERARCHIES and USER_ANALYTIC_VIEWS dictionary views. These views are queried with hierarchical semantics using the SELECT .. FROM AV(HIERARCHIES..) pattern. "AV aware" applications such as Oracle Analytic Cloud use this pattern for ease of SQL generation and to use advanced features of the analytic view.

SELECT statements using the GROUP BY pattern select from an alternate relational representation of hierarchies and analytic views. These SELECT statements can access all data in analytic views, including calculated measures, allowing you to query an analytic view with almost any business intelligence tool. Different tools work best with specific relational representations of hierarchies and analytic views. Therefore, you create that representation by creating a new view that selects from the hierarchy or analytic view.

This tutorial introduces the relational representation of the analytic view. It uses the LiveSQL AV schema data, which many other Live SQL tutorials use. You can easily compare hierarchical and relational style SQL by running this and other LiveSQL tutorials.

Important: Required Oracle Versions for This Tutorial

The features that enable the relational representation of the analytic view are available in Oracle 23c and the Oracle Autonomous Database.

As of today, Oracle Live SQL runs Oracle 19c. If you would like to run the code in this tutorial, copy and paste into a SQL worksheet tool that is connected to Oracle 23c or the Oracle Autonomous Database (including Oracle Autonomous Database on the Oracle Cloud Free Tier).

Required Permissions

Installing sample data requires the EXECUTE privilege on the DBMS_CLOUD package. Alternatively, CSV files can be downloaded and loaded into tables using an application such as Oracle SQL Developer.

Creating analytic view objects requires the following privileges:

• CREATE ATTRIBUTE DIMENSION
• CREATE HIERRACHY
• CREATE ANALYTIC VIEW

On Autonomous Database, these privileges are included in the DWROLE role.

Install Sample Data

The following script creates tables and loads data from an Oracle Object Store using DBMS_CLOUD.

CREATE TABLE time_dim (
    "MONTH_ID"           VARCHAR2(30),
    "MONTH_NAME"         VARCHAR2(40),
    "MONTH_LONG_NAME"    VARCHAR2(30),
    "MONTH_END_DATE"     DATE,
    "MONTH_OF_QUARTER"   NUMBER,
    "MONTH_OF_YEAR"      INTEGER,
    "QUARTER_ID"         VARCHAR2(30),
    "QUARTER_NAME"       VARCHAR2(40),
    "QUARTER_END_DATE"   DATE,
    "QUARTER_OF_YEAR"    NUMBER,
    "SEASON"             VARCHAR2(10),
    "SEASON_ORDER"       NUMBER,
    "YEAR_ID"            VARCHAR2(30),
    "YEAR_NAME"          VARCHAR2(40),
    "YEAR_END_DATE"      DATE);

BEGIN
  DBMS_CLOUD.COPY_DATA (
  table_name => 'TIME_DIM',
    file_uri_list => 'https://objectstorage.uk-london-1.oraclecloud.com/p/tTVLBYgRx4xVM6DD74MWIiRnMO3UHgWA1ibqleMJqz7TTnehMw19_ZEFBG8wY5ky/n/adwc4pm/b/data_library/o/d1945/table=TIME_DIM/partition=all_rows/*.csv',
  format => '{"delimiter":",","recorddelimiter":"newline","skipheaders":"1","quote":"\\\"","rejectlimit":"1000","trimspaces":"rtrim","ignoreblanklines":"false","ignoremissingcolumns":"true","dateformat":"DD-MON-YYYY HH24:MI:SS"}'
  );
END;
/

CREATE TABLE product_dim (
    "CATEGORY_ID"       INTEGER,
    "CATEGORY_NAME"     VARCHAR2(100),
    "DEPARTMENT_ID"     INTEGER,
    "DEPARTMENT_NAME"   VARCHAR2(100)
);

BEGIN
  DBMS_CLOUD.COPY_DATA (
  table_name => 'PRODUCT_DIM',
    file_uri_list => 'https://objectstorage.uk-london-1.oraclecloud.com/p/tTVLBYgRx4xVM6DD74MWIiRnMO3UHgWA1ibqleMJqz7TTnehMw19_ZEFBG8wY5ky/n/adwc4pm/b/data_library/o/d1945/table=PRODUCT_DIM/partition=all_rows/*.csv',
  format => '{"delimiter":",","recorddelimiter":"newline","skipheaders":"1","quote":"\\\"","rejectlimit":"1000","trimspaces":"rtrim","ignoreblanklines":"false","ignoremissingcolumns":"true","dateformat":"DD-MON-YYYY HH24:MI:SS"}'
  );
END;
/

CREATE TABLE geography_dim (
    "REGION_ID"             VARCHAR2(120),
    "REGION_NAME"           VARCHAR2(100),
    "COUNTRY_ID"            VARCHAR2(2),
    "COUNTRY_NAME"          VARCHAR2(120),
    "STATE_PROVINCE_ID"     VARCHAR2(120),
    "STATE_PROVINCE_NAME"   VARCHAR2(400)
);

BEGIN
  DBMS_CLOUD.COPY_DATA (
  table_name => 'GEOGRAPHY_DIM',
    file_uri_list => 'https://objectstorage.uk-london-1.oraclecloud.com/p/tTVLBYgRx4xVM6DD74MWIiRnMO3UHgWA1ibqleMJqz7TTnehMw19_ZEFBG8wY5ky/n/adwc4pm/b/data_library/o/d1945/table=GEOGRAPHY_DIM/partition=all_rows/*.csv',
  format => '{"delimiter":",","recorddelimiter":"newline","skipheaders":"1","quote":"\\\"","rejectlimit":"1000","trimspaces":"rtrim","ignoreblanklines":"false","ignoremissingcolumns":"true","dateformat":"DD-MON-YYYY HH24:MI:SS"}'
  );
END;
/

CREATE TABLE sales_fact (
    "MONTH_ID"            VARCHAR2(10),
    "CATEGORY_ID"         NUMBER,
    "STATE_PROVINCE_ID"   VARCHAR2(120),
    "UNITS"               NUMBER,
    "SALES"               NUMBER
);

BEGIN
  DBMS_CLOUD.COPY_DATA (
  table_name => 'SALES_FACT',
    file_uri_list => 'https://objectstorage.uk-london-1.oraclecloud.com/p/tTVLBYgRx4xVM6DD74MWIiRnMO3UHgWA1ibqleMJqz7TTnehMw19_ZEFBG8wY5ky/n/adwc4pm/b/data_library/o/d1945/table=SALES_FACT/partition=all_rows/*.csv',
  format => '{"delimiter":",","recorddelimiter":"newline","skipheaders":"1","quote":"\\\"","rejectlimit":"1000","trimspaces":"rtrim","ignoreblanklines":"false","ignoremissingcolumns":"true","dateformat":"DD-MON-YYYY HH24:MI:SS"}'
  );
END;
/

Create Analytic View Objects

The next series of statements create analytic view objects.

-- Time dimension

CREATE OR REPLACE ATTRIBUTE DIMENSION time_attr_dim
USING time_dim
ATTRIBUTES (
    year_name
    , quarter_name
    , month_name
    , month_end_date)
LEVEL year
    KEY year_name
LEVEL quarter
    KEY quarter_name
    DETERMINES (year_name)
LEVEL month
    KEY month_name
    ORDER BY month_end_date
    DETERMINES (month_end_date,quarter_name);

-- Time hierarchy

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

-- Product dimension

CREATE OR REPLACE ATTRIBUTE DIMENSION product_attr_dim
USING product_dim
ATTRIBUTES (
    department_name
    , category_name
    , category_id)
LEVEL department
    KEY department_name
LEVEL category
    KEY category_id
    ALTERNATE KEY category_name
    MEMBER NAME category_name
    DETERMINES (department_name);

-- Product Hierarchy

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

-- Geography dimension

CREATE OR REPLACE ATTRIBUTE DIMENSION geography_attr_dim
USING geography_dim
ATTRIBUTES (
    region_name
    , country_name
    , state_province_name
    , state_province_id)
LEVEL region
    KEY region_name
LEVEL country
    KEY country_name
    DETERMINES (region_name)
LEVEL state_province
    KEY state_province_id
    ALTERNATE KEY state_province_name
    MEMBER NAME state_province_name
    DETERMINES (country_name);

-- Geography hierarchy

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

-- Analytic view

CREATE OR REPLACE ANALYTIC VIEW sales_av
USING sales_fact
DIMENSION BY (
    time_attr_dim
        KEY month_id REFERENCES month_name
        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 AGGREGATE BY SUM
    -- Sales prior period
    , sales_prior_period AS (LAG(sales) OVER (HIERARCHY time_hier OFFSET 1))
    -- Sales change from prior period
    , sales_chg_prior_period AS (LAG_DIFF(sales) OVER (HIERARCHY time_hier OFFSET 1))
    -- Sales percent change from prior period
    , sales_pct_chg_prior_period AS (LAG_DIFF_PERCENT(sales) OVER (HIERARCHY time_hier OFFSET 1))
    -- Sales year ago
    , sales_year_ago AS (LAG(sales) OVER (HIERARCHY time_hier OFFSET 1 ACROSS ANCESTOR AT LEVEL year))
    -- Sales change from year ago
    , sales_chg_year_ago AS (LAG_DIFF(sales) OVER (HIERARCHY time_hier OFFSET 1 ACROSS ANCESTOR AT LEVEL year))
    -- Sales percent change from year ago.
    , sales_pct_chg_year_ago AS (LAG_DIFF_PERCENT(sales) OVER (HIERARCHY time_hier OFFSET 1 ACROSS ANCESTOR AT LEVEL year))
    -- Sales share of product within level (all members)
    , sales_shr_of_product AS (SHARE_OF(sales HIERARCHY product_hier MEMBER ALL))
    );

Examine the Sample Data

Become familiar with the sample data.

Hierarchies and analytic views access data from tables or views, typically a star schema or a 'flat' fact table (or some blend). This tutorial uses dimension tables from a star schema with sales data that varies by time, product and geography. In each dimension table there are _ID columns that are used as keys and _NAME columns that are used as textual descriptors. (In the attribute dimension objects, note the KEY and MEMBER_NAME properties of levels.) Other columns might be used for purposes such as sorting.

Select columns of the TIME_DIM table that are used by the analytic view.

SELECT
    year_name
    , quarter_name
    , month_name
    , month_id
    , month_end_date
FROM
    time_dim
ORDER BY
    month_end_date;

View columns of the PRODUCT_DIM table that are used by the analytic view.

SELECT
    department_name
    , category_id
    , category_name
FROM
    product_dim
ORDER BY
    department_name
    , category_name;

View columns of the GEOGRAPHY_DIM table that are used by the analytic view.

SELECT
    region_name
    , country_name
    , state_province_id
    , state_province_name
FROM
    geography_dim
ORDER BY
    region_name
    , country_name
    , state_province_name;

View columns of the SALES_FACT table.

SELECT * FROM sales_fact WHERE rownum < 20;

The following query returns data aggregated by year, department and country. This uses the familiar SELECT .. SUM .. FROM .. GROUP BY pattern that is used by most business intelligence tools. Note that the dimension tables join the fact tables using the _ID columns.

SELECT t.year_name,
  p.department_name,
  g.country_name,
  SUM(f.sales)
FROM time_dim t,
  product_dim p,
  geography_dim g,
  sales_fact f
WHERE t.month_id        = f.month_id
AND p.category_id       = f.category_id
AND g.state_province_id = f.state_province_id
GROUP BY t.year_name,
  p.department_name,
  g.country_name
ORDER BY t.year_name,
  p.department_name,
  g.country_name;

Sample Hierarchical Queries

Every hierarchy and analytic view has a hierarchical representation that includes attributes, hierarchical attributes, and measures. The data sources (tables and views), joins, aggregation rules, and measure calculations are embedded in the analytic view and thus do not need to be expressed within the query. You can think of the HIERARCHIES clause as replacing joins and GROUP BY.

The following query SELECTs from the analytic view using hierarchical columns. SELECT .. FROM ... HIERARCHIES ... WHERE is the basic template used to query the hierarchical representation of the analytic view. Note that aggregation operators are not required and that the query selects a calculated measure.

SELECT
    time_hier.member_name      AS time
  , product_hier.member_name   AS product
  , geography_hier.member_name AS geography
  , sales
  , sales_year_ago
  , sales_chg_year_ago
  , ROUND(sales_pct_chg_year_ago,2) as sales_pct_chg_year_ago
FROM
    sales_av HIERARCHIES (
        time_hier
    , product_hier
    , geography_hier
    )
WHERE
        time_hier.level_name = 'YEAR'
    AND product_hier.level_name = 'DEPARTMENT'
    AND geography_hier.level_name = 'REGION'
    AND time_hier.year_name = 'CY2015'
ORDER BY
    time_hier.hier_order
  , product_hier.hier_order
  , geography_hier.hier_order;

The next query selects sales and the share of sales by product. Note that the query follows the same pattern as the previous query.

SELECT
    time_hier.member_name      AS time
  , product_hier.member_name   AS product
  , sales
  , ROUND(sales_shr_of_product,2) AS sales_shr_of_product
FROM
    sales_av HIERARCHIES (
        time_hier
    , product_hier
    )
WHERE
        time_hier.level_name = 'YEAR'
    AND product_hier.level_name = 'DEPARTMENT'
    AND time_hier.year_name = 'CY2015'
ORDER BY
    time_hier.hier_order
  , product_hier.hier_order;

These are typical examples of the type of queries that an "AV-aware" application might generate.

Create a Relational Representation of the Analytic View

Unless your tool or application generates AV-aware queries, it most likely generates queries using the SELECT .. SUM .. FROM .. GROUP BY pattern. Analytic views can also be queried with this pattern, allowing just about any tool to query an analytic view.

Because each tool might work best with different variations of the relational representation, you create the view using a CREATE VIEW statement. This selects columns from the analytic view and includes the FACT ROWS keywords to indicate that queries will be processed by the analytic view (rather than the analytic view simply supplying rows that will be processed afterward in SQL).

The following CREATE VIEW statement creates a relational representation of the analytic view that includes columns representing attributes (rather than keys) and the measure of the analytic view.

Note that attributes and measures are qualified by hierarchy name. For the purpose of creating this view, measures are members of a MEASURES hierarchy.

CREATE OR REPLACE VIEW sales_av_view AS
    SELECT
        year_name
      , quarter_name
      , month_name
      , month_end_date
      , department_name
      , category_name
      , region_name
      , country_name
      , state_province_name
      , measures.sales
      , measures.sales_prior_period
      , measures.sales_chg_prior_period
      , measures.sales_pct_chg_prior_period
      , measures.sales_year_ago
      , measures.sales_chg_year_ago
      , measures.sales_pct_chg_year_ago
      , measures.sales_shr_of_product
    FROM
        sales_av FACT ROWS;

The CREATE VIEW statement ends with FACT ROWS. This tells the database that this is a relational representation of the analytic view, enabling GROUP BY transformation into the analytic view.

Like other views, the relational representation of the analytic view is represented in the USER_VIEWS and USER_TAB_COLUMNS dictionary views.

SELECT * FROM user_views WHERE view_name = 'SALES_AV_VIEW';

SELECT
    table_name
  , column_name
  , data_type
  , data_length
FROM
    user_tab_columns
WHERE
    table_name = 'SALES_AV_VIEW'
ORDER BY
    column_id;

Note that column names do not include the hierarchy qualifier.

Also like other views, columns may be aliased. For example:

CREATE OR REPLACE VIEW sales_av_view AS
    SELECT
        time_hier.year_name                 AS year
      , time_hier.quarter_name              AS quarter
      , time_hier.month_name                AS month
      , time_hier.month_end_date            AS month_end_date
      , product_hier.department_name        AS department
      , product_hier.category_name          AS category
      , geography_hier.region_name          AS region
      , geography_hier.country_name         AS country
      , geography_hier.state_province_name  AS state_province
      , measures.sales                      AS sales
      , measures.sales_prior_period         AS sales_prior_period
      , measures.sales_chg_prior_period     AS sales_change_prior_period
      , measures.sales_pct_chg_prior_period AS sales_percent_change_prior_period
      , measures.sales_year_ago             AS sales_year_ago
      , measures.sales_chg_year_ago         AS sales_change_year_ago
      , measures.sales_pct_chg_year_ago     AS sales_percent_change_year_ago
      , measures.sales_shr_of_product       AS sales_share_of_product
    FROM
        sales_av fact ROWS;

SELECT
    table_name
  , column_name
  , data_type
  , data_length
FROM
    user_tab_columns
WHERE
    table_name = 'SALES_AV_VIEW'
ORDER BY
    column_id;

Most BI tools can use quoted column names. If this is the case with your tool, a view such as this will present nicely in the tool.

CREATE OR REPLACE VIEW sales_av_view AS
    SELECT
        time_hier.year_name                 AS "Year"
      , time_hier.quarter_name              AS "Quarter"
      , time_hier.month_name                AS "Month"
      , time_hier.month_end_date            AS "Month End Date"
      , product_hier.department_name        AS "Department"
      , product_hier.category_name          AS "Category"
      , geography_hier.region_name          AS "Region"
      , geography_hier.country_name         AS "Country"
      , geography_hier.state_province_name  AS "State Province"
      , measures.sales                      AS "Sales"
      , measures.sales_prior_period         AS "Sales Prior Period"
      , measures.sales_chg_prior_period     AS "Sales Change Prior Period"
      , measures.sales_pct_chg_prior_period AS "Sales Percent Change Prior Period"
      , measures.sales_year_ago             AS "Sales Year Ago"
      , measures.sales_chg_year_ago         AS "Sales Change Year Ago"
      , measures.sales_pct_chg_year_ago     AS "Sales Percent Change Year Ago"
      , measures.sales_shr_of_product       AS "Sales Share of Product"
    FROM
        sales_av FACT ROWS;

SELECT
    table_name
  , column_name
  , data_type
  , data_length
FROM
    user_tab_columns
WHERE
    table_name = 'SALES_AV_VIEW'
ORDER BY
    column_id;

If column names are unique across hierarchies, the hierarchy qualifier may be omitted. For example:

CREATE OR REPLACE VIEW sales_av_view AS
    SELECT
        year_name                   AS year
      , quarter_name                AS quarter
      , month_name                  AS month
      , month_end_date
      , department_name             AS department
      , category_name               AS category
      , region_name                 AS region
      , country_name                AS country
      , state_province_name         AS state_province
      , sales
      , sales_prior_period
      , sales_chg_prior_period
      , sales_pct_chg_prior_period
      , sales_year_ago
      , sales_chg_year_ago
      , sales_pct_chg_year_ago
      , sales_shr_of_product
    FROM
        sales_av FACT ROWS;

Query the Relational Representation

Before continuing with this module, be sure to run the last CREATE VIEW statement in the previous module.

A relational representation of the analytic view is queried using the SELECT .. SUM .. FROM .. GROUP BY pattern. Like the hierarchical representation of the analytic view, the relational representation is a flattened (joined) view of the dimension and fact table tables. No joins are required. Unlike the hierarchical representation, the view returns only detailed rows. Use GROUP BY to return aggregate rows.

Calculated measures in an analytic view are not understood by GROUP BY. For example, GROUP BY does not understand how the Sales Percent Change Year Ago (SALES_PCT_CHG_YEAR_AGO) measure is calculated and cannot aggregate it. (In the analytic view, data is first aggregated and then the percent change year ago is calculated).

Since GROUP BY does not understand how a calculated measure is processed, calculated measures are selected using the AV_AGGREGATE aggregation operator. AV_AGGREGATE is a pass-through function that allows the calculated value to pass through GROUP BY unchanged.

The following query selects from the relational representation of the SALES_AV analytic view.

SELECT
    year
  , department
  , region
  , AV_AGGREGATE(sales)                  AS sales
  , AV_AGGREGATE(sales_year_ago)         AS sales_year_ago
  , AV_AGGREGATE(sales_chg_year_ago)     AS sales_chg_year_ago
  , AV_AGGREGATE(sales_pct_chg_year_ago) AS sales_pct_chg_year_ago
FROM
    sales_av_view
GROUP BY
    year
  , department
  , region;

If would like to view the SQL plan, you can use the tool of your choice (for example, Oracle SQL Developer) or run the following commands. Note that the plan shows that the SALES_AV analytic view is accessed.

If you wish to run commands, you will need a PLAN_TABLE.

CREATE TABLE PLAN_TABLE ( 
        statement_id       varchar2(30), 
        plan_id            number, 
        timestamp          date, 
        remarks            varchar2(4000), 
        operation          varchar2(30), 
        options            varchar2(255), 
        object_node        varchar2(128), 
        object_owner       varchar2(30), 
        object_name        varchar2(30), 
        object_alias       varchar2(65), 
        object_instance    numeric, 
        object_type        varchar2(30), 
        optimizer          varchar2(255), 
        search_columns     number, 
        id                 numeric, 
        parent_id          numeric, 
        depth              numeric, 
        position           numeric, 
        cost               numeric, 
        cardinality        numeric, 
        bytes              numeric, 
        other_tag          varchar2(255), 
        partition_start    varchar2(255), 
        partition_stop     varchar2(255), 
        partition_id       numeric, 
        other              long, 
        distribution       varchar2(30), 
        cpu_cost           numeric, 
        io_cost            numeric, 
        temp_space         numeric, 
        access_predicates  varchar2(4000), 
        filter_predicates  varchar2(4000), 
        projection         varchar2(4000), 
        time               numeric, 
        qblock_name        varchar2(30), 
        other_xml          clob)
NOCOMPRESS;

Now you can run EXPLAIN PLAN.

TRUNCATE TABLE plan_table;
 EXPLAIN PLAN
SET STATEMENT_ID = '1' INTO plan_table FOR
SELECT
    year
  , department
  , region
  , AV_AGGREGATE(sales)                  AS sales
  , AV_AGGREGATE(sales_year_ago)         AS sales_year_ago
  , AV_AGGREGATE(sales_chg_year_ago)     AS sales_chg_year_ago
  , AV_AGGREGATE(sales_pct_chg_year_ago) AS sales_pct_chg_year_ago
FROM
    sales_av_view
GROUP BY
    year
  , department
  , region;

SELECT LPAD('............................',2*(LEVEL-1)) ||operation operation,
  OPTIONS,
  object_name,
  position
FROM plan_table
START WITH id       = 0
 AND statement_id      = '1'
 CONNECT BY PRIOR id = parent_id
 AND statement_id      = '1';

The next example selects data at the quarter, category, and country levels.

SELECT
    quarter
  , category
  , country
  , AV_AGGREGATE(sales)                  AS sales
  , AV_AGGREGATE(sales_year_ago)         AS sales_year_ago
  , AV_AGGREGATE(sales_chg_year_ago)     AS sales_chg_year_ago
  , AV_AGGREGATE(sales_pct_chg_year_ago) AS sales_pct_chg_year_ago
FROM
    sales_av_view
GROUP BY
    quarter
  , category
  , country;

Some Advantages of Using an Analytic View

This is a good time to note some advantages of using the analytic view:

• Underlying tables are masked from the application.
• Aggregation rules can be embedded in the analytic view.
• Joins are not required.
• Calculated measures are expressed in the analytic view and thus do not need to be expressed in the query.

If this query were to select from tables:

• It would be a three pass query 1) aggregating data, 2) calculating year ago, and 3) calculated the percent change year ago.
• The calculation expressions would need to change each time the GROUP BY clauses changed.
• It would require joins.

Pass Through Functions in Business Intelligence Tools

The use of AV_AGGREGATE requires the ability to use a pass-through function in a business intelligence tool. For example, in Tableau, that is the RAWSQL function.

KEY, ALTERNATE KEY, and GROUP BY

Aggregate values are produced by the analytic view using the values in the level KEY attribute. That is, the SQL generated by the analytic view uses the column of the level KEY in GROUP BY.

There are use cases where the level KEY is not the value that a user wishes to view in a business intelligence tool. For example, when the level KEY is an integer. In that case, the user would more likely want to view a different column with text value.

The attribute dimension for product uses the CATEGORY_ID column a the level KEY because it joins to the fact able and CATEGORY_NAME as an ALTERNATE KEY. ALTERNATE KEY attributes can be used in GROUP BY.

CREATE OR REPLACE ATTRIBUTE DIMENSION product_attr_dim
USING product_dim
ATTRIBUTES (
    department_name
    , category_name
    , category_id)
LEVEL department
    KEY department_name
LEVEL category
    KEY category_id
    ALTERNATE KEY category_name
    MEMBER NAME category_name
    DETERMINES (department_name);

There must be a 1:1 relationship between the level KEY and the ALTERNATE KEY in order for the GROUP BY of the level KEY and the GROUP BY of the ALTERNATE KEY to return the same data values. The DBMS_HIERARCHY.VALIDATE_HIERARCHY and DBMS_HIERARCHY.VALIDATE_ANALYTIC_VIEW functions can be used to test that 1:1 relationships between the level KEY and ALTERNATE KEYs exist as expected

The following statement creates the product attribute dimension without CATEGORY_NAME as an ALTERNATE KEY.

CREATE OR REPLACE ATTRIBUTE DIMENSION product_attr_dim
USING product_dim
ATTRIBUTES (
    department_name
    , category_name
    , category_id)
LEVEL department
    KEY department_name
LEVEL category
    KEY category_id
--    ALTERNATE KEY category_name
     MEMBER NAME category_name
    DETERMINES (department_name);

If CATEGORY_NAME is used in GROUP BY, an error is returned by the query. Remember that in the SALES_AV_VIEW view, YEAR is a alias for YEAR_NAME and CATEGORY is an alias for CATEGORY_NAME.

SELECT
    year
  , category
  , AV_AGGREGATE(sales_pct_chg_year_ago) AS sales_pct_chg_year_ago
FROM
    sales_av_view
GROUP BY
    year
  , category;

Correct the attribute dimension by adding back CATEGORY_NAME as an ALTERNATE KEY.

CREATE OR REPLACE ATTRIBUTE DIMENSION product_attr_dim
USING product_dim
ATTRIBUTES (
    department_name
    , category_name
    , category_id)
LEVEL department
    KEY department_name
LEVEL category
    KEY category_id
    ALTERNATE KEY category_name
     MEMBER NAME category_name
    DETERMINES (department_name);

Fact Columns

All measures, including FACT measures, are calculated using operators and expressions of the measure. Aggregation operators of a FACT measure cannot be overridden in the query. For example, a measure defined as the SUM of SALES cannot be overriden as AVG(SALES) in a query using GROUP BY.

Columns in the fact table can, however, be selected through the analytic view in a GROUP BY type query. These columns are identified in the analytic view using the FACT COLUMNS clause. A column in the fact table can be used as a measure in the analytic view and as a FACT COLUMN.

For example, a SALES column can be used as a measure and be listed in FACT COLUMNS. A column that is not used as a measure can be used as a FACT COLUMN.

The following CREATE ANALYTIC VIEW statement defines SALES a measure (sales FACT sales) and includes the SALES column in FACT column. The UNITS column, which is not defined as a FACT measure, is also included in FACT COLUMNS.

CREATE OR REPLACE ANALYTIC VIEW sales_av
USING sales_fact
DIMENSION BY (
    time_attr_dim
        KEY month_id REFERENCES month_name
        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 AGGREGATE BY SUM
    -- Sales prior period
    , sales_prior_period AS (LAG(sales) OVER (HIERARCHY time_hier OFFSET 1))
    -- Sales change from prior period
    , sales_chg_prior_period AS (LAG_DIFF(sales) OVER (HIERARCHY time_hier OFFSET 1))
    -- Sales percent change from prior period
    , sales_pct_chg_prior_period AS (LAG_DIFF_PERCENT(sales) OVER (HIERARCHY time_hier OFFSET 1))
    -- Sales year ago
    , sales_year_ago AS (LAG(sales) OVER (HIERARCHY time_hier OFFSET 1 ACROSS ANCESTOR AT LEVEL year))
    -- Sales change from year ago
    , sales_chg_year_ago AS (LAG_DIFF(sales) OVER (HIERARCHY time_hier OFFSET 1 ACROSS ANCESTOR AT LEVEL year))
    -- Sales percent change from year ago.
    , sales_pct_chg_year_ago AS (LAG_DIFF_PERCENT(sales) OVER (HIERARCHY time_hier OFFSET 1 ACROSS ANCESTOR AT LEVEL year))
    -- Sales share of product within level (all members)
    , sales_shr_of_product AS (SHARE_OF(sales HIERARCHY product_hier MEMBER ALL))
    )
FACT COLUMNS (sales, units);

FACT COLUMNS are included in the FACT ROWS view using FACT$. Note that when a column is used as both a measure and a FACT COLUMN, the column alias must be unique.

CREATE OR REPLACE VIEW sales_av_view AS
    SELECT
        time_hier.year_name                 AS year
      , time_hier.quarter_name              AS quarter
      , time_hier.month_name                AS month
      , time_hier.month_end_date            AS month_end_date
      , product_hier.department_name        AS department
      , product_hier.category_name          AS category
      , geography_hier.region_name          AS region
      , geography_hier.country_name         AS country
      , geography_hier.state_province_name  AS state_province
      -- These are fact columns
      , facts$.sales                         AS sales_fact
      , facts$.units                         AS units_fact
      -- These are analytic view measures
      , measures.sales                      AS sales
      , measures.sales_prior_period         AS sales_prior_period
      , measures.sales_chg_prior_period     AS sales_change_prior_period
      , measures.sales_pct_chg_prior_period AS sales_percent_change_prior_period
      , measures.sales_year_ago             AS sales_year_ago
      , measures.sales_chg_year_ago         AS sales_change_year_ago
      , measures.sales_pct_chg_year_ago     AS sales_percent_change_year_ago
      , measures.sales_shr_of_product       AS sales_share_of_product
    FROM
        sales_av fact ROWS;

Measures are selected with the AV_AGGREGATE operator and fact columns are selected using relational aggregation operators. The following query includes SALES as both a fact column and a measure, and UNITS as a fact column as both SUM and AVG.

SELECT
    year
  , department
  , region
  , AV_AGGREGATE(sales)                  AS sales
  , SUM(sales_fact)                      AS sales_fact
  , SUM(units_fact)                      AS units_fact
  , AVG(units_fact)                      AS avg_units_fact
FROM
    sales_av_view
GROUP BY
    year
  , department
  , region;

Querying fact columns through the analytic view allows the flexibility of applying different aggregation operators in the SELECT statement while offering the offering advantages of the analytic view such as eliminating the need for joins, hiding the complexity of underlying tables, and the ability to use a materialized cache (see later section in this document).

Relational and Dimensional Semantics Compared

The hierarchical representation is consistent with OLAP cube semantics. In the hierarchical representation of the analytic view, each row has a deterministic value independent of the WHERE clause. Data are aggregated first and the WHERE clause filters the resulting row set.

The relational representation of the analytic view is consistent with relational query semantics of tables. In the relational representation of the analytic view, the WHERE clause is applied prior to aggregation, and as a result, the value of any given row is determined in part by the WHERE clause.

(The hierarchical representation of the analytic view can actually blend relational and hierarchical semantics using FILTER FACT clause in a SELECT statement. For the purpose of comparing the hierarchical and relational representations, this tutorial focuses on the WHERE clause. See the Using FILTER FACT to Extend Analytic View Aggregation in SELECT for details.)

Consider the following queries that select sales for the year CY2015.

This hierarchical query selects sales for CY2015, and Europe and Africa. Rows are returned for each geography.

SELECT
    time_hier.member_name      AS time
  , geography_hier.member_name AS geography
  , sales
FROM
    sales_av HIERARCHIES (
        time_hier
    , geography_hier
    )
WHERE
        time_hier.member_name = 'CY2015'
    AND geography_hier.level_name = 'REGION'
    AND geography_hier.member_name IN ( 'Europe', 'Africa' )
ORDER BY
    2;

The same rows and same measure values are returned with the following relational query.

SELECT
    year
  , region
  , AV_AGGREGATE(sales) AS sales
FROM
    sales_av_view
WHERE
        year = 'CY2015'
    AND region IN ( 'Europe', 'Africa' )
GROUP BY
    year
  , region
ORDER BY
    2;

If geography is eliminated from the select list and HIERARCHIES clause of the hierarchical query, no rows are returned. (When a hierarchy is not listed in the HIERARCHY clause, the level defaults to ALL. Europe and Africa are not members of the ALL level.)

SELECT
    time_hier.member_name      AS time
  , sales
FROM
    sales_av HIERARCHIES (
        time_hier
    )
WHERE
        time_hier.member_name = 'CY2015'
    AND geography_hier.member_name IN ( 'Europe', 'Africa' );

If geography is eliminated from the select list and GROUP BY of the relational query, the sales for CY2015 is the aggregate of Europe and Africa.

SELECT
    year
  , AV_AGGREGATE(sales) AS sales
FROM
    sales_av_view
WHERE
        year = 'CY2015'
    AND region IN ( 'Europe', 'Africa' )
GROUP BY
    year;

Eliminate the REGION filter and note that the value for CY2015 changes to the aggregate of all geographies.

SELECT
    year
  , AV_AGGREGATE(sales) AS sales
FROM
    sales_av_view
WHERE
    year = 'CY2015'
GROUP BY
    year;

This is equivalent to dropping the GEOGRAPHY_HIER hierarchy from the hierarchical query.

SELECT
    time_hier.member_name      AS time
  , sales
FROM
    sales_av HIERARCHIES (
        time_hier
    )
WHERE
    time_hier.member_name = 'CY2015'
ORDER BY
    1;

The next set of examples illustrates this effect on calculated measures.

Consider the following examples which return sales and sales year ago by year. The first year in this data set is CY2011, so it will always return NULL for SALES_YEAR_AGO. CY2011 is the prior year to CY2012, so SALES_YEAR_AGO for CY2012 can return a non NULL value.

The hierarchical query returns a non NULL value for CY2011. The analytic view calculates the value and the filter is applied to the returning row set. This is consistent with OLAP cube semantics.

SELECT
    year_name
    , sales
    , sales_year_ago
FROM
    sales_av HIERARCHIES (time_hier)
WHERE
    time_hier.level_name = 'YEAR'
    AND year_name != 'CY2011'
ORDER BY
    year_name;

The relational query returns null for SALES_YEAR_AGO for CY2012 because the filter is applied prior to GROUP BY. This is consistent with relational semantics.

SELECT
    year
  , AV_AGGREGATE(sales)          AS sales
  , AV_AGGREGATE(sales_year_ago) AS sales_year_ago
FROM
    sales_av_view
WHERE
    year != 'CY2011'
GROUP BY
    year
ORDER BY
    year;

With the filter eliminated, the relational query returns a non NULL values for SALES_YEAR_AGO in CY2012.

SELECT
    year
    , AV_AGGREGATE(sales)          AS sales
    , AV_AGGREGATE(sales_year_ago) AS sales_year_ago
FROM
    sales_av_view
GROUP BY
    year
ORDER BY
    year;

When querying the relational representation, the HAVING clause can be used to exclude CY2011 from the query result while returning Sales Year Ago for CY2012.

SELECT
    year
  , AV_AGGREGATE(sales)          AS sales
  , AV_AGGREGATE(sales_year_ago) AS sales_year_ago
FROM
    sales_av_view
GROUP BY
    year
HAVING
    year != 'CY2011'
ORDER BY
    year;

Dimension Views and Star Queries

The examples so far have queried a single, flattened view of the analytic view. You might want to add dimension views to use as lookup tables or your application might want to map to dimension views and join them to the fact table. The relational representation of the analytic view supports this star style model. You might be tempted to join the dimension table to the relational representation of the analytic view as in this query.

SELECT
    t.year_name
  , AV_AGGREGATE(f.sales)
FROM
    time_dim      t
  , sales_av_view f
WHERE
    t.month_name = f.month
GROUP BY
    t.year_name;

This will result in:

ORA-18388: Invalid usage of analytic view aggregation function.

If you query a fact column, nulls are returned.

SELECT
    t.year_name
  , SUM(f.sales)
FROM
    time_dim      t
  , sales_av_view f
WHERE
    t.month_name = f.month
GROUP BY
    t.year_name;

An analytic view can be joined to tables, but not within the context of a FACT ROWS view. A fact rows can can be joined to a dimension view created from the analytic view using the STAR ROWS DIMENSION keywords.

CREATE OR REPLACE VIEW time_dim_view AS
    SELECT
        time_hier.year_name
      , time_hier.quarter_name
      , time_hier.month_name
    FROM
        sales_av
    STAR ROWS DIMENSION time_attr_dim;

This view can be joined to the FACT ROWS view.

SELECT
    t.year_name
  , AV_AGGREGATE(f.sales)
  , SUM(sales_fact)
FROM
    time_dim_view      t
  , sales_av_view f
WHERE
    t.month_name = f.month
GROUP BY
    t.year_name;

With both the dimension view and the view of the analytic fact as STAR ROWS and FACT ROWS views, the Database understands the full context of the query and can best optimize the generated SQL.

Relational Representation and the Materialized Cache

The analytic view MATERIALIZED CACHE clause allows the analytic view to access data stored in aggregate tables. These aggregate tables are designed specifically for the analytic view, and are relatively small and efficient as compared to the typical materialized view used by a star schema. Materialized caches may be manually created at specific levels of aggregation or they may be system managed.

(A good starting strategy is to create a materialized cache at the first or second level from the top of a hierarchy. This will typically have the greatest benefit while also being small in size.)

Using the following statement, a materialized cache is added to the SALES_AV analytic view. Note the CACHE clause.

CREATE OR REPLACE ANALYTIC VIEW sales_av
USING sales_fact
DIMENSION BY (
    time_attr_dim
        KEY month_id REFERENCES month_name
        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 AGGREGATE BY SUM
    , units FACT units AGGREGATE BY SUM
    , sales_prior_period AS (LAG(sales) OVER (HIERARCHY time_hier OFFSET 1))
    , sales_chg_prior_period AS (LAG_DIFF(sales) OVER (HIERARCHY time_hier OFFSET 1))
    , sales_pct_chg_prior_period AS (LAG_DIFF_PERCENT(sales) OVER (HIERARCHY time_hier OFFSET 1))
    , 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))
    , sales_shr_of_product AS (SHARE_OF(sales HIERARCHY product_hier MEMBER ALL))
    )
CACHE
    -- Cache specification for sales and units at the year, department, and country levels
    MEASURE GROUP (
        sales
        , units)
        LEVELS (
          time_hier.year,
          product_hier.department,
          geography_hier.country)
        MATERIALIZED
FACT COLUMNS (sales, units);

This PL/SQL code will create the aggregate table.

DECLARE
  mvsql CLOB;
BEGIN
  mvsql := dbms_hierarchy.get_mv_sql_for_av_cache('SALES_AV', 0);

  EXECUTE IMMEDIATE
    'CREATE TABLE sales_av_cache AS ' || mvsql;

END;
/

If you want to see the SQL, you run the following SELECT statement.

SELECT TO_CHAR(dbms_hierarchy.get_mv_sql_for_av_cache (
    analytic_view_name => 'SALES_AV'
    , cache_idx => 0 )) FROM dual;

The table contains aggregate data at the specified levels. The analytic view will access this table for any query at or above the levels of the materialized cache.

The materialized cache can be a materialized view or a table. In this tutorial it has been created as a table. The analytic view will be replaced, now with MATERIALIZED USING sales_av_cache. USING directs the database to always use the table when it is possible to do so. (Note that this rewrite is not using materialized view query rewrite.)

CREATE OR REPLACE ANALYTIC VIEW sales_av
USING sales_fact
DIMENSION BY (
    time_attr_dim
        KEY month_id REFERENCES month_name
        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 AGGREGATE BY SUM
    , units FACT units AGGREGATE BY SUM
    , sales_prior_period AS (LAG(sales) OVER (HIERARCHY time_hier OFFSET 1))
    , sales_chg_prior_period AS (LAG_DIFF(sales) OVER (HIERARCHY time_hier OFFSET 1))
    , sales_pct_chg_prior_period AS (LAG_DIFF_PERCENT(sales) OVER (HIERARCHY time_hier OFFSET 1))
    , 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))
    , sales_shr_of_product AS (SHARE_OF(sales HIERARCHY product_hier MEMBER ALL))
    )
CACHE
    -- Cache specification for sales and units at the year, department, and country levels
    MEASURE GROUP (
        sales
        , units)
        LEVELS (
          time_hier.year,
          product_hier.department,
          geography_hier.country)
        MATERIALIZED USING sales_av_cache
FACT COLUMNS (sales, units);

The following query, at the year, department, and country levels, will use the materialized cache.

SELECT
    year
  , department
  , region
  , AV_AGGREGATE(sales)                  AS sales
  , SUM(sales_fact)                      AS sales_fact
  , SUM(units_fact)                      AS units_fact
FROM
    sales_av_view
WHERE
    region = 'Africa'
GROUP BY
    year
  , department
  , region;

Access to the SALES_AV_CACHE table can be seen in the execution plan.

TRUNCATE TABLE plan_table;
EXPLAIN PLAN
SET STATEMENT_ID = '2' INTO plan_table FOR
SELECT
    year
  , department
  , region
  , AV_AGGREGATE(sales)                  AS sales
  , SUM(sales_fact)                      AS sales_fact
  , SUM(units_fact)                      AS units_fact
FROM
    sales_av_view
WHERE
    region = 'Africa'
GROUP BY
    year
  , department
  , region;

You can view the plan with the following query. Note that the analytic view is accessing the SALES_AV_CACHE table rather than the SALES_FACT table.

SELECT LPAD('............................',2*(LEVEL-1)) ||operation operation,
  OPTIONS,
  object_name,
  position
FROM plan_table
START WITH id       = 0
 AND statement_id      = '2'
 CONNECT BY PRIOR id = parent_id
 AND statement_id      = '2'; 

So you now see an additional advantage of using the analytic view, transparent access to aggregate tables.

The materialized cache is a rich feature deserving of its own tutorial. That said, here are a few important points of information:

• The materialized cache can be manually managed, as in this example, or automatically managed.
• See the DBMS_AVTUNE package form more information on automatically managed materialized caches.
• The materialized cache services both hierarchical and relational style queries.
• The materialized cache pre-aggregates data. You can choose when and how to refresh it, and whether the analytic view is allowed to use a stale aggregate table.
• The materialized cache may contain multiple level and measure groups, each with their own table, to optimize different workloads.

Summary

In this tutorial you learned that the analytic view can be queried with both hierarchical and relational GROUP BY style queries, with each providing full access the the data of the analytic view. Relational GROUP BY style queries allow any tool that includes support for aggregation pass-through functions to access measures calculated by the analytic view, and any tool can query columns of the fact table using relational aggregation operators.

There are many advantages to using analytic view as compared to accessing tables directly. The analytic view:

• Provides the owner of a data set to provide an in-database semantic model that presents data to many application as a 'single version of the truth'.
• Can extend a data set with rich calculations, easing the burden on users to define calculations in business intelligence tools.
• Provides a flattened presentation of data, simplifying the definition of data sets in business intelligence tools and query generation in custom applications.
• Provides an simple and efficient method of creating and using aggregate table to improve query performance.

Questions or Comments?

Get in touch at william.endress@oracle.com.