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.
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:
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.