Siebel Data Warehouse Installation and Administration Guide > Customizing the Siebel Data Warehouse > Siebel Data Warehouse Customization Steps for Non-Siebel Data >

Adding a Column in a Standard Dimension Table in Siebel Data Warehouse

Example: Loading an attribute called SKU for existing products that have already been loaded.

• New DDLs: New extension tables or preconfigured extension tables
• New Mappings: SDE and SIL mappings for extension tables
• Case: Bringing data in for dimension tables
• Reference: CustomTemplateSDE and SIL_WaveDimension in Custom_DW_Folder

To add a column in a standard dimension table

1. Add a column either in the standard or custom extension tables. There should be a 1:1 relationship with the parent table. Do not modify the standard tables, because doing so will affect upgrades.
2. The custom stage table should be populated with incremental data. This is an important performance consideration. It requires a process on the source side that detects new or modified records. This allows custom SDE mappings to process incremental data during each Refresh ETL execution.
3. While populating the extension table, design lookups (or write SQL overrides) for the SQL Qualifier Transformation in the Informatica mapping to identify the primary key (ROW_WID) of the parent row for the additional attribute. The primary key (ROW_WID) of the parent row is then used as the primary key (ROW_WID) of the extension table. Template mappings are provided in the CUSTOM_DW_REP folder (for example, CustomTemplateSDE_WaveDimension').
4. The custom SDE mappings should populate the stage extension tables (standard or custom). The custom extension table for staging should include the INTEGRATION_ID and DATASRC_NUM_ID. This combination enables the unique identification for each row by the data source. The data for this INTEGRATION_ID column should be populated by a process that identifies such an ID from the parent data source. The DATASRC_NUM_ID column should have the same value of the DATASRC_NUM_ID of the parent it is extending.
5. Once stage data is populated, a custom SIL mapping can be developed to move data from the extension stage to the extension table (of either the factor dimension table). The process should contain a look up either based on the INTEGRATION_ID and DATASRC_NUM_ID to find the ROW_WID for the extension table from the parent fact/dimension table. For an example, refer to CustomTemplateSIL_WaveDimension.
6. If there are rows for which there is no matching ROW_WID in the parent tables, they should be ignored. Use the appropriate update strategy for modifying existing data.
7. Make the custom SIL process depend on the custom SDE process and SIL process of the dimension table you are extending, because this mapping will require the borrowing of the foreign key for the link.