Siebel Customer-Centric Enterprise Warehouse Installation and Configuration Guide > Configuring Siebel Customer-Centric Enterprise Warehouse for Oracle 11i >

Mapping Source Customer Hierarchies to the Customers Dimension Table

Customer hierarchies are typically custom-defined in Oracle Applications. If you want to include this data in the data warehouse, you must perform two customization processes:

• Import the hierarchies into the TI_CUSTOMERS staging table for Oracle 11i.
• Redefine the category lookup so that the new category data is loaded into the Siebel Customer-Centric Enterprise Warehouse.

The two processes are illustrated in Figure 40.

Figure 40. Oracle Applications: Customization Processes for Custom Customer Hierarchies Load

To load the source-defined customer hierarchies into the TI_CUSTOMERS staging table for Oracle 11i, you must first edit the MPLT_BCI_CUSTOMERS Business Component mapplet to extract the hierarchy in addition to the customer information. After your Business Component is set up to extract the customer hierarchies, you must verify that the M_I_CUSTOMERS_EXTRACT extract mapping outputs this data to the data warehouse.

To map Oracle-defined customer hierarchies to the Customers dimension table

1. In PowerCenter Designer, open the Configuration for Oracle Applications v11i folder.
2. Open the MPLT_BCI_CUSTOMERS Business Component mapplet for Oracle 11i.

   Modify the mapplet to extract the customer hierarchy columns.

   If the hierarchies and customers are maintained in the same Oracle Applications source table, then load these columns into the SQL Source qualifier and map them to the Business Component output.

   However, if the hierarchies and customers are stored in two different tables, then the Business Component must be modified to include both source tables so that it can include both sets of information.

   • Modify the M_I_CUSTOMERS_EXTRACT extract mapping, to map the source customer hierarchy columns to the extension hierarchy columns in the TI_CUSTOMERS staging table.

     If the source table has hierarchy codes, but no descriptions associated with these codes, map the Oracle Applications codes to both the hierarchy name columns and the hierarchy code columns. Hierarchy name columns are named as CUST_HIERX_NAME, where X denotes the level of the customer hierarchy.

     If the source table has code values, but the corresponding descriptions are in a different source table, you must build new codes mappings that load the data into the IA_CODES table.

3. Save your changes to the repository.

   NOTE:  After you complete the previous process, you must modify a hierarchy lookup in the customer dimension so that the system extracts the new categories.

To configure the category lookup

1. In PowerCenter Workflow Manager, open the Configuration for Oracle Applications v11i folder.
2. Open the S_M_I_CUSTOMERS_LOAD session for Oracle 11i, to open the Edit Tasks box.
3. In the Transformations tab, modify the lookup in the MPLT_ADI_CUSTOMERS.LKP_CUST_HIERX field in the IA_CODES table by adding the new category in the SQL statement.

   Select the arrow to edit the WHERE clause.

   You can use the following statement as a sample of how to structure your SQL statement:

   WHERE IA_CODES.CATEGORY = 'GENERIC'AND IA_CODES.LANGUAGE = 'E'

   For example, if you have mapped something to the CUST_HIER1_CODE, then the SQL to the IA_CODES table is now a new category code in place of GENERIC.

4. Validate and save your changes to the repository.