If the join is a simple join, the following fields are displayed:
•Table 1 and Table 2: both populated by selecting a recently-used table, or by using the Choose Database Item dialog.
A Filter field is provided at the top of the interface, which allows you to enter characters to limit the items displayed in the list to only those with names containing the supplied string.
If one or more auxiliary databases has been configured at the current RPI client, the Choose Database section is displayed. This allows you to specify the context from which databases will be listed. Databases that do not support joins (currently only Cassandra) are not listed.
Choose Database is not shown if no auxiliary databases have been configured at the current RPI client.
All tables from the selected context are shown. You must select both Table 1 and Table 2. Tables 1 and 2 must be different.
You can select tables from within a single database, or you can select a data warehouse and auxiliary table to create a join between databases.
When one or more auxiliary databases have been configured at the current RPI client, table names are qualified with the database to which they belong.
Changing a table removes all existing join keys from the Join key details grid (it is therefore necessary to specify the join's tables before its join key pair(s)).
Note that, when creating a cross-database join to an anonymous auxiliary database, you can join to a suppression table only.
•Cardinality: this property is only enabled for simple joins and is disabled for multiple joins. A simple join’s cardinality defines the relationship between records in one of the join’s tables and records in the other…for example, Customer and Orders tables might have a One to Many cardinality (a given customer might have several orders). Cardinality is set using a dropdown that exposes the following values:
o Not Known (the default value)
o One to One
o One to Many
o Many to One
o Many to Many
Cardinality is used during the execution of selection rules, audiences and fulfillment activities. An incorrectly-set join cardinality can result in a failure at audience execution (e.g. when Customer to Order is defined as Many to One).
•Source database: if the current join is not cross-database, this read-only property displays the name of database from which the join's tables are sourced. If a cross-database join, it displays the auxiliary database's name
•Scope: this read-only property is set on selection of both Table 1 and 2, and provides an indication of the nature of the join, being set to one of two values:
o Within the same database
o From auxiliary database to data warehouse
•Join Key Pairs: a separate grid listing existing join key pairs. It contains two columns (Join keys 1 and 2).
A new join key pair is added automatically upon selection of Table 1 and Table 2. Table 1’s Join key is set to the table’s primary key. If no primary key exists, the first column (alphabetically) is selected instead. Table 2’s Join key is set to the same column name as Table 1’s (if it exists), else to its primary key, or the first column (alphabetically) in the absence of a primary key.
You can also add a new join key pair manually. The new join key pair's Join keys 1 and 2 default to the first columns (alphabetically) in the respective tables. You can also configure an existing join key pair. Again, the Choose Database Item dialog is used to specify columns (this time it lists only columns from the relevant table). You can remove one or more existing join key pairs. Finally, at least one join key pair must be specified.