15.00 - Data Warehousing - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Data Warehousing

Consider the following simple decision support example using the same database to emphasize the contrast in processing requirements: A business analyst needs to find the answer to a specific question about pencil customers so he can target them for a promotion. The natural language query the analyst performs is this: which customers placed the majority of their orders for pencils in the first quarter of this year? The graphic diagrams the actions taken against the database.

The processing flow is outlined in the following process stages:

1 The database engine interrogates the Item table for the item number for pencils, which is 3421.

2 The database engine next scans the Order Item Shipped table looking for all orders associated with item number 3421.

3 The particular row examined in this example shipped 0 pencils, indicating that the order was back ordered.

4 The engine accesses the Order Item Backordered table row having an order and item number matching that examined in stage 3 and finds that 144 pencils were back ordered.

5 The database engine must now locate the customer who placed this order because the whole point of the query was to identify customers who had ordered pencils in the first quarter of the year.

To do this, the engine must access the Order table, where it finds that customer number 2 placed the order in question.

6 The database engine accesses the Customer table to identify the name of the customer associated with customer number 2, which is James.

There are also three key aspects of this query that deserve your attention:

  • The large number of tables that had to be accessed in order to answer it.
  • This complex access path through many tables is a hallmark of decision support analysis. And remember: this is an extremely simple example!

  • The tables were not just touched lightly in response to the query, but required massive searches, and sometimes multiple scans are required.
  • Once the data required to answer the query has been gathered, it must be processed further using aggregation, joins, sorts, conditional requirements, and so on.
  • All this processing is heavily resource-intensive.

    The following table presents a summary contrast of DW and OLTP processing.

     

    OLTP Transaction

    Attribute

    DW Query

    No

    Multiple tables scanned

    Yes

    No

    Large volumes of data examined

    Yes

    No

    Processing-intensive

    Yes

    No

    Response time is a function of database size

    Yes