Oracle Database 12c Release 2: Analytic Views & SQL Developer

thatjeffsmith SQL Developer 3 Comments

Tell Others About This Story:

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.

Let the fun begin!

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.

Quick, that sounds cool.

Pick a table…the wizard will then try to identify and model some dimension keys, measures, and attribute dimensions.

Pick a table, the wizard looks for likely candidates.

I can of course change any of the guesses that may or may not have been spot on.

The ‘defaults’ – you can go and un-check or change any of these.

And of course I can see the generated DDL – and here’s what some of that looks like.

The code!

So I click, ‘OK’, and the tree refreshes, and I suddenly have new toys to play with!

Check whatever parameters you want to shape your query.

Hit the ‘Play’ Button, then toggle to the data panel…

Kinda boring, but it’s our data.

…or much more interesting, go over to the graph page!

The same charting system you’ll see in SQL Developer’s Reports feature.

There’s so, so, so much more than this.

Do you have 45 minutes? If so, try this very nice hands on lab.

Teach me!!!

Tell Others About This Story:

Comments 3

    1. thatjeffsmith Post

Leave a Reply

Your email address will not be published. Required fields are marked *