Variable Creation - Example #2 - Teradata Warehouse Miner

Teradata Warehouse Miner User Guide - Volume 2ADS Generation

Product
Teradata Warehouse Miner
Release Number
5.4.5
Published
February 2018
Language
English (United States)
Last Update
2018-05-03
dita:mapPath
qhj1503087326201.ditamap
dita:ditavalPath
ft:empty
dita:id
B035-2301
Product Category
Software

Parameterize a Variable Creation Analysis as follows.

  1. Select TWM_CUSTOMER as the Available Table.
  2. Do the following to create a variable:
    1. Drag and drop the following column from the Columns list to the Variables tab on the right. Note the variable name will default to the column name.
      • TWM_CUSTOMER.cust_id
  3. Do the following to create a variable:
    1. Click on the New button.
    2. Drag and drop the Maximum (Aggregation) SQL Element on to the empty argument in the variable.
  4. Drag and drop a Number (Literal) on to the empty argument in the Maximum, and rename the variable acct to "acct".
  5. Select TWM_ACCOUNTS as the Available Table.
  6. Do the following to create a variable:
    1. Drag and drop the following columns. Note the variable name will default to the column name.
      • TWM_ACCOUNTS.ending_balance
  7. Drag and drop an Average (Aggregation) SQL Element over ending_balance, and rename the variable "bal".
  8. Select TWM_TRANSACTIONS as the Available Table.
  9. Do the following to create a variable:
    1. Drag and drop the following columns. Note the variable name will default to the column name.
      • TWM_TRANSACTIONS.tran_id
  10. Drag and drop a Count (Aggregation) SQL Element over tran_id, and rename the variable "nbr_trans".
  11. Select TWM_ACCOUNTS as the Available Table.
  12. Go to the INPUT-variables-Dimensions tab.
  13. Click on the New button three times to create three dimension values and rename them CC, CK, and SV.
  14. Drag TWM_ACCOUNTS.acct_type to each of the three dimension values.
  15. Drag and drop an Equals (Comparison) SQL Element on top of each instance of acct_type in the three dimensions.
  16. Drag and drop a String (Literal) SQL Element into the second argument of Equals.
  17. Do the following to specify a string value for the three dimensions:
    1. Change the Properties of the dimensions CC, CK and SV.
    2. Specify a string value of CC, CK and SV for each dimension, respectively.
    3. Modify the Else condition to ELSE ZERO.
  18. Select TWM_TRANSACTIONS as the Available Table.
  19. Go to the INPUT-variables-Dimensions tab.
  20. Click on the New button four times to create four dimension values.
  21. Drag TWM_ACCOUNTS.tran_date to each of the four dimension values.
  22. Drag and drop a Quarter of Year (Calendar) SQL Element on top of each instance of tran_date in the four dimension values
  23. Drag and drop an Equals (Comparison) SQL Element on top of each Quarter of Year instance in the four dimension values.
  24. Drag and drop a Number (Literal) SQL Element into the second argument of the Equals.
  25. Specify a number of 1-4 for each of the four dimension values by double-clicking on Number, and entering the values. Rename each dimension value Q1-Q4 accordingly.
  26. Go to the INPUT-dimensions tab.
  27. Apply the dimension values to the variables as follows:
    • acct - CK, CC, SV
    • bal - CK, CC, SV
    • nbr_trans - Q1, Q2, Q3, Q4
  28. Go to the INPUT-anchor table tab.
  29. Select TWM_CUSTOMER as the anchor table.
  30. Specify the Join Paths from TWM_CUSTOMER to each of the following by clicking on the Wizard button. The Wizard will initially display the join path selected in the Join Paths window.
    1. From Anchor Table (TWM_CUSTOMER) to TWM_ACCOUNTS

      TWM_CUSTOMER.cust_id --> TWM_ACCOUNTS.cust_id

    2. From Anchor Table (TWM_CUSTOMER) to TWM_TRANSACTIONS

      TWM_CUSTOMER.cust_id --> TWM_ACCOUNTS.cust_id

      TWM_ACCOUNTS.acct_nbr --> TWM_TRANSACTIONS.acct_nbr

  31. Go to the OUTPUT-storage tab.
  32. Select Store the tabular output of this analysis in the database.
  33. Specify that a Table should be created named twm_tutorials_vc2.

For this example, the Variable Creation Analysis generated the following results. Once again, the SQL is not shown:

Only the first 10 rows shown.
Variable Creation - Example #2 Data
cust_id CK_acct SV_acct CC_acct CK_bal SV_bal CC_bal Q1_nbr Q2_nbr Q3_nbr Q4_nbr
1362480 1.00 1.00 1.00 54.77 196.73 4.08 113 17 17 10
1362481 0.00 0.00 0.00 0.00 0.00 0.00 0 0 0 0
1362484 1.00 1.00 1.00 50.46 374.50 108.74 113 27 20 27
1362485 1.00 0.00 1.00 26.34 0.00 463.16 13 18 50 90
1362486 1.00 1.00 0.00 1656.14 58.12 0.00 12 10 13 15
1362487 1.00 1.00 1.00 707.41 2.38 481.00 17 25 25 36
1362488 1.00 0.00 0.00 122.42 0.00 0.00 26 39 27 7
1362489 1.00 1.00 1.00 79.60 52.69 4.49 56 51 44 5
1362492 1.00 0.00 1.00 443.84 0.00 476.92 3 42 64 21
1362496 0.00 1.00 0.00 0.00 251.06 0.00 3 3 3 3