Public SQL Developer Web Dashboards & Charts

Anyone who can reach this URL will be able to see my Strava data, that’s what I mean by ‘Public.’ It’s not locked down with any security privilege or access control list on the webserver.

Click on the pic to access the Dashboard.

Do me a favor…go click on that and have a go at the data now.

What you should see:

6 charts
some of those charts can be toggled to a Grid view to see the raw data
each have a ‘refresh’ feature – but my data is static, so no fun there
mouse over the chart to see the data points, the pie charts are fun to play with especially
open up your browser developer tools if you want to see the REST APIs involved
share buttons for the dashboard and each chart for quick-and-easy embeds

Embeds will look like this for example, in Slack –

What are we looking at?

This is one of our new SQL Developer Web features – Charts. Charts are a visualization of a defined SQL query’s result set. A dashboard is one or more charts grouped together. Opening a Chart or Dashboard will cause the one or more queries to be executed.

If your session isn’t already authenticated and authorized, you’ll be prompted for sign-on credentials – if said charts and dashboards are protected by an ORDS Privilege.

I’ve talked about Charts before, from start to finish here.

Sharing My Data

By making this ‘Dashboard’ open or public, what am I serving up, exactly? Well, you’ll be able to access the REST endpoints that back this page, and it’s associated charts.

My Charts/Dashboards/REST APIs are defined in a schema called ‘CHARTS.’

The dashboard page will be calling REST APIs in the CHARTs schema, and executed as CHARTS user.

Those charts call SQL that query a table in a different schema, which we’ll call ‘DATA’.

Querying data from a different schema than where the REST APIs/Charts are defined.

There is no Strava data in my CHARTS schema.

I’ve granted READ on my STRAVA table in DATA to my CHARTS user.

The READ privilege is effectively the same as SELECT except that it doesn’t not allow SELECT FOR UPDATE (which will create locks and block others – BAD!)

I’ve created a PUBLIC SYNONYM on DATA.CHARTS, to make defining my chart queries more convenient. And because I hate recalling this syntax from scratch, I’m using the SQL worksheet’s PUBLIC SYNONYM create dialog to do the work for me.

These dialogs will show you the DDL you generate, and the output from having executed said DDL.

My CHARTS user was created with CONNECT, RESOURCE, REST ENABLED, and a solo READ GRANT on DATA.STRAVA. That’s it.

I gave CHARTS quota on the tablespace, but that’s totally not necessary for this demo.

Bud Endress made one of these too –

Bud is our Product Manager (hero) for Analytic Views in the database. I’ve talked about these a bit before, but Bud’s went and made a dashboard that takes advantage of querying some AVs he’s created overtop some health data.

All powered by SQL, ORDS, Oracle Database, and Oracle JET.

Bud’s SQL is pretty simple like mine, BUT his magic is the logic back-ended by the Analytic Views he’s created.

The SQL driving a chart looks like this:

SELECT
    d261_geography_hier.member_name   AS geography,
    pct_uninsured_chg_year_ago * 100
FROM
    d261_small_area_health_av HIERARCHIES (
        d261_year_hier,
        d261_geography_hier
    )
WHERE
    d261_year_hier.member_unique_name = '[YEAR].&amp;[2019]'
    AND d261_geography_hier.parent_unique_name = '[ALL].[ALL]'
ORDER BY
    pct_uninsured_chg_year_ago ASC;