Oracle Live SQL - Tutorial: Creating Analytic Views

Module 1

Introduction

This is an in-depth tutorial that walks you through designing a very functional analytic view. Please take your time. You will be rewarded with a solid understanding of the most important features and characteristics of analytic views.

An analytic view is a type of view that can be used to extend a data set with aggregate data, calculated measures and description metadata. Analytic views dramatically simplify the SQL needed to execute analytic queries.

Typical use cases for analytic views include:

Extending a data set with time series, hierarchical (e.g., share of parent) and other calculations.
Simplifying the development of business intelligence applications (for example, a BI dashboard built in Oracle Application Express) due to embedded calculations and simplified SQL generation.
Improving query performance of applications that cannot otherwise access aggregate tables and are not optimized specifically for the Oracle Database.

Analytic views do not store data. Analytic views can access data in:

Tables, views and external tables.
Star and snowflake schema
Flat/denormalized fact tables.
Aggregate fact tables.

As a general rule:

Smaller data sets will perform well when the analytic view is layered over flat/denormalized fact tables.
Larger data sets will perform well using a star schema (better yet, a star schema loaded into the in-memory column store using the Oracle Database In-Memory option).
Aggregate tables can dramatically improve query performance for any style schema.

The analytic view created in this tutorial will use a star schema consisting of 3 dimension tables and a fact table. All tables are in the AV sample schema.

The time dimension table includes data for months, quarters and years.
The geography dimension table includes data for states/provinces, countries and regions.
The product dimension table contains data for categories and departments.
The fact table contains sales data.

Before creating the analytic view, review the sample data.

Time dimension data.


SELECT * FROM av.time_dim ORDER BY month_end_date;

Year level data.


SELECT DISTINCT year_id,
  year_name,
  year_end_date
FROM av.time_dim
ORDER BY year_end_date;

Quarter level data.


SELECT DISTINCT quarter_id,
  quarter_name,
  quarter_of_year,
  quarter_end_date,
  year_id,
  year_name
FROM av.time_dim
ORDER BY quarter_end_date;

Product dimension data.


SELECT * FROM av.product_dim;

Geography dimension data.


SELECT * FROM av.geography_dim;

Fact data.


SELECT * FROM av.sales_fact WHERE rownum <= 20;

A star query joins the dimension tables to the fact table and aggregates sales.


SELECT
  t.year_name,
  p.department_name,
  g.region_name,
  SUM(f.sales)
FROM
 av.time_dim t,
 av.product_dim p,
 av.geography_dim g,
 av.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.region_name
ORDER BY
  t.year_name,
  p.department_name,
  g.region_name;

In this tutorial you will create 1 attribute dimension and 1 hierarchy each using the AV.TIME_DIM, AV.PRODUCT_DIM and AV.GEOGRAPHY_DIM tables and 1 analytic view using the AV.SALES_FACT table.

Module 2

Creating Attribute Dimensions and Hierarchies

Most metadata used by dimensions and hierarchies is defined in an attribute dimension object. This allows the metadata for attributes and levels to be re-used by multiple hierarchies, promoting consistency and simplifying the definition of the hierarchy.

An attribute dimension may be as simple as a list of attributes and levels defined only with a key attribute or a rich collection of metadata that provides applications with a variety of hierarchical attributes and presentation metadata.

You will start by creating a simple attribute dimension and a hierarchy. Later, you will add a variety of properties to the attribute dimensions and hierarchies.

The first attribute dimension and hierarchy will use only the _ID columns from the TIME_DIM table. These will support a hierarchy with three levels.

Review data in the _ID columns in the TIME_DIM table.

SELECT month_id,
  quarter_id,
  year_id
FROM av.time_dim
ORDER BY year_id,
  quarter_id;

Some notes about the attribute dimension object:

The USING clause identifies the dimension table (or view). A single table or view name may be provided.
The ATTRIBUTES list includes a list of columns in the dimension table that will be used by hierarchies. Column names may be used 'as is' or they may be aliased.
The LEVEL clause defines a level that can be used in hierarchy for aggregation and navigation. Each attribute dimension must have at least one level.
The KEY property of a level specifies the attribute that provides the key values of that level. The key attribute is the value at that level that is used when aggregating data and executing calculated measures. You might think of the key attribute at the primary key of the level.
The DETERMINES property of a level includes a list of attributes that have values which can be uniquely identified by the KEY attribute of the level. For example for each MONTH_ID value there is only one QUARTER_ID value. When levels are used in a hierarchy a parent level inherits the determined attributes of child level. For example, if the MONTH level determines the QUARTER_ID attribute and the QUARTER level is a child of the YEAR level, it is assumed that the MONTH level also determines YEAR_ID.
Optionally, the attribute dimension may include an ALL MEMBER clause. This allows you to specify the value for the single, top-most hierarchy value. If an ALL MEMBER clause is not included the top-most value defaults to 'ALL'.

IMPORTANT: The DETERMINES property is one of the more difficult concepts to understand about analytic views. Pay careful attention to any descriptions and examples involving the DETERMINES property. The DETERMINES property plays three very important roles:

Whether an attribute is included as a column in a hierarchy.
How SQL generated by the analytic views when it is queries in optimized.
How the MEMBER_UNIQUE_NAME value is formed.

Whether an attribute is determined by the KEY attribute of a level can be answered by a simple question - "for each value of the key attribute is there only 1 value of the determined attribute?" If the answer is yes, it may (and probably should) be a determined attribute.

This question can be answered with a simple query:


SELECT
  year_id,
  COUNT(DISTINCT year_name)
FROM
  av.time_dim
GROUP BY
  year_id;

If the values of COUNT DISTINCT is always 1, the attribute may be determined by the level KEY attribute. In the case of year_name, it may be determined by YEAR_ID at the YEAR level.

Can QUARTER_NAME be determined by YEAR_ID (KEY attribute of the YEAR level)? Let's check:


SELECT
  year_id,
  COUNT(DISTINCT quarter_name)
FROM
  av.time_dim
GROUP BY
  year_id;

No, COUNT DISTINCT is greater than 1 for at least one year.

There are hierarchy and analytic view validation procedures that can check if data meets the rules of DETERMINES. (That will be covered in a different tutorial).

Well, that discussion was probably a bit exhausting for a 'getting started' tutorial but it's important. Thanks for hanging in there!

Create the attribute dimension for time. (Note that in this very simple attribute dimension the ALL MEMBER clause been omitted.)

CREATE OR REPLACE ATTRIBUTE DIMENSION time_attr_dim
USING av.time_dim 
ATTRIBUTES 
 (year_id,
  quarter_id,
  month_id)
LEVEL MONTH
  KEY month_id
  DETERMINES (quarter_id)
LEVEL QUARTER
  KEY quarter_id
  DETERMINES (year_id)
LEVEL YEAR
  KEY year_id;

You cannot select from an attribute dimension, but you can see it in the data dictionary. Note that the ALL_MEMBER_NAME as defaulted to ALL because the CREATE statement did not include the ALL MEMBER NAME clause.

SELECT * FROM user_attribute_dimensions WHERE dimension_name = 'TIME_ATTR_DIM';

The list of attributes in the attribute dimension.

SELECT * FROM user_attribute_dim_attrs WHERE dimension_name = 'TIME_ATTR_DIM';

A list of levels of the attribute dimension.

SELECT * FROM USER_ATTRIBUTE_DIM_LEVELS;

The hierarchy object is simply a list of levels. Lower (more detailed) levels are children of higher (more aggregated) levels. The hierarchy object inherits all the metadata about levels and attributes from the attribute dimension.

Create a hierarchy using the TIME_ATTR_DIM attribute dimension.

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

Levels of the hierarchy

SELECT * FROM USER_HIER_LEVELS WHERE hier_name = 'TIME_HIER';

Select from the TIME_HIER hierarchy.

SELECT *
FROM time_hier
ORDER BY hier_order;

This is a simple but functional hierarchy. While functional, there are important features not included in this hierarchy. Note that values in the MEMBER_NAME column might not be easily readable and the Quarter and Year levels, the MEMBER_CAPTION and MEMBER_DESCRIPTION columns do not return data and the ordering of time periods is not correct for reporting or time series calculations (for example, note that February comes before January).

Module 3

MEMBER NAME, MEMBER CAPTION and MEMBER DESCRIPTION Properties

The MEMBER NAME, MEMBER CAPTION and MEMBER DESCRIPTION properties of a level are used to populate hierarchical attribute columns of the same name. These attributes are typically used for textual descriptions of the KEY attribute. For example ‘CY2014’ for YEAR_ID 14, ‘Q1CY2014’ for QUARTER_ID 114 and ‘Cameras and Accessories’ for CATEGORY_ID -532. These properties provide the opportunity to use different text descriptions for the same KEY attribute, for example ‘Jan-14’ and ‘January 2014.’

IMPORTANT: There should be a a 1:1 relationship between KEY attribute values and values of the MEMBER NAME, MEMBER CAPTION and MEMBER DESCRIPTION columns. If this is not true, there might be unexpected results when querying the hierarchy and analytic views that reference the hierarchy. It is assumed that the KEY attribute determines the MEMBER NAME, MEMBER CAPTION and MEMBER DESCRIPTION, so those attributes do not need to be listed in the DETERMINES property.

To add these properties:

Add the _NAME columns to the ATTRIBUTES list.
Add MEMBER_NAME, MEMBER_CAPTION and MEMBER_DESCRIPTION properties to levels.

Create the attribute dimension object.

CREATE OR REPLACE ATTRIBUTE DIMENSION time_attr_dim
USING av.time_dim
ATTRIBUTES
 (year_id,
  year_name,
  quarter_id,
  quarter_name,
  month_id,
  month_name,
  month_long_name)
LEVEL MONTH
  KEY month_id
  MEMBER NAME month_name
  MEMBER CAPTION month_name
  MEMBER DESCRIPTION month_long_name
  DETERMINES (quarter_id)
LEVEL QUARTER
  KEY quarter_id
  MEMBER NAME quarter_name
  MEMBER CAPTION quarter_name
  MEMBER DESCRIPTION quarter_name
  DETERMINES (year_id)
LEVEL YEAR
  KEY year_id
  MEMBER NAME year_name
  MEMBER CAPTION year_name
  MEMBER DESCRIPTION year_name;

If attributes used for MEMBER NAME, MEMBER CAPTION or MEMBER DESCRIPTION are not a text data type, convert them to text using TO_CHAR. For example, TO_CHAR(category_id) could be used if you wanted to use the CATEGORY_ID column in the PRODUCT_DIM table.

TIME_HIER hierarchy will automatically use these new properties. The hierarchy does not need to be replaced or compiled.

SELECT the _NAME, MEMBER_NAME, MEMBER_CAPTION and MEMBER_DESCRIPTION columns from the TIME_HIER hierarchy.

SELECT year_name,
  quarter_name,
  month_name,
  month_long_name,
  member_name,
  member_caption,
  member_description
FROM time_hier
ORDER BY hier_order;

Module 4

ORDER BY Property

By default, values are sorted alphabetically within level by the KEY attribute value. This can be seen in the TIME_HIER hierarchy with the following query.

Select from TIME_HIER ordering by HIER_ORDER. Note the sort order of months within quarters.

SELECT year_id,
  quarter_id,
  month_id,
  member_name,
  hier_order
FROM time_hier
ORDER BY hier_order;

Sorting time periods alphabetically is probably not how a user would like to display data in a report and is not appropriate for time series calculations. For example, the LAG of Jan-12 should Dec-11 rather than Feb-11.

The AV.TIME_DIM table includes _END_DATE columns that can be used to sort time values.

Select from the TIME_DIM table ordering by YEAR_END_DATE.

SELECT distinct year_id,
 year_name,
 year_end_date
FROM av.time_dim
ORDER BY year_end_date;

Select from the TIME_DIM table ordering by QUARTER_END_DATE.

SELECT distinct quarter_id,
  quarter_name,
  quarter_end_date
FROM av.time_dim
ORDER BY quarter_end_date;

Select from the TIME_DIM table ordering by MONTH_END_DATE.

SELECT distinct month_id,
  month_name,
  month_end_date
FROM av.time_dim
ORDER BY month_end_date;

To use the _END_DATE columns to sort time values:

Add the _END_DATE attributes the attribute list.
Add the _END_DATE columns to the ORDER BY property of each level.

Create the attribute dimension.

CREATE OR REPLACE ATTRIBUTE DIMENSION time_attr_dim
USING av.time_dim
ATTRIBUTES
 (year_id,
  year_name,
  year_end_date,
  quarter_id,
  quarter_name,
  quarter_end_date,
  month_id,
  month_name,
  month_long_name,
  month_end_date)
LEVEL MONTH
  KEY month_id
  MEMBER NAME month_name
  MEMBER CAPTION month_name
  MEMBER DESCRIPTION month_long_name
  ORDER BY month_end_date
  DETERMINES (quarter_id)
LEVEL QUARTER
  KEY quarter_id
  MEMBER NAME quarter_name
  MEMBER CAPTION quarter_name
  MEMBER DESCRIPTION quarter_name
  ORDER BY quarter_end_date
  DETERMINES (year_id)
LEVEL YEAR
  KEY year_id
  MEMBER NAME year_name
  MEMBER CAPTION year_name
  MEMBER DESCRIPTION year_name
  ORDER BY year_end_date;

Select from TIME_HIER ordering by HIER_ORDER. Note that all time periods are now correctly sorted.

SELECT year_id,
  year_name,
  quarter_id,
  quarter_name,
  month_id,
  member_name,
  hier_order
FROM time_hier
ORDER BY hier_order;

The ORDER BY property for each level could also be set using only the MONTH_END_DATE column by using the the MIN or MAX operator (either will work because the sort order is the same using each method).

CREATE OR REPLACE ATTRIBUTE DIMENSION time_attr_dim
USING av.time_dim
ATTRIBUTES
 (year_id,
  year_name,
  --year_end_date
  quarter_id,
  quarter_name,
  -- quarter_end_date,
  month_id,
  month_name,
  month_long_name,
  month_end_date)
LEVEL MONTH
  KEY month_id
  MEMBER NAME month_name
  MEMBER CAPTION month_name
  MEMBER DESCRIPTION month_long_name
  ORDER BY month_end_date
  DETERMINES (quarter_id)
LEVEL QUARTER
  KEY quarter_id
  MEMBER NAME quarter_name
  MEMBER CAPTION quarter_name
  MEMBER DESCRIPTION quarter_name
  ORDER BY MAX month_end_date
  DETERMINES (year_id)
LEVEL YEAR
  KEY year_id
  MEMBER NAME year_name
  MEMBER CAPTION year_name
  MEMBER DESCRIPTION year_name
  ORDER BY MAX month_end_date;

Query the TIME_HIER hierarchy and note that the values are correctly sorted.

SELECT year_id,
  year_name,
  quarter_id,
  quarter_name,
  month_id,
  member_name,
  hier_order
FROM time_hier
ORDER BY hier_order;

Module 5

Columns of the TIME_HIER Heararchy (and more about DETERMINES)

All columns of the TIME_HIER hierarchy now return data. Select the key attribute columns.

SELECT year_id,
  quarter_id,
  month_id
FROM time_hier
ORDER BY hier_order;

Note that some columns return NULL values. This is because the hierarchy returns rows for values at each level of the hierarchy. Rows with lower level data will return data for attributes in the current level or higher levels providing that the attributes at the higher level are determined by the lower level. For example, month level rows can return data for attributes at the quarter and year levels. This is more easily seen by adding the LEVEL_NAME and MEMBER_UNIQUE_NAME columns to the query.

SELECT year_id,
  quarter_id,
  month_id,
  level_name,
  member_unique_name
FROM time_hier
ORDER BY hier_order;

Select the MEMBER_NAME, MEMBER_CAPTION and MEMBER_DECRIPTION columns. The values returned for these columns are always the values at the level of the key attribute value (as seen in MEMBER_UNIQUE_NAME).

SELECT member_name,
  member_caption,
  member_description,
  month_name,
  level_name,
  member_unique_name
FROM time_hier
ORDER BY hier_order;

The hierarchy includes columns for each of the attributes used by the MEMBER NAME, MEMBER DESCRIPTION and MEMBER CAPTION properties. Like the key attributes, values are returned at and above the level of the row.

SELECT year_name,
  quarter_name,
  month_name,
  level_name,
  member_unique_name
FROM time_hier
ORDER BY hier_order;

The last CREATE ATTRIBUTE DIMENSION TIME_ATTR_DIM statement used the MONTH_END_DATE attribute for the ORDER BY property of all levels. Can that attribute by queried from the TIME_HIER hierarchy? Give it a try.

SELECT year_name,
  quarter_name,
  month_name,
  month_end_date,
  level_name,
  member_unique_name
FROM time_hier
ORDER BY hier_order;

The MONTH_END_DATE column does not exist in the hierarchy because it is not a determined attribute. Because the attributes used by the MEMBER NAME, MEMBER CAPTION and MEMBER DESCRIPTION properties are assumed to have a 1:1 relationship with the key attribute values, they are automatically considered to be determined attributes. It is not necessary to list these attributes in the DETERMINES property of a level.

The ORDER BY property can use an attribute that does not have a 1:1 relationship with the key attribute value if either the MIN or MAX operator is used. Therefore, they are not automatically considered to be determined attributes. For example the YEAR level can use the MAX of the MONTH_END_DATE attribute even though there are 12 MONTH_END_DATE values for each year.

SELECT distinct year_id,
  year_name,
  month_end_date
FROM av.time_dim
ORDER BY month_end_date;

Setting ORDER BY to MAX(month_end_date) reduces the number of values for each year to 1 as can be see in the following query.

SELECT year_id,
  year_name,
  MAX(month_end_date)
FROM av.time_dim
GROUP BY year_id,
  year_name
ORDER BY MAX(month_end_date);

MONTH_END_DATE could be used as a determined attribute at the MONTH level because there is a 1:1 relationship between MONTH_ID and MONTH_END_DATE, but it cannot be used as a determined attribute at the QUARTER or YEAR levels because there is not a 1:1 relationship with the key attributes of those levels.

The following CREATE statement adds the MONTH_END_DATE column to hierarchy views by including MONTH_END_DATE in the DETERMINES property of the MONTH level.

CREATE OR REPLACE ATTRIBUTE DIMENSION time_attr_dim
USING av.time_dim
ATTRIBUTES
 (year_id,
  year_name,
  --year_end_date
  quarter_id,
  quarter_name,
  -- quarter_end_date,
  month_id,
  month_name,
  month_long_name,
  month_end_date)
LEVEL MONTH
  KEY month_id
  MEMBER NAME month_name
  MEMBER CAPTION month_name
  MEMBER DESCRIPTION month_long_name
  ORDER BY month_end_date
  DETERMINES (quarter_id, month_end_date)
LEVEL QUARTER
  KEY quarter_id
  MEMBER NAME quarter_name
  MEMBER CAPTION quarter_name
  MEMBER DESCRIPTION quarter_name
  ORDER BY MAX month_end_date
  DETERMINES (year_id)
LEVEL YEAR
  KEY year_id
  MEMBER NAME year_name
  MEMBER CAPTION year_name
  MEMBER DESCRIPTION year_name
  ORDER BY MAX month_end_date;

Run the query selecting MONTH_END_DATE again.

SELECT year_name,
  quarter_name,
  month_name,
  month_end_date,
  level_name,
  member_unique_name
FROM time_hier
ORDER BY hier_order;

Attributes that are created by the hierarchy are referred to as hierarchical attributes. The following query selects each of the hierarchical attribute columns of the hierarchy.

SELECT member_unique_name,
  member_name,
  member_caption,
  member_description,
  level_name,
  hier_order,
  depth,
  is_leaf,
  parent_level_name,
  parent_unique_name
 FROM time_hier
 ORDER BY hier_order;

Module 6

Creating an Analytic View

An analytic view requires at least 1 hierarchy and a fact table with at least 1 measure column. Keys of the fact table should join to the key attribute of the lowest level in the attribute dimension (which should be the primary key of the dimension table).

The first analytic view will use the TIME_HIER hierarchy and the AV.SALES_FACT table. It will contain a single measure, SALES.

Some notes about the analytic view object:

The USING clause identifies the fact table (or view). A single table name or view name may be provided.
Joins and hierarchies are specified in the DIMENSION BY clause.

Include the attribute dimensions of any hierarchies that will be used with the analytic view.
The KEY property identifies the join key of the fact table. The KEY property references an attribute, not a column. If you have not aliased attributes in the attribute dimension, the attribute and column names are the same.
The REFERENCES property identifies the key attribute in the attribute dimension that the fact table joins to. This should be the key attribute that is derived from the primary key of the dimension table. In the case of a flat/denormalized fact table use REFERENCES DISTINCT.
Each hierarchy that will be used with the analytic view is listed in the HIERARCHIES parameter. Identify 1 hierarchy as the DEFAULT hierarchy.

Measures are listed in the MEASURES clause.

Use the FACT keyword to specify that the measure maps to a column in the fact table in the form measure name FACT fact table column.
Calculated measures are defined in the MEASURES clause. You will see that a later module.

Create the analytic view.

CREATE OR REPLACE ANALYTIC VIEW sales_av
USING av.sales_fact                   -- Refers to the SALES_FACT table.
DIMENSION BY                          -- List of attribute dimensions
  (time_attr_dim                      -- TIME_ATTR_DIM attribute dimension
    KEY month_id REFERENCES month_id  -- Dimension KEY joins to fact column.
    HIERARCHIES (                     -- List of hierarchies from the attar dim.
      time_hier DEFAULT))
MEASURES                              -- List of measures
 (sales FACT sales)                   -- SALES measure references SALES column.
DEFAULT MEASURE SALES;

You can see that the analytic view has been created with the following query.

SELECT * FROM user_analytic_views;

Because analytic views return both detail and aggregate level data across all hierarchies in the query it is very likely that the analytic view has to potential to return very large numbers of rows. Therefore, nearly every query of an analytic view should include filters to limit the number of rows returned. With only 1 hierarchy that returns only 86 rows, the SALES_AV analytic view is the exception to this rule. (This is probably the only time you will SELECT * from an analytic view!)

SELECT *
FROM sales_av HIERARCHIES(time_hier)
ORDER BY time_hier.hier_order;

Note that the analytic view includes all the columns of the TIME_HIER hierarchy and SALES at detail and aggregate levels.

Select sales at the year level.

SELECT member_name,
  sales
FROM sales_av HIERARCHIES (time_hier)
WHERE time_hier.level_name = 'YEAR';

Select sales at the quarter level.

SELECT year_name,
  member_name,
  sales
FROM sales_av HIERARCHIES (time_hier)
WHERE time_hier.level_name = 'QUARTER';

Select sales for months in year CY2015.

SELECT member_name,
  sales
FROM sales_av HIERARCHIES(time_hier)
WHERE time_hier.level_name = 'MONTH'
AND time_hier.year_name = 'CY2015'
ORDER BY time_hier.hier_order;

Note that aggregation operators, GROUP BY and joins are not included in these queries. They are not needed because these are part of the definition of the analytic view and the analytic view returns both detail and aggregate level data.

Module 7

Adding a Fact Table Measure to the Analytic View

To add additional measures from the fact table, simply include the measure in the MEASURES list.

Add the UNITS measure to the SALES_AV analytic view.

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))
MEASURES
 (sales FACT sales,
  units FACT units)
DEFAULT MEASURE SALES;

Measures that are derived from the fact table can be seen in the following query.

SELECT * FROM user_analytic_view_base_meas;

View the SALES and UNITS data at the YEAR level.

SELECT time_hier.member_name as TIME,
 sales,
 units
FROM
 sales_av HIERARCHIES(time_hier)
WHERE time_hier.level_name = 'YEAR';

Module 8

Setting Aggregation Operators

The analytic views you have created so far have not included aggregation functions. In this case the analytic view defaults to using SUM. This can be seen by running the following query.

SELECT analytic_view_name,
  default_aggr
FROM user_analytic_views;

The measure definitions do not include aggregation functions. As a result, they default to the aggregation operator of the analytic view.

SELECT a.analytic_view_name,
  a.default_aggr,
  b.measure_name,
  b.aggr_function
FROM user_analytic_views a,
  user_analytic_view_base_meas b
WHERE a.analytic_view_name = b.analytic_view_name;

You can set the default aggregation operator of the analytic view using the DEFAULT AGGREGATE BY clause. Change the default aggregation operator to AVG. (Note that the DEFAULT AGGREGATE BY clause follows the DEFAULT MEASURE clause.)

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))
MEASURES
 (sales FACT sales,
  units FACT units)
DEFAULT MEASURE SALES
DEFAULT AGGREGATE BY AVG;

Check the default aggregation operator.

SELECT analytic_view_name,
  default_aggr
FROM user_analytic_views;

Select sales and units at the year_level.

SELECT member_name,
  sales AS avg_sales,
  units AS avg_units
FROM sales_av HIERARCHIES (time_hier)
WHERE time_hier.level_name = 'YEAR';

Aggregation functions can also be set for each measure. The next analytic view will set the default aggregation operator to SUM and add the AVG_SALES and AVG_UNITS measures. Also, note that more than one measure can be created using a single fact column.

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))
MEASURES
 (sales FACT sales,
  avg_sales FACT sales AGGREGATE BY AVG,
  units FACT units,
  avg_units FACT units AGGREGATE BY AVG 
  )
DEFAULT MEASURE SALES
DEFAULT AGGREGATE BY SUM;

Select both the sums and the averages. Note that the average is the average of the lowest levels of the hierarachy. In the example, the average of days.

SELECT member_name,
  sales              AS sum_sales,
  units              AS sum_units,
  ROUND(avg_sales,0) AS avg_sales,
  ROUND(avg_units,0) AS avg_units
FROM sales_av HIERARCHIES (time_hier)
WHERE time_hier.level_name = 'YEAR';

Create the analytic view with sales measures creating using a variety of aggregation functions.

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))
MEASURES
 (sales FACT sales,
  avg_sales FACT sales AGGREGATE BY AVG,
  count_sales FACT sales AGGREGATE BY COUNT,
  max_sales FACT sales AGGREGATE BY MAX,
  min_sales FACT sales AGGREGATE BY MIN,
  stddev_sales FACT sales AGGREGATE BY STDDEV,
  variance_sales FACT sales AGGREGATE BY VARIANCE,
  units FACT units,
  avg_units FACT units AGGREGATE BY AVG 
  )
DEFAULT MEASURE SALES
DEFAULT AGGREGATE BY SUM;

The aggregation functions of the analytic view and the measures.

SELECT a.analytic_view_name,
  a.default_aggr,
  b.measure_name,
  b.aggr_function
FROM user_analytic_views a,
  user_analytic_view_base_meas b
WHERE a.analytic_view_name = b.analytic_view_name;

Select all of the sales measures.

SELECT time_hier.member_name,
  sales,
  ROUND(avg_sales,0) AS avg_sales,
  count_sales,
  max_sales,
  min_sales,
  ROUND(stddev_sales,0) AS stddev_sales,
  ROUND(variance_sales,0) AS variance_sales
FROM sales_av HIERARCHIES (time_hier)
WHERE time_hier.level_name = 'YEAR';

Module 9

Adding Hierarchies to the Analytic View

Most analytic views will use more than one hierarchy from one or more attribute dimensions. In the case of the SALES_AV analytic view, start by creating attribute dimensions and hierarchies from the PRODUCT_DIM and GEOGRAPHY_DIM tables.

Create the PRODUCT_ATTR_DIM attribute dimension.

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

Create the PRODUCT_HIER hierarchy.

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

Select data from the PRODUCT_HIER hierarchy.

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

Create the GEOGRAPHY_ATTR_DIM attribute dimension.

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

Create the GEOGRAPHY_HIER hierarchy.

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

Select data from the GEOGRAPHY_HIER hierarchy.

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

Add the attribute dimensions and hierarchies to the DIMENSION BY list.

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,
  units FACT units
  )
DEFAULT MEASURE SALES;

With only the TIME_HIER hierarchy in the HIERARCHIES parameter, the query returns rows for only time periods.

Select from the SALES_AV analytic view using only the TIME_HIER hierarchy.

SELECT time_hier.member_name as TIME,
 sales,
 units
FROM
 sales_av HIERARCHIES(time_hier)
WHERE time_hier.level_name in ('YEAR')
ORDER BY time_hier.hier_order;

To access rows for the products and geographies, add the PRODUCT_HIER and GEOGRAPHY_HIER hierarchies to the HIERARCHIES parameter. Note that as more hierarchies are added it is important to use filters in the query to reduce the potential of returning very large numbers of rows.

Select from the SALES_AV analytic view with all three hierarchies.

SELECT time_hier.member_name AS Time,
 product_hier.member_name AS Product,
 geography_hier.member_name AS Geography,
 sales,
 units
FROM
 sales_av HIERARCHIES (time_hier, product_hier, geography_hier)
WHERE time_hier.level_name in ('YEAR')
  AND product_hier.level_name in ('DEPARTMENT')
  AND geography_hier.level_name in ('REGION')
ORDER BY time_hier.hier_order,
  product_hier.hier_order,
  geography_hier.hier_order;

Module 10

Adding a Calculated Measure

Calculated measures are added to the MEASURES list using expressions. Expressions reference elements of the analytic view rather than columns. SQL single row expressions can be used in addition to analytic view expressions.

Add the SALES_YEAR_AGO and SALES_PCT_CHG_YEAR_AGO calculated measures to the analytic view. Note that ROUND (a SQL single row function) is used in the SALES_PCT_CHG_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_pct_chg_year_ago AS (ROUND(LAG_DIFF_PERCENT(sales) OVER (HIERARCHY time_hier OFFSET 1 ACROSS ANCESTOR AT LEVEL year),2)),
  units FACT units
  )
DEFAULT MEASURE SALES;

The SALES and SALES_PCT_CHG_YEAR_AGO measures can be queried by including them in the SELECT list.

SELECT time_hier.member_name AS Time,
 product_hier.member_name AS Product,
 geography_hier.member_name AS Geography,
 sales,
 sales_year_ago,
 sales_pct_chg_year_ago
FROM
 sales_av HIERARCHIES (time_hier, product_hier, geography_hier)
WHERE time_hier.level_name in ('YEAR')
  AND product_hier.level_name in ('DEPARTMENT')
  AND geography_hier.level_name in ('REGION')
  AND time_hier.member_name in ('CY2014','CY2015')
ORDER BY time_hier.hier_order,
  product_hier.hier_order,
  geography_hier.hier_order;

Creating Analytic Views - Deep Dive