Mostly Making oracle easy, mostly:
Search for:
In SQL Developer

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

3 Mins Read

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!!!
Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Related Posts

12 Comments

  1. Kathryn Reply

    How do I get the Analytic Views section to appear on my tree?

    I’m using sqldeveloper Version 17.3.1.279 and the tree shows OLAP Option and then Scheduler – thers is no node for Analytic Views.

    • Kathryn

      Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

    • thatjeffsmith Post Author

      Please confirm you haven’t disabled any features or turned on the tree filtering in preferences.

    • Kathryn

      Tree filtering is disabled, all the features are ticked except Haddop, RDF Graph, Real Time SQL Monitoring and TimesTen

    • thatjeffsmith Post Author

      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.

    • Kathryn

      Done that. Still no sign of Analyic Views. Is there a database parameter (in the DB) that needs to be set?

    • thatjeffsmith Post Author

      Yup, your COMPATIBLE flag most likely

      we look for this –

      SELECT VALUE
  FROM database_compatible_level v
 WHERE v.value LIKE '12.2%'
    OR v.value LIKE '18%'
    OR v.value LIKE '19%'
    OR v.value LIKE '2%'
    • Kathryn

      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

      grrr…

      I’ll have to find out why we have not set this to 12.2.

      Many thanks for your help Jeff.

  2. edson Reply

    Does this version of SQL DEVELOPER Version 4.2 create any object in the database without user consent?

Write A Comment