Siebel Business Analytics Applications Installation and Administration Guide > Customizing the Siebel Data Warehouse > Siebel Data Warehouse ETL Process Concepts >

Important Notes About Siebel Data Warehouse Customizations

All custom work, unless stated otherwise, must be done in the Custom folder so that the custom work can be preserved during an Informatica repository upgrade. Doing work on the standard folder should be avoided whenever possible. Whenever custom work is done in the standard folder, a careful note of the custom work should be recorded so that the customizations can be reapplied whenever the Informatica repository is upgraded. An upgrade of the Informatica repository overrides any changes to the standard folder.

Additional Customization Instructions

• Table definitions in Informatica. Make sure that the SQL style is set to Oracle while importing the table definitions from external data sources. Even if the actual data source is of another database type, such as DB2 or MSSQL, it does not affect the logic of how data gets loaded.
• Update strategy. For loading new fact and dimension tables or loading data into the existing tables, design a custom process on the source side to detect the new and modified records. The SDE process should be designed to load only the changed data (new and modified). If the data is loaded without the incremental process, the data that was previously loaded will be updated again, which is a costly process. For example, the logic in the OTB SIL mappings looks up the destination tables based on the INTEGRATION_ID and DATASRC_NUM_ID and returns the ROW_WID if the combination exists, in which case it updates the record. If the lookup returns null, it inserts the record instead.
• ETL process. When using multiple sources for the data warehouse, you can decide to load from all of them at the same time or at different time frequencies using different execution plans.
• Truncating target tables. Truncating should be done through the DAC. A single task has place holders for a full load, and one for an incremental load.
  • For the SDE workflows, the commands for full load and incremental load are the same. They should have Truncate Always flag checked. For these kinds of tasks, the command for full load and incremental load are based on the same mapping.
  • For SIL workflows, the command can be different for full and incremental loads. They should have the Truncate For Full Load option checked. When a table gets truncated, the indices are automatically dropped and created after the data is loaded. And so, the workflow for full load command can have the Bulk Load option turned on, and, therefore, you can have an optimized version of the mapping that does not need to look up for finding if a record needs to be inserted or updated.
  • Auxiliary tasks need to be run only during incremental mode. So, for these tasks, the full load command is empty. No truncate options should be set.

    	SDE Sessions	SIL Sessions	Aux Sessions
    Truncate Option	Truncate Always	Truncate for Full Load	None

• ETL_PROC_WID. Use the same ETL_PROC_WID in W_PARAM_G table in custom mappings. ETL_PROC_WID is a reference key to Run History in the DAC. To use the same ETL_PROC_WID, copy the reusable lookup (called LKP_ETL_PROC_WID) defined in the Siebel_DW_Rep folder. The input to the lookup is a constant (hard coded to 1).
• DATASRC_NUM_ID. Even though preconfigured mappings look up W_PARAM_G for getting this value, for customizations that move data from other sources, you should hard code the value with a number other than 1, which is reserved for the main Siebel transaction database in custom mappings.
• Creating indices and naming conventions. Staging tables typically do not require any indices. Use care to determine if indices are required on staging tables. Create indices on all the columns that the ETL will use for dimensions and facts (for example, ROW_WIDs of Dimensions and Facts, INTEGRATION_ID and DATASRC_NUM_ID, and flags). Carefully consider which columns or combination of columns filter conditions should exist, and define indices to improve query performance. Inspect the OTB objects for guidance. Name all the newly created tables as WC_. This helps visually isolate the new tables from the OTB tables. Keep good documentation of the customizations done; this helps when upgrading your data warehouse. Once the indices are decided upon, they should be registered in the DAC, either manually or by right-clicking on the certain table and invoking the Import Indices command.
• Currency. For currency-related data, populate the table with the base currency and exchange date field (in order to convert the data appropriately). The data for currency conversion should be maintained in the main Siebel data source. (The currency data is maintained by converting all the currency information into a single Base Currency code specified in the DAC ETL preferences.)
• Day Dimension. For the data that is related to W_DAY_D, use the reusable transformation EXP_DAY_DIMENSION_FK_RESOLUTION. Note that this transformation will take a date as input and return the foreign key to the Day dimension as output in the appropriate format (YYYYMMDD). Copy the reusable transformation and use it.
• List Of Values. This applies in particular to Type 1 and 2. The preconfigured columns that depend on a list of values have a language-dependent column and language-independent column. Use the mapplet MPLT_LOV_TRANSLATION to populate the language-dependent and independent columns in the dimension tables. For fact tables, use MPLT_LOV_D_ROW_WID to create a new foreign key to the LOV dimension. One could also deal with translations directly in SQL overrides to improve performance.