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.
Hi Jeff, where do I find this SSB_AV view…could not see in the queries definitions.
Any advise please?
You need an Oracle Autonomous Data Warehouse Cloud Service, and then you should see a ‘SSB’ schema. My post should have a link to the docs in there that discusses the schema and benchmarked queries/workloads.