Using lookup tables

Lookup tables are useful for replacing source data with the actual data that you want to appear in the data warehouse. For example, a lookup table could be used to replace a zip code with a full address or, conversely, to replace a full address with a zip code.

To link a lookup table column to a logical entity column:

1. Click the link to the desired task in the Data Warehouse panel. The Manage Data Warehouse Tasks window opens.

2. In the Mappings column, click the mapping for the logical entity containing the result column (with the data that you want to replace). The Edit Mapping - Name window opens.

3. Hover the mouse cursor over the relevant data warehouse column and then click the Lookup button that appears to the right of the column name. The Select Lookup Table window opens.

   1. From the Database drop-down list, select the database containing the lookup table.

      Information note

      The database must reside in your data warehouse.

   2. From the Schema drop-down list, select the schema containing your source lookup tables.
   3. Select either Table or View according to the lookup table type.

   4. From the Table drop-down list, select the lookup table.

      The right side of the Select Lookup Table window displays the lookup table columns and their data types. To view the data in the lookup table, click the Show Lookup Data button.

   5. After you have selected the lookup table, click OK.

4. After selecting the lookup table, the Lookup Transformations - Table Name.Column Name window opens. The window is divided into the following panes:

   • Upper pane: The upper part of the right pane (Condition) displays the condition expression, which stipulates the condition(s) for performing the lookup.

   • Lower pane: The lower part of the right pane (Result Column) displays the column result expression, which stipulates what data to replace in the target column.

5. To change the lookup table, click the Change Lookup Table button above the lookup table columns and then perform steps a. to d. above.
6. To view the lookup table or landing table data, click the Show Lookup Data or Show Landing Data buttons respectively.

7. To specify condition(s) for performing the lookup, click the Create Expression button (which changes to Edit Expression after an expression has been created) above the Condition expression. The Condition Expression - Column Name window opens.

   You can create an expression using the landing and lookup table columns on the left.

   For an example, see Lookup example. For information on creating expressions, see Creating expressions.

8. To specify what data to replace or add if the lookup conditions are met, click the Create Expression button (which changes to Edit Expression after an expression has been created) above the Result Column expression. The Result Expression - Column Name window opens.

   You can create an expression using the landing and lookup table columns on the left.

   For an example, see Lookup example. For information on creating expressions, see Creating expressions.

9. To preview the results, click the Preview Results button.
10. Click OK to save your settings and close the Lookup Transformations - Table Name.Column Name window.

Using lookup tables that do not have a task for CDC mapping

When the Store Changes option is enabled in the Replicate task, Replicate creates Change Tables in the landing zone. These tables contain only the changes to the original data. The Compose task CDC task reads the changes from Change Tables and applies them to the target tables. However, if the landing zone contains dedicated lookup tables (i.e. tables that are not associated with any Compose task), Compose will not be able to apply changes to these tables.

There are two ways of handling such a scenario, both of which are described below.

Method 1

Define another Replicate task with the Apply Changes replication option enabled.

Method 2

1. Discover the landing site and add all the lookup tables to the Compose model without any relation to/from other entities.

2. Either, define lookups from the data warehouse hub tables to the newly added entities.

   OR

   Create relationships from the data warehouse hub tables to the newly added entities.

   Information note

   Creating relationships may not be a viable option when the lookup tables are complex.

3. Define a new data warehouse Change Tables Only task that updates the lookup tables.
4. Ensure that the new task runs before the data warehouse task.

The advantage of this method is twofold: a.) All the tables used in the mappings are managed by Compose, and b.) Only one Replicate task needs to be defined (which also means that the database transaction logs are read only once). The disadvantage is that you need to ensure that the task that updates the lookup entities always runs before any data warehouse task.