-- 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;