Querying Analytic Views - Deep Dive

View All Tutorials Login to Run Tutorial

Tutorial Querying Analytic Views - Deep Dive
Description This tutorial provides an introduction to querying hierarchies and analytic views. This is a good starting point for anyone who is new to analytic views.
Tags analytic views, hierarchy, hierarchies
Area SQL General
Contributor William Endress (Oracle)
Created Wednesday January 04, 2017
Modules 12

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 to Analytic Views
Analytic Views organize data using a hierarchical model and allow you to extend the data set with aggregate data and calculated measures. Data is presented in a view that is easily queried with relatively simple SQL. Like standard relational views, analytic views:
- Are metadata objects (that is, they do not store data).
- Can be queried using SQL.
- Can access data from other database objects such as tables, views and external tables.
- Can join multiple tables into a single view.
In addition, analytic views:
- Organize data into a rich business model using hierarchical concepts.
- Include system-generated columns with hierarchical data.
- Automatically aggregates data.
- Include embedded measure calculations that are easily defined using syntax based on the logical model.
- Includes presentation metadata.
A typical query selecting from tables includes each table in the FROM clause, joins and aggregations (GROUP BY). If measure calculations are required those must be expressed in the SELECT list. The FROM clause, joins, aggregations and measures calculations are included in the definition of the analytic view. As a result, queries selecting from analytic views can be much simpler. This approach has several benefits, including:
- Simplified and faster application development. It is much easier to define calculations within analytic views than it is to write or generate complex SELECT statements.
- Calculation rules can be defined once in the Database and re-used by any number of applications, providing end-users with greater freedom in the choice of reporting tools without concern for inconsistent results.
- The ability to increase the value of applications by enhancing data sets with aggregate data and measure calculations.
Module 2
Setup
This tutorial requires an analytic view and supporting objects. Create these objects before continuing on to the next module by clicking on "Execute the SQL required by this tutorial" before the list of modules in this tutorial.

Note that the sample analytic view objects select from tables in the AV sample schema.

After running the setup script you can see that the objects have been created with the following queries.

Attribute dimensions.
```
SELECT * FROM user_attribute_dimensions;
```
Hierarchies.
```
SELECT * FROM user_hierarchies;
```
Analytic view.
```
SELECT * FROM user_analytic_views;
```
Module 3
Examining the Sample Data
Hierarchies and analytic views access data from tables or views, typically a star schema. This tutorial uses dimension tables from a star schema with sales data that varies by time, product and geography. In each dimension table there _ID columns that are used as keys and _NAME columns that are used as textual descriptors. Other columns might be used for purposes such as sorting.

Notes:
- All data is in the AV schema.
- There are 1:1 relationships between values in the _ID and _NAME columns.
- Time periods may be sorted using _END_DATE columns.
View data in the AV.TIME_DIM table.
```
SELECT * FROM av.time_dim ORDER BY month_end_date;
```
View years.
```
SELECT DISTINCT year_id,
  year_name,
  year_end_date
FROM av.time_dim
ORDER BY year_end_date;
```
View months.
```
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;
```
View data in the AV.PRODUCT_DIM table.
```
SELECT * FROM av.product_dim;
```
View the AV.GEOGRAPHY_DIM table.
```
SELECT * FROM av.geography_dim;
```
View data in the AV,SALES_FACT Table.
```
SELECT * FROM av.sales_fact WHERE rownum < 20;
```
The following query returns data aggregated by year, department and country.
```
SELECT t.year_name,
  p.department_name,
  g.country_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.country_name
ORDER BY t.year_name,
  p.department_name,
  g.country_name;
```
Module 4
About Analytic View Objects
There are three types of objects that are used with analytic views: attribute dimensions, hierarchies, and analytic views.

An attribute dimension is a metadata object that references tables or views and organizes columns into higher-level objects such as attributes and levels. Most metadata related to dimensions and hierarchies is defined in the attribute dimension object.

Hierarchies are a type of view. Hierarchies return detail and aggregate level keys ("hierarchy values") and attributes of those values. As the name implies, hierarchies organize data using hierarchical relationships between data. Hierarchies reference attribute dimension objects.

Analytic views are a type of view that return fact data. Analytic views reference both fact tables and hierarchies. Both hierarchy and measure data is selected from analytic views.

Hierarchies are typically used when an application needs to lookup hierarchy values and attributes. For example, to get a list of values for a pick list. Analytic views are used to query measure (fact) data. Because all columns of each hierarchy are included in the analytic view there is no need to join hierarchies and analytic views.
Module 5
Selecting from Hierarchies
Hierarchies return data from one of more levels that have been defined in an attribute dimension. Every hierarchy will have data from levels and a single row for an ALL level (a single top level aggregate).

Hierarchies include two types of columns:
- Attribute columns contain values for each attribute used by a level in the hierarchy. These values typically come directly from dimension ta ble(s). For example, the _ID and _NAME columns in the sample tables will be presented as attribute columns.
- Hierarchical attribute columns contain values that have been created by the database using data from attribute columns. Hierarchical attributes help aggregate, navigate, filter and present hierarchies.
Begin familiarizing yourself with hierarchies by selecting from them using SELECT *.

At this point, just notice that there are many different types of columns and that rows are returned for values at detail and aggregate levels.

View data in the TIME_HIER hierarchy.
```
 SELECT *
FROM time_hier;
```
View data in the PRODUCT_HIER hierarchy.
```
SELECT *
FROM product_hier;
```
View data in the GEOGRAPHY_HIER hierarchy.
```
SELECT *
FROM geography_hier;
```
Module 6
Attribute Columns
Each column of the referenced dimension table may be presented in a hierarchy. The column name may be used as is or be aliased.

The following queries present the attribute columns of the TIME_HIER hierarchy. Note that each attribute is presented as a separate column and there are rows for each level (years, quarters and months).
```
SELECT month_id,
  month_name,
  month_end_date,
  quarter_id,
  quarter_name,
  quarter_end_date,
  year_id,
  year_name,
  year_end_date
FROM time_hier;
```
Select attribute columns from the PRODUCT_HIER hierarchy.
```
SELECT category_id,
  category_name,
  department_id,
  department_name
FROM product_hier;
```
Select attribute columns from the GEOGRAPHY_HIER hierarchy.
```
SELECT region_id,
  region_name,
  country_id,
  country_name,
  state_province_id,
  state_province_name
FROM geography_hier;
```
Module 7
Hierarchical Attribute Columns
Hierarchical attributes are created by the database. In some cases attribute columns are unpivoted into a single hierarchical attribute column, creating new rows for aggregate levels. In other cases, the database calculates values for hierarchical attribute columns.

MEMBER_NAME and LEVEL_NAME Columns

The MEMBER_NAME column is typically used to return a text description of the key value. For example, “Brazil “ for COUNTRY_ID “BR” or “Camera and Accessories” for CATEGORY_ID “-534”. The LEVEL_NAME column includes the level name of the key attribute value.

Select MEMBER_NAME and LEVEL_NAME columns from the TIME_HIER hierarchy.
```
SELECT member_name,
  level_name
FROM time_hier;
```
Select MEMBER_NAME and LEVEL_NAME columns from the PRODUCT_HIER hierarchy.
```
SELECT member_name,
  level_name
FROM product_hier;
```
Select MEMBER_NAME and LEVEL_NAME columns from the GEOGRAPHY_HIER hierarchy.
```
SELECT member_name,
  level_name
FROM geography_hier;
```
Hierarchical columns can be used for a variety of purposes, including filtering and sorting. For example, the LEVEL_NAME column can be used to filter based on level of aggregation.

Select MEMBER_NAME and LEVEL_NAME columns from the TIME_HIER hierarchy filtered to 'YEAR'.
```
SELECT member_name,
  level_name
FROM time_hier
WHERE level_name = 'YEAR';
```
Select MEMBER_NAME and LEVEL_NAME columns from the PRODUCT_HIER hierarchy filtered to 'DEPARTMENT'.
```
SELECT member_name,
  level_name
FROM product_hier
WHERE level_name = 'DEPARTMENT';
```
Select MEMBER_NAME and LEVEL_NAME columns from the GEOGRAPHY_HIER hierarchy filtered to 'REGION' and 'COUNTRY'.
```
SELECT member_name,
  level_name
FROM geography_hier
WHERE level_name IN ('REGION','COUNTRY');
```
HIER_ORDER Column

The HIER_ORDER column sorts values within parents in the hierarchy. For example, Quarters within Years and Months within Quarters. You can choose how HIER_ORDER is calculated when you define levels in the attribute dimension object.

Select from the TIME_HIER hierarchy ordering by HIER_ORDER.
```
SELECT member_name,
  level_name,
  hier_order
FROM time_hier
ORDER BY hier_order;
```
Select from the PRODUCT_HIER hierarchy ordering by HIER_ORDER.
```
SELECT member_name, 
  level_name,
  hier_order
FROM product_hier
ORDER BY hier_order;
```
Select from the GEOGRAPHY_HIER hierarchy ordering by HIER_ORDER.
```
SELECT member_name,
  level_name,
  hier_order
FROM geography_hier
ORDER BY hier_order;
```
IS_LEAF and DEPTH Columns

The IS_LEAF column indicates if a value is at the detail (lowest) level of the hierarchy or at an aggregate level. In the following examples, IS_LEAF is used to filter for the detail rows of the hierarchies. (Only the attribute columns are selected, so these queries return the same rows and columns as the dimension tables that the hierarchies refer to.)

Use IS_LEAF to select only the detail rows of the TIME_HIER hierarchy.
```
SELECT month_id,
  month_name,
  month_end_date,
  quarter_id,
  quarter_name,
  quarter_end_date,
  year_id,
  year_name,
  year_end_date
FROM time_hier
WHERE is_leaf = 1
ORDER BY hier_order;
```
Use IS_LEAF to select only the detail rows of the PRODUCT_HIER hierarchy.
```
SELECT category_id,
  category_name,
  department_id,
  department_name
FROM product_hier
WHERE is_leaf = 1
ORDER BY hier_order;
```
Use IS_LEAF to select only the detail rows of the GEOGRAPHY_HIER hierarchy.
```
SELECT state_province_id,
  state_province_name,
  country_id,
  country_name,
  region_id,
  region_name
FROM geography_hier
WHERE is_leaf = 1
ORDER BY hier_order;
```
IS_LEAF might also be used to indicate whether value is drillable to a lower level of detail. The DEPTH column indicates how distant a value is from the top most value in the hierarchy.

Use IS_LEAF and DEPTH to query the TIME_HIER hierarchy in outline form.
```
SELECT
 CASE is_leaf
   WHEN 0 then lpad(' ',depth * 2,'.') || '+ ' || member_name 
   ELSE lpad(' ',depth * 3,'.') || member_name END AS DRILL,
   depth
FROM time_hier
ORDER BY hier_order;
```
Use IS_LEAF and DEPTH to query the PRODUCT_HIER hierarchy in outline form.
```
SELECT
 CASE is_leaf
   WHEN 0 then lpad(' ',depth * 2,'.') || '+ ' || member_name 
   ELSE lpad(' ',depth * 3,'.') || member_name END AS DRILL,
   depth
FROM product_hier
ORDER BY hier_order;
```
Use IS_LEAF and DEPTH to query the GEOGRAPHY_HIER hierarchy in outline form.
```
SELECT
 CASE is_leaf
   WHEN 0 then lpad(' ',depth * 2,'.') || '+ ' || member_name 
   ELSE lpad(' ',depth * 3,'.') || member_name END AS DRILL,
   depth
FROM geography_hier
ORDER BY hier_order;
```
MEMBER_UNIQUE_NAME and PARENT_UNIQUE_NAME Columns

The MEMBER_UNIQUE_NAME is the primary key of the hierarchy. As the primary key, it creates values that are unique across all level of the hierarchy. Depending on your data, the values in MEMBER_UNIQUE_NAME might be a simple concatenation of level name and key attribute value or a m ore complex value that also includes ancestors.

For now, simply note that MEMBER_UNIQUE_NAME is a unique value. The PARENT_UNIQUE_NAME column contains the unique name of the parent value in the hierarchy.

The following examples are the simplest form of MEMBER_UNIQUE_NAME.

Select MEMBER_UNIQUE_NAME from the TIME_HIER hierarchy.
```
SELECT year_id,
  quarter_id,
  month_id,
  level_name,
  member_unique_name,
  parent_unique_name
FROM time_hier
ORDER BY hier_order;
```
Select MEMBER_UNIQUE_NAME from the PRODUCT_HIER hierarchy.
```
SELECT department_id,
  category_id,
  level_name,
  member_unique_name,
  parent_unique_name
FROM product_hier
ORDER BY hier_order;
```
Select MEMBER_UNIQUE_NAME from the GEOGRAPHY_HIER hierarchy.
```
SELECT state_province_id,
  country_id,
  region_id,
  level_name,
  member_unique_name,
  parent_unique_name
FROM geography_hier
ORDER BY hier_order;
```
The MEMBER_UNIQUE_NAME and PARENT_UNIQUE_NAME are particularly useful for drill down type queries.

Start with Year level data.
```
SELECT member_name,
  member_unique_name
FROM time_hier
WHERE level_name = 'YEAR'
ORDER BY hier_order;
```
Drill to children of CY2014 using PARENT_UNIQUE_NAME.
```
SELECT member_name,
  member_unique_name
FROM time_hier
WHERE parent_unique_name = '[YEAR].&[14]'
ORDER BY hier_order;
```
Drill to children of Q3CY2014.
```
SELECT member_name,
  member_unique_name
FROM time_hier
WHERE parent_unique_name = '[QUARTER].&[314]'
ORDER BY hier_order;
```
MEMBER_CAPTION and MEMBER_DESCRIPTION Columns

The MEMBER_CAPTION and MEMBER_DESCRPTION columns are also typically used for text descriptors.

Select MEMBER_CAPTION and MEMBER_DESCRIPTION from the TIME_HIER hierarchy.
```
SELECT member_name,
  member_caption,
  member_description
FROM time_hier
ORDER BY hier_order;
```
Module 8
Selecting from Analytic Views
Analytic views join hierarchies to a fact table and include all columns from each hierarchy, measures from the fact table and calculated measures. Like hierarchies, analytic views return detail and aggregate level data.

A SELECT statement querying an analytic view contains a SELECT list, a FROM clause and in most cases should include a WHERE clause. The SELECT list includes attributes and measures. The FROM clause includes only the analytic view. The HIERARCHIES parameter lists the hierarchies to be used in the query.

Columns from hierarchies are always qualified by hierarchy name and may also be qualified by owner and attribute dimension name. In most cases each hierarchy referenced in the SELECT list is included in the HIERARCHIES parameter.

Select the MEMBER_NAME column from the TIME_HIER hierarchy and SALES for data at the YEAR level.
```
SELECT time_hier.member_name AS TIME,
  sales
FROM sales_av HIERARCHIES (time_hier)
WHERE time_hier.level_name = 'YEAR'
ORDER BY time_hier.hier_order;
```
Note that this query:
- Does not include an aggregation function (e.g., SUM) or GROUP BY clause. This is because the aggregation rules are included in the definition analytic view.
- Does not include joins. Joins are also included in the definition of the analytic view.
- Does not select the YEAR_ID or YEAR_NAME columns. Instead, it selects the MEMBER_NAME column. Either will return the same data, but the MEMBER_NAME column returns data at level of aggregation.< /li>
Select SALES at the YEAR, QUARTER and MONTH levels.
```
SELECT time_hier.member_name AS TIME,
  sales
FROM sales_av HIERARCHIES (time_hier)
WHERE time_hier.level_name IN ('YEAR','QUARTER','MONTH')
ORDER BY time_hier.hier_order;
```
HIERARCHIES Parameter

The HIERACHIES parameter allows a query to return detail and aggregate level values of the listed hierarchies and to filter using attributes of a hierarchy. If a column is selected from a hierarchy which is not listed in the hierarchies parameter only data from the database defined top-most value is returned (that is, where DEPTH = 0).

The following query selects time values at the year level and includes the TIME_HIER hierarchy in the HIERARCHIES parameter.
```
SELECT time_hier.member_name AS TIME,
  sales
FROM sales_av HIERARCHIES (time_hier)
WHERE time_hier.level_name = 'YEAR'
ORDER BY time_hier.hier_order;
```
PRODUCT_HIER.MEMBER_NAME as been added to the SELECT list, but PRODUCT_HIER has not been added to the HIERARCHIES parameter. Only data for the ALL PRODUCTS member (that top most aggregate value) are returned by this query.
```
SELECT time_hier.member_name AS TIME,
  product_hier.member_name AS PRODUCT,
  sales
FROM sales_av HIERARCHIES (time_hier)
WHERE time_hier.level_name = 'YEAR'
ORDER BY time_hier.hier_order;
```
In the next query a filter using PRODUCT_HIER.LEVEL_NAME has been added, but PRODUCT_HIER is still not in the HIERACHIES parameter. This query will return no data.
```
SELECT time_hier.member_name AS TIME,
  product_hier.member_name AS PRODUCT,
  sales
FROM sales_av HIERARCHIES (time_hier)
WHERE time_hier.level_name = 'YEAR'
  AND product_hier.level_name = 'DEPARTMENT'
ORDER BY time_hier.hier_order;
```
PRODUCT_HIER has been added to the HIERARACHIES parameter. This query returns data at the DEPARTMENT level of the PRODUCT_HIER hierarchy.
```
SELECT time_hier.member_name AS TIME,
  product_hier.member_name AS PRODUCT,
  sales
FROM sales_av HIERARCHIES (time_hier, product_hier)
WHERE time_hier.level_name = 'YEAR'
  AND product_hier.level_name = 'DEPARTMENT'
ORDER BY time_hier.hier_order;
```
The Importance of Using Filters

Even analytic views defined over small tables have the potential to return large numbers of rows because they return both detail level and aggregate level data. Therefore, it is usually very important to include filters (that is, a WHERE clause) in queries selecting from analytic views.

Select SALES for all rows of the TIME_HIERARCHY hierarchy.
```
SELECT time_hier.member_name AS TIME,
  time_hier.depth            AS TIME_DEPTH,
  sales
FROM sales_av HIERARCHIES (time_hier)
ORDER BY time_hier.hier_order;
```
With only a single, small hierarchy such as the TIME_HIER hierarchy (86 rows) the lack of a WHERE clause is not a problem. As more hierarchies are used (that is, listed in the HIERARCHIES parameter), the analytic view has the capacity to return many more rows. Even though the sample dimension tables and fact table used in this tutorial are very small, a query using all three hierarchies has the potential to return up to 260,064 rows (86 time periods * 12 products * 252 geographies).

Select Sales data from the SALES_AV analytic view using filters on each hierarchy.
```
SELECT time_hier.member_name AS TIME,
  product_hier.member_name   AS product,
  geography_hier.member_name AS geography,
  sales
FROM sales_av HIERARCHIES (
  time_hier,
  product_hier,
  geography_hier
  )
WHERE time_hier.member_name  = 'CY2014'
AND product_hier.level_name = 'DEPARTMENT' 
AND geography_hier.parent_unique_name = '[REGION].&[EUROPE]'
ORDER BY time_hier.hier_order,
   geography_hier.hier_order,
   product_hier.hier_order;
```
Module 9
Selecting Calculated Measures
Calculated measures are defined in the analytic view and presented as columns. To select a calculated measure, just include it in the SELECT list.

Select the SALES, SALES_SHARE_GEOG_PARENT and SALES_PRIOR_PERIOD measures from the SALES_AV analytic view.

Note the following:
- The query filters to the time period ‘CY2014’ and reports both the current year sales and the prior period (CY2103) in this case. The database has automatically expands filters to access CY2103 data (so you don’t have to).
- The SALES_SHARE_GEOG_PARENT measure calculates the ratio of Sales for the current time, product and geography to the Sales of the parent geography (North America, in this case). This is another example of the database automatically expanding filters to access data needed by calculations.
```
SELECT time_hier.member_name AS TIME,
  product_hier.member_name   AS product,
  geography_hier.member_name AS geography,
  sales,
  ROUND(sales_share_geog_parent,2) as sales_share_geog_parent,
  sales_prior_period
FROM sales_av HIERARCHIES (
  time_hier,
  product_hier,
  geography_hier
  )
WHERE time_hier.member_name  = 'CY2014'
AND product_hier.level_name = 'DEPARTMENT' 
AND geography_hier.parent_unique_name = '[REGION].&[NORTH_AMERICA]'
ORDER BY time_hier.hier_order,
  geography_hier.hier_order,
  product_hier.hier_order;
```
The query is easily changed to drill down on Mexico. Note that the only part of the query that needs to change is the value in the PARENT_UNIQUE_NAME filter. Otherwise, the query was unchanged.

Drill down to Mexico using the PARENT_UNIQUE_NAME value.
```
SELECT time_hier.member_name AS TIME,
  product_hier.member_name   AS product,
  geography_hier.member_name AS geography,
  sales,
  round(sales_share_geog_parent,2) AS sales_share_geog_parent,
  sales_prior_period
FROM sales_av HIERARCHIES (
  time_hier,
  product_hier,
  geography_hier
  )
WHERE time_hier.member_name  = 'CY2014'
AND product_hier.level_name = 'DEPARTMENT' 
AND geography_hier.parent_unique_name = '[COUNTRY].&[MX]'
ORDER BY time_hier.hier_order,
  geography_hier.hier_order,
  product_hier.hier_order;
```

Module 10

Using SQL Functions

SQL functions may be used with analytic views, just like any other table or view. For example, SQL functions might be used to format output or perform calculations over the top of an analytic view. Because the analytic view provides easy access to aggregate data, using SQL to perform calculations over the analytic view is typically easier than the equivalent query against tables.

Use SQL functions and the DEPTH column to create a formatted report.

SELECT lpad(' ',time_hier.depth * 2) || time_hier.member_name AS TIME,
  lpad(' ',product_hier.depth * 2) || product_hier.member_name   AS product,
  TO_CHAR(sales,'$999,999,999,999') AS sales,
  lpad(round(sales_share_prod_parent,2) * 100 || '%',24,' ') as sales_share_prod_parent,
  TO_CHAR(sales_prior_period,'$999,999,999,999') AS sales_prior_period
FROM sales_av HIERARCHIES (
  time_hier,
  product_hier
  )
WHERE time_hier.level_name IN ('YEAR')
AND product_hier.level_name IN ('DEPARTMENT','CATEGORY')
ORDER BY time_hier.hier_order,
  product_hier.hier_order;

The next example queries the analytic view in an inner query and uses the RANK window function in the outer query. The analytic view conveniently provides input to the RANK (TIME and PRODUCT_LEVEL to PARTITION BY and ORDER BY at an aggregate level of SALES).

Use the RANK function over the SALES_AV analytic view to rank values at the DEPARTMENT level within YEAR.

WITH sales as (
  SELECT time_hier.member_name      AS time,
    time_hier.hier_order            AS time_order,
    product_hier.member_name        AS product,
    product_hier.hier_order         AS product_order,
    product_hier.level_name         AS product_level,
    product_hier.parent_unique_name AS product_parent,
    sales AS sales
  FROM sales_av HIERARCHIES (
    time_hier,
    product_hier
    )
  WHERE time_hier.level_name IN ('YEAR')
  AND product_hier.level_name IN ('DEPARTMENT')
  )
SELECT time,
  product,
  sales,
  rank() OVER (PARTITION BY time, product_parent ORDER BY sales DESC) AS prod_rank_within_parent
FROM sales
ORDER BY
  time_order,
  sales DESC;

As another example of the ease of using the hierarchical attribute columns, the previous query is updated to rank Categories within Departments. Only the value in the product LEVEL_NAME filter needs to change, the remainder of the query remains unchanged.

Change the PRODUCT_HIER.LEVEL_NAME filter to CATEGORY to rank within DEPARTMENT and YEAR.

WITH sales as (
  SELECT time_hier.member_name AS TIME,
    time_hier.hier_order       AS time_order,
    product_hier.member_name   AS product,
    product_hier.hier_order    AS product_order,
    product_hier.level_name   AS product_level,
    product_hier.parent_unique_name AS product_parent,
    sales AS sales
  FROM sales_av HIERARCHIES (
    time_hier,
    product_hier
    )
  WHERE time_hier.level_name IN ('YEAR')
  AND product_hier.level_name IN ('CATEGORY')
  )
SELECT time,
  product,
  sales,
  rank() OVER (PARTITION BY time, product_parent ORDER BY sales DESC) AS prod_rank_within_parent
FROM sales
ORDER BY
  time_order,
  sales DESC;

Module 11

Inner Select and WITH Clause Examples

Just like any other query, an AV query can be used as within an inner select or WITH clause. As you have probably noticed by now, most queries selecting from an analytic view use very similar templates. You can easily re-use these templates within inner selects.

Rank geographies by sales with the analytic view query in an inner select.


SELECT
    product,
    geography,
    RANK() OVER(
        PARTITION BY product
        ORDER BY
            sales DESC
    ) AS sales_geog_rank,
    sales
FROM
    (
        SELECT
            time_hier.member_name        AS time,
            product_hier.member_name     AS product,
            geography_hier.member_name   AS geography,
            sales
        FROM
            sales_av HIERARCHIES (
                time_hier,
                product_hier,
                geography_hier
            )
        WHERE
            time_hier.member_name = 'CY2014'
            AND product_hier.level_name = 'DEPARTMENT'
            AND geography_hier.level_name = 'REGION'
    )
ORDER BY
    1,
    3;

Rank geographies by sales with the analytic view query in a WITH clause.


WITH my_query AS (
    SELECT
        time_hier.member_name        AS time,
        product_hier.member_name     AS product,
        geography_hier.member_name   AS geography,
        sales
    FROM
        sales_av HIERARCHIES (
            time_hier,
            product_hier,
            geography_hier
        )
    WHERE
        time_hier.member_name = 'CY2014'
        AND product_hier.level_name = 'DEPARTMENT'
        AND geography_hier.level_name = 'REGION'
)
SELECT
    product,
    geography,
    RANK() OVER(
        PARTITION BY product
        ORDER BY
            sales DESC
    ) AS sales_geog_rank,
    sales
FROM
    my_query
ORDER BY
    1,
    3;

Pivot years to columns with the analytic view query as an inner select.


SELECT
    *
FROM
    (
        SELECT
            time_hier.member_name        AS time,
            product_hier.member_name     AS product,
            geography_hier.member_name   AS geography,
            sales
        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'
    ) PIVOT (
        SUM ( sales )
        FOR time
        IN ( 'CY2011' AS cy2011, 'CY2012' AS cy2012, 'CY2013' AS cy2013, 'CY2014' AS cy2014, 'CY2015' AS cy2015 )
    )
ORDER BY
    product,
    geography;

Pivot years to columns with the analytic view query in a WITH clause.


WITH my_table AS (
    SELECT
        time_hier.member_name        AS time,
        product_hier.member_name     AS product,
        geography_hier.member_name   AS geography,
        sales
    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'
)
SELECT
    *
FROM
    my_table PIVOT (
        SUM ( sales )
        FOR time
        IN ( 'CY2011' AS cy2011, 'CY2012' AS cy2012, 'CY2013' AS cy2013, 'CY2014' AS cy2014, 'CY2015' AS cy2015 )
    )
ORDER BY
    product,
    geography;

Module 12

Joining Hierarchies, Analytic Views and Tables

Hierarchies and analytic views can be joined to tables just like any other object that can be queried in the database.

Hierarchies are generally referenced in the FROM clause just like any other table or views. Note that in this example values in the TIME_HIER hierarchy are filtered to the Month level because the hierarchy joins the TIME_DIM table at the month level


SELECT
    t.year_name,
    p.department_name,
    g.region_name,
    SUM(f.sales) AS sales
FROM
    time_hier          t,              -- Hierarchy
    av.product_dim     p,              -- Table
    av.geography_dim   g,              -- Table
    av.sales_fact      f               -- Table
WHERE
    t.month_id = f.month_id            -- Table joins to hierarchy
    AND t.level_name = 'MONTH'         -- Months only from hierarchy
    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;

When joining tables and analytic views it is usually best to use a WITH clause. This example aggregates from the table and then joins to the analytic view at the aggregate levels. The is probably the most common use case.


-- SELECT from the table.
WITH my_table_query AS (
    SELECT
        t.year_name,
        p.department_name,
        g.region_name,
        SUM(f.sales) AS 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
),
-- SELECT from the analytic view.
  my_av_query AS (
    SELECT
        time_hier.member_name        AS time,
        product_hier.member_name     AS product,
        geography_hier.member_name   AS geography,
        sales_prior_period
    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'
)
-- Final select
SELECT
    a.year_name,
    a.department_name,
    a.region_name,
    a.sales,
    b.sales_prior_period
FROM
    my_table_query   a,
    my_av_query      b
WHERE
    a.year_name = b.time
    AND a.department_name = b.product
    AND a.region_name = b.geography;

In the next example, the SEASON column is joined into the analytic view query from the TIME_DIM table using the WITH clause. (Note that this sample data does not include all months. The query works as expected.)


WITH my_av_query AS (
    SELECT
        time_hier.member_name        AS time,
        product_hier.member_name     AS product,
        geography_hier.member_name   AS geography,
        sales_prior_period
    FROM
        sales_av HIERARCHIES (
            time_hier,
            product_hier,
            geography_hier
        )
    WHERE
        time_hier.level_name = 'MONTH'
        AND month_name = month_name
        AND time_hier.year_name = 'CY2014'
        AND product_hier.level_name = 'DEPARTMENT'
        AND geography_hier.level_name = 'REGION'
)
SELECT
    a.time,
    b.season,
    a.product,
    a.geography,
    a.sales_prior_period
FROM
    my_av_query a,
    av.time_dim b
WHERE a.time = b.month_name;

Additional Information

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

Querying Analytic Views - Deep Dive

Prerequisite SQL

Introduction to Analytic Views

Setup

Examining the Sample Data

About Analytic View Objects

Selecting from Hierarchies

Attribute Columns

Hierarchical Attribute Columns

Selecting from Analytic Views

Selecting Calculated Measures

Using SQL Functions

Inner Select and WITH Clause Examples

Joining Hierarchies, Analytic Views and Tables

Additional Information