Querying Analytic Views

Before You Begin

This 15-minute tutorial shows you how to query analytic views (AVs) with SQL to achieve the same functionality as Microsoft’s Multidimensional Expression language. You can also use the DBMS_MDX_ODBO package, the Multidimensional Expression language interface provided by PL/SQL. The tutorial also shows you how to add additional calculated measures to a query of an AV.

Background

Before Oracle Database 18c, querying an AV always produced non-visual totals only whereas the Multidimensional Expression language is able to produce visual totals.

What is a non-visual result versus a visual result? Consider the following sales data from code1.

Predicates specified in a WHERE clause of a non-visual totals query reduce the rows returned, but they don't affect the aggregated measure data. In the following table, even if you query sales data for the first and second quarters of the year, the sum aggregates all quarters of the parent level (year).
Visual totals affect aggregated measured data. If a node has descendants in the selection, only those descendants are used to aggregate up to that node.

For example, if the selection includes the year 2016 but none of its descendants, the data for 2016 is aggregated from all of its leaves. In this case, the visual and non-visual totals are equivalent.

If the selection includes 2016 but only Q1-2016 and Q2-2016, data for 2016 using only those quarters is aggregated. In the following table, if you query sales data for the first and second quarters of the year, the sum aggregates only the quarters defined in the query for the parent level (year).

code2.

Oracle Database 18c introduces filter-before aggregate predicates for SQL to be able to produce visual totals from AVs.

What Do You Need?

Oracle Database 18c
A container database (CDB) with one pluggable database (PDB)
A fact table, SALES_FACT, and three dimension tables, GEOGRAPHY_DIM, PRODUCT_DIM and TIME_DIM to create an analytic view.
The data for the SALES_FACT table is available in sales_fact.dat. Download sales_fact.dat to the labs directory created on your server /home/oracle/labs. The file contains records like: Apr-11,-536,(S/P)_AT,136,42113.49 Apr-11,-536,(S/P)_DE,144,44475.36 Apr-11,-536,(S/P)_FI,94,25824.53
The fields are MONTH_ID,CATEGORY_ID,STATE_PROVINCE_ID,UNITS,SALES.
The data for the GEOGRAPHY_DIM table is available in geography_dim.dat. Download geography_dim.dat to the labs directory created on your server /home/oracle/labs. The file contains records such as: AFRICA,Africa,ZM,Zambia,LUSAKA_ZM,Lusaka - ZM SOUTH_AMERICA,South America,VE,Venezuela,CARABOBO_VE,Carabobo - VE NORTH_AMERICA,North America,CA,Canada,BRITISH_COLUMBIA_CA,British Columbia - CA The fields are REGION_ID,REGION_NAME,COUNTRY_ID,COUNTRY_NAME,STATE_PROVINCE_ID,STATE_PROVINCE_NAME.
The data for the PRODUCT_DIM table is available in product_dim.dat. Download product_dim.dat to the labs directory created on your server /home/oracle/labs. The file contains records such as: -520,Portable Music and Video,-530,Total iPlayer Family -519,Cameras and Camcorders,-534,Camcorders and Accessories -518,Computers,-533,All Computer Furniture The fields are DEPARTMENT_ID,DEPARTMENT_NAME,CATEGORY_ID,CATEGORY_NAME.
The data for the TIME_DIM table is available in time_dim.dat. Download time_dim.dat to the labs directory created on your server /home/oracle/labs. The file contains records such as: 11,CY2011,31-DEC-11,211,Q2CY2011,30-JUN-11,2,Apr-11,Apr-11,30-APR-11,4,April 2011,Spring,1,1 12,CY2012,31-DEC-12,212,Q2CY2012,30-JUN-12,2,Apr-12,Apr-12,30-APR-12,4,April 2012,Spring,1,1 13,CY2013,31-DEC-13,213,Q2CY2013,30-JUN-13,2,Apr-13,Apr-13,30-APR-13,4,April 2013,Spring,1,1 The fields are YEAR_ID,YEAR_NAME,YEAR_END_DATE,QUARTER_ID,QUARTER_NAME,QUARTER_END_DATE,QUARTER_OF_YEAR,MONTH_ID,MONTH_NAME,MONTH_END_DATE,MONTH_OF_YEAR,MONTH_LONG_NAME,SEASON,SEASON_ORDER,MONTH_OF_QUARTER.

Create a User AV and Tables

sqlplus system@PDB_ORCL
Enter password: password

Create the directory where the files will reside.
```
CREATE DIRECTORY ext_dir AS '/home/oracle/labs';
```
Create the user AV, and grant the user the CREATE SESSION, UNLIMITED TABLESPACE, CREATE TABLE, CREATE ATTRIBUTE DIMENSION, CREATE HIERARCHY, CREATE ANALYTIC VIEW system privileges, and read and write privileges on the logical directory just created.
```
CREATE USER av IDENTIFIED BY password;
```
```
GRANT CREATE SESSION, UNLIMITED TABLESPACE, CREATE TABLE, 
      CREATE ATTRIBUTE DIMENSION, CREATE HIERARCHY, CREATE ANALYTIC VIEW 
TO av;
GRANT read, write ON DIRECTORY ext_dir TO av;
```

Create the first dimension table.

CREATE TABLE AV.GEOGRAPHY_DIM (  
  region_id varchar2(120), region_name varchar2(120), country_id varchar2(2),
  country_name varchar2(120), state_province_id varchar2(120),
  state_province_name varchar2(400));

Create the second dimension table.

CREATE TABLE AV.PRODUCT_DIM (
  department_id number(22), department_name varchar2(100),
  category_id number(22), category_name varchar2(100));

Create the third dimension table.

CREATE TABLE AV.TIME_DIM (
 YEAR_ID varchar2(30), YEAR_NAME varchar2(40), YEAR_END_DATE DATE,
 QUARTER_ID varchar2(30), QUARTER_NAME varchar2(40), QUARTER_END_DATE DATE,
 QUARTER_OF_YEAR number(22), MONTH_ID varchar2(30), MONTH_NAME varchar2(40),
 MONTH_END_DATE DATE, MONTH_OF_YEAR number(22),
 MONTH_LONG_NAME varchar2(30), SEASON varchar2(10), SEASON_ORDER number(22),
 MONTH_OF_QUARTER number(22));

Create the fact table and quit the session.

CREATE TABLE AV.SALES_FACT (
  MONTH_ID varchar2(10), CATEGORY_ID number(22),
  STATE_PROVINCE_ID varchar2(120), UNITS number(22), SALES number(22));

EXIT

Load the first table from the data files.

cd /home/oracle/labs

sqlldr av@PDB_ORCL table=geography_dim
Password: password
...
Express Mode Load, Table: GEOGRAPHY_DIM
...
Table GEOGRAPHY_DIM:
  181 Rows successfully loaded.

Load the second table from the data files.

sqlldr av@PDB_ORCL table=product_dim
Password: password
...
Express Mode Load, Table: PRODUCT_DIM
...
Table PRODUCT_DIM:
  8 Rows successfully loaded.

Load the third table from the data files.

sqlldr av@PDB_ORCL table=time_dim
Password: password
...
Express Mode Load, Table: TIME_DIM
...
Table TIME_DIM:
  60 Rows successfully loaded.

Load the fourth table from the data files.

sqlldr av@PDB_ORCL table=sales_fact
Password: password
...
Express Mode Load, Table: SALES_FACT
...
Table SALES_FACT:
  5961 Rows successfully loaded.

AV.

sqlplus av@PDB_ORCL
Enter password: password

Create the time attribute dimension from code3, and create the time hierarchy.

CREATE OR REPLACE HIERARCHY time_hier
       USING time_attr_dim
       (month  CHILD OF  quarter CHILD OF year);

Create the product attribute dimension from code4, and create the product hierarchy.

CREATE OR REPLACE HIERARCHY product_hier
       USING product_attr_dim 
       (category CHILD OF department);

Create the geography attribute dimension from code5, and create the geography hierarchy.

CREATE OR REPLACE HIERARCHY geography_hier
       USING geography_attr_dim
       (state_province CHILD OF country CHILD OF region);

Create the analytic view.

CREATE OR REPLACE ANALYTIC VIEW sales_av USING 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;

Query the AVs

Look at the TIME_HIER hierarchy.

SELECT year_name, quarter_name, month_name, depth, parent_level_name 
FROM   time_hier;

Read the result from code6.

Query the AV to find the values of sales for all years, per year and per quarter.

SELECT time_hier.member_name, sales
FROM   sales_av HIERARCHIES(time_hier)
WHERE  time_hier.level_name IN  ('ALL','YEAR','QUARTER')
ORDER BY time_hier.hier_order;

Read the values of sales for all years, per year and per quarter from code7.

Query the AV to find the values of sales for all months for the first and second quarters of all years.

SELECT time_hier.member_name, sales
FROM   sales_av HIERARCHIES(time_hier)
WHERE  time_hier.level_name = 'MONTH' AND
TO_CHAR(time_hier.month_end_date,'Q') IN (1,2)
ORDER BY time_hier.hier_order;

Read the values of sales for all months for the first and second quarters of all years from code8.

Query the AVs Using Filter-Before Aggregate Predicates

You can add filters in the FROM clause of the predicate by specifying the clause ANALYTIC VIEW and then using the clause FILTER FACT to specify the filters. The USING clause in the analytic view in the FROM clause specifies the analytic view that provides the hierarchies and measures for the query.

Report the sales at the year and quarter levels, but only for the first half of each year. With Oracle Database 18c, you can use FILTER FACT to filter by hierarchies. This example filters on months where the quarter of year of the MONTH_END_DATE attribute is 1 or 2. The hierarchy used is TIME_HIER.
```
SELECT time_hier.member_name, sales
FROM ANALYTIC VIEW ( USING sales_av  
                     HIERARCHIES(time_hier)
                     FILTER FACT (time_hier TO level_name = 'MONTH' 
                                  AND TO_CHAR(month_end_date,'Q') IN (1,2)))
WHERE time_hier.level_name IN ('ALL','YEAR','QUARTER')
ORDER BY time_hier.hier_order;
```
Do you get visual or non-visual totals? Read the result from code9. The filter-before aggregate predicate, for year 2011, filtered out the forth quarter (see Q4CY2011 in the previous query). The resulting total of sales for year 2011 aggregates only the first two quarters. This is the same behavior for all years. The aggregated sum of sales for all years is smaller in the second query than in the previous query. SQL execution with filter-before aggregate predicates reports visual totals.

Now filter on two hierarchies, TIME_HIER and GEOGRAPHY_HIER. Report the sales for the first half of each year (first and second quarters) in 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;

Read the result.

TIME   GEOGRAPHY             SALES
------ ---------------- ----------
CY2011 North America      38931636
CY2012 North America      39129154
CY2013 North America      41586079
CY2014 North America      13119131

Add another country and execute the query again. Report the sales for the first half of each year (first and second quarters) in Mexico, Canada and Chile.

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','Chile')))
WHERE time_hier.level_name = 'YEAR' AND geography_hier.level_name = 'REGION'
ORDER BY time_hier.hier_order;

Read the result.

TIME   GEOGRAPHY             SALES
------ ---------------- ----------
CY2011 North America      38931636
CY2011 South America       4880427
CY2012 North America      39129154
CY2012 South America       4764838
CY2013 South America       5423944
CY2013 North America      41586079
CY2014 South America       1618375
CY2014 North America      13119131

This time, add the ALL level in the predicate to the query and execute the query again Report the global sales total for each year of each region.

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','Chile')))
WHERE time_hier.level_name IN ('ALL','YEAR')
AND   geography_hier.level_name = 'REGION'
ORDER BY time_hier.hier_order;

Read the result from code10. Does the sales total for North America include all years all the quarters and all countries? No. Each hierarchy specifies a filter-before aggregate predicate that filters the leaves of that hierarchy before aggregating the measures. Therefore the sales values are the aggregated values for only the first and second quarters of each year.

Query the AVs Using Calculated Measures

The USING clause in the FROM clause specifies the analytic view that provides the hierarchies and other measures for the query. Using the clause ADD MEASURES defines additional calculated measures to the query.

Define two calculated measures by adding the total of sales for the period before each period and the percentage of change in sales against the prior period.

SELECT time_hier.member_name, sales, sales_prior_period,
       ROUND(sales_prior_period_pct_change,3) AS percent_change_sales
FROM ANALYTIC VIEW ( USING sales_av 
                     HIERARCHIES(time_hier)
                     ADD MEASURES (
    sales_prior_period AS (LAG(sales) OVER (HIERARCHY time_hier OFFSET 1)),
    sales_prior_period_pct_change AS (LAG_DIFF_PERCENT(sales)
                                      OVER (HIERARCHY time_hier OFFSET 1))))
WHERE time_hier.level_name = 'YEAR'
ORDER BY time_hier.hier_order;

Read the result.

MEMBER_N      SALES SALES_PRIOR_PERIOD PERCENT_CHANGE_SALES
-------- ---------- ------------------ --------------------
CY2011    593507775
CY2012    568622304          593507775                -.042
CY2013    611827904          568622304                 .076
CY2014    196300122          611827904                -.679

Query the AVs Using Filter-Before Aggregate Predicates and Calculated Measures

Report sales, the total of sales for the period before each period and the percentage of change in sales against the prior period for the first half of years in Mexico and Canada. Use the query from code11.

TIME   GEOGRAPHY             SALES SALES_PRIOR_PERIOD PERCENT_CHANGE_SALES
------ ---------------- ---------- ------------------ --------------------
CY2011 North America      38931636
CY2012 North America      39129154           38931636                 .005
CY2013 North America      41586079           39129154                 .063
CY2014 North America      13119131           41586079                -.685

Add a WHERE clause and see if it affects the aggregated measure data. Use the query from code12.
```
TIME   GEOGRAPHY             SALES SALES_PRIOR_PERIOD PERCENT_CHANGE_SALES
------ ---------------- ---------- ------------------ --------------------
CY2011 North America      38931636
CY2012 North America      39129154           38931636                 .005
CY2013 North America      41586079           39129154                 .063
```
Adding a WHERE clause in a query doesn't affect the aggregation of the measure data. It simply reduces the rows the query returns after applying the filter-before aggregation predicates and the added measure calculations.

Clean Up the Environment

Drop the AV user.
```
DROP USER av CASCADE;
```
Quit the session.
```
EXIT
```