Channels ▼

Ken North

Dr. Dobb's Bloggers

Measuring Performance for Analytics, Decision Support, and the Data Warehouse

July 04, 2012

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.

The performance metric ([email protected]) 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/[email protected]

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).

Related Reading

More Insights

Currently we allow the following HTML tags in comments:

Single tags

These tags can be used alone and don't need an ending tag.

<br> Defines a single line break

<hr> Defines a horizontal line

Matching tags

These require an ending tag - e.g. <i>italic text</i>

<a> Defines an anchor

<b> Defines bold text

<big> Defines big text

<blockquote> Defines a long quotation

<caption> Defines a table caption

<cite> Defines a citation

<code> Defines computer code text

<em> Defines emphasized text

<fieldset> Defines a border around elements in a form

<h1> This is heading 1

<h2> This is heading 2

<h3> This is heading 3

<h4> This is heading 4

<h5> This is heading 5

<h6> This is heading 6

<i> Defines italic text

<p> Defines a paragraph

<pre> Defines preformatted text

<q> Defines a short quotation

<samp> Defines sample computer code text

<small> Defines small text

<span> Defines a section in a document

<s> Defines strikethrough text

<strike> Defines strikethrough text

<strong> Defines strong text

<sub> Defines subscripted text

<sup> Defines superscripted text

<u> Defines underlined text

Dr. Dobb's encourages readers to engage in spirited, healthy debate, including taking us to task. However, Dr. Dobb's moderates all comments posted to our site, and reserves the right to modify or remove any content that it determines to be derogatory, offensive, inflammatory, vulgar, irrelevant/off-topic, racist or obvious marketing or spam. Dr. Dobb's further reserves the right to disable the profile of any commenter participating in said activities.

Disqus Tips To upload an avatar photo, first complete your Disqus profile. | View the list of supported HTML tags you can use to style comments. | Please read our commenting policy.