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;