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.
   • 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.

   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.

   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.