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

Configuring Product Categories

As initially configured, the Siebel Customer-Centric Enterprise Warehouse extracts product categories where the CATEGORY_SET_ID is 2 or 27. However, it is likely that the categories you extract from the source system are different from these prepackaged categories. Therefore, you must reconfigure your product categories by making two customizations:

• Identify and extract only the categories that you want to report against.
• Properly position the data so that it loads into the Siebel Customer-Centric Enterprise Warehouse.

There are two dimension tables that have built-in product hierarchies—the Product and Sales Product dimension tables. These dimension tables share one category staging table. ETL extracts the Product and Sales Product staging tables separately, and then joins these tables with the shared category staging table to load hierarchies. The category extract mapping controls the category sets that are used in the Product and Sales Product dimensions. The load mappings for the Product and Sales Product dimensions specifies which Category Set is used to load to the hierarchy columns.

To configure product category extract from Oracle Applications

1. Identify the categories that need to be mapped to the extension hierarchy columns.

   These categories are extracted from the source and placed in the hierarchy column specified.

2. In PowerCenter Workflow Manager, open the Configuration for Oracle Applications v11i.
3. Open the S_M_I_CATEGORY_EXTRACT session with the Task Developer.
4. In the Mappings tab, click MPLT_BCI_CATEGORY.SQ_MTL_CATEGORIES.
5. In the right pane, scroll down and click SQL Query to edit session SQL override.
6. Click the arrow to edit the WHERE clause.

   The following statement is an example of how to structure the WHERE clause:

   WHERE...MTL_CATEGORY_SETS_B.CATEGORY_SET_ID IN (27,2)

   In this example, the WHERE clause extracts categories where the Category Set ID is 27 or 2.

7. Click OK, and then click OK to close the Edit Tasks box.

To modify the default Category Set

1. Open the file_parameters_ora11i.csv file in the $pmserver\srcfiles folder
2. Replace the default Category Set ID (27) with your new value.

   The following table is an example of the file_parameters_ora11i.csv file.

3. Save and close the file.

To reconfigure the product hierarchy loads

1. In PowerCenter Designer, open the Configuration for Oracle Applications v11i folder, and expand the Mapplets folder.
2. Open the MPLT_SAI_PRODUCTS mapplet for Oracle 11i.
3. Double-click the EXP_PRODUCTS expression transformation to open the Edit Transformation box.
4. In the Ports tab, scroll down to find the hierarchy code port.

   Hierarchy levels are named with the following convention EXT_PROD_HIERX_CODE, where X denotes the level within the hierarchy. For example, if you want to edit the first level of your hierarchy, you must edit the definition for EXT_PROD_HIER1_CODE port. The first two levels are preconfigured as follows:

   EXT_PROD_HIER1_CODE = IIF(ISNULL(INP_SEGMENT1) OR ISNULL(INP_SEGMENT2), NULL,

   INP_SEGMENT1||'~'||INP_SEGMENT2)

   EXT_PROD_HIER2_CODE = INP_SEGMENT1

5. Modify the expression that defines your hierarchy code.
6. Validate and save your changes to the repository.

NOTE:  You can configure six hierarchy extension columns in the Siebel Customer-Centric Enterprise Warehouse. To resolve the names for each level, you need to extend the IA_CODES table with the correct codes when configuring the new hierarchy levels.