Analytic Views are one of the main features for Business Intelligence introduced with Oracle 12c Release 2. They provide a lot of new possibilities for analytical queries on star schemas. This blog post gives an overview of the new functionality.
In my last blog post, I wrote about the new objects Attribute Dimensions and Hierarchies in Oracle 12.2. They are required to define additional metadata and hierarchy information for the dimension tables of a star schema. This information is needed for another new type of objects in Oracle 12c Release 2: Analytic Views.
An Analytic View is created on top of a fact table and several dimensions and hierarchies. It supports aggregations of measures on different hierarchy levels for each dimension. Additionally, different kinds of calculated measures can be defined in an Analytic View. This simplifies many queries in BI reports and OLAP tools.
The following picture shows the different types of metadata objects that are created in this context:
- For each dimension table, an Attribute Dimension is defined.
- On top of each Attribute Dimension, at least one Hierarchy is defined. A dimension may have multiple hierarchies.
- On the fact table, one or more Analytic Views are defined, each of them referring several Attributes Dimensions and Hierarchies.
Creating an Analytic View
The Analytic View created here is an example of Oracle’s Live SQL Tutorial Creating an Analytic View for the Sales History (SH) Sample Schema. The Attribute Dimension and Hierarchy for the dimension table PRODUCTS are explained in my last blog post. The complete definition of the metadata objects for all dimensions of the SH schema and the corresponding DDL statements are described in the Live SQL Tutorial.
The first version of the Analytic View SH_SALES_HISTORY_AV contains only the base measures AMOUNT_SOLD and QUANTITY_SOLD. It can be created with the following DDL statement (which I copied from the tutorial, too):
CREATE OR REPLACE ANALYTIC VIEW sh_sales_history_av
USING sh.sales
DIMENSION BY
(
sh_times_attr_dim
KEY time_id REFERENCES time_id
HIERARCHIES (sh_times_calendar_hier DEFAULT, sh_times_fiscal_hier),
sh_products_attr_dim
KEY prod_id REFERENCES prod_id
HIERARCHIES (sh_products_hier DEFAULT),
sh_customers_attr_dim
KEY cust_id REFERENCES cust_id
HIERARCHIES (sh_customers_hier DEFAULT),
sh_channels_attr_dim
KEY channel_id REFERENCES channel_id
HIERARCHIES (sh_channels_hier DEFAULT),
sh_promotions_attr_dim
KEY promo_id REFERENCES promo_id
HIERARCHIES (sh_promotions_hier DEFAULT)
)
MEASURES (
amount_sold FACT amount_sold,
quantity_sold FACT quantity_sold
)
DEFAULT MEASURE amount_sold;
The statement does not create a new table or any kind of database segment, but an additional metadata layer on top of a star schema. It consists of the following parts:
- USING: This part defines the base fact table (or view) that is used for the Analytic View. In this example, the fact table SALES of the SH schema is the base table.
- DIMENSION BY: All dimensions that are referred in the Analytic View must be defined here. Important: Not the dimension tables are named, but the Attribute Dimensions that were created before with a CREATE ATTRIBUTE DIMENSION command.
- KEY … REFERENCES: For each dimension, the dimension key of the fact table and the corresponding key in the Attribute Dimension must be specified.
- HIERARCHIES: For each dimension, one default hierarchy and optionally additional hierarchies can be specified. The Hierarchy objects have to be defined before with a CREATE HIERARCHY command.
- MEASURES: Here, the measures are defined. These can be either base measures of the fact table, as in the example above, or calculated measures, as we will see later. One of the measures can be specified as default measure. Optionally, an aggregation function can be defined. The default is SUM, which is the typical aggregation function for additive measures.
The example does not contain all options that are available. There are much more possibilities like user-defined classifications, aggregate clauses, caching specifications, etc.
Queries on an Analytic View
A query on an Analytic View looks like a regular SELECT statement at the first glance, but the syntax is slightly different. After the FROM clause, the hierarchies for the specific query must be defined. In the SELECT clause and in the WHERE condition, all attributes including additional metadata attributes (e.g. MEMBER_NAME, LEVEL_NAME, DEPTH) can be used. The attribute HIER_ORDER of each hierarchy is typically used to sort the result set.
The following query shows the amount and quantity sold per year, along the product hierarchy for product category ‘Electronics’:
SELECT sh_times_calendar_hier.member_name AS calendar_hier
, RPAD(‘ ‘, sh_products_hier.depth * 2)
||sh_products_hier.member_name AS products_hier
, amount_sold
, quantity_sold
FROM sh_sales_history_av
HIERARCHIES (sh_times_calendar_hier, sh_products_hier)
WHERE sh_times_calendar_hier.level_name = ‘CALENDAR_YEAR’
AND sh_products_hier.prod_category = ‘Electronics’
ORDER BY
sh_times_calendar_hier.hier_order
, sh_products_hier.hier_order
, sh_customers_hier.hier_order;
The result of the query returns 66 rows and does not only contain the measures per product, but also the total for the hierarchy levels ‘Subcategory’ and ‘Category’:
CALENDAR_HIER PRODUCTS_HIER AMOUNT_SOLD QUANTITY_SOLD
————- —————————————————— ———– ————-
1998 Electronics 1775688.87 9853
1998 Game Consoles 11.99 1
1998 Y Box 11.99 1
1998 Home Audio 1568900.57 2688
1998 8.3 Minitower Speaker 578374.62 1043
1998 Home Theatre Package with DVD-Audio/Video Play 990525.95 1645
1998 Y Box Accessories 73744.81 2684
1998 Extension Cable 1685.89 211
1998 Xtend Memory 72058.92 2473
1998 Y Box Games 133031.5 4480
1998 Adventures with Numbers 35771.66 2147
1998 Endurance Racing 97259.84 2333
1999 Electronics 2916369.92 24105
1999 Game Consoles 272901.18 884
1999 Y Box 272901.18 884
…
2001 Fly Fishing 16726.41 2102
2001 Martial Arts Champions 53100.3 2506
2001 Smash up Boxing 100546.16 3121
66 rows selected.
Of course, it would be possible to generate the same result with a “traditional” SQL statement, but because of the subtotals of each hierarchy level, this statement would be more complex. The following SQL query returns the same result:
WITH prod_level AS (
SELECT t.calendar_year
, p.prod_subcategory
, p.prod_category
, ‘ ‘||p.prod_name AS products_hier
, SUM(s.amount_sold) AS amount_sold
, SUM(s.quantity_sold) AS quantity_sold
, t.calendar_year||’/’||p.prod_category||’/’||p.prod_subcategory||’/’||p.prod_name AS sort_order
FROM sales s
JOIN times t ON (s.time_id = t.time_id)
JOIN products p ON (s.prod_id = p.prod_id)
WHERE p.prod_category = ‘Electronics’
GROUP BY
t.calendar_year
, p.prod_name
, p.prod_subcategory
, p.prod_category
ORDER BY
t.calendar_year
, p.prod_subcategory
, p.prod_name
)
, subcat_level AS (
SELECT calendar_year
, ‘ ‘||prod_subcategory AS products_hier
, SUM(amount_sold) AS amount_sold
, SUM(quantity_sold) AS quantity_sold
, calendar_year||’/’||prod_category||’/’||prod_subcategory AS sort_order
FROM prod_level
GROUP BY
calendar_year
, prod_category
, prod_subcategory
)
, cat_level AS (
SELECT calendar_year
, ‘ ‘||prod_category AS products_hier
, SUM(amount_sold) AS amount_sold
, SUM(quantity_sold) AS quantity_sold
, calendar_year||’/’||prod_category AS sort_order
FROM prod_level
GROUP BY
calendar_year
, prod_category
)
SELECT calendar_year
, products_hier
, amount_sold
, quantity_sold
, sort_order
FROM prod_level
UNION ALL
SELECT calendar_year
, products_hier
, amount_sold
, quantity_sold
, sort_order
FROM subcat_level
UNION ALL
SELECT calendar_year
, products_hier
, amount_sold
, quantity_sold
, sort_order
FROM cat_level
ORDER BY sort_order;
This simple example shows one advantage of Analytic Views: The complexity of the SQL statements can be reduced in ad-hoc queries, reports or generated SQL statements of OLAP tools. But how about performance? This is another story – and probably the subject of a future blog post.
Calculated Measures in Analytic Views
The Analytic View above contains only base measures, i.e. columns of the fact table. But the most powerful advantage of Analytic Views is the variety of calculated measures that can be defined in the Analytic View definition. These can be simple single row expression, e.g. total_amount AS (amount_sold * quantity_sold), but also complex windowing expressions like those you know from Analytic Functions. The following DDL statement shows how the existing Analytic View SH_SALES_HISTORY_AV can be enhanced with several calculated measures (in white color). The example is a subset of the use case in the Live SQL Tutorial – the original view definition contains some more calculated measures.
CREATE OR REPLACE ANALYTIC VIEW sh_sales_history_av
USING sh.sales
DIMENSION BY
(
sh_times_attr_dim
KEY time_id REFERENCES time_id
HIERARCHIES (sh_times_calendar_hier DEFAULT, sh_times_fiscal_hier),
sh_products_attr_dim
KEY prod_id REFERENCES prod_id
HIERARCHIES (sh_products_hier DEFAULT),
sh_customers_attr_dim
KEY cust_id REFERENCES cust_id
HIERARCHIES (sh_customers_hier DEFAULT),
sh_channels_attr_dim
KEY channel_id REFERENCES channel_id
HIERARCHIES (sh_channels_hier DEFAULT),
sh_promotions_attr_dim
KEY promo_id REFERENCES promo_id
HIERARCHIES (sh_promotions_hier DEFAULT)
)
MEASURES (
— Amount sold maps to the fact table.
amount_sold FACT amount_sold,
— Quantity sold maps to the fact table.
quantity_sold FACT quantity_sold,
— Ratio of amount sold for the current value to the parent product value.
amt_sold_shr_parent_prod AS
(SHARE_OF(amount_sold HIERARCHY sh_products_hier PARENT)),
— Ratio of amount sold for the current value to the parent customer value.
sales_shr_parent_cust AS
(SHARE_OF(amount_sold HIERARCHY sh_customers_hier PARENT)),
— Sales Calendar Year to Date
sales_cal_ytd AS (SUM(amount_sold) OVER
(HIERARCHY sh_times_calendar_hier BETWEEN UNBOUNDED PRECEDING AND CURRENT MEMBER WITHIN ANCESTOR AT LEVEL calendar_year)),
— Sales same period 1 year ago.
sales_cal_year_ago as (LAG(amount_sold) OVER
(HIERARCHY sh_times_calendar_hier OFFSET 1 ACROSS ANCESTOR AT LEVEL calendar_year)),
— Change in sales for the current period as compared to the same period 1 year ago.
sales_chg_cal_year_ago as (LAG_DIFF(amount_sold) OVER
(HIERARCHY sh_times_calendar_hier OFFSET 1 ACROSS ANCESTOR AT LEVEL calendar_year)),
— Percent change in sales for the current period as compared to the same period 1 year ago.
sales_pctchg_cal_year_ago as (LAG_DIFF_PERCENT(amount_sold) OVER
(HIERARCHY sh_times_calendar_hier OFFSET 1 ACROSS ANCESTOR AT LEVEL calendar_year))
)
DEFAULT MEASURE amount_sold;
Whereas single row expressions can also be implemented with Virtual Columns (see blog post Derived Measures and Virtual Columns), the flexibility of calculated measure expressions in an Analytic View is much higher. An overview of possible expressions with examples can be found in the Oracle Database Data Warehousing Guide 12c Release 2, chapter 26. In the Database SQL Language Reference manual, the syntax definition of Calculated Measures Expressions is explained.
Conclusion
I think for most of us, it will take some time to get experience with these new expressions and appreciate their power. This was already the case with Analytic Functions in Oracle 8i, with the Model Clause in Oracle 10g or with SQL Pattern Matching in Oracle 12c Release 1. I am sure this will not be my last blog post about Analytic Views. This powerful feature gives us a lot of new benefits in BI applications and ad-hoc queries.
