Bookshelf v7.5.2: Using Custom Templates

Siebel Data Warehouse Installation and Administration Guide > Extending the Siebel Data Warehouse > ETL Process Description >

Using Custom Templates

Use the custom templates provided in Custom_DW_Rep for ALL changes and modifications. For more information, read Description of Custom Templates.

Adding Extra Columns From the Same Source Table

Follow these guidelines:

Use predefined columns in an existing extension table or create a new extension table with the appropriate columns.

Create a custom mapping by copying an existing mapping (templates SDE and SIL) to load the extension table column based on the provided templates.

Create new sessions for the new map in the customization folder, Custom_DW_Rep.

Adding Extra Columns From a Related Source Table

Follow these guidelines:

Use predefined columns in an existing extension table or create a new extension table with appropriate columns.

Create new map (SDE and SIL) to load the extension table column based on the provided templates. The related table should be joined in Source Qualifier's SQL (first transform in a mapping).

Create new sessions for the new map in the customization folder, Custom_DW_Rep.

Modifying Columns in the Siebel Data Warehouse

Follow these guidelines:

Do not modify the existing column.

Use extension tables column for new values.

Use the guidelines in Adding Extra Columns From the Same Source Table and Adding Extra Columns From a Related Source Table.

Creating a New Dimension and Using it in an Existing Fact

Follow these guidelines:

Create a DDL for new dimension based on the standard structure (with appropriate system columns). Create staging table for this dimension.

Add an entry of the source table in S_ETL_SRC_TABLE with table ID starting from 10,000.

Create custom SDEINC_FindInsertUpdate_passX to map create image table.

Create corresponding custom SDEINC_IdentifyDeletes_passX map.

Add sessions in the Create Incremental Image batch in appropriate place (details table processed first).

Create a new custom map SDE_XYZ to populate the dimension. Add the incremental row image join section in the SQL so that it pulls only the incremental data. Use existing maps as examples on how to populate the system columns.

Create a custom mapping SIL_XYZ to populate the new dimension from the staging table. Use existing maps as an examples on how to populate the system columns.

Use an extension table's predefined foreign keys of the fact table that will join to this new dimension or create a new extension table with predefined structure to hold a new foreign key if you have exhausted the foreign keys in existing extension table.

Create new mappings (SDE and SIL) by copying existing SDE and SIL mappings to populate the extension table columns.

Create appropriate sessions in the customized sessions area.

Creating a New Dimension for New Facts

Follow these guidelines:

Follow the guidelines in Creating a New Dimension and Using it in an Existing Fact (for capturing incremental for both dimensions as well as facts) but you do not have to modify the existing facts. It is an entirely new set.

Use lookups for getting foreign keys to the new dimension.

Use existing maps as examples.

Creating a New Fact

Follow these guidelines:

Same as Creating a New Dimension for New Facts (for capturing incremental for both dimension as well as fact).

Use lookups for getting foreign keys to the existing dimension.

Use existing maps as examples.