For each Variable Creation analysis, appropriate join paths must be set up, if columns from multiple tables are used in creating the variables, applied dimensions or expert clauses. The first step in putting together the Join Paths is to determine what the Anchor Table and Anchor Keys are.
The Anchor Table is a table that contains all of the key values to be included in the final data set. Physically, this can be a table or a view residing in Teradata. The Anchor Keys are determined by the values in the selected Anchor Columns in the Anchor Table. The Anchor Columns must uniquely identify the rows in the Anchor Table, otherwise unpredictable results may occur when joining this table with an anchor table replacement.
Join paths must be specified from the Anchor Table or a previously joined referenced table to every other table referenced in a variable, applied dimension or expert clause (such as a WHERE clause). This information is used to build up a FROM clause for each table or view to be left outer joined with the anchor table in order to include the appropriate anchor key values in the data set (although other join styles may be selected).
The following is an example of a simple join path between two tables. Note that the containing databases can differ as can the joining table names and column names.
db1.tbl1.cust_id = db2.tbl2.cid
In some cases, more than two tables must be joined together to reach a commonly used table. By way of an example, a transaction table may not contain the customer identifier that forms the primary index of the anchor table, but an account number instead, which is tied to customer identifier in a third table which contains both values.
db1.tbl1.cust_id = db2.tbl2.cust_id AND db2.tbl2.acct_id = db3.tbl3.acct_id
Of course, more complex examples can occur in practice and can be accommodated by a join path with sufficient conditions combined together.
The Variable Creation function includes a Join Path wizard to make it easier to build up complex join paths. Also, join paths can be automatically extracted from other analyses in any project loaded into the workspace. This suggests that join paths can be created once in a Variable Creation analysis, and then copied into a project to be used as a template.
In order to provide flexibility, it is possible to join the anchor table or previously joined referenced table differently to different tables, as options are included for Left Outer, Right Outer, Full Outer, Inner and Cross Joins, as well as the option to Omit a join to one or all tables (in which case, join conditions may be specified in the WHERE clause, if desired).