16.10 - Derived Data Attributes - Teradata Database

Teradata Database Design

Product
Teradata Database
Release Number
16.10
Release Date
June 2017
Content Type
User Guide
Publication ID
B035-1094-161K
Language
English (United States)

Derived attributes are attributes that are not atomic. Their data can be derived from atomic attributes in the database. Because they are not atomic, they violate the rules of normalization.

Derived attributes fall into these basic types:

  • Summary (aggregate) data
  • Data that can be directly derived from other attributes

Approaches to Handling Standalone Derived Data

There are occasions when you might want to denormalize standalone calculations for performance reasons. Base the decision to denormalize on the following demographic information, all of which is derived through the ATM process.

  • Number of tables and rows involved
  • Access frequency
  • Data volatility
  • Data change schedule

Guidelines for Handling Standalone Derived Data

As a general rule, using an aggregate join index or a global temporary table is preferable to denormalizing the physical implementation of the fully normalized logical model.

The following table provides guidelines on handling standalone derived data attributes by denormalization. The decisions are all based on the demographics of the particular data. When more than one recommended approach is given, and one is preferable to the other, the entries are ranked in order of preference.

Access Frequency Change Rating Update Frequency Recommended Approach
High High Dynamic
  1. Use an aggregate join index or global temporary table.
  2. Denormalize the physical implementation of the model.
High High Scheduled Use an aggregate join index or global temporary table.
High Low Dynamic Use an aggregate join index or global temporary table.
High Low Scheduled
  • Use an aggregate join index or global temporary table.
  • Produce a batch report that calculates the aggregates whenever it is run.
Low Unknown Unknown Calculate the information on demand rather than storing it in the database.

Any time the number of tables and rows involved is small, calculate the derived information on demand.

Reasons Not to Denormalize Using Derived Data

The following items deal with the issues of derived data without denormalizing user base data tables:

  • Aggregate join index (see Aggregate Join Indexes)
  • Global temporary table with derived column definitions
  • View with derived column definitions