Bookshelf Home | Contents | Index | Search | PDF

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

Description of Custom Templates

---

The custom templates provided in Custom_DW_Rep is described in this section.

Dimension With Mapping Table Entry

This set of templates shows you how to get the column Race from the S_CONTACT table.

CustomTemplateSDE_PersonDimension

Using the SDE map, populate the attrib_02 column of extension stage table (W_PERSON_DSX) with value from S_CONTACT.RACE source column. This is a fairly straightforward map. The different transformations are:

SQ_S_CONTACT. The SQL in the SQL override section contains the link to the "increment row image" table so that only the incremental data is captured.

EXPTRANS. There is a lookup (LKP_DATASOURCE_NUM_ID) to get the datasource_num_id from W_PARAM_G table. The W_PARAM_G gets populated in the beginning of Refresh_Extract_Siebel_DW" batch. Note that all the system columns are being populated in addition to the attrib column. The attrib_01 is populated with string "Contact" because the combination of (Integration_id, Datasource_num_id, Contact_type) is used to lookup for the ROW_WID value. In most of the dimensions only the (Integration_id, Datasource_num_id) is used for this purpose.

CustomTemplateSIL_PersonDimension

Using the SIL map, populate the attrib_02 column of the final extension table to the person dimension (W_PERSON_DX). The different transformations are:

SQ_W_PERSON_DSX. There is no SQL in the override. It is straight query from W_PERSON_DSX table that has been populated with incremental rows.

EXPTRANS1. Defines W_DIMENSION_WID for the dimension. This dimension has an entry in the W_DIM_TABLES_G because it has mapping data entry in the mapping table W_MAP_DIM_M.

LKP_W_MAP_DIM_M. Lookup the W_MAP_DIM_M table to get ROW_WID for the row.

LKP_W_PERSON_DX. Lookup the target table to find out "Insert or Update." This information will be used by update strategy.

EXPTRANS. To get the Insert/Update flag and ETL_PROC_WID by using lookup LKP_ETL_PROC_WID from W_PARAM_G table. This table is populated in the beginning of the Refresh_Extract_Siebel_DW batch.

FILTRANS. Passing only those rows that have entry in the parent dimension table.

UPDTRANS. Does Insert or update based on the Insert/Update flag.

CustomTemplateSDE_WaveDimension

This is similar to Dimension With Mapping Table Entry, with the difference that in SIL map there is a lookup transform to the parent dimension table W_WAVE_D instead of a lookup to mapping table. There is no mapping data entry for the wave dimension.

CustomTemplateSIL_WaveDimension

This is similar to Dimension With Mapping Table Entry, with the difference that in SIL map there is a lookup transform to the parent dimension table W_WAVE_D instead of a lookup to mapping table. There is no mapping data entry for the wave dimension.

Facts, Attributes, and Measures

This template shows how to attach an existing dimension (account dim) to a fact (Asset fact).

CustomTemplateSDE_AssetFact

Using this SDE map, populate the staging extension table for the Asset fact with incremental data. The different transformations are:

SQ_S_ASSET. This selects the OWNER_ACCNT_ID that will ultimately be used to join with the account dim in the fact. Note the extra WHERE clause joining to the incremental image table.

EXPTRANS. This gets DATASOURCE_NUM_ID using lookup LKP_DATA_SOURCE_NUM_ID from table W_PARAM_G. This table is populated in the beginning of "Refresh Siebel DW."

Finally the FK_01_ID field of the extension stage table W_ASSET_FSX gets populated. All the system columns are also getting populated.

CustomTemplateSIL_AssetFact

Using this SIL map, populate the final extension table for the asset fact. The different transformations are:

SQ_W_ASSET_FSX. Note the SQL in the override. This joins with the dimension table W_ACCOUNT_D to get the ROW_WID of the dimension row to be populated in the foreign key id column of the extension table.

LKP_W_ASSET_F. To get the ROW_WID of the parent fact table W_ASSET_F.

LKP_W_ASSET_FX. To get the Insert/Update flag that will be used by the update strategy to find out whether to insert or update this row.

EXPTRANS. Gets ETL_PROC_WID using the lookup LKP_ETL_PROC_WID from table W_PARAM_G. This table gets populated in the beginning of Refresh Siebel DW. Also generates the Insert/Update flag based on the previous transform LKP_W_ASSET_FSX.

UPDTRANS. Updates or inserts based on the Insert/Update flag.

Bookshelf Home | Contents | Index | Search | PDF