Elevator Pitch
Dimensional analysis of data traditionally means copying data to a cube, as the realtime alternative is lots of advanced SQL. In Oracle 12.2 that became a lot easier with analytic views, where the advanced code is done in small pieces declaratively - then the database writes the heavy SQL itself.
Description
Traditionally to model and analyze your data with dimensions and hierarchies, either you copy your data to a data warehouse and from there to a dimensional cube, or you have to write quite a lot of rather advanced code and SQL to emulate the same directly on your relational data.
With analytic views (introduced in Oracle Database version 12.2), you declaratively define the dimensions and hierarchies similar to creating a cube, but it is only stored as a metadata layer on top of your tables; it is a multidimensional view of your relational data. The SQL language has been expanded to allow a simpler query syntax on the analytic views to utilize these metadata and automatically use advanced analytic functions to give you dimensional analysis on real-time data. As SQL is used below the covers, performance can be tuned using query rewrite with materialized views or In-Memory options or any other option you would normally use for SQL tuning.
This session will show code examples covering a lot of the syntax you have for creating and using analytic views.
Notes
Analytic views in version 12.2 provides a syntax for modelling data much like most dimensional cube databases with dimensions, hierarchies, attributes and measures. It is just not stored as a cube, but is only a metadata layer on top of the relational layer. Topics included in this session:
- CREATE ATTRIBUTE DIMENSION
- CREATE HIERARCHY
- Ordering hierarchies
- NAME, CAPTION and DESCRIPTION
- Relations between levels of hierarchies
- CREATE ANALYTIC VIEW
- Measures from multiple fact tables
- Calculated measures
- Hierachical syntax in analytic functions
- Special analytic functions like LAG_DIFF, LAG_DIFF_PERCENT, SHARE_OF
- Querying analytic views at various levels of the hierarchies - SQL enhancements
- Formatting metadata