Exploring the new SSB Schema with SQL Developer Web

The Autonomous Data Warehouse folks have built a nice sample schema for testing analytic views and doing ‘cool Star Schema stuff.’ It’s called, the Star Schema Benchmark (SSB).

If you’re looking for a relational diagram or data dictionary, I can show you how to get one with Oracle SQL Developer Web. Step one, you just need an Autonomous Data Warehouse Cloud Service.

Once you’re logged in, just proceed to the Data Modeler component.

SSB Diagram with SQL Developer Web

You’ll see four tables once you set your schema selector to ‘SSB.’ Next, you want to drag those tables into the diagram space. The Modeler will draw something like this, once you’ve set the Diagram mode to ‘Star.’

If you’d like to get the DDL for these objects, just ask for it…

Let’s go query this stuff!

So switching over to the SQL Worksheet, I’m going to drag and drop the tables over, and ask to JOIN the tables for me.

That’s way too many columns, so I’ll shorten it up a bit, and run the query. Same button and keyboard shortcut as you’ll find in the desktop copy of SQL Developer 🙂

Queries and plans

If you follow the docs, the schema comes pre-baked with some cool queries. If I take this one and ask for an Explain Plan, SQL Developer will happily show it.

SELECT
    DWDATE_HIER.MEMBER_NAME     AS YEAR,
    PART_HIER.MEMBER_NAME       AS PART,
    CUSTOMER_HIER.C_REGION,
    CUSTOMER_HIER.MEMBER_NAME   AS CUSTOMER,
    LO_QUANTITY,
    LO_REVENUE
FROM
    SSB.SSB_AV HIERARCHIES (
        DWDATE_HIER,
        PART_HIER,
        CUSTOMER_HIER
    )
WHERE
    DWDATE_HIER.D_YEAR = '1998'
    AND DWDATE_HIER.LEVEL_NAME = 'MONTH'
    AND PART_HIER.LEVEL_NAME = 'MANUFACTURER'
    AND CUSTOMER_HIER.C_REGION = 'AMERICA'
    AND CUSTOMER_HIER.LEVEL_NAME = 'NATION'
ORDER BY
    DWDATE_HIER.HIER_ORDER,
    PART_HIER.HIER_ORDER,
    CUSTOMER_HIER.HIER_ORDER;

Quick AND Easy

You can spin up your own Autonomous Database in the Oracle Cloud (takes on average about 3 minutes) and then when you’re done, click on over to SQL Developer Web and start having a go at your data – no need to download anything, setup any wallets/drivers/firewalls/etc.

Of course I CAN and probably WILL do all those things, I just now have a choice to stay in my browser if I want to.