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

Resolving the Dimension Key Using Database Joins

The ADI provides lookups for dimension keys by default. However, if the dimension table is large, you can provide the dimension key to the ADI by joining the dimension table and the fact table in the database.

If you supply the key, then the ADI does not perform the lookup and instead resolves the dimension key within the load mapping itself. In this case, you modify the SQL statement in PowerCenter to join the tables.

To load a dimension key using a database join

1. In PowerCenter Designer, open the applicable source system configuration folder.
2. Open the applicable load mapping.
3. Add the dimension table (the table that contains the dimension key) as a source system to your load mapping.
4. Drag and drop the surrogate key column from the dimension source system definition to the Source Qualifier.
5. Double-click the Source Qualifier transformation to open the Edit Transformations box.
6. In the Properties tab, edit the SQL statement to put in the join conditions between the dimension table and the fact extract table.
7. Drag and drop the surrogate key column from the Source Qualifier to an available EXT_*_KEY port in the Source Adapter mapplet.
8. Validate and save your changes to the repository.

In Figure 33, the dimension key resolution is performed in the Customer Dimension table in the database by joining the Customer Dimension table to the Sales Orders extract table. The dimension key is then passed to the ADI, and is then loaded into the Sales Orders Fact table.

Figure 33. Dimension Key Resolution