CALCMATRIX Example 1 (Teradata Database) - Teradata Warehouse Miner

Teradata Warehouse Miner User Guide - Volume 2ADS Generation
Product
Teradata Warehouse Miner
Release Number
5.4.4
Published
July 2017
Language
English (United States)
Last Update
2018-05-03
dita:mapPath
fcf1492702067123.ditamap
dita:ditavalPath
ft:empty
dita:id
B035-2301
Product Category
Software

This example is based on Example 1 for the CALCMATRIX Table Operator in the SQL Functions, Operators, Expressions, and Predicates document, B035-1145, and illustrates how an SSCP matrix can be built using nested CALCMATRIX Table Operators and two SQL Text with Arguments SQL elements on the Tables tab.

The following figure shows a Calcmatrix Table Operator.

CALCMATRIX Example 1: Table Operator (calcmatrix)

  1. Enter the components of the Calcmatrix Table Operator from the top down, as follows:
    1. Open Table Operator (calcmatrix).
      1. Set Name/Value Pairs to “PHASE('COMBINE')”.
      2. Set Order/Hash/Partition to “Hash By List = p”.
    2. Open SQL Text ((select 1 as p.twmA…).
      1. Enter “(select 1 as p, twmAlias.* from <P1> twmAlias)”.
    3. Open Table Operator (calcmatrix).
      1. Set Name/Value Pairs to “PHASE('LOCAL')”.
      2. Leave all fields blank.
    4. Open SQL Text (select income, age,…).
      1. Enter “(select income, age, nbr_children from twm_source.twm_customer)”.
  2. After defining Function Table1 on the Tables tab of the Variable Creation analysis, select columns from Function Table1 onto the Variables tab.

    Notice that the Input Source must first be changed to Function Table and Function Tables to Function Table1 on the left-hand side of the input screen.

    CALCMATRIX Example 1: Variables tab with columns selected

  3. After executing the analysis, the SQL generated is shown on the RESULTS—SQL tab.
    CALCMATRIX Example 1: Generated SQL

    The following results are displayed on the RESULTS-Data tab.

    CALCMATRIX Example 1: Table results