TPC-DS - Schema

> (Data|State|Operand) Management and Processing > (Data Type|Data Structure) > (Relation|Table) - Tabular data > Relation - Database Benchmark > TPC - DS

1 - About

The TPC-DS schema is a snowflake schema. It consists of multiple dimension and fact tables. Each dimension has a single column surrogate key. The fact tables join with dimensions using each dimension table's surrogate key.

Advertising

3 - Table List

3.1 - Fact

The schema includes seven fact tables:

  • A pair of fact tables focused on the product sales and returns for each of the three channels. ie:
    • Store Sales and returns
    • Catalog Sales and returns
    • Web Sales and returns
  • A single fact table that models inventory for the catalog and internet sales channels.
    • Inventory
Type Name Abbreviation
Fact Store Sales SS
Fact Store Returns SR
Fact Catalog Sales CS
Fact Catalog Returns CR
Fact Web Sales WS
Fact Web Returns WR
Fact Inventory INV
Advertising

3.2 - Dimension

In addition, the schema includes 17 dimension tables that are associated with all sales channels. The following clauses specify the logical design of each table:

  • The name of the table, along with its abbreviation (listed parenthetically)
  • A logical diagram of each fact table and its related dimension tables
  • The high-level definitions for each table and its relationship to other tables
  • The scaling and cardinality information for each column
Type Name Abbreviation
Dimension Store S
Dimension Call Center CC
Dimension Catalog Page CP
Dimension Web Site WEB
Dimension Web Page WP
Dimension Warehouse W
Dimension Customer C
Dimension Customer Address CA
Dimension Customer Demographics CD
Dimension Date Dim D
Dimension Household Demographics D
Dimension Item I
Dimension Income Band IB
Dimension Promotion P
Dimension Reason R
Dimension Ship Mode SM
Dimension Time Dim T
Dimension dsdgen_version NA
Advertising

4 - Snowflakes

4.1 - Store

4.2 - Catalog

4.3 - Web