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.
How do I get the Analytic Views section to appear on my tree?
I’m using sqldeveloper Version 188.8.131.529 and the tree shows OLAP Option and then Scheduler – thers is no node for Analytic Views.
Are you connected to a 12R2 database?
Oracle Database 12c Enterprise Edition Release 184.108.40.206.0 – 64bit Production
Please confirm you haven’t disabled any features or turned on the tree filtering in preferences.
Tree filtering is disabled, all the features are ticked except Haddop, RDF Graph, Real Time SQL Monitoring and TimesTen
I would try two things: 1)get a copy of 18.2 and 2)turn those features back on – it’s possible there’s an expected dependency on those features.
Done that. Still no sign of Analyic Views. Is there a database parameter (in the DB) that needs to be set?
Yup, your COMPATIBLE flag most likely
we look for this –
aha thats it.
We upgraded a few months ago from 12.1 and the compatible parameter is still 12.1.0
SQL> sho parameter compat
NAME TYPE VALUE
———————————— ———– ——————————
compatible string 12.1.0
noncdb_compatible boolean FALSE
plsql_v2_compatibility boolean FALSE
I’ll have to find out why we have not set this to 12.2.
Many thanks for your help Jeff.
Does this version of SQL DEVELOPER Version 4.2 create any object in the database without user consent?
Not that I know of, and it would be a bug if it did.