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))