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.