One of the major new features in Oracle Database 12c Release 2 is Analytic Views.
An analytic view is a type of view that can be used to easily extend the content of a star schema with aggregated data, measure calculations and descriptive metadata, and to simplify the SQL needed to access data.
Analytic views layer a hierarchical/dimensional model over data. Analytic views are defined over the dimension tables and fact table of a star schema. Hierarchies are defined over dimension tables. The analytic view references hierarchies and a fact table.
I’m not here to introduce this new feature itself, more just to let you know that Oracle SQL Developer v4.2 has a TON of support for it – courtesy the same team that built this database feature.
If you’d like to take a tutorial on this subject, LiveSQL has a nice on-line demo – no database required!
The SQLDev Stuff
It starts in the Tree.
Or wait, don’t already have a Analytic View to play with? We have kind of a quick-start wizard for you. And I used it, on my Hockey Stats table, which has a ton of fun data to slice and dice.
Just right-click on the analytic view tree node item, and ask for the ‘Quick Analytic View…’ option.
Pick a table…the wizard will then try to identify and model some dimension keys, measures, and attribute dimensions.
I can of course change any of the guesses that may or may not have been spot on.
And of course I can see the generated DDL – and here’s what some of that looks like.
So I click, ‘OK’, and the tree refreshes, and I suddenly have new toys to play with!
Hit the ‘Play’ Button, then toggle to the data panel…
…or much more interesting, go over to the graph page!
There’s so, so, so much more than this.
Do you have 45 minutes? If so, try this very nice hands on lab.