====== TPC-DS Benchmark How To Guide ======

Commands:

Create Oracle user TPCDS

   SQL> CREATE USER "TPCDS" PROFILE "DEFAULT" IDENTIFIED BY "*******" DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" QUOTA UNLIMITED ON "USERS" ACCOUNT UNLOCK GRANT CREATE TABLE TO "TPCDS" GRANT "CONNECT" TO "TPCDS";

Get DSGen and unpack

  * mkdir /tmp/dsgen
  * cd /tmp/dsgen
  * wget https://www.inf.unibz.it/wiki/_media/public/dsgen_v1.1.0.zip
  * unzip dsgen_v1.1.0.zip

Compile/build binaries

  * cp tools/Makefile.suite tools/Makefile
  * cat tools/Makefile | grep ^OS
  * Result should be: OS	=	LINUX
  * cd tools
  * make

Generate load data

  * mkdir /tmp/tpc
  * ./dsdgen -scale 1 -dir /tmp/tpc

Import tables into Oracle

  * su - oracle
  * cd /tmp/dsgen/tools
  * sqlplus tpcds/* @tpcds.sql
  * sqlplus tpcds/* @tpcds_source.sql

Load data

  * su - oracle
  * mkdir tpc-ds
  * cd tpc-ds


Prepare control files:

  * vi call_center.ctl

{(xssnipper>,1, call_center.ctl slide,
load data
infile '/tmp/tpc/call_center.dat'
into table call_center
fields terminated by '|'
( CC_CALL_CENTER_SK, CC_CALL_CENTER_ID, CC_REC_START_DATE date "YYYY-MM-DD", CC_
REC_END_DATE date "YYYY-MM-DD", CC_CLOSED_DATE_SK, CC_OPEN_DATE_SK, CC_NAME, CC_
CLASS, CC_EMPLOYEES, CC_SQ_FT, CC_HOURS, CC_MANAGER, CC_MKT_ID, CC_MKT_CLASS, CC
_MKT_DESC, CC_MARKET_MANAGER, CC_DIVISION, CC_DIVISION_NAME, CC_COMPANY, CC_COMP
ANY_NAME, CC_STREET_NUMBER, CC_STREET_NAME, CC_STREET_TYPE, CC_SUITE_NUMBER, CC_
CITY, CC_COUNTY, CC_STATE, CC_ZIP, CC_COUNTRY, CC_GMT_OFFSET, CC_TAX_PERCENTAGE 
)
)}

  * vi catalog_page.ctl

{(xssnipper>,1, catalog_page.ctl slide,
load data
infile '/tmp/tpc/catalog_page.dat'
into table catalog_page
fields terminated by '|'
( CP_CATALOG_PAGE_SK, CP_CATALOG_PAGE_ID, CP_START_DATE_SK, CP_END_DATE_SK, CP_D
EPARTMENT, CP_CATALOG_NUMBER, CP_CATALOG_PAGE_NUMBER, CP_DESCRIPTION,
CP_TYPE )
)}

  * vi catalog_returns.ctl

{(xssnipper>,1, catalog_page.ctl slide,
load data
infile '/tmp/tpc/catalog_returns.dat'
into table catalog_returns
fields terminated by '|'
( CR_RETURNED_DATE_SK, CR_RETURNED_TIME_SK, CR_ITEM_SK, CR_REFUNDED_CUSTOMER_SK, CR_REFUNDED_CDEMO_SK, CR_REFUNDED_HDEMO_SK, CR_REFUNDED_ADDR_SK, CR_RETURNING_CUSTOMER_SK, CR_RETURNING_CDEMO_SK, CR_RETURNING_HDEMO_SK, CR_RETURNING_ADDR_SK, CR_CALL_CENTER_SK, CR_CATALOG_PAGE_SK, CR_SHIP_MODE_SK, CR_WAREHOUSE_SK, CR_REASON_SK, CR_ORDER_NUMBER, CR_RETURN_QUANTITY, CR_RETURN_AMOUNT, CR_RETURN_TAX, CR_RETURN_AMT_INC_TAX, CR_FEE, CR_RETURN_SHIP_COST, CR_REFUNDED_CASH, CR_REVERSED_CHARGE, CR_STORE_CREDIT, CR_NET_LOSS )
)}

  * vi catalog_sales.ctl

{(xssnipper>,1, catalog_sales.ctl slide,
load data
infile '/tmp/tpc/catalog_sales.dat'
into table catalog_sales
fields terminated by '|'
( CS_SOLD_DATE_SK, CS_SOLD_TIME_SK, CS_SHIP_DATE_SK, CS_BILL_CUSTOMER_SK, CS_BILL_CDEMO_SK, CS_BILL_HDEMO_SK, CS_BILL_ADDR_SK, CS_SHIP_CUSTOMER_SK, CS_SHIP_CDEMO_SK, CS_SHIP_HDEMO_SK, CS_SHIP_ADDR_SK, CS_CALL_CENTER_SK, CS_CATALOG_PAGE_SK, CS_SHIP_MODE_SK, CS_WAREHOUSE_SK, CS_ITEM_SK, CS_PROMO_SK, CS_ORDER_NUMBER, CS_QUANTITY, CS_WHOLESALE_COST, CS_LIST_PRICE, CS_SALES_PRICE, CS_EXT_DISCOUNT_AMT, CS_EXT_SALES_PRICE, CS_EXT_WHOLESALE_COST, CS_EXT_LIST_PRICE, CS_EXT_TAX, CS_COUPON_AMT, CS_EXT_SHIP_COST, CS_NET_PAID, CS_NET_PAID_INC_TAX, CS_NET_PAID_INC_SHIP, CS_NET_PAID_INC_SHIP_TAX, CS_NET_PROFIT )
)}

  * vi customer_address.ctl

{(xssnipper>,1, customer_address.ctl slide,
load data
infile '/tmp/tpc/customer_address.dat'
into table customer_address
fields terminated by '|'
( CA_ADDRESS_SK,CA_ADDRESS_ID,CA_STREET_NUMBER,CA_STREET_NAME,CA_STREET_TYPE,CA_SUITE_NUMBER,CA_CITY,CA_COUNTY,CA_STATE,CA_ZIP,CA_COUNTRY,CA_GMT_OFFSET,CA_LOCATION_TYPE )
)}


  * vi customer.ctl

{(xssnipper>,1, customer.ctl slide,
load data
infile '/tmp/tpc/customer.dat'
into table customer
fields terminated by '|'
( C_CUSTOMER_SK, C_CUSTOMER_ID, C_CURRENT_CDEMO_SK, C_CURRENT_HDEMO_SK, C_CURRENT_ADDR_SK, C_FIRST_SHIPTO_DATE_SK, C_FIRST_SALES_DATE_SK, C_SALUTATION, C_FIRST_NAME, C_LAST_NAME, C_PREFERRED_CUST_FLAG, C_BIRTH_DAY, C_BIRTH_MONTH, C_BIRTH_YEAR, C_BIRTH_COUNTRY, C_LOGIN, C_EMAIL_ADDRESS, C_LAST_REVIEW_DATE )
)}



  * vi customer_demographics.ctl

{(xssnipper>,1, customer_demographics.ctl slide,
load data
infile '/tmp/tpc/customer_demographics.dat'
into table customer_demographics
fields terminated by '|'
( CD_DEMO_SK,CD_GENDER,CD_MARITAL_STATUS,CD_EDUCATION_STATUS,CD_PURCHASE_ESTIMATE,CD_CREDIT_RATING,CD_DEP_COUNT,CD_DEP_EMPLOYED_COUNT,CD_DEP_COLLEGE_COUNT )
)}



  * vi date_dim.ctl

{(xssnipper>,1, date_dim.ctl slide,
load data
infile '/tmp/tpc/date_dim.dat'
into table date_dim
fields terminated by '|'
( D_DATE_SK,D_DATE_ID,D_DATE date "YYYY-MM-DD",D_MONTH_SEQ,D_WEEK_SEQ,D_QUARTER_SEQ,D_YEAR,D_DOW,D_MOY,D_DOM,D_QOY,D_FY_YEAR,D_FY_QUARTER_SEQ,D_FY_WEEK_SEQ,D_DAY_NAME,D_QUARTER_NAME,D_HOLIDAY,D_WEEKEND,D_FOLLOWING_HOLIDAY,D_FIRST_DOM,D_LAST_DOM,D_SAME_DAY_LY,D_SAME_DAY_LQ,D_CURRENT_DAY,D_CURRENT_WEEK,D_CURRENT_MONTH,D_CURRENT_QUARTER,D_CURRENT_YEAR )
)}



  * vi dbgen_version.ctl

{(xssnipper>,1, dbgen_version.ctl slide,
load data
infile '/tmp/tpc/dbgen_version.dat'
into table dbgen_version
fields terminated by '|'
( DV_VERSION, DV_CREATE_DATE, DV_CREATE_TIME, DV_CMDLINE_ARGS )
)}



  * vi household_demographics.ctl

{(xssnipper>,1, household_demographics.ctl slide,
load data
infile '/tmp/tpc/household_demographics.dat'
into table household_demographics
fields terminated by '|'
( HD_DEMO_SK, HD_INCOME_BAND_SK, HD_BUY_POTENTIAL, HD_DEP_COUNT, HD_VEHICLE_COUNT )
)}



  * vi income_band.ctl

{(xssnipper>,1, income_band.ctl slide,
load data
infile '/tmp/tpc/income_band.dat'
into table income_band
fields terminated by '|'
(IB_INCOME_BAND_SK, IB_LOWER_BOUND, IB_UPPER_BOUND )
)}



  * vi inventory.ctl

{(xssnipper>,1, inventory.ctl slide,
load data
infile '/tmp/tpc/inventory.dat'
into table inventory
fields terminated by '|'
( INV_DATE_SK, INV_ITEM_SK, INV_WAREHOUSE_SK, INV_QUANTITY_ON_HAND )
load data
infile '/tmp/tpc/item.dat'
into table item
fields terminated by '|'
( I_ITEM_SK, I_ITEM_ID, I_REC_START_DATE date "YYYY-MM-DD", I_REC_END_DATE date "YYYY-MM-DD", I_ITEM_DESC, I_CURRENT_PRICE, I_WHOLESALE_COST, I_BRAND_ID, I_BRAND, I_CLASS_ID, I_CLASS, I_CATEGORY_ID, I_CATEGORY, I_MANUFACT_ID, I_MANUFACT, I_SIZE, I_FORMULATION, I_COLOR, I_UNITS, I_CONTAINER, I_MANAGER_ID, I_PRODUCT_NAME )
)}



  * vi item.ctl

{(xssnipper>,1, item.ctl slide,
load data
infile '/tmp/tpc/item.dat'
into table item
fields terminated by '|'
( I_ITEM_SK, I_ITEM_ID, I_REC_START_DATE date "YYYY-MM-DD", I_REC_END_DATE date "YYYY-MM-DD", I_ITEM_DESC, I_CURRENT_PRICE, I_WHOLESALE_COST, I_BRAND_ID, I_BRAND, I_CLASS_ID, I_CLASS, I_CATEGORY_ID, I_CATEGORY, I_MANUFACT_ID, I_MANUFACT, I_SIZE, I_FORMULATION, I_COLOR, I_UNITS, I_CONTAINER, I_MANAGER_ID, I_PRODUCT_NAME )
)}

  * vi promotion.ctl

{(xssnipper>,1, promotion.ctl slide,
load data
infile '/tmp/tpc/promotion.dat'
into table promotion
fields terminated by '|'
( P_PROMO_SK, P_PROMO_ID, P_START_DATE_SK, P_END_DATE_SK, P_ITEM_SK, P_COST, P_RESPONSE_TARGET, P_PROMO_NAME, P_CHANNEL_DMAIL, P_CHANNEL_EMAIL, P_CHANNEL_CATALOG, P_CHANNEL_TV, P_CHANNEL_RADIO, P_CHANNEL_PRESS, P_CHANNEL_EVENT, P_CHANNEL_DEMO, P_CHANNEL_DETAILS, P_PURPOSE, P_DISCOUNT_ACTIVE )
)}


  * vi reason.ctl

{(xssnipper>,1, reason.ctl slide,
load data
infile '/tmp/tpc/reason.dat'
into table reason
fields terminated by '|'
( R_REASON_SK, R_REASON_ID, R_REASON_DESC )
)}


  * vi ship_mode.ctl

{(xssnipper>,1, ship_mode.ctl slide,
load data
infile '/tmp/tpc/ship_mode.dat'
into table ship_mode
fields terminated by '|'
( SM_SHIP_MODE_SK,SM_SHIP_MODE_ID,SM_TYPE,SM_CODE,SM_CARRIER,SM_CONTRACT )
)}



  * vi store.ctl

{(xssnipper>,1, store.ctl slide,
load data
infile '/tmp/tpc/store.dat'
into table store
fields terminated by '|'
( S_STORE_SK, S_STORE_ID, S_REC_START_DATE date "YYYY-MM-DD", S_REC_END_DATE date "YYYY-MM-DD", S_CLOSED_DATE_SK, S_STORE_NAME, S_NUMBER_EMPLOYEES, S_FLOOR_SPACE, S_HOURS, S_MANAGER, S_MARKET_ID, S_GEOGRAPHY_CLASS, S_MARKET_DESC, S_MARKET_MANAGER, S_DIVISION_ID, S_DIVISION_NAME, S_COMPANY_ID, S_COMPANY_NAME, S_STREET_NUMBER, S_STREET_NAME, S_STREET_TYPE, S_SUITE_NUMBER, S_CITY, S_COUNTY, S_STATE, S_ZIP, S_COUNTRY, S_GMT_OFFSET, S_TAX_PRECENTAGE )
)}

  * vi store_returns.ctl

{(xssnipper>,1, store_returns.ctl slide,
load data
infile '/tmp/tpc/store_returns.dat'
into table store_returns
fields terminated by '|'
( SR_RETURNED_DATE_SK, SR_RETURN_TIME_SK, SR_ITEM_SK, SR_CUSTOMER_SK, SR_CDEMO_SK, SR_HDEMO_SK, SR_ADDR_SK, SR_STORE_SK, SR_REASON_SK, SR_TICKET_NUMBER, SR_RETURN_QUANTITY, SR_RETURN_AMT, SR_RETURN_TAX, SR_RETURN_AMT_INC_TAX, SR_FEE, SR_RETURN_SHIP_COST, SR_REFUNDED_CASH, SR_REVERSED_CHARGE, SR_STORE_CREDIT, SR_NET_LOSS )
)}



  * vi store_sales.ctl

{(xssnipper>,1, store_sales.ctl slide,
load data
infile '/tmp/tpc/store_sales.dat'
into table store_sales
fields terminated by '|'
( SS_SOLD_DATE_SK, SS_SOLD_TIME_SK, SS_ITEM_SK, SS_CUSTOMER_SK, SS_CDEMO_SK, SS_HDEMO_SK, SS_ADDR_SK, SS_STORE_SK, SS_PROMO_SK, SS_TICKET_NUMBER, SS_QUANTITY, SS_WHOLESALE_COST, SS_LIST_PRICE, SS_SALES_PRICE, SS_EXT_DISCOUNT_AMT, SS_EXT_SALES_PRICE, SS_EXT_WHOLESALE_COST, SS_EXT_LIST_PRICE, SS_EXT_TAX, SS_COUPON_AMT, SS_NET_PAID, SS_NET_PAID_INC_TAX, SS_NET_PROFIT )
)}



  * vi time_dim.ctl

{(xssnipper>,1, time_dim.ctl slide,
load data
infile '/tmp/tpc/time_dim.dat'
into table time_dim
fields terminated by '|'
( T_TIME_SK,T_TIME_ID,T_TIME,T_HOUR,T_MINUTE,T_SECOND,T_AM_PM,T_SHIFT,T_SUB_SHIFT,T_MEAL_TIME )
)}



  * vi warehouse.ctl

{(xssnipper>,1, warehouse.ctl slide,
load data
infile '/tmp/tpc/warehouse.dat'
into table warehouse
fields terminated by '|'
( W_WAREHOUSE_SK,W_WAREHOUSE_ID,W_WAREHOUSE_NAME,W_WAREHOUSE_SQ_FT,W_STREET_NUMBER,W_STREET_NAME,W_STREET_TYPE,W_SUITE_NUMBER,W_CITY,W_COUNTY,W_STATE,W_ZIP,W_COUNTRY,W_GMT_OFFSET )
)}


  * vi web_page.ctl

{(xssnipper>,1, web_page.ctl slide,
load data
infile '/tmp/tpc/web_page.dat'
into table web_page
fields terminated by '|'
( WP_WEB_PAGE_SK, WP_WEB_PAGE_ID, WP_REC_START_DATE date "YYYY-MM-DD", WP_REC_END_DATE date "YYYY-MM-DD", WP_CREATION_DATE_SK, WP_ACCESS_DATE_SK, WP_AUTOGEN_FLAG, WP_CUSTOMER_SK, WP_URL, WP_TYPE, WP_CHAR_COUNT, WP_LINK_COUNT, WP_IMAGE_COUNT, WP_MAX_AD_COUNT )
)}



  * vi web_returns.ctl

{(xssnipper>,1, web_returns.ctl slide,
load data
infile '/tmp/tpc/web_returns.dat'
into table web_returns
fields terminated by '|'
( WR_RETURNED_DATE_SK, WR_RETURNED_TIME_SK, WR_ITEM_SK, WR_REFUNDED_CUSTOMER_SK, WR_REFUNDED_CDEMO_SK, WR_REFUNDED_HDEMO_SK, WR_REFUNDED_ADDR_SK, WR_RETURNING_CUSTOMER_SK, WR_RETURNING_CDEMO_SK, WR_RETURNING_HDEMO_SK, WR_RETURNING_ADDR_SK, WR_WEB_PAGE_SK, WR_REASON_SK, WR_ORDER_NUMBER, WR_RETURN_QUANTITY, WR_RETURN_AMT, WR_RETURN_TAX, WR_RETURN_AMT_INC_TAX, WR_FEE, WR_RETURN_SHIP_COST, WR_REFUNDED_CASH, WR_REVERSED_CHARGE, WR_ACCOUNT_CREDIT, WR_NET_LOSS )
)}



  * vi web_sales.ctl

{(xssnipper>,1, web_sales.ctl slide,
load data
infile '/tmp/tpc/web_sales.dat'
into table web_sales
fields terminated by '|'
( WS_SOLD_DATE_SK, WS_SOLD_TIME_SK, WS_SHIP_DATE_SK, WS_ITEM_SK, WS_BILL_CUSTOMER_SK, WS_BILL_CDEMO_SK, WS_BILL_HDEMO_SK, WS_BILL_ADDR_SK, WS_SHIP_CUSTOMER_SK, WS_SHIP_CDEMO_SK, WS_SHIP_HDEMO_SK, WS_SHIP_ADDR_SK, WS_WEB_PAGE_SK, WS_WEB_SITE_SK, WS_SHIP_MODE_SK, WS_WAREHOUSE_SK, WS_PROMO_SK, WS_ORDER_NUMBER, WS_QUANTITY, WS_WHOLESALE_COST, WS_LIST_PRICE, WS_SALES_PRICE, WS_EXT_DISCOUNT_AMT, WS_EXT_SALES_PRICE, WS_EXT_WHOLESALE_COST, WS_EXT_LIST_PRICE, WS_EXT_TAX, WS_COUPON_AMT, WS_EXT_SHIP_COST, WS_NET_PAID, WS_NET_PAID_INC_TAX, WS_NET_PAID_INC_SHIP, WS_NET_PAID_INC_SHIP_TAX, WS_NET_PROFIT )
)}



  * vi web_site.ctl

{(xssnipper>,1, web_site.ctl slide,
load data
infile '/tmp/tpc/web_site.dat'
into table web_site
fields terminated by '|'
( WEB_SITE_SK, WEB_SITE_ID, WEB_REC_START_DATE date "YYYY-MM-DD", WEB_REC_END_DATE date "YYYY-MM-DD", WEB_NAME, WE
B_OPEN_DATE_SK, WEB_CLOSE_DATE_SK, WEB_CLASS, WEB_MANAGER, WEB_MKT_ID, WEB_MKT_CLASS, WEB_MKT_DESC, WEB_MARKET_MAN
AGER, WEB_COMPANY_ID, WEB_COMPANY_NAME, WEB_STREET_NUMBER, WEB_STREET_NAME, WEB_STREET_TYPE, WEB_SUITE_NUMBER, WEB
_CITY, WEB_COUNTY, WEB_STATE, WEB_ZIP, WEB_COUNTRY, WEB_GMT_OFFSET, WEB_TAX_PERCENTAGE )
)}


Import the data using Oracle Loader

  * sqlldr tpcds/* control=call_center.ctl
  * sqlldr tpcds/* control=catalog_page.ctl
  * sqlldr tpcds/* control=catalog_returns.ctl
  * sqlldr tpcds/* control=catalog_sales.ctl
  * sqlldr tpcds/* control=customer_address.ctl
  * sqlldr tpcds/* control=customer.ctl
  * sqlldr tpcds/* control=customer_demographics.ctl
  * sqlldr tpcds/* control=date_dim.ctl
  * sqlldr tpcds/* control=dbgen_version.ctl
  * sqlldr tpcds/* control=household_demographics.ctl
  * sqlldr tpcds/* control=income_band.ctl
  * sqlldr tpcds/* control=inventory.ctl
  * sqlldr tpcds/* control=item.ctl
  * sqlldr tpcds/* control=promotion.ctl
  * sqlldr tpcds/* control=reason.ctl
  * sqlldr tpcds/* control=ship_mode.ctl
  * sqlldr tpcds/* control=store.ctl
  * sqlldr tpcds/* control=store_returns.ctl
  * sqlldr tpcds/* control=store_sales.ctl
  * sqlldr tpcds/* control=time_dim.ctl
  * sqlldr tpcds/* control=warehouse.ctl
  * sqlldr tpcds/* control=web_page.ctl
  * sqlldr tpcds/* control=web_returns.ctl
  * sqlldr tpcds/* control=web_sales.ctl
  * sqlldr tpcds/* control=web_site.ctl
 

===== Links =====


[[http://www.tpc.org/tpcds/default.asp]]\\
[[http://agni.csa.iisc.ernet.in/CASL/tpcds]]\\
[[http://www.orafaq.com/wiki/SQL*Loader_FAQ]]