Siebel Customer-Centric Enterprise Warehouse Installation and Configuration Guide > Configuring Common Components of the Siebel Customer-Centric Enterprise Warehouse > About Resolving Dimension Keys >

Configuring Lookup in Additional or Custom Tables

The predefined extension dimension keys in the fact tables point to the IA_DIMENSION table. If you have custom dimension tables to add, or want to join additional dimension tables to a Siebel Customer-Centric Enterprise Warehouse fact table, you can link the dimension tables to the fact tables by modifying the SQL statement for the extension dimension keys in the fact tables.

Each fact table has at least three extension dimension keys, allowing you to store additional dimension tables. To join a new dimension table to a fact table, you need to modify the fact table's load mapping or session, which involves two tasks:

• Defining the Key ID in the Source Adapter mapplet of the fact table load.
• Modifying the session to perform a SQL statement for the lookup that is used to resolve the Key ID and redirect the lookup to the dimension table of your choice.

To reset the Dimension Key ID in the Source Adapter mapplet

1. In PowerCenter Designer, open the applicable source system configuration folder.
2. Open the applicable fact load mapping's Source Adapter mapplet.
3. Double-click the Expression transformation to open the Edit Transformations box.
4. Edit the EXT_[SUBJECT]_DIMX_KEY port.

   For example, if you want to join the IA_GL_ACCOUNTS dimension table to the IA_SALES_IVCLNS fact table, you could join it to the EXT_SIVL_DIM1_ID.

   NOTE:  Make sure that the level at which you define the Dimension Key ID in the fact mapping is the same grain at which the Key ID is defined in the dimension table's extract mapping.

5. Validate and save your changes to the repository.

To resolve the extension column Dimension Key ID in the fact load

1. In PowerCenter Workflow Manager, open the applicable source system configuration folder.
2. Double-click the applicable session for the fact load mapping to open the Edit Tasks box.
3. In the Transformations tab, edit the Lookup SQL Override field for the dimension key lookup in the ADI mapplet.

   By default, the lookup points to the IA_DIMENSIONS table.

4. Point the lookup to the new dimension table.

   For example, If you are joining the IA_GL_ACCOUNTS table, then you would change the references from IA_DIMENSIONS to IA_GL_ACCOUNTS.

5. Click OK, and then click OK to exit the Edit Tasks dialog box.