The Transaction Processing Performance Council (TPC) produces specifications for benchmarks to compare hardware and software configurations used for applications such as online transaction processing (OLTP) and decision support. The benchmarks are database-agnostic and they use SQL that conforms to international standards. The TPC owes its origins to interest in OLTP performance, but the data warehouse, analytics, and decision support have emerged as major areas of interest.

The original decision support benchmark, TPC-H, was developed almost two decades ago. The latest, TPC-DS, is essentially a leap forward that's consistent with current data warehouse practices.

The model for TPC-DS is a data warehouse of a retailer with store sales, catalog sales and web sales. The benchmark application sells products, tracks inventory, and ships from warehouses. It acquires data for the data warehouse from an initial bulk data load and from a continuous integration process known as trickle updates. After organizing and consolidating data in the warehouse, the application supports several types of requests for information (ad hoc, reporting, data mining, and analytics).

TPC-DS includes an extract-transform-load (ETL) process and a de-normalized schema with a large number of tables and columns of non-uniform data. The database design is multiple snow-flake schemas with diverse types and indexes on tables. Besides 19 dimension tables, there are seven fact tables that contain 99% of the data.

Figure 1: TPC-DS Database

The data volume for a TPC-DS benchmark can range from 100 gigabytes (GB) to 100 terabytes (TB), with linear scaling of the fact tables. The ETL process transforms normalized into de-normalized data and maintains history-keeping dimensions.

TPC-DS includes 99 query templates that can generate an extremely large query set. TPC-DS uses SQL:99 with analytical functions, such as CUBE and ROLLUP. It executes reporting queries, ad-hoc queries, and queries using substitution parameters. The templates support generation of queries with large sort and GROUP BY operations, large aggregations, and joins of multiple tables. The templates can also generate IO-intensive and CPU-intensive queries.

Reports of the TPC-DS result include three primary metrics.

• Performance metric QphDS@SF
• Price-Performance metric $/QphDS@SF
• System Availability Date

The performance metric (QphDS@SF) is a measurement of single query execution time, execution time with multiple concurrent users, execution of concurrent updates, and bulk load performance. The Price-Performance metric is based on the price of the system configuration used for the benchmark and described in detail in the full disclosure report.

The secondary metrics that TPC-DS provides include:

• Power Test Elapsed Time and the elapsed time of each query in the Power Test
• Throughput Test 1 and 2 elapsed times
• Load Time
• Energy metrics report the power per performance expressed as Watts/QphDS@SF

The TPC has produced a new decision support benchmark that's an improvement over TPC-H; for example, it uses 26 tables instead of 8. It's more reflective of today's data warehousing environment and scales to measure performance with databases having different data volumes.

For more detail about the new benchmark, consult the TPC-DS 1.1 Specification (April 2012).