An Introduction to Analytic Views in Oracle 12.2

In my last blog post on Oracle Database 12.2 I mentioned the new Analytics View feature. Now in this post I am going to describe the basic structure of what an Analytic View is, and then show an example of creating a very simple one. Note – the Oracle documentation for syntax is very thin at the moment and this information has therefore been taken from various sources as well as some trial and error.
Anyway, enough of that… what actually is an Analytic View? As I described previously, it’s essentially a database structure to encapsulate an OLAP cube, providing an easy to use object for querying over hierarchical levels with automatic aggregation, storage of metadata and a whole lot more. Think of them as a basic RPD in Oracle Business Intelligence. Oracle describes them as:

Analytic Views (AV’s) provide a fast and efficient way to create analytic queries of data stored in existing database tables and views. Analytic views organize data using a dimensional model. They allow you to easily add aggregations and calculations to data sets and to present data in views that can be queried with relatively simple SQL.

Now let’s look at the structure of an AV. The model is made up of three objects.

  • Dimension Attribute – Defines a dimension along with the hierarchical levels (multiple hierarchies possible)
  • Hierarchy – Defines the hierarchical structure of a dimension (parent/child)
  • Analytic View – Brings together the hierarchies and defines the measures

A diagram can be useful here to illustrate.

Av Diagram

There are other attributes and concepts that can be used within the above, such as classifications (which allow for the holding of metadata), however I intend to cover those in a future post as that really is a whole topic within itself. So for the purposes of this post we will keep everything very simple.

Update – 23/03/2017 – I have added this as a shared LiveSQL script. Click here to view.


 

To create the AV we need some data. I could use the SH (Sales History) schema however I chose instead to quickly mock up some data to represent employees making sales on a given date within a year. This gives the following extremely simple star schema:

Star Schema

And using a method to generate rows we can very quickly create some sample data. Then we do a few warehouse things like bitmap indexes. You can pre-seed dbms_random if you want reproducible data.

-- Create a calendar table Create Table calendar As Select To_Char(v.day_dt,'YYYYMMDD') row_wid, v.day_dt, To_Char(v.day_dt, 'YYYY') year_name,         To_Char(v.day_dt, 'Q') qtr_name, To_Char(v.day_dt,'MM') month_name,         To_Char(v.day_dt,'IW') week_num  From (   Select Trunc(Sysdate,'YYYY') + Level - 1 day_dt     From dual   Connect By Level <= Add_Months(Trunc(Sysdate,'YYYY'),12) - Trunc(Sysdate,'YYYY') ) v;  -- Create an employees table with a manager reporting line Create Table employees As  Select rownum row_wid, v.empno, v.ename,        Coalesce(v.lvl1_mgr, v.ename) lvl1_mgr,        Coalesce(v.lvl2_mgr, lvl1_mgr, v.ename) lvl2_mgr,        Coalesce(v.lvl3_mgr, v.lvl2_mgr, lvl1_mgr, v.ename) lvl3_mgr   From ( Select e.empno, e.ename,        Trim('|' From RegExp_Substr(Sys_Connect_By_Path(e.ename,'|'),'\|[^\|]+',1,1)) Lvl1_mgr,       Trim('|' From RegExp_Substr(Sys_Connect_By_Path(e.ename,'|'),'\|[^\|]+',1,2)) Lvl2_mgr,       Trim('|' From RegExp_Substr(Sys_Connect_By_Path(e.ename,'|'),'\|[^\|]+',1,3)) Lvl3_mgr   From emp e Start With e.mgr Is Null Connect By Prior e.empno=e.mgr) v;  -- Pre-seed for reproducible data --Exec dbms_random.seed(1);  -- Mock up 100k rows of fact data Create Table emp_sales As Select To_Char(Trunc(Sysdate,'YYYY')+Round(dbms_random.value(0,364)),'YYYYMMDD') date_wid,        Round(dbms_random.value(1,(Select Count(*) From emp))) emp_wid,        Round(dbms_random.value(1,1000),2) amount   From dual Connect By Level < 100000;  --- Some indexes Create Bitmap Index emp_sales_cal On emp_sales(date_wid); Create Bitmap Index emp_sales_emp On emp_sales(emp_wid); Create Index cal_yr On calendar(year_name); Create Index cal_mnt On calendar(month_name); Create Index cal_wk On calendar(week_num); Create Index cal_day On calendar(day_dt); Create Index emp_no On employees(empno); Create Index emp_mgr1 On employees(lvl1_mgr); 

Note that as we are using a 12c database we don’t need to gather stats as that is done for us when the table is created now.

So with our test data we can start building up our AV. We have two hierarchies – date and employee, so we need two attribute dimensions for those. As I mentioned previously I’m not going to record metadata in these for this post.

-- Date attribute Dimension Create or Replace Attribute Dimension date_dim Using calendar Attributes (   year_name,   qtr_name,   month_name,   week_num,   day_dt,   row_wid ) Level cal_day   Key row_wid   Member Name To_Char(row_wid)   Order by row_wid   Determines (week_num) Level cal_week   Key week_num   Member Name To_Char(week_num)   Order by week_num   Determines (month_name) Level cal_month   Key month_name   Member Name To_Char(month_name)   Order by month_name   Determines (qtr_name) Level cal_qtr   Key qtr_name   Member Name To_Char(qtr_name)   Order by qtr_name   Determines (year_name) Level cal_year   Key year_name   Member Name To_Char(year_name)   Order by year_name All Member Name 'Total';  -- Employee attribute dimension Create or Replace Attribute Dimension emp_dim Using employees Attributes (   empno,   ename,   lvl1_mgr,   lvl2_mgr,   lvl3_mgr,   row_wid ) Level emp   Key row_wid   Member Name To_Char(empno)   Order By empno   Determines (lvl3_mgr) Level lvl3_mgr   Key lvl3_mgr   Member Name lvl3_mgr   Order By lvl3_mgr   Determines (lvl2_mgr) Level lvl2_mgr   Key lvl2_mgr   Member Name lvl2_mgr   Order By lvl2_mgr   Determines (lvl1_mgr) Level lvl1_mgr   Key lvl1_mgr   Member Name lvl1_mgr   Order By lvl1_mgr All Member Name 'Total'; 

In the real-world we would of course use ID values for the level keys to deal with the situation where two managers share the same name. Now we have the attribute dimensions we need to create the hierarchie objects for those.

Create Hierarchy calendar_hier Using date_dim (   cal_day Child Of   cal_month Child Of   cal_qtr Child Of   cal_year );  Create Hierarchy employee_hier Using emp_dim (   emp Child Of   lvl3_mgr Child Of   lvl2_mgr Child Of   lvl1_mgr ); 

And finally we create our Analytic View object which for now has a single measure amount.

Create or Replace Analytic View emp_sales_av Using emp_sales Dimension By (   date_dim     Key date_wid References row_wid Hierarchies (calendar_hier Default),   emp_dim     Key emp_wid References row_wid Hierarchies (employee_hier Default) ) Measures (   amount Fact amount ); 

And that’s it. We now have an object which we can query by date and/or employee. A few examples.

Total sales by Qtr:

Select calendar_hier.member_name, amount   From emp_sales_av Hierarchies (calendar_hier) Where calendar_hier.level_name = 'CAL_QTR' Order By calendar_hier.member_name;  1	12396018.66 2	12384153.46 3	12569988.99 4	12508621.16 

Level 2 manager sales by year:

Select calendar_hier.member_name yr, employee_hier.member_name mgr2, amount   From emp_sales_av Hierarchies (calendar_hier, employee_hier) Where calendar_hier.level_name = 'CAL_YEAR'   And employee_hier.level_name = 'LVL2_MGR';  2016	JONES	19254014.79 2016	KING	1938454.63 2016	CLARK	5688824.85 2016	BLAKE	22977488 

2016 sales for employee 7369

Select amount   From emp_sales_av Hierarchies (calendar_hier, employee_hier) Where employee_hier.level_name = 'EMP'   And calendar_hier.level_name='CAL_YEAR'   And employee_hier.empno='7369'   And calendar_hier.member_name='2016';    3863684.15 

This demo was produced on an Oracle Exadata Express Cloud Instance. Each of the above queries executed in well under quarter of a second. Quite impressive.
Next posts will be covering calculated measures, metadata and the data dictionary, and some analysis of what is actually happening when the optimizer receives an AV query.

Leave a Reply

Your email address will not be published. Required fields are marked *

20 − 6 =

This site uses Akismet to reduce spam. Learn how your comment data is processed.