Back onto analytical views, today I’m going to demo using calculated fact measures in analytic views as opposed to standard fact measures.
You may recall from my Introduction to Analytic Views post that we can create simple fact measures within the fact table itself as follows.
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 );
Useful, and with a bit of manipulation, subqueries etc. we can do quite a bit with that basic measure using plan SQL. There is however no need to go through all that effort. Analytic Views support Calculated Measures, and that makes them a whole lot more useful. Taking our model used previously we can quickly and easily extend it to include many more derived measures; and these functions extend out far further than the existing Analytic Functions available in database <lt; 12.2 (although you will note syntactical similarities). We can build measures based on the hierarchies with the AV, and even on levels within the hierarchy. So to add a simple measure of Sales Year to Date, it’s as easy as:
amount_ytd As (Sum(amount) Over (Hierarchy calendar_hier Between Unbounded Preceding and Current Member Within Ancestor At Level cal_year))
Breaking that statement up a little:
- amount_ytd As – Create a column amount_ytd which is calculated as…
- Sum(amount) Over – The sum of the amount, using the following rules
- Hierarchy calendar_hier – The calendar hierarchy
- Between Unbounded Preceding and Current Member – When aggregating the rows in our range, go back as far as you wish but only up to the date of the current record
- Within Ancestor At Level cal_year – Limits the range to all members at the current level that share an ancestor – this in case, cal_year.
Querying the view for one employee:
Select calendar_hier.year_name, calendar_hier.month_name, Round(amount) amt, Round(amount_ytd) amt_ytd From emp_sales_av Hierarchies (calendar_hier, employee_hier) Where calendar_hier.level_name = 'CAL_MONTH' And employee_hier.level_name = 'EMP' And employee_hier.empno=7369 Order By calendar_hier.year_name, calendar_hier.month_name;
We can do far more than that though. What if we wanted to see employee sales for just Qtr 3, but comparing them to Qtr2? We could write subqueries, pivots etc, or we could simply do this:
amount_qago As (Lag(Amount) Over (Hierarchy calendar_hier Offset 1 Across Ancestor At Level cal_qtr))
With our query becoming
Select calendar_hier.year_name, calendar_hier.qtr_name, Round(amount) amt, Round(amount_qago) amt_qtr_ago From emp_sales_av Hierarchies (calendar_hier, employee_hier) Where calendar_hier.level_name = 'CAL_QTR' And employee_hier.level_name = 'EMP' And employee_hier.empno=7369 And calendar_hier.member_name='3' Order By calendar_hier.year_name, calendar_hier.qtr_name;
There are many , many other examples of things we can do such as (but not limited to) percentage shares of dimensions, lag percentage shares (i.e. how has my percentage share changed compared to last year?), and a whole host of things based on hierarchy levels and ranges.
Now that the SQL Reference for 12.2 has been released, hopefully we’ll see some more exotic examples of what can be achieved with AV’s, although we may have to wait until on-premise is made available before things really start to take off.
This will be my last post for 2016 so Merry Christmas and a Happy New Year to all.