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';
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);You will create several different versions of an analytic view, each with different calculations. These analytic views require 3 attribute dimensions and 3 hierarchies. Before continuing, create these objects clicking on "Execute the SQL required by this tutorial" which precedes the list of modules.
SHARE_OF expressions calculate the ratio of a hierarchy value to the parent value, an ancestor value or to a specific hierarchy value. For example, the ratio of sales for a month to the parent of that month or to the year of that month. In same cases you might also define certain ratios using the QUALIFY expression.
The SHARE_OF expression is similar to the SQL RATIO_TO_REPORT expression in that it calculates the ratio of a row to the aggregate (e.g., SUM) of a grouping of rows. RATIO_TO_REPORT requires that you explicitly express the aggregate groupings across all columns using the query partition clause. The SHARE_OF expression only requires that you express the aggregate within a single hierarchy. Aggregates across all other hierarchies are calculated automatically.
The SHARE_OF expression works by computing the ratio of a measure of a hierarchy value to the measure of all hierarchy values in the same level, to the parent of the hierarchy value or to an ancestor of a hierarchy value.
The analytic view will automatically expand the SQL to access hierarchy values that are outside the scope of a WHERE clause. For example if the query selects SALES and SALES_SHARE_OF_PARENT at the month level, the analytic view will automatically expand the query to access the quarter level data needed for the calculation.SHARE_OF parent calculates the ratio of the current member to the parent of the current member in the same hierarchy. The following example creates measure for Sales Share of Parent in the time, product and geography hierarchies.
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_share_of_time_parent AS (SHARE_OF(sales HIERARCHY time_hier PARENT)),
sales_share_of_product_parent AS (SHARE_OF(sales HIERARCHY product_hier PARENT)),
sales_share_of_geog_parent AS (SHARE_OF(sales HIERARCHY geography_hier PARENT))
)
DEFAULT MEASURE SALES;
The following query selects time members, sales and the share of sales in the time hierarchy.
SELECT
product_hier.member_name AS product_member,
time_hier.member_name AS time_member,
time_hier.level_name AS time_level,
sales,
ROUND(sales_share_of_time_parent,2) AS sales_share_of_time_parent
FROM
sales_av HIERARCHIES (product_hier, time_hier)
WHERE
time_hier.level_name in ('YEAR','QUARTER','MONTH')
AND product_hier.member_name = 'Cameras and Accessories'
ORDER BY
time_hier.hier_order;
The following query selects product members, sales and the share of sales in the product hierarchy.
SELECT
time_hier.member_name AS time_member,
product_hier.member_name AS product_member,
product_hier.level_name AS product_level,
sales,
ROUND(sales_share_of_product_parent,2) AS sales_share_of_product_parent
FROM
sales_av HIERARCHIES (time_hier, product_hier)
WHERE
product_hier.level_name in ('ALL','DEPARTMENT','CATEGORY')
AND time_hier.member_name = 'CY2011'
ORDER BY
product_hier.hier_order;SHARE_OF ANCESTOR calculates the ratio of the current hierarchy value to an ancestor value. For example, the ratio of Sales at a Day, a Month or a Quarter to a Year. The following example creates measures for:
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_share_of_year AS (SHARE_OF(sales HIERARCHY time_hier LEVEL year)),
sales_share_of_department AS (SHARE_OF(sales HIERARCHY product_hier LEVEL department)),
sales_share_of_region AS (SHARE_OF(sales HIERARCHY geography_hier LEVEL region))
)
DEFAULT MEASURE SALES;
Query the Time hierarchy. Note that data at each level is a ratio to the ancestor at the Year level.
SELECT
time_hier.member_name AS time_member,
time_hier.level_name as time_level,
sales,
ROUND(sales_share_of_year,2) AS sales_share_of_time_year
FROM
sales_av HIERARCHIES (time_hier)
WHERE
time_hier.level_name in ('YEAR','QUARTER','MONTH')
ORDER BY
time_hier.hier_order;
This might be a little easier to see by querying only the Year and one descendant level at a time. First with Year and Quarter.
SELECT
time_hier.member_name AS time_member,
time_hier.level_name as time_level,
sales,
ROUND(sales_share_of_year,2) AS sales_share_of_time_year
FROM
sales_av HIERARCHIES (time_hier)
WHERE
time_hier.level_name in ('YEAR','QUARTER')
ORDER BY
time_hier.hier_order;
Next by Year and Month.
SELECT
time_hier.member_name AS time_member,
time_hier.level_name as time_level,
sales,
ROUND(sales_share_of_year,2) AS sales_share_of_time_year
FROM
sales_av HIERARCHIES (time_hier)
WHERE
time_hier.level_name in ('YEAR','MONTH')
ORDER BY
time_hier.hier_order;
The next query illustrates that Year level data does not need to selected for the calculations to return data. The query will be automatically expanded to fetch Year level data for the SHARE_OF expression.
SELECT
time_hier.member_name AS time_member,
time_hier.level_name as time_level,
sales,
ROUND(sales_share_of_year,2) AS sales_share_of_time_year
FROM
sales_av HIERARCHIES (time_hier)
WHERE
time_hier.level_name = 'MONTH'
ORDER BY
time_hier.hier_order;
Query the Product hierarchy. Note that data at each level is a ratio of Sales of the current hierarchy value to Sales of the ancestor at the Department level.
SELECT
product_hier.member_name AS product_member,
product_hier.level_name as product_level,
sales,
ROUND(sales_share_of_department,2) AS sales_share_of_department
FROM
sales_av HIERARCHIES (product_hier)
WHERE
time_hier.level_name in ('ALL','DEPARTMENT','CATEGORY')
ORDER BY
product_hier.hier_order;To calculate the ratio of a hierarchy value to all values in the same level create a SHARE_OF measure that calculates the ratio of the current value to the top most aggregate value. The can be done using the MEMBER and ALL keywords.
For example:
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_share_of_all_time AS (SHARE_OF(sales HIERARCHY time_hier MEMBER ALL)),
sales_share_of_all_product AS (SHARE_OF(sales HIERARCHY product_hier MEMBER ALL)),
sales_share_of_all_geography AS (SHARE_OF(sales HIERARCHY geography_hier MEMBER ALL))
)
DEFAULT MEASURE SALES;
Both MEMBER and ALL are keywords and thus are not quoted and case insensitive. The MEMBER keyword indicates that a specific hierarchy value will be specified. The ALL keyword is a shortcut to referencing the ALL MEMBER NAME value which is the top most hierarchy value.
Select the SALES_SHARE_OF_ALL_PRODUCT measure at the Department level.
SELECT
time_hier.member_name AS time,
product_hier.member_name AS product,
product_hier.level_name AS product_level,
TO_CHAR(sales,'999,999,999,999') AS sales,
ROUND(sales_share_of_all_product,3) * 100 || '%' AS sales_share_of_all_product
FROM
sales_av HIERARCHIES (product_hier, time_hier)
WHERE
product_hier.level_name = 'DEPARTMENT'
AND time_hier.member_name = 'CY2015'
ORDER BY
product_hier.hier_order;
Select the SALES_SHARE_OF_ALL_PRODUCT measure at the Category level.
SELECT
time_hier.member_name AS time,
product_hier.member_name AS product,
product_hier.level_name AS product_level,
TO_CHAR(sales,'999,999,999,999') AS sales,
ROUND(sales_share_of_all_product,3) * 100 || '%' AS sales_share_of_all_product
FROM
sales_av HIERARCHIES (product_hier, time_hier)
WHERE
product_hier.level_name = 'CATEGORY'
AND time_hier.member_name = 'CY2015'
ORDER BY
product_hier.hier_order;In each of the previous examples the SHARE_OF expression was used to create a measure that was the ratio of a hierarchy value to a parent or ancestor value. You can use the MEMBER keyword to create a measure that is the ratio of a hierarchy value to a specific hierarchy value.
In the previous module you saw how MEMBER ALL can be used to reference the top most value of a hierarchy. The MEMBER keyword can also be use to reference a specific value. For example, if you would like to create a measure that is the ratio of any hierarchy value to the value for year 2001 you could use SHARE_OF ... MEMBER .. VALUE.
The value used by the MEMBER keyword is the key attribute value of the level preceded by the level name. In the TIME_HIER hierarchy the key attribute value for year is in the YEAR_ID attribute. The key attribute value can also be seen in the last element of the MEMBER_UNIQUE_NAME attribute. This can be seen in the following query.
SELECT member_name, year_id, member_unique_name FROM time_hier WHERE level_name = 'YEAR' ORDER BY hier_order;
The following example creates a ratio of sales to the value of sales for year 2001.
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_ratio_to_year_2011 AS (SHARE_OF(sales HIERARCHY time_hier MEMBER year ['11']))
)
DEFAULT MEASURE SALES;
The following query selects at the year and department levels.
SELECT
time_hier.member_name AS time,
product_hier.member_name AS product,
TO_CHAR(sales,'999,999,999,999') AS sales,
ROUND(sales_ratio_to_year_2011,3) * 100 || '%' AS sales_ration_to_year_2011
FROM
sales_av HIERARCHIES (product_hier, time_hier)
WHERE
product_hier.level_name = 'DEPARTMENT'
AND time_hier.level_name = 'YEAR'
ORDER BY
product_hier.hier_order,
time_hier.hier_order;One of the benefits of using an analytic view is simplified SQL generation and avoidance of data errors. The following examples compare ratio to parent queries using tables and an analytic view.
Create an analytic view with share to parent calculations.
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_share_of_time_parent AS (SHARE_OF(sales HIERARCHY time_hier PARENT)),
sales_share_of_product_parent AS (SHARE_OF(sales HIERARCHY product_hier PARENT)),
sales_share_of_geog_parent AS (SHARE_OF(sales HIERARCHY geography_hier PARENT))
)
DEFAULT MEASURE SALES;
The first example looks for the share of quarters to years.
The table example uses RATIO_TO_REPORT to calculate the ratio of a sales values at the quarter level to its' parent (years). The RATIO_TO_REPORT function doesn't understand the relationship between years and quarters, so it is up to the application to understand this relationship as well as relationships to any other columns in the select list. This is seen in PARTITION BY of RATIO_TO_REPORT.
SELECT
p.category_name,
t.year_name,
t.quarter_name,
SUM(f.sales),
ROUND(RATIO_TO_REPORT(SUM(f.sales)) OVER (PARTITION BY t.year_name, p.category_name),2) AS sales_ratio_to_report
FROM
av.time_dim t,
av.product_dim p,
av.sales_fact f
WHERE
t.month_id = f.month_id
AND p.category_id = f.category_id
GROUP BY
p.category_name,
t.year_name,
t.quarter_name;
With the analytic view the application just selects from the SALES_SHARE_OF_TIME_PARENT column. The application doesn't need to understand the relationship between year and quarter or any other column in the select list. Those relationships are embedded in the analytic view (as well as joins and aggregation).
SELECT
product_hier.member_name AS product_member,
time_hier.year_name AS year_name,
time_hier.member_name AS time_member,
sales,
ROUND(sales_share_of_time_parent,2) AS sales_share_of_time_parent
FROM
sales_av HIERARCHIES (product_hier, time_hier)
WHERE
time_hier.level_name = 'QUARTER'
AND product_hier.level_name = 'CATEGORY'
ORDER BY
product_hier.member_name,
time_hier.hier_order;
If the table query changes to month level data and DEPARTMENT_NAME is added to the SELECT list, QUARTER_NAME must also be added to the SELECT list the RATIO_TO_REPORT calculation must be updated to PARTITION BY those columns. Failure to do so might result in unexpected results.
SELECT
p.department_name,
p.category_name,
t.year_name,
t.quarter_name,
t.month_name,
SUM(f.sales),
ROUND(RATIO_TO_REPORT(SUM(f.sales)) OVER (PARTITION BY t.year_name, t.quarter_name, p.department_name, p.category_name),2) AS sales_share_of_quarter
FROM
av.time_dim t,
av.product_dim p,
av.sales_fact f
WHERE
t.month_id = f.month_id
AND p.category_id = f.category_id
GROUP BY
p.department_name,
p.category_name,
t.year_name,
t.quarter_name,
t.month_name;
If we 'forget' to PARTITION BY quarter_name, the query returned incorrect values for time share to parent.
SELECT
p.department_name,
p.category_name,
t.year_name,
t.quarter_name,
t.month_name,
SUM(f.sales),
ROUND(RATIO_TO_REPORT(SUM(f.sales)) OVER (PARTITION BY t.year_name,
p.department_name, p.category_name),2) AS sales_share_of_quarter
FROM
av.time_dim t,
av.product_dim p,
av.sales_fact f
WHERE
t.month_id = f.month_id
AND p.category_id = f.category_id
GROUP BY
p.department_name,
p.category_name,
t.year_name,
t.quarter_name,
t.month_name;
When querying the analytic view, all that is needed is changing the TIME_HIER.LEVEL_NAME filter to 'MONTH'. DEPARTMENT_NAME and CATEGORY_NAME are added to be consistent with the table query.
SELECT
product_hier.department_name AS department_name,
product_hier.category_name AS category_name,
product_hier.member_name AS product_member,
time_hier.year_name AS year_name,
time_hier.quarter_name AS quarter_name,
time_hier.member_name AS time_member,
sales,
ROUND(sales_share_of_time_parent,2) AS sales_share_of_time_parent
FROM
sales_av HIERARCHIES (product_hier, time_hier)
WHERE
time_hier.level_name = 'MONTH'
AND product_hier.level_name = 'CATEGORY'
ORDER BY
product_hier.hier_order,
time_hier.hier_order;
Omitting DEPARTMENT_NAME, YEAR_NAME AND QUARTER_NAME from the analytic_view query does not change the SALES_SHARE_OF_TIME_PARENT calculation because the analytic view knows that QUARTER is the parent of MONTH and the relationships between TIME and PRODUCT hierarchies.
SELECT
--product_hier.department_name AS department_name,
product_hier.member_name AS product_member,
-- time_hier.year_name AS year_name,
-- time_hier.quarter_name AS quarter_name,
time_hier.member_name AS time_member,
sales,
ROUND(sales_share_of_time_parent,2) AS sales_share_of_time_parent
FROM
sales_av HIERARCHIES (product_hier, time_hier)
WHERE
time_hier.level_name = 'MONTH'
AND product_hier.level_name = 'CATEGORY'
ORDER BY
product_hier.hier_order,
time_hier.hier_order;