In my previous blog post An Introduction to Analytic Views in Oracle 12.2 I briefly mentioned that one of the killer features of Analytic Views is their ability to hold a huge amount of metadata within the database objects themselves, which can then be accessed and utilized from the application tier. Last time we built up a very simple Employee Sales view, which I will reuse again for the purposes of this demo. If you haven’t already read the previous blog then I’d suggest doing that before continuing here.
So taking our very simple data model – what does it actually represent? Well, I have already told you that, it represents sales by employees, however what if I wanted the database to provide you with that information? So you don’t need to maintain a separate set of documentation or have a local expert who can recall the details from memory. Well, using the Classification clause we can do just that. We can modify the view definition we created previously to the following:
Create or Replace Analytic View emp_sales_av Classification Caption Value 'Employee Sales' Classification Description Value 'Employee Sales Amount over Time' 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 Classification Caption Value 'Sales Amount' );
Note I have also given a more descriptive classification to the measure we defined. If we now create a very simple application (I am going to use Oracle Application Express due to its power, ease of use and the fact that it comes ready shipped with an Oracle Cloud Exadata Express instance), we can make use of that metadata within the application very easy by using the dictionary views for AV’s. First, we will get the information about our model and display it.
Select * From ( Select uavc.classification, To_Char(uavc.value) value From user_analytic_view_class uavc Where uavc.analytic_view_name = 'EMP_SALES_AV' ) Pivot (Max(value) For classification In ('CAPTION' "Model Name",'DESCRIPTION' "Description")) 
Then we will use our query from the previous blog post however modify the headings to use metadata stored within the data dictionary.
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';
We can easily do this by taking advantage of Apex’s capability to populate column headings from a PL/SQL source. First we of course need to add similar Classification‘s to our attribute dimension objects, however that’s as easy as before. I.e.
Create or Replace Attribute Dimension date_dim Using calendar Attributes ( year_name Classification Title Value 'Year', qtr_name Classification Title Value 'Quarter', month_name Classification Title Value 'Month', week_num Classification Title Value 'Week Number', day_dt Classification Title Value 'Date', row_wid ) -- And the rest of the Attribute Dimension
Now create a function prototype that returns a colon-delimited header string (as per Apex requirements.
Declare x Varchar2(4000); Begin Select Listagg(value,':') Within Group (Order by sort_order) Into x From ( Select aadac.value, Case aadac.attribute_name When 'YEAR_NAME' Then 1 When 'LVL2_MGR' Then 2 End sort_order From user_attribute_dim_attr_class aadac Where (aadac.dimension_name, aadac.attribute_name) In (('DATE_DIM','YEAR_NAME'),('EMP_DIM','LVL2_MGR')) Union All Select value, 3 From user_analytic_view_meas_class uavmc Where (uavmc.analytic_view_name,uavmc.measure_name) In (('EMP_SALES_AV','AMOUNT')) ); Return x; End; Modify the header source to use that.
Refresh our report, and voila! We see the headings as defined in the database dictionary.
Now this might seem like overkill however you need to step back and appreciate the beauty and possibilities of this somewhat. We have a single source of the truth which all developers can access and utilize. If one thing changes, we can modify the database object extremely easily and those changes will propagate throughout every application, seamlessly and immediately. If I decided to rename “Sales Amount” to just “Sales”, I can do that on the database, and my application picks that up straight away.
There are a whole host of dictionary views listed here or you can even use the new Analytic View reports of SQL Developer 4.2 (I prefer the former!). Of course you don’t just need to use Classifications for descriptive names. You can hold a whole host of various information such as format masks for numbers and dates, column defaults and more. Just be wary of overusing the functionality in place of something that should actually be a dimension.
Look out for up and coming posts around other aspects of Analytic Views and new features of Database 12.2.