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:
create user LOOKER identified by <<new_looker_account_password>>;
alter user LOOKER
default tablespace DATA
temporary tablespace TEMP
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
select SQL.SQL_ID, SQL.SQL_TEXT
from V$SQL sql ,v$session sess
where SESS.SQL_ADDRESS = SQL.ADDRESS
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
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
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)
CURSOR_NAME pls_integer default dbms_sql.open_cursor;
select COUNT(*) into IGNORE
where USERNAME = USER
and SID = P_SID
and SERIAL# = P_SERIAL#;
if (IGNORE = 1)
'alter system kill session '''
|| P_SID || ',' || P_SERIAL# || '''',
IGNORE := dbms_sql.execute(CURSOR_NAME);
'You do not own session ''' ||
P_SID || ',' || P_SERIAL# ||
create or replace synonym LOOKER.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.