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;
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:
In addition, analytic views:
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:
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;
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:
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;
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.
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:
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;
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;
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;
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:
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;
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: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;
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;
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;
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;