SSB Schema creation on Oracle DB by using Autonomous and SQL Developer

The Star Schema benchmark (SSB) is designed to be used to measure performance of database products in support of classical data warehouses. It is based on the TPC-H benchmark. The main change from TPC-H to SSB is the merge of the tables LINEITEM and ORDERS into table LINEORDER.

One of the problems that typically arises when setting up the SSB on Oracle is creating the empty database objects that can be populated afterwards. Other options to achieve this are outlined in some of the resources mentioned at the end of the article.

Prerequisites

• Access to an existing ADB service
• Oracle SQL Developer installed locally on the Client workstation
• The current method will use the Oracle Autonomous Database service accessed via Oracle SQL Developer client. How to connect the Oracle SQL Developer client to the autonomous service (either TP or DW) is a prerequisite and not in the scope of this article

Procedure

The method is based on exporting the DDL from the ADB via SQL Developer.

1. In Oracle SQL Developer client, navigate to Tools -> Database Export… :
2. In the next pop-up
   • Fill in the connection info (sufficient rights are required, in this case I used ADMIN)
   • Ensure you check ‘Export DDL’ and the other desired options
   • Uncheck ‘Export Data’.
   • Save as single file and specify the path
   • Click ‘Next‘.
3. In the next screen, choose the object types: Click ‘Next‘.
4. In the next window, choose More — fill-in the schema ‘SSB’ — select type ‘ALL OBJECTS‘ — Lookup:
5. Select all the listed objects on the left pane and move them to the right by clicking the double right arrow: Click Next.
6. On the next screen, click ‘Finish’ and the object create script will be ready soon:
7. As an example I am sharing here the object create script I did using this method.

Note 1: The method above is not SSB specific, it can be used to export the DDL specific to any schema or object/s.

Note 2: I link here the create user script alongside the relevant role (Oracle Clous specific).

Note 3: For on-prem, this script can be used to create the user.

References

• Oracle Setting up the Star Schema Benchmark (SSB) Tips
• Download StarSchemaBenchmark-master.zip 
• Setting up the Star Schema Benchmark (SSB) in Oracle
• Create the Star Schema Benchmark (SSB) within an Oracle Database
• Star Schema Benchmark Revision 3, June 5, 2009
• Steps to load the Star Schema Benchmark (SSB) into an Oracle Database