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'
Dimension By (
Key date_wid References row_wid Hierarchies (calendar_hier Default),
Key emp_wid References row_wid Hierarchies (employee_hier Default)
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.