The Star Schema Benchmark (SSB) is a data model/schema with the possibility to use generated data, that can be used to Benchmark the Performance of any Data Warehouse. The following article describes the steps in order to create the Schema within an Oracle Database. Only the following file small zip file needs to be downloaded, since the data will be generated on the machine itself.
Download dbgen File
The File can be downloaded here: http://www.cs.umb.edu/~poneil/dbgen.zip

The zip file was downloaded into a Virtual Box Image (DeveloperDaysVM2017-02-10_19), but this procedure should work with any other Oracle Database and Linux OS.
Unzip dbgen File
After downloading unzip the dbgen.zip file with the below command:
[oracle@vbgeneric ssb]$ unzip dbgen.zip

This creates the folder:

Compilation of file
[oracle@vbgeneric ssb]$ cd dbgen/
[oracle@vbgeneric dbgen]$ make

[oracle@vbgeneric dbgen]$ ls –ltr

Generation of Database files
[oracle@vbgeneric dbgen]$ ./dbgen -s 205 -T c

This will generate a Customer Table File

After executing the remaining commands to create the Database Tables Files:
[oracle@vbgeneric dbgen]$ ./dbgen -s 7 -T p
[oracle@vbgeneric dbgen]$ ./dbgen -s 1000 -T s
[oracle@vbgeneric dbgen]$ ./dbgen -s 1 -T d
[oracle@vbgeneric dbgen]$ ./dbgen -s 16 -T l

The following files are created by the dbgen executable:

Declare schema and external Tables
Run the following script in SQL Developer:
CREATE USER ssb IDENTIFIED BY ssb;
GRANT CREATE SESSION,
CREATE TABLE,
CREATE ANY DIRECTORY,
UNLIMITED TABLESPACE
TO ssb;
CREATE OR REPLACE DIRECTORY ssb_dir AS '/home/oracle/Downloads/ssb/dbgen';
GRANT READ, WRITE ON DIRECTORY ssb_dir TO ssb;
--external Line Order Table
CREATE TABLE ssb.ext_lineorder
(
lo_orderkey INTEGER,
lo_linenumber NUMBER(1, 0),
lo_custkey INTEGER,
lo_partkey INTEGER,
lo_suppkey INTEGER,
lo_orderdate INTEGER,
lo_orderpriority CHAR(15),
lo_shippriority CHAR(1),
lo_quantity NUMBER(2, 0),
lo_extendedprice NUMBER,
lo_ordtotalprice NUMBER,
lo_discount NUMBER(2, 0),
lo_revenue NUMBER,
lo_supplycost NUMBER,
--lo_ordsupplycost NUMBER, -- this is mentioned in 2.2 Notes(c) but isn't in the layout or sample queries, so not needed?
lo_tax NUMBER(1, 0),
lo_commitdate INTEGER,
lo_shipmode CHAR(10)
)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY ssb_dir
ACCESS PARAMETERS (
FIELDS
TERMINATED BY '|'
MISSING FIELD VALUES ARE NULL
)
LOCATION('lineorder.tbl*'))
PARALLEL 4;
--Target Lineorder Table, the external Table will be loaded into the below Table
CREATE TABLE ssb.lineorder
(
lo_orderkey INTEGER NOT NULL,
lo_linenumber NUMBER(1, 0) NOT NULL,
lo_custkey INTEGER NOT NULL,
lo_partkey INTEGER NOT NULL,
lo_suppkey INTEGER NOT NULL,
lo_orderdate NUMBER(8,0) NOT NULL,
lo_orderpriority CHAR(15) NOT NULL,
lo_shippriority CHAR(1) NOT NULL,
lo_quantity NUMBER(2, 0) NOT NULL,
lo_extendedprice NUMBER NOT NULL,
lo_ordtotalprice NUMBER NOT NULL,
lo_discount NUMBER(2, 0) NOT NULL,
lo_revenue NUMBER NOT NULL,
lo_supplycost NUMBER NOT NULL,
--lo_ordsupplycost NUMBER not null, -- this is mentioned in 2.2 Notes(c) but isn't in the layout or sample queries, so not needed?
lo_tax NUMBER(1, 0) NOT NULL,
lo_commitdate NUMBER(8,0) NOT NULL,
lo_shipmode CHAR(10) NOT NULL
);
CREATE TABLE ssb.ext_part
(
p_partkey INTEGER,
p_name VARCHAR2(22),
p_mfgr CHAR(6),
p_category CHAR(7),
p_brand1 CHAR(9),
p_color VARCHAR2(11),
p_type VARCHAR2(25),
p_size NUMBER(2, 0),
p_container CHAR(10)
)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY ssb_dir
ACCESS PARAMETERS (
FIELDS
TERMINATED BY '|'
MISSING FIELD VALUES ARE NULL
)
LOCATION('part.tbl'));
CREATE TABLE ssb.part
(
p_partkey INTEGER NOT NULL,
p_name VARCHAR2(22) NOT NULL,
p_mfgr CHAR(6) NOT NULL,
p_category CHAR(7) NOT NULL,
p_brand1 CHAR(9) NOT NULL,
p_color VARCHAR2(11) NOT NULL,
p_type VARCHAR2(25) NOT NULL,
p_size NUMBER(2, 0) NOT NULL,
p_container CHAR(10) NOT NULL
);
CREATE TABLE ssb.ext_supplier
(
s_suppkey INTEGER,
s_name CHAR(25),
s_address VARCHAR2(25),
s_city CHAR(10),
s_nation CHAR(15),
s_region CHAR(12),
s_phone CHAR(15)
)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY ssb_dir
ACCESS PARAMETERS (
FIELDS
TERMINATED BY '|'
MISSING FIELD VALUES ARE NULL
)
LOCATION('supplier.tbl'));
CREATE TABLE ssb.supplier
(
s_suppkey INTEGER NOT NULL,
s_name CHAR(25) NOT NULL,
s_address VARCHAR2(25) NOT NULL,
s_city CHAR(10) NOT NULL,
s_nation CHAR(15) NOT NULL,
s_region CHAR(12) NOT NULL,
s_phone CHAR(15) NOT NULL
);
CREATE TABLE ssb.ext_customer
(
c_custkey INTEGER,
c_name VARCHAR2(25),
c_address VARCHAR2(25),
c_city CHAR(10),
c_nation CHAR(15),
c_region CHAR(12),
c_phone CHAR(15),
c_mktsegment CHAR(10)
)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY ssb_dir
ACCESS PARAMETERS (
FIELDS
TERMINATED BY '|'
MISSING FIELD VALUES ARE NULL
)
LOCATION('customer.tbl'));
CREATE TABLE ssb.customer
(
c_custkey INTEGER NOT NULL,
c_name VARCHAR2(25) NOT NULL,
c_address VARCHAR2(25) NOT NULL,
c_city CHAR(10) NOT NULL,
c_nation CHAR(15) NOT NULL,
c_region CHAR(12) NOT NULL,
c_phone CHAR(15) NOT NULL,
c_mktsegment CHAR(10) NOT NULL
);
CREATE TABLE ssb.ext_date_dim
(
d_datekey NUMBER(8,0),
d_date CHAR(18),
d_dayofweek CHAR(9), -- defined in Section 2.6 as Size 8, but Wednesday is 9 letters
d_month CHAR(9),
d_year NUMBER(4, 0),
d_yearmonthnum NUMBER(6, 0),
d_yearmonth CHAR(7),
d_daynuminweek NUMBER(1, 0),
d_daynuminmonth NUMBER(2, 0),
d_daynuminyear NUMBER(3, 0),
d_monthnuminyear NUMBER(2, 0),
d_weeknuminyear NUMBER(2, 0),
d_sellingseason CHAR(12),
d_lastdayinweekfl NUMBER(1, 0),
d_lastdayinmonthfl NUMBER(1, 0),
d_holidayfl NUMBER(1, 0),
d_weekdayfl NUMBER(1, 0)
)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY ssb_dir
ACCESS PARAMETERS (
FIELDS
TERMINATED BY '|'
MISSING FIELD VALUES ARE NULL
)
LOCATION('date.tbl'));
CREATE TABLE ssb.date_dim
(
d_datekey NUMBER(8,0) NOT NULL,
d_date CHAR(18) NOT NULL,
d_dayofweek CHAR(9) NOT NULL, -- defined in Section 2.6 as Size 8, but Wednesday is 9 letters
d_month CHAR(9) NOT NULL,
d_year NUMBER(4, 0) NOT NULL,
d_yearmonthnum NUMBER(6, 0) NOT NULL,
d_yearmonth CHAR(7) NOT NULL,
d_daynuminweek NUMBER(1, 0) NOT NULL,
d_daynuminmonth NUMBER(2, 0) NOT NULL,
d_daynuminyear NUMBER(3, 0) NOT NULL,
d_monthnuminyear NUMBER(2, 0) NOT NULL,
d_weeknuminyear NUMBER(2, 0) NOT NULL,
d_sellingseason CHAR(12) NOT NULL,
d_lastdayinweekfl NUMBER(1, 0) NOT NULL,
d_lastdayinmonthfl NUMBER(1, 0) NOT NULL,
d_holidayfl NUMBER(1, 0) NOT NULL,
d_weekdayfl NUMBER(1, 0) NOT NULL
);

Check that the external files can be accessed by the Database. For testing execute the following query:
select count (*) from ssb.ext_date_dim;

Copy from External Tables to Database Tables
From the external Table the data can be loaded to the Database tables using the following commands:
ALTER TABLE ssb.lineorder PARALLEL 4;
ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+ APPEND */ INTO ssb.date_dim SELECT * FROM ssb.ext_date_dim;
commit;
DROP TABLE ssb.ext_date_dim;
select count (*) from ssb.date_dim;
INSERT /*+ APPEND */ INTO ssb.part SELECT * FROM ssb.ext_part;
commit;
DROP TABLE ssb.ext_part;
select count (*) from ssb.part;
INSERT /*+ APPEND */ INTO ssb.supplier SELECT * FROM ssb.ext_supplier;
commit;
DROP TABLE ssb.ext_supplier;
select count (*) from ssb.supplier;
INSERT /*+ APPEND */ INTO ssb.customer SELECT * FROM ssb.ext_customer;
commit;
DROP TABLE ssb.ext_customer;
select count (*) from ssb.customer;
INSERT /*+ APPEND */ INTO ssb.lineorder SELECT * FROM ssb.ext_lineorder;
commit;
DROP TABLE ssb.ext_lineorder;
select count (*) from ssb.lineorder;
Create Indexes and Constraints on Database Tables
Execute the following commands in SQL Developer:
ALTER TABLE ssb.lineorder
ADD CONSTRAINT pk_lineorder PRIMARY KEY(lo_orderkey, lo_linenumber);
ALTER TABLE ssb.part
ADD CONSTRAINT pk_part PRIMARY KEY(p_partkey);
ALTER TABLE ssb.supplier
ADD CONSTRAINT pk_supplier PRIMARY KEY(s_suppkey);
ALTER TABLE ssb.customer
ADD CONSTRAINT pk_customer PRIMARY KEY(c_custkey);
ALTER TABLE ssb.date_dim
ADD CONSTRAINT pk_date_dim PRIMARY KEY(d_datekey);
ALTER TABLE ssb.lineorder
ADD CONSTRAINT fk_lineitem_customer FOREIGN KEY(lo_custkey) REFERENCES ssb.customer(c_custkey);
ALTER TABLE ssb.lineorder
ADD CONSTRAINT fk_lineitem_part FOREIGN KEY(lo_partkey) REFERENCES ssb.part(p_partkey);
ALTER TABLE ssb.lineorder
ADD CONSTRAINT fk_lineitem_supplier FOREIGN KEY(lo_suppkey) REFERENCES ssb.supplier(s_suppkey);
ALTER TABLE ssb.lineorder
ADD CONSTRAINT fk_lineitem_orderdate FOREIGN KEY(lo_orderdate) REFERENCES ssb.date_dim(d_datekey);
ALTER TABLE ssb.lineorder
ADD CONSTRAINT fk_lineitem_commitdate FOREIGN KEY(lo_commitdate) REFERENCES ssb.date_dim(d_datekey);
After running all commands, the Schema is created and the tables can be queried:

The following also shows the checked Table Size within the Oracle Database:

This Article is based on:
An alternative approach of loading the SSB data is described here: