- On the Variable Creation dialog, click on INPUT.
Click on anchor table.
Variable Creation > Input > Anchor Table
On this screen, select:
Anchor Table — Pull-down with a list of all tables and views used to create variables, dimensions and/or specified in an expert clause. Select the 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.
Note that if no tables or views are referenced in the variables, applied dimensions or expert clauses of this analysis, the pull-down list for this field, and in fact each field on this form, will be empty, and this form can be ignored.
- Available Anchor Columns — All columns within the table selected as the Anchor Table.
Selected Anchor Columns — The columns within the Anchor Table that uniquely identify its rows may optionally be specified here. By default, the primary index columns of the selected Anchor Table are initially selected. For a view, these must be selected manually, although for the standard CALENDAR view in the SYS_CALENDAR database, the calendar_date column is used by default.
The Anchor Columns are used for Anchor Table replacement by the Refresh or Publish analysis. To bypass replacement of the anchor table in this analysis, simply deselect any selected Anchor Columns. The Anchor Columns are also used to generate group by columns if the Group By Style on the Analysis Parameters screen is set to Group by anchor columns and there are aggregations present in the analysis variables or expert clauses (or if any variables contain applied dimensions). Therefore, if aggregations are present and the Anchor Columns have been deselected, a Group By Style other than Group by anchor columns must be selected.
The Anchor Columns are not used to join the Anchor Table to other tables referenced in this analysis, as this duty is performed by the Join Paths, specified on this same screen.
Finally, some guidelines for special situations should be noted. If it is expected that an analysis will be refreshed with a modified Anchor Table using a Refresh analysis, or that it will be published using a Publish analysis, it is best not to refer to any columns in the anchor table other than selected Anchor Columns. Otherwise, if the new Anchor Table is not a subset of the original, the non-anchor columns (that will be selected from the original table) will have null values for rows not in the original anchor table.
It is also important in this situation not to include non-Anchor Columns in the left side of join paths unless the modified anchor table will always contain these columns.
When the Anchor Table is a foreign table accessed via QueryGrid, Anchor Columns should not be selected since anchor table replacement via a Refresh or Publish analysis is not supported in this case.
Skip Anchor Join — Ordinarily, when Anchor Columns are selected from the Anchor Table in a Variable Creation or Build ADS analysis, and when non-Anchor Columns are also selected from the Anchor Table, and the analysis is published or refreshed with anchor table replacement, the generated SQL selects anchor columns from the new anchor table and non-anchor columns from the original anchor table. When the Skip Anchor Join checkbox is checked, the joining of the original and new anchor tables is skipped, simplifying the SQL and allowing replacement of the anchor table in the Model Manager or AppCenter application as a simple table substitution. Note, however, that this only works if the new Anchor Table contains both the Anchor Columns and selected non-Anchor Columns. Also, selection of the Skip Anchor Join option is not necessary if non-Anchor Columns are not selected from the Anchor Table.For example, when changing the Anchor Table from twm_customer to twm_customer_analysis with a Refresh analysis, the code might ordinarily look like the following.
SELECT "_twmVC0"."cust_id" AS "cust_id" ,"_twmVCrefresh"."income" AS "income" FROM "twm_source"."twm_customer_analysis" AS "_twmVC0" LEFT OUTER JOIN "twm_source"."twm_customer" AS "_twmVCrefresh" ON "_twmVC0"."cust_id" = "_twmVCrefresh"."cust_id"while the code resulting from selecting the Skip Anchor Join option might look like the following.
SELECT "_twmVC0"."cust_id" AS "cust_id" ,"_twmVC0"."income" AS "income" FROM "twm_source"."twm_customer_analysis" AS "_twmVC0"
Note that in this example, valid results will only be obtained if twm_customer_analysis contains both cust_id (the Anchor Column) and income (a non-Anchor Column).
Aliases… — When the Aliases… button is selected, the Table Alias Assignment dialog appears. It contains a grid with the names of all of the tables, views and Function Tables referenced in the query built by the analysis, along with a column provided so that the user may type an alias next to any or all of the listed tables, views and Function Tables. An alias may be assigned to a volatile table created by another analysis, but the alias will not be saved when the project is closed.Table Alias Assignment dialog
The Table Alias Assignment dialog also contains a Load... button that causes the projects loaded in the Project Explorer window to be searched for Variable Creation and Build ADS analyses with existing assigned aliases.If multiple assigned aliases are found for a given table, the last one encountered in the search will be assigned.
If this feature is not used, aliases are automatically assigned by appending a number to a built-in alias prefix, though the user may specify the Table Alias Prefix to use on the analysis parameters tab.
Join Paths — A list of all Join Paths, connecting the anchor table to each other table referenced in the analysis (i.e., in a Variable, Dimension or expert clause) is given here. By right-clicking on a Join Path, the join style can be set to Left Outer Join, Inner Join, Right Outer Join, Full Outer Join, Cross Join or Omit.
If a Cross Join is selected, it results in a join path without join steps. Validation is performed including a count of the rows in the table to be joined. If Omit is selected, a warning message is displayed to indicate that a Cross Join will result unless appropriate Where Clause conditions are specified. The result of selecting Omit is that no join clause is included for the table.
Join Steps — A list of the Join Steps comprising the Join Path currently selected above is given here. Each Join Step consists of two columns connected by an operator, which defaults to the equals operator. By right-clicking on a Join Step its operator can be set to equals (=), not equals (<>), greater than (>), greater than or equals (≥), less than (<) or less than or equals (≤). The join steps are connected by logical AND operators in the generated SQL.
Note that a Join Path of style Cross Join does not contain Join Steps.
Search Conditions for selected Join Path — Join Steps can be supplemented by Search Conditions, as displayed here (such as “income > 0”), or they can be supplemented or replaced by more complex Join Conditions than can be created with the use of Join Steps alone. Any Search Conditions or Join Conditions displayed here are displayed as a conditional expression in SQL, which will automatically be connected to any Join Steps with a logical AND operator.
One case in which it might become necessary to supply Search Conditions is when one table is joined to another using one or more “intermediate” tables that otherwise do not appear in the overall query. Although it is true that an intermediate join table can be defined using the Join Path Wizard, it is not possible to specify true Search Conditions on an intermediate table using the Wizard. To do so requires that the entire intermediate join condition and search condition be defined as Search Conditions for the overall join path.
By way of an example, the best way to introduce intermediate join tables that might have search conditions is to edit the Search Condition for the join path from, say table A to D, introducing only one intermediate table at a time, say first joining A to B. Doing this introduces a new join path from A to B. Proceed to define once again a Search Condition for the original join path from A to D, introducing a join between B and C, for example. Again, this creates a new join path from A to C. Finally, edit the Search Condition from A to D and join C to D in this Search Condition. This creates three join paths, labeled A B, A C, and A D (even though they actually join A B C D).
Join Paths that contain a Search Condition are not candidates for loading into another analysis, either automatically or by selecting the Load button, described below. Conversely, if a Join Path is missing Join Steps but contains a Search Condition, the Load button can be used to attempt to supply the missing Join Steps, but the missing Join Steps are not supplied automatically, as described below.
- Qualify column names — Search Conditions and/or complex Join Conditions can be displayed either with simple column names (such as “income”), or fully qualified names with database and table names specified (such as “twm_source”.”twm_customer”.”income”).
Edit — Displays a dialog based on the variables panel, where an SQL expression containing Search Conditions and/or complex Join Conditions can be created or edited.
It is worth noting that any column appearing in a Search Condition must belong to a table in the current or a preceding Join Path. If this is not the case, it may result in the SQL error “Improper column reference in the search condition of a joined table”. This error can also occur if anchor table replacement is in effect and a variable reference involving a column from the original anchor table other than an anchor column is included in a Search Condition.
Load — To load join paths from other Variable Creation analyses in a loaded project, click on Load. This causes each Variable Creation analysis in a loaded project to be searched for missing join paths. Missing join paths are those that have no Join Steps, with the exception of those of style Cross Join which cannot have Join Steps. The first join path encountered, if any, for each missing join path is used. When the load operation is complete, an informational message displays at the bottom of the form summarizing the results of the search.
Finally, note that if a join path is missing when an analysis is executed, the Load operation is performed automatically to try to correct the error. In any case, it is recommended to use caution in relying on join paths loaded from another analysis and to always make sure that the join path has the desired effect in the new context.
Wizard… — Sets the join paths using the following dialog.Join Paths Wizard
From — Initially, this is the Anchor Table along with a list of all of its columns, but it may be changed to any preceding join path target table (i.e., a table referenced in the analysis that is the target of a preceding join path). If more than one table is required in the Join Path, these are specified through subsequent clicks of the Add button. Highlight the column to join to that specified in To below.Volatile or derived tables may need to be joined, and these will appear in the display as the name of the analysis that produces the table. To select one of these types of table in the selector, first select the database of the logged in user and these tables will be listed first before the permanent tables in the database (volatile tables are built in the User database).
To — Initially, this is the target or right-side table in the Join Path, along with a list of all columns within that table. If the Anchor Table or starting table is not simply joined directly to this table, it can be changed via pull-down. If more than one table is required in the Join Path, these are specified through subsequent clicks of the Add button. Highlight the column to join to that specified in From above.
Refer to the note above about volatile or derived tables in the From selector.
Steps — Clicking on the Add button populates the Steps area. Similarly, highlighting a Step and clicking on the Remove button removes that particular step. Steps should be entered such that the first step begins with the anchor table (on the left side) and the last step ends with the target table for the join path (on the right side). Additionally, the target or right side tables should be grouped together in the list of steps and not alternate in value (that is, table1, table1, table2, not table1, table2, table1).
The operator of a Join Step may be changed by right-clicking on the Join Step.
- Add — Adds a Join Step built from the currently selected columns. The operator is equals (=) by default, but can be changed by right clicking on the Join Step.
- Remove — Removes the currently selected Join Step.
- Up/Down Arrows — Clicking on the up or down arrow to the right of the Steps display moves the currently selected Join Step up or down in the list.
- Left/Right Arrows — Clicking on the left arrow to the right of the From and To table selectors will move the currently selected To table to the From selector and set the To selector to the target or right side table for the Join Path. Clicking on the right arrow will move the currently selected From table to the To selector and set the From selector to the source or left side table for the Join Path.
- Finish — Accepts all changes and returns to the anchor panel.
- Back — Returns to the previous Join Path.
- Next — Proceeds to the next Join Path.
- Cancel — Discards all changes and returns to the anchor panel.
- From — Initially, this is the Anchor Table along with a list of all of its columns, but it may be changed to any preceding join path target table (i.e., a table referenced in the analysis that is the target of a preceding join path). If more than one table is required in the Join Path, these are specified through subsequent clicks of the Add button. Highlight the column to join to that specified in To below.
- Anchor Table — Pull-down with a list of all tables and views used to create variables, dimensions and/or specified in an expert clause. Select the 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.