Posts tagged Sql
Connecting Looker to Oracle Autonomous Data Warehouse Cloud

Earlier in the week I wrote-up my first impressions of Oracle Autonomous Data Warehouse Cloud (ADWC) on this blog, and said at the end I’d follow with another post on how to connect ADWC to Looker, the cloud-based BI tool I usually query Google BigQuery with in my role as Analytics Product Manager at Qubit.

For this initial example I uploaded a few tables of workout and other data into ADWC using SQL Developer, exporting the relevant tables out of BigQuery in CSV format and then uploading them into a user account I created for the purpose in AWDC. The table I’ll concentrate on for this example is the STRAVA_RIDES table, each row detailing an individual cycle workout as recorded through the Strava smartphone app.

The first step in connecting-up Looker to ADWC, or indeed any Oracle Database, is to run some scripts that set up Oracle as Looker expects to find it. Unlike most other BI tools I’ve used with Oracle, Looker expects to connect through a specific user login (“LOOKER”) that is then granted SELECT access to any tables and views you want to report on in other schemas. This user login also needs to have some views and synonyms created to give it access to the V$ system views within Oracle that report on active sessions, and an ability to kill long-running sessions through a PL/SQL package that calls ALTER SYSTEM … KILL SESSION.

The commands to run for regular Oracle databases are detailed on the Looker website but you need to alter them slightly to use ADWC’s superuser account name (“ADMIN”) instead of SYS when initially connecting and when creating the LOOKER_HASH function for symmetric aggregate handling, along with various other changes due to differences in how various objects are named in AWDS vs. regular Oracle Database.

I’ve listed the commands I ran on my ADWC instance below, they should work for you but if not then check out the “Autonomous Data Warehouse Cloud for Experienced Oracle Database Users” section in Using Autonomous Data Warehouse Cloud that explains the differences between the new autonomous and regular Oracle Database server versions.

First create the Looker account and grant the relevant roles and priviledges:

connect ADMIN/<<your_admin_password>>
create user LOOKER identified by <<new_looker_account_password>>;
alter user LOOKER
default tablespace DATA
temporary tablespace TEMP
account unlock;

alter user LOOKER quota unlimited on DATA;
alter user LOOKER default role RESOURCE;
grant CREATE SESSION to LOOKER;
GRANT UNLIMITED TABLESPACE TO LOOKER; 
GRANT CREATE TABLE TO LOOKER;
grant select on -- <all tables that will be used by looker>;

Now create the views that Looker uses to understand what sessions are active and the SQL that’s currently being executed to provide data for looks and dashboard tiles:

create or replace view LOOKER_SQL 
as
select SQL.SQL_ID, SQL.SQL_TEXT
from V$SQL sql ,v$session sess
where SESS.SQL_ADDRESS = SQL.ADDRESS
and SESS.USERNAME='LOOKER';
create or replace synonym LOOKER.LOOKER_SQL for LOOKER_SQL;
grant select ON LOOKER.LOOKER_SQL to LOOKER;
create or replace view LOOKER_SESSION as 
SELECT SID, USERNAME, TYPE, STATUS, SQL_ID, "SERIAL#", AUDSID
FROM V$SESSION
WHERE USERNAME='LOOKER';
create or replace synonym LOOKER.LOOKER_SESSION FOR LOOKER_SESSION;
GRANT SELECT ON LOOKER.LOOKER_SESSION TO LOOKER;

Next, create the Oracle PL/SQL function that Looker uses as part of symmetric aggregate handling, and a function that Looker can use to “kill” runaway database queries that are taking too long to return results back to you.

create or replace function LOOKER_HASH(bytes raw, prec number)   return raw as   
begin
return(DBMS_CRYPTO.HASH(bytes, prec));
end;
create or replace synonym LOOKER.LOOKER_HASH for LOOKER_HASH;
grant execute on LOOKER.LOOKER_HASH to LOOKER;  
grant execute on ADMIN.LOOKER_HASH to LOOKER;
create or replace procedure LOOKER_KILL_QUERY(P_SID in VARCHAR2,
P_SERIAL# in VARCHAR2)
is
CURSOR_NAME pls_integer default dbms_sql.open_cursor;
IGNORE pls_integer;
begin
select COUNT(*) into IGNORE
from V$SESSION
where USERNAME = USER
and SID = P_SID
and SERIAL# = P_SERIAL#;
if (IGNORE = 1)
then
dbms_sql.parse(CURSOR_NAME,
'alter system kill session '''
|| P_SID || ',' || P_SERIAL# || '''',
dbms_sql.native);
IGNORE := dbms_sql.execute(CURSOR_NAME);
else
raise_application_error(-20001,
'You do not own session ''' ||
P_SID || ',' || P_SERIAL# ||
'''');
end if;
end;
create or replace synonym LOOKER.LOOKER_KILL_QUERY 
for ADMIN.LOOKER_KILL_QUERY;
grant execute on ADMIN.LOOKER_KILL_QUERY to LOOKER;

Next over to the Looker configuration. You’ll need to be on the Looker 5.12.12 or higher release with an instance hosted in the US to get the integration working as of the time of writing so that “ADWC” is listed as a connection type and the ADWC wallet integration works; if you’re running Looker as a hosted instance you’ll also need to speak with support to have them copy across the wallet files to the correct location on the Looker server.

To create the connection, enter the following details:

  • Name : Name of your connection, e.g. “rittman_adwc”
  • Dialect : Oracle ADWC (only appears with Looker 5.12.12+)
  • Host:Port : from the TNSNAMES.ORA file in your ADWC wallet zip file
  • Username : LOOKER (as per the account setup in previous steps)
  • Password : password of LOOKER account
  • Temp Database : LOOKER (as per previous steps)
  • Persistent Derived Tables : checked
  • Service Name : From TNSNAMES.ORA in your ADWC wallet zip file
  • Additional Database Params : TNSNAMES.ORA SSL Server Cert DN

To show the Service Name and Additional Database Params fields you first have to save the connection, then tick the “Use TNS” checkbox to reveal the fields. To find your host:port, service name and SSL Server Cert DN values first download the wallet zip file for your ADWC instance from the ADWC Service Console, unzip the archive and then locate the details you need in the TNSNAMES.ORA file as shown below. In my case I chose to use the “medium” ADWC instance type for my connection settings.

Then, save and test your connection. The step that checks that persistent derived tables will probably fail if you try this around the time of my writing as there’s a known bug in the step that checks this feature, it’ll no doubt be fixed soon but if the rest of the checks pass you should be good.

Finally, it’s just then a case of importing your table metadata into Looker and creating explores and a model as you’d do with any other data source, like this:

In this instance I’ve updated the Strava Rides LookML view to turn the relevant metric fields into measures, define a primary key for the view and remove or hide fields that aren’t relevant to my analysis, like this:

Now I can start to analyze my Strava workout data I previously uploaded to Oracle Autonomous Data Warehouse, starting with average cadence and speed along with total distance and Strava’s “suffer score” for each of my workouts:

and then looking to see how much correlation there is between distance and personal strava records being broken on my five longest rides.

In the background, Looker is sending Oracle and ADWC-specific SQL to Oracle Autonomous Data Warehouse Cloud, with the SQL tab in the Explore interface showing me the actual SQL for each query as its sent.

Should I wish to check how much of the storage and CPU capacity available for my ADWC instance is being used, I can do this from ADWC’s Service Console.

So there you have it — Looker powered by an Oracle Autonomous Data Warehouse Cloud, and no need for an Oracle DBA to get it all running for you.

Timeline Charts, Derived Tables and Analytic Functions in Looker 5

One of the data streams that comes into my personal Google BigQuery data warehouse is a record of all the Hue light switch settings and illuminance levels, Nest thermostat settings and motion sensor readings coming in via the Samsung Smart Things hub under the stairs back home. For the Hue lightbulbs I get a stream 1s and 0s that initially get stored in a four-column table in BigQuery, like this:

I then create a SQL view over that table that joins to a lookup table containing device types and room allocated for each of the devices in the data stream, and sets a metric type for each recording so that I can analyze all the thermostat readings together, bring together all the light switch settings and so on.

Its then quite straightforward to bring this SQL view into Looker and add it to the rest of the data streams I’ve brought together there so I can see, for example, whether the kitchen lights get turned-off after everyone’s gone upstairs after breakfast or whether I should try and automate those as well as my kettle.

Looker 5 introduced another way to visualize data with defined start and end events in the form of the new Timeline Chart, a feature I tweeted about the other day when I brought data on tasks from Asana into my BigQuery data warehouse via a Fivetran integration.

It’d be interesting to use this type of chart to show how one particular light switch changed from on to off and then on again over a period of time, or take all the Hue lights for a given part of the house and see whether anybody turns the lights off ever … or whether they need to be linked to the motion sensors and automatically turned-off after a period of inactivity in each room.

To use this type of chart I first need to create an “end date” field for each of my event records, so each timeline has a start and end date with other fields being optional for label and value. Looking at the list of events for the light switch I looked at earlier and ordering the events by time recorded you can see the information I need is there:

SELECT
*
FROM
`aerial-vehicle-148023.personal_metrics.smartthings_readings`
WHERE
device = 'kitchen seating area spot 1'
AND raw_value IN ('off','on')
ORDER BY
date_time

What this calls for is the LEAD BigQuery Standard SQL analytic function that returns a value based on a subsequent row to the one we’re working with, and I can combine that with the TIMESTAMP_DIFF function to compute the time between the start and end event dates as that would be useful to know too. The SQL expressions for the two new fields would therefore be:

LEAD(date_time, 1) OVER (PARTITION BY device, metric ORDER BY date_time) end_date_time,
timestamp_DIFF(LEAD(date_time, 1) OVER (PARTITION BY device, metric ORDER BY date_time),date_time,second) as value_duration_seconds

I could add these column expressions into the SQL view definition in BigQuery and order the rows by device, metric and timestamp, like this:

SELECT
date_time as date_time,
device as device,
metric,
value,
raw_value,
LEAD(date_time, 1) OVER (PARTITION BY device, metric ORDER BY date_time) as end_date_time,
timestamp_DIFF(LEAD(date_time, 1) OVER (PARTITION BY device, metric ORDER BY date_time),date_time,second) as value_duration_seconds
from `aerial-vehicle-148023.personal_metrics.smartthings_readings`
where device = 'kitchen seating area spot 1'
and metric = 'switch'
and raw_value in ('on','off')
order by 2,3,1

Executing that SQL shows the logic is working, but I’d then have to maintain that view within BigQuery and that might not be the most convenient place to add new code.

Instead, I could just go into Looker and create a new view there based on a derived table SQL expression and do the work there. Right now my LookML model looks like the excerpt below, where you can see the sensor readings view joined into the rest of the explore so all my data can be analyzed together.

connection: "rittman_bigquery"
include: "*.view.lkml" # include all views in this project
include: "*.dashboard.lookml" # include all dashboards in this project
explore: date_dim {
case_sensitive: no
label: "Data Warehouse"
join: fluentd_transactions {
type: left_outer
sql_on: ${date_dim.date_minute5} = ${fluentd_transactions.date_minute5} ;;
relationship: many_to_many
}
join: smartthings_readings {
type: left_outer
sql_on: ${date_dim.date_minute5} = ${smartthings_readings.date_minute5} ;;
relationship: one_to_many
}
join: fluentd_uber_rides {
type: left_outer
sql_on: ${date_dim.date_minute5} = ${fluentd_uber_rides.date_minute5} ;;
relationship: many_to_many
}
}

I now create a new LookML view that uses a derived table SQL query as the view definition rather than simply referencing an existing BigQuery table. Note how I’ve used the same view_label as the main LookML view containing my event data, so that the dimensions and metric I define here appear alongside all the other smart device fields the same explore view.

view: device_event_end_and_timespan {
view_label: "6 - Smart Devices"
derived_table: {
sql: SELECT
date_time as date_time,
device as device,
metric as metric,
LEAD(date_time, 1) OVER (PARTITION BY device, metric ORDER BY date_time) as end_date_time,
timestamp_DIFF(LEAD(date_time, 1) OVER (PARTITION BY device, metric ORDER BY date_time),date_time,second) as value_duration_seconds
from `aerial-vehicle-148023.personal_metrics.smartthings_readings`
order by 2,3,1;;
}
dimension: date_time {
type: date_time
hidden: yes
sql: ${TABLE}.date_time ;;
}
dimension: device {
type: string
hidden: yes
sql: ${TABLE}.device ;;
}
dimension: metric {
type: string
hidden: yes
sql: ${TABLE}.metric ;;
}
dimension_group: end_date_time {
group_label: "End Date"
label: "End"
type: time
timeframes: [
raw,
time,
hour,
hour3,
hour4,
hour6,
hour12,
hour_of_day,
time_of_day,
minute,
minute5,
minute10,
minute15,
minute30,
day_of_week,
day_of_month,
day_of_year,
date,
week,
week_of_year,
month,
month_name,
month_num,
quarter,
year
]
sql: ${TABLE}.end_date_time ;;
}
dimension: pk {
primary_key: yes
hidden: yes
type: string
sql: concat(cast(${TABLE}.start_date_time as string), ${TABLE}.device) ;;
}
measure: value_duration_seconds {
type: average
sql: ${TABLE}.value_duration_seconds ;;
}
}

Then I join this derived table view back into the explore within my LookML model, as below:

connection: "rittman_bigquery"
include: "*.view.lkml" # include all views in this project
include: "*.dashboard.lookml" # include all dashboards in this project
explore: date_dim {
case_sensitive: no
label: "Data Warehouse"
join: fluentd_transactions {
type: left_outer
sql_on: ${date_dim.date_minute5} = ${fluentd_transactions.date_minute5} ;;
relationship: many_to_many
}
join: smartthings_readings {
type: left_outer
sql_on: ${date_dim.date_minute5} = ${smartthings_readings.date_minute5} ;;
relationship: one_to_many
}
join: device_event_end_and_timespan {
type: left_outer
sql_on: ${smartthings_readings.date_time} = ${device_event_end_and_timespan.date_time} and
${smartthings_readings.device} = ${device_event_end_and_timespan.device} and
${smartthings_readings.metric} = ${device_event_end_and_timespan.metric};;
relationship: one_to_one
}
join: fluentd_uber_rides {
type: left_outer
sql_on: ${date_dim.date_minute5} = ${fluentd_uber_rides.date_minute5} ;;
relationship: many_to_many
}

and now I can create timeline charts that show me which lights were on over a particular period of time, like this:

or more importantly, work out why the bathroom never seems to be free when you’ve got two teenage kids in the house.

Analytic Views, Oracle Database 12.2

An interesting new feature came out with the 12c Release of Oracle Database Enterprise Edition called Analytic Views that looks, at first sight, to be an extension of the existing dimensions and materialized views features the Oracle database has had since the 8i release. For anyone who remembers those features being added to the Oracle Database back in the mid 90’s (and if you don’t, here’s a youthful-looking blogger writing about that topic on a soon-to-be-rather-controversial DBA website), the CREATE DIMENSION statement allowed you to define an aggregation rule for use by the database’s query optimizer, like this:

CREATE DIMENSION products
LEVEL product IS (products.prod_id, products.prod_desc, products.prod_list_price, products.prod_min_price, products.prod_name, products.prod_pack_size, products.prod_status, products.supplier_id, products.prod_unit_of_measure, products.prod_weight_class)
LEVEL subcategory IS (products.prod_subcategory, products.prod_subcat_desc)
LEVEL category IS (products.prod_category, products.prod_cat_desc)
HIERARCHY prod_hier (product CHILD OF subcategory CHILD OF category)
ATTRIBUTE product DETERMINES (products.prod_category, products.prod_id)
ATTRIBUTE subcategory DETERMINES (products.prod_category, products.prod_subcategory)
ATTRIBUTE category DETERMINES products.prod_category;

These aggregation rules could be thereafter be used by the optimizer to work out whether to rewrite the users’ query to use a pre-computed aggregate to answer that query instead of rolling-up from the detail-level the user specified in their query. Since then, subsequent releases of the Oracle Database have introduced a separate but related feature, the OLAP Option, that used technology originated from the old Express Server to create what were called Analytic Workspaces, fully-featured OLAP multi-dimensional caches together with their own programming language and suite of applications for sales and budgeting/planning.

Over the years Oracle introduced features to integrate analytic workspaces with the SQL query language used by the core database rather than developers having to learn and use OLAP DML, Oracle OLAP’s proprietary programming language, and the dimensional model provided by the OLAP Option and all OLAP servers is just as relevant today as it was back in the 1970’s when that technology first became available, and at that time and until recently the other innovation provided by dedicated multidimensional OLAP Servers — ultra-fast queries made possible by the use of a separate pre-computed and highly-indexed mid-tier data cache — was so valuable to users that the overhead in maintaining these caches, and the query latency they introduced because of the time it took to refresh and recompute the aggregates — made sense.

Until recently that is, when hardware used to host database servers suddenly became able to host the entire database in-memory and disk I/O was no longer a problem, and fast CPUs with multiple cores and large amounts of on-board cache RAM meant pre-computing calculated measures was no longer needed. So along came Oracle Database 12c and the In-Memory Option, that until the incident with the WiFi kettle was my claim to fame as the person Larry Ellison quoted on stage at Oracle when I said it was so easy to use it was “almost boring”.

Increasingly, it no longer made any sense to define and then maintain a separate multi-dimensional OLAP cache in a sort-of-embedded-server like Oracle OLAP, when the so-easy-to-use-it’s-boring Database In-Memory Option along with materialized views and bitmap indexes were just as fast to return query results to users … especially when this in-memory option also came with another feature, vector transformation plans, that enabled a special type of in-memory aggregation using the vector processing features in modern CPUs and had been developed — rather interestingly — by none other than the OLAP Option development team headed up by none other than … Bud Endress, the long-term development lead for Oracle OLAP and before that, Express Server.

So what’s going on here then? Well back then this adding of special aggregation capabilities into the in-memory database feature by the old OLAP Server team clearly signalled that this was where the Oracle intended to take the technology legacy from their standalone and then partly-integrated multidimensional OLAP server cache. but then the second shoe dropped back at Oracle Openworld two years ago when Analytic Views were first launched in a presentation by … Bud Endress.

[embed]https://twitter.com/markrittman/status/659776557523869696[/embed]

Analytic Views then came along with the 12.2 release of Oracle OLAP and fairly obviously were the other half to the puzzle of where Oracle were planning to take the people, ideas and inspiration that came out of Oracle OLAP and the twin core user benefits of a dimensional model, and fast response to user queries involving aggregation and slice-and-dice of structured datasets. Not only that though, they also included a new take on the simplified SQL query format that Oracle OLAP introduced a few years ago, gave users the ability to build out a complete Oracle BI-style business model right over the data in the database … and, as if that wasn’t enough surprises, support (via third-party Simba ODBC drivers) for MDX.


Taking the IoT, wearables and social media data I’ve been collecting and storing up until now in Google BigQuery and transferring that across to an Oracle Database 12c 12.2 database with Analytic Views and the In-Memory Option enabled, and using Oracle SQL Developer 4.2 with its new Analytic Views functionality now included, you can see that there is a new dedicated section for Analytic Views in the main interface not unlike the old Analytic Workspace Manager standalone tool from the Oracle 10g/11g days.

Creating a new analytic view involves one of two routes in SQL Developer 4.2 (or you could of course script it all using DDL statements); you can create a quick-and-dirty analytic view over a single table using the Quick Analytic View menu option as per the screenshot below that then introspects your data to determine candidate measure and dimension columns, but this doesn’t seem to handle the sorts of wide, multiple level-per-attribute dimension denormalized tables that are common in distributed query engines like Google BigQuery that prefer those over joining lots of separate tables;

or, as I did for my testing you can normalize your fact and dimension tables so they’re separate and joined via foreign key constraints and then create in turn your attribute dimensions, hierarchies and attribute views containing measures and aggregation rules as shown in the screenshots below.

So far, very much in the same vein as creating relational and OLAP Option cube structures in previous database versions and developer tools in the past, and you can view the commands that SQL Developer will then send to the database using one of the dialog tabs, giving you a set of DDL commands looking like this:

CREATE ATTRIBUTE DIMENSION DEVICE_DIMENSION 
DIMENSION TYPE STANDARD
CAPTION 'DEVICE DIMENSION'
DESCRIPTION 'DEVICE DIMENSION'
USING DEVICE_DIM
ATTRIBUTES (
DEVICE AS DEVICE
CAPTION 'DEVICE'
DESCRIPTION 'DEVICE' ,
DEVICETYPE AS DEVICETYPE
CAPTION 'DEVICETYPE'
DESCRIPTION 'DEVICETYPE' ,
ROOM AS ROOM
CAPTION 'ROOM'
DESCRIPTION 'ROOM'
)
LEVEL DEVICE_LEVEL
LEVEL TYPE STANDARD
CAPTION 'DEVICE LEVEL'
DESCRIPTION 'DEVICE LEVEL'
KEY ( "DEVICE")
MEMBER NAME DEVICE
MEMBER CAPTION DEVICE
MEMBER DESCRIPTION DEVICE
ORDER BY MIN DEVICE ASC NULLS FIRST
DETERMINES ( "DEVICE")
LEVEL ROOM_LEVEL
LEVEL TYPE STANDARD
CAPTION 'ROOM LEVEL'
DESCRIPTION 'ROOM LEVEL'
KEY ( "ROOM")
MEMBER NAME ROOM
MEMBER CAPTION ROOM
MEMBER DESCRIPTION ROOM
ORDER BY MIN ROOM ASC NULLS FIRST
DETERMINES ( "ROOM")
LEVEL DEVICETYPE
LEVEL TYPE STANDARD
CAPTION 'Devicetype'
DESCRIPTION 'Devicetype'
KEY ( "DEVICETYPE")
MEMBER NAME TO_CHAR("DEVICETYPE")
ORDER BY MIN DEVICETYPE ASC NULLS LAST
DETERMINES ( "DEVICETYPE")
ALL
MEMBER NAME 'ALL'
MEMBER CAPTION 'ALL'
MEMBER DESCRIPTION 'ALL';
CREATE ATTRIBUTE DIMENSION DATES_DIMENSION 
CAPTION 'DATES DIMENSION'
DESCRIPTION 'DATES DIMENSION'
USING DATES_DIM
ATTRIBUTES (
DATE_DATE AS DATE_DATE
CAPTION 'DATE_DATE'
DESCRIPTION 'DATE_DATE' ,
DAY_NAME AS DAY_NAME
CAPTION 'DAY_NAME'
DESCRIPTION 'DAY_NAME' ,
DAY_NUMBER AS DAY_NUMBER
CAPTION 'DAY_NUMBER'
DESCRIPTION 'DAY_NUMBER' ,
YEAR AS YEAR
CAPTION 'YEAR'
DESCRIPTION 'YEAR' ,
YEAR_DAY_OF_YEAR AS YEAR_DAY_OF_YEAR
CAPTION 'YEAR_DAY_OF_YEAR'
DESCRIPTION 'YEAR_DAY_OF_YEAR' ,
YEAR_MM AS YEAR_MM
CAPTION 'YEAR_MM'
DESCRIPTION 'YEAR_MM'
)
LEVEL DATE_DATE
CAPTION 'Date Date'
DESCRIPTION 'Date Date'
KEY ( "DATE_DATE")
MEMBER NAME TO_CHAR("DATE_DATE")
MEMBER CAPTION TO_CHAR("DATE_DATE")
MEMBER DESCRIPTION TO_CHAR("DATE_DATE")
ORDER BY MIN DATE_DATE ASC NULLS FIRST
DETERMINES ( "DAY_NAME" , "DAY_NUMBER" , "YEAR_DAY_OF_YEAR")
LEVEL YEAR
CAPTION 'Year'
DESCRIPTION 'Year'
KEY ( "YEAR")
ALTERNATE KEY YEAR
MEMBER NAME YEAR
MEMBER CAPTION YEAR
MEMBER DESCRIPTION YEAR
ORDER BY MIN YEAR ASC NULLS FIRST
LEVEL YEAR_MM
CAPTION 'Year Mm'
DESCRIPTION 'Year Mm'
KEY ( "YEAR_MM")
MEMBER NAME YEAR_MM
MEMBER CAPTION YEAR_MM
MEMBER DESCRIPTION YEAR_MM
ORDER BY MIN YEAR_MM ASC NULLS FIRST
ALL
MEMBER NAME 'ALL'
MEMBER CAPTION 'ALL'
MEMBER DESCRIPTION 'ALL';
CREATE HIERARCHY DATES_HIERARCHY 
CAPTION 'DATES_HIERARCHY'
DESCRIPTION 'DATES_HIERARCHY'
USING DATES_DIMENSION
( "DATE_DATE" CHILD OF "YEAR_MM" CHILD OF "YEAR" );
CREATE HIERARCHY DEVICE_TYPE_HIERARCHY 
CAPTION 'DEVICE_TYPE_HIERARCHY'
DESCRIPTION 'DEVICE_TYPE_HIERARCHY'
USING DEVICE_DIMENSION
( "DEVICE_LEVEL" CHILD OF "DEVICETYPE" );
CREATE HIERARCHY ROOM_HIERARCHY 
CAPTION 'ROOM_HIERARCHY'
DESCRIPTION 'ROOM_HIERARCHY'
USING DEVICE_DIMENSION
( "DEVICE_LEVEL" CHILD OF "ROOM_LEVEL" );
CREATE ANALYTIC VIEW IOT_DEVICES_AV 
CAPTION 'IOT_DEVICE_READINGS'
DESCRIPTION 'IOT_DEVICE_READINGS'
USING IOT_DEVICES
DIMENSION BY (
DEVICE_DIMENSION AS DEVICE_DIMENSION KEY ( "DEVICE") REFERENCES ( "DEVICE")
HIERARCHIES
(
DEVICE_TYPE_HIERARCHY AS DEVICE_TYPE_HIERARCHY DEFAULT,
ROOM_HIERARCHY AS ROOM_HIERARCHY
),
DATES_DIMENSION AS DATES_DIMENSION KEY ( "DATE_TIME") REFERENCES ( "DATE_DATE")
HIERARCHIES
(
DATES_HIERARCHY AS DATES_HIERARCHY DEFAULT
)
)
MEASURES (
VALUE FACT VALUE CAPTION 'VALUE'
DESCRIPTION 'VALUE'
)
DEFAULT MEASURE VALUE
DEFAULT AGGREGATE BY AVG;

But the bit that’s to my mind interesting in the Analytic View creation dialog is the Cache Measure Groups option in the left-hand menu of the SQL Developer screen.

This, and the vector transformation feature introduced with Oracle Database 12.1, are the key to how Analytic Workspaces will return aggregated query results as fast, if not faster, than using a dedicated multidimensional OLAP cache layer. Optimisations in the SQL parser for analytic views that on one hand enable simplified SQL queries without the need for GROUP BY or join clauses, like this:

SELECT time_hier.member_name as TIME,
sales,
sales_prior_period
FROM
sales_av HIERARCHIES(time_hier)
WHERE time_hier.level_name IN ('YEAR','QUARTER')
ORDER BY time_hier.hier_order;

also use these cache measure group settings to hard-wire the Analytic View into any pre-created materialized views containing aggregations of your measures, side-stepping the usual query rewrite mechanism used by the database query optimizer to ensure, along with vector transformations, that users have split-second response times to their queries whether at detail-level or aggregated across one or more of the analytic view hierarchies. You can try this feature out for yourself using Oracle’s Live SQL demo environment here, and it’s important to understand what this feature and the integration of analytic views with the in-memory database option with its vector transformation feature really means — OLAP aggregation rethough for today’s in-memory database servers.

So this, in my opinion, is where Oracle are headed in terms of meeting the needs of database customers who want fast, easy-to-maintain OLAP query functionality that provides the response time of a dedicated OLAP server but does it through leveraging the in-memory capabilities of Oracle Database 12c, the people and ideas from the OLAP Option product and the huge amounts of memory and compute capacity you get in modern servers. All very interesting, and interesting as well to see where the high-end traditional relational database vendors are taking OLAP analysis now old-school OLAP servers have had their day but dimensional analysis and dimensional business models are still just as relevant.