The Denorm analysis is provided to denormalize or “flatten out” (sometimes referred to as pivoting) a table so it can be used as an analytic data set. This is done by removing part of a multi-part index and replicating remaining columns based upon the unique values of the removed index column.
Many analytical techniques from the statistical and artificial intelligence communities require a denormalized table, or data set, as input. The Denorm function is provided to help analytical modelers and database administrators save considerable time and effort when a denormalized table needs to be constructed from data which exists in relational form in the data warehouse. The aggregations typically 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 typically refer to the columns as variables.
Given a table name, the names of index columns to remove, the names of index columns to retain, the names of remaining columns to denormalize, the values of the removed index columns to denormalize, and finally the names of any already denormalized columns to retain, the Denorm analysis creates a new denormalized table. All columns other than the retain key and denormalize columns are dropped in the new table, unless they are specified as columns to retain. In this case, they should already be denormalized; that is, they have the same value for each of the removed key columns.
New columns names are concatenated from the prefix associated by the user with the Values to Denormalize (which occur in the Index Remove Columns), and the alias or name of the Denormalize Column.
An option is provided which allows you to specify an aggregation method in the case where new columns have multiple values to choose from. A user specified aggregation method, specifically MIN, MAX, AVG, SUM or COUNT, should only be 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 by).
Finally an option to specify zero instead of NULL, the default, for the value of those denormalized columns for which the index is not defined, is also provided.
The Denorm analysis is parameterized by specifying the table and column(s) to analyze, options unique to the Denorm analysis, as well as specifying the desired results and SQL or Expert Options.