Mapping dimension tables

Map a new dimension table to make data that augments data in a base table, such as demographics based on postal codes, accessible to processes in flowcharts.

Before you begin

Before you map a user table, ensure that the table uses only the data types that Unica Campaign supports.

About this task

Dimension tables must be mapped to a database table. Also, dimension tables must be related to one or more base tables that are mapped to tables in the same data source (that is, the same database). In defining the dimension table, you can specify the join conditions between the base and dimension tables.

Procedure

  1. Use one of the following methods to open the New table definition - Select the table type dialog:
    • If you are editing a flowchart, open the Admin menu and select Tables, or
    • Select Settings > Campaign settings, then click Manage table mappings.
    Note: You cannot map a dimension table from a Select process.
  2. Select Dimension table, then click Next.
  3. Select the table that you want to map from the Source table list.

    The source fields in the selected table are mapped automatically to fields in the base dimension table that you are creating. To change the default selections, select fields from the Source table fields list or the New table fields List, and use the Add, Remove, Up 1, and Down 1 buttons, until the table is mapped as required, then click Next.

    Note: You can click on the Field name column in the New table fields section to automatically sort column names alphabetically in ascending or descending order.
  4. (Optional) Change the name Unica Campaign will use for the dimension table and its fields.
    1. To change the table name, edit the name in the Unica Campaign table name field.
    2. To change a field name, select the mapping in the New table fields list, and edit the text in the Unica Campaign Field name field, then click Next.
  5. Specify the dimension table's key(s), and how the table will be joined to base record tables.
  6. Select one or more keys in the Key field(s) list.
  7. Check Key field(s) are normalized in this table, if this is the case.
  8. Select the Table join method, then click Next.
    • The option Always use inner join will always use an inner join between the base table and this dimension table, returning only audience IDs from the base table that appear in the dimension table.
    • The Always use outer join option will always perform an outer join between the base table and this dimension table (which provides the best results if the dimension table is known not to contain at least one row for every audience ID in the base table).
    • The default setting of auto uses an inner join in the Select and Segment processes and an outer join in output processes (Snapshot, Mail List, and Call List). This typically provides the desired behavior where values in the dimension table are required for selection criteria, but NULLs should be output for missing audience IDs for any dimension table fields that are output.
  9. If base record tables exist, the Specify Relationship to Base Tables screen opens. Check the box to the left of any base record table that is related to the dimension table you are creating Specify the join fields, then click Next.
  10. You can choose to pre-compute the distinct values and frequency counts for specific fields, or allow users to perform real-time profiling of data in the base record table.
  11. Click Finish.

Results

You have created the dimension table. The data can now be used in flowchart processes.