Skip to end of metadata
Go to start of metadata

TPC-DS Benchmark

The TPC Benchmark DS (TPC-DS) is a decision support benchmark that models several generally applicable aspects of a decision support system, including queries and data maintenance. Although the underlying business model of TPC-DS is a retail product supplier, the database schema, data population, queries, data maintenance model and implementation rules have been designed to be broadly representative of modern decision support systems. (http://www.tpc.org/tpcds/)

This benchmark illustrates decision support systems that:

  • Examine large volumes of data
  • Give answers to real-world business questions
  • Execute queries of various operational requirements and complexities (e.g., ad-hoc, reporting, iterative OLAP, data mining)
  • Are characterized by high CPU and IO load
  • Are periodically synchronized with source OLTP databases through database maintenance functions

Official scale factors of datasets are 100GB, 300GB, 1TB, 3TB, 10TB, 30TB and 100TB.

DDL for TPC-DS datasets

The TPC-DS schema models the sales and sales returns process for an organization that employs three primary sales channels: store, catalogs, and the Internet. The schema includes 7 fact tables and 17 dimension tables.

  • Fact tables: store_sales, store_returns, catalog_sales, catalog_returns, web_sales, web_returns, inventory
  • Dimension tables: store, call_center, catalog_page, web_site, web_page, warehouse, customer, customer_address, customer_demographics, date_dim, household_demographics, item, income_band, promotion, reason, ship_mode, time_dim


The following DDL statements are for them in alphabetical order.

DDL

TPC-DS Queries

TPC-DS provides 99 queries. Since Tajo does not support some features like WITH clause, ROLLUP operation, and STDDEV_SAMP function, I omit queries containing them. The following are 26 queries tested on Tajo.

 

Q3
Q7
Q15
Q19
Q21 (modified: '+ 30 days' --> '+ 30')
Q25
Q26
Q29
Q34
Q37 (modified: '+ 60 days' --> '+ 60')
Q40 (modified: '30 days' --> '30')
Q42
Q43
Q46
Q50
Q52
Q55
Q62
Q65
Q68
Q76
Q79
Q84
Q91
Q93
Q99

Reference

 

 

  • No labels