Denorm | Vantage Analytics Library - Denorm - Vantage Analytics Library

Vantage Analytics Library User Guide
Deployment
VantageCloud
VantageCore
Edition
VMware
Enterprise
IntelliFlex
Lake
Product
Vantage Analytics Library
Release Number
2.2.0
Published
June 2025
ft:locale
en-US
ft:lastEdition
2025-07-02
dita:mapPath
ibw1595473364329.ditamap
dita:ditavalPath
iup1603985291876.ditaval
dita:id
zyl1473786378775
Product Category
Teradata Vantage
The Denorm analysis function denormalizes (or flattens out) a table to use the table as an analytic data set (sometimes referred to as pivoting or converting rows into columns). This is achieved by removing part of a multi-part index and replicating the remaining columns based on the unique values of the removed index column.
The term index is being used here in place of the term key.

Many analytical techniques from the statistical and artificial intelligence communities require a denormalized table, or data set, as input. The Denorm function is provided when a denormalized table needs to be constructed from data which exists in relational form in the data warehouse. The aggregations used in the construction of a denormalized table (AVG, SUM, MIN, MAX, and COUNT) are provided in the Denorm function as user-selectable options.

Analytical modelers typically refer to the rows of a denormalized table as observations and the columns as variables. The Denorm analysis creates a new denormalized table when given the following information:
  • Table name
  • Names of index columns to remove
  • Names of index columns to retain
  • Names of of remaining columns to denormalize
  • Values of the removed index columns to denormalize
  • Names of any columns already denormalized to retain
All columns other than the retain key and denormalize columns are dropped in the new table, unless specified as columns to retain. In this case, the columns are already denormalized (having the same value for each of the removed key columns).

New column names are concatenated from the prefix associated with the values to denormalize (which occur in the index remove columns) and the alias or name of the denormalize column. You can optionally specify an aggregation method when new columns have multiple values to choose from. User-specified aggregation methods (specifically, MIN, MAX, AVG, SUM, or COUNT) are only used when there are non-unique index values or when a part of the index is being ignored, that is, when part of the index is neither being retained nor removed (denormalized).

You can optionally specify zero instead of NULL (default) for the value of the denormalized columns where the index is not defined. The Denorm analysis is parameterized by specifying the following:
  • Tables and columns to analyze
  • Options unique to the Denorm analysis
  • The desired results and SQL or Expert options

While multiple data types are compatible with the Denorm function, only character, numeric, and date type data are validated within the function.