-- Time attibute dimension.
CREATE OR REPLACE ATTRIBUTE DIMENSION time_attr_dim
USING av.time_dim
ATTRIBUTES
(year_id,
year_name,
--year_end_date
quarter_id,
quarter_name,
-- quarter_end_date,
month_id,
month_name,
month_long_name,
month_end_date)
LEVEL MONTH
KEY month_id
MEMBER NAME month_name
MEMBER CAPTION month_name
MEMBER DESCRIPTION month_long_name
ORDER BY month_end_date
DETERMINES (quarter_id, month_end_date)
LEVEL QUARTER
KEY quarter_id
MEMBER NAME quarter_name
MEMBER CAPTION quarter_name
MEMBER DESCRIPTION quarter_name
ORDER BY MAX month_end_date
DETERMINES (year_id)
LEVEL YEAR
KEY year_id
MEMBER NAME year_name
MEMBER CAPTION year_name
MEMBER DESCRIPTION year_name
ORDER BY MAX month_end_date;
-- Time hierarchy.
CREATE OR REPLACE HIERARCHY time_hier
USING time_attr_dim
(month CHILD OF
quarter CHILD OF
year);
-- Product attribute dimension.
CREATE OR REPLACE ATTRIBUTE DIMENSION product_attr_dim
USING av.product_dim
ATTRIBUTES
(department_id,
department_name,
category_id,
category_name)
LEVEL DEPARTMENT
KEY department_id
MEMBER NAME department_name
MEMBER CAPTION department_name
ORDER BY department_name
LEVEL CATEGORY
KEY category_id
MEMBER NAME category_name
MEMBER CAPTION category_name
ORDER BY category_name
DETERMINES(department_id)
ALL MEMBER NAME 'ALL PRODUCTS';
-- Product hierarchy.
CREATE OR REPLACE HIERARCHY product_hier
USING product_attr_dim
(CATEGORY
CHILD OF department);
-- Geography attribute dimension.
CREATE OR REPLACE ATTRIBUTE DIMENSION geography_attr_dim
USING av.geography_dim
ATTRIBUTES
(region_id,
region_name,
country_id,
country_name,
state_province_id,
state_province_name)
LEVEL REGION
KEY region_id
MEMBER NAME region_name
MEMBER CAPTION region_name
ORDER BY region_name
LEVEL COUNTRY
KEY country_id
MEMBER NAME country_name
MEMBER CAPTION country_name
ORDER BY country_name
DETERMINES(region_id)
LEVEL STATE_PROVINCE
KEY state_province_id
MEMBER NAME state_province_name
MEMBER CAPTION state_province_name
ORDER BY state_province_name
DETERMINES(country_id)
ALL MEMBER NAME 'ALL CUSTOMERS';
-- Geography hierarchy.
CREATE OR REPLACE HIERARCHY geography_hier
USING geography_attr_dim
(state_province
CHILD OF country
CHILD OF region);
-- Analytic view.
CREATE OR REPLACE ANALYTIC VIEW sales_av
USING av.sales_fact
DIMENSION BY
(time_attr_dim
KEY month_id REFERENCES month_id
HIERARCHIES (
time_hier DEFAULT),
product_attr_dim
KEY category_id REFERENCES category_id
HIERARCHIES (
product_hier DEFAULT),
geography_attr_dim
KEY state_province_id
REFERENCES state_province_id
HIERARCHIES (
geography_hier DEFAULT)
)
MEASURES
(sales FACT sales,
units FACT units
)
DEFAULT MEASURE SALES;Before getting started, review data the in the AV sample schema that will be used by this tutorial. The AV schema includes several dimension tables and a fact table containing sales data.
The TIME_DIM table.
SELECT * FROM av.time_dim;The PRODUCT_DIM table.
SELECT * FROM av.product_dim;The GEOGRAPHY_DIM table.
SELECT * FROM av.geography_dim;The SALES_FACT table.
SELECT * FROM av.sales_fact WHERE rownum < 50;When selecting data from tables, data is first filtered using the WHERE clause and aggregated using GROUP BY (or some variant of GROUP BY such as GROUPING SETS).
The following query select data for years.
SELECT
t.year_name,
SUM(f.sales)
FROM
av.time_dim t,
av.sales_fact f
WHERE
t.month_id = f.month_id
GROUP BY
t.year_name
ORDER BY
t.year_name;
The next query also selects data for years, but also includes a filter rows in the TIME_DIM table to only those months that are in the first and second quarters of the year. (That is, January through June. As a result, the SUM of sales for years includes only the data first the first half of the year.
SELECT t.year_name,
SUM(f.sales)
FROM av.time_dim t,
av.sales_fact f
WHERE
t.month_id = f.month_id
AND TO_CHAR(t.month_end_date,'Q') in (1,2)
GROUP BY t.year_name
ORDER BY year_name;
The next example also returns Regions and filters to countries Mexico and Canada. The values of North American are the totals of Mexico and Canada.
SELECT t.year_name,
g.region_name,
SUM(f.sales)
FROM av.time_dim t,
av.geography_dim g,
av.sales_fact f
WHERE t.month_id = f.month_id
AND g.state_province_id = f.state_province_id
AND TO_CHAR(t.month_end_date,'Q') in (1,2)
AND g.country_name IN ('Mexico','Canada')
GROUP BY t.year_name,
g.region_name
ORDER BY g.region_name,
t.year_name;
The important concept to keep in mind is that aggregation occurs after filtering.
In the analytic view, measures are aggregated using hierarchies and the aggregation operator of the measure. Filters in the SELECT statement (that is, the WHERE clause) filter the rows returned by the analytic view, but do not change aggregated values returned by the analytic views.
In other words, the analytic view aggregates first and the SELECT statement filters the results. If queries over tables "filter, then aggregate" you can think of queries after analytic views as "aggregate, then filter".
Each approach as advantages. Filtering before aggregation provides flexibility. Aggregating before filtering provides consistency in that the aggregate values are always the same regardless of how the query is written.
The following query returns the same rows with the same values as the first query that selected from tables.
SELECT time_hier.member_name,
sales
FROM sales_av HIERARCHIES(time_hier)
WHERE time_hier.level_name = 'YEAR'
ORDER BY time_hier.hier_order;
Let's see what happens when a filter for the first two quarters of the year are added to the query of the analytic view
SELECT time_hier.member_name,
sales
FROM sales_av HIERARCHIES(time_hier)
WHERE time_hier.level_name = 'YEAR'
AND TO_CHAR(month_end_date,'Q') IN (1,2)
ORDER BY time_hier.hier_order;
No rows are returned. This is because values for MONTH_END_DATE are NULL for year level member in the TIME_HIER hierarchy.
SELECT * FROM time_hier WHERE level_name = 'YEAR';
Keep in mind that in the hierarchy view there are rows for all hierarchy members at all rows. When another attribute (MONTH_END_DATE in this case) is not determined by the member that attribute will be NULL.
The FILTER FACT keyword can be used in the USING clause to filter data prior to processing by the analytic view. This could be thought of as an inner select to the analytic view query.
In the following query FILTER FACT is used to filter to QUARTER level hierarchy members that begin with 'Q1' and Q2'. Note that FILTER FACT references elements in hierarchy objects rather than columns in a table. The analytic view SQL generator will automatically access underlying objects as needed to resolve the query.
SELECT time_hier.member_name, sales
FROM ANALYTIC VIEW (
USING sales_av HIERARCHIES(time_hier)
FILTER FACT (
time_hier TO level_name = 'QUARTER' AND (quarter_name like 'Q1%' OR quarter_name like 'Q2%')
)
)
WHERE time_hier.level_name = 'YEAR'
ORDER BY time_hier.hier_order;
Note that the previous query returned the same rows and values for sales as the table query returning sales for the first half of each year.
FILTER FACT can be used to filter multiple hierarchies. The next example returns YEAR and REGION level members where years aggregate only the first two quarters the countries Mexico and Canada.
SELECT time_hier.member_name AS time,
geography_hier.member_name AS geography,
sales
FROM ANALYTIC VIEW (
USING sales_av HIERARCHIES(time_hier, geography_hier)
FILTER FACT (
time_hier TO level_name = 'QUARTER' AND (quarter_name like 'Q1%' OR quarter_name like 'Q2%'),
geography_hier TO level_name = 'COUNTRY' AND country_name in ('Mexico','Canada')
)
)
WHERE time_hier.level_name = 'YEAR'
AND geography_hier.level_name = 'REGION'
ORDER BY time_hier.hier_order;