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

Adding New Data as a Whole Row into the Standard Dimension Table in Siebel Data Warehouse

Example: Loading Contact Data From an External Source

To add new data as a whole row into the standard dimension table

  1. Identify and understand the existing structure of staging tables. Refer to Siebel Data Warehouse Data Model Reference for the table structure. Non-system columns can include the null value.
  2. Create a custom SDE mapping to load the data into the staging table in the Custom_DW_Folder for this purpose. The staging table needs to be populated with incremental data (rows that have been added or changed since the last Refresh ETL process), for performance reasons.
  3. Populate the INTEGRATION_ID column with the unique identifier for the record.

    The combination of INTEGRATION_ID and DATASRC_NUM_ID is unique. When importing the data, make sure that a unique identifier for the external data source is inserted in the DATASRC_NUM_ID column. The DATASRC_NUM_ID is set to 1 for mappings that source data from the Siebel OLTP. This is a reserved value and is used in all standard mappings. For example, a value of 2 can be defined for DATASRC_NUM_ID in the custom SDE mapping. The standard SDE mappings populate the INTEGRATION_ID column of the dimension staging table (used for resolving the dimension's Siebel OLTP ROW_ID value). The custom process must be used to populate the same column with a unique identifier from the external data source.

  4. After the data is populated in the staging table, use the standard SIL mappings to populate the dimension target tables.
  5. Use one data source per dimension.

    This is a best practice. To consolidate data for a dimension row from multiple data sources, complete that process prior to the custom SDE mapping process. For example, to consolidate contacts from Siebel and Peoplesoft applications, complete the consolidation process (including removing duplicates, cleansing, and so on) before loading the contact list into the Siebel staging table.

  6. Modify the SDE and SIL mappings of all the related fact tables (fact tables that need to be linked to this dimension).

    The custom fact SDE mappings must populate the foreign key column of the changed dimension (using a custom map table process to convert from Siebel's row IDs to the external data source row IDs). The custom SIL mapping should be modified to use the appropriate DATASRC_NUM_ID, because the standard SIL mappings assume DATASRC_NUM_ID for the dimensions are the same as the fact table's DATASRC_NUM_ID.

  7. It is very important to decide when the data is going to be loaded. If it is going to be loaded along with the Siebel source data, you must be careful with how you handle failure recovery. The preconfigured workflows truncate the target staging table prior to loading. Upon failure, when the DAC server restarts the task, all the data is truncated and all the data is loaded again.

    If the data from the external source gets loaded into the same staging table, be careful with how you handle this situation, since you cannot use the truncate table functionality. The data migrating into the staging table is not incrementally loaded, and, therefore, should be cleaned up prior to attempting to load this table again.

    In such a case, it is recommended that you encapsulate the extract part from both the sources inside an Informatica workflow. Upon failure of either of the extracts, the whole workflow gets rerun. Note that the data from both the sources should be run at the same time, all the time.

    If it is decided that the data is going to be loaded at different time frequencies, then the new SDE workflows need not depend on the preconfigured SDE workflows and can use the Truncate Table option for failure recovery. In this case, in the DAC Design view, define a new execution plan in the Execution Plans tab, and define the new data source in the Database Connections subtab. Make sure the shared SIL process depends on the SDE processes from both sources.

    Figure 11 provides a graphical representation of this process.

Figure 11.  Adding New Data as a Whole Row into the Standard Dimension Table
Click for full size image

 Siebel Data Warehouse Installation and Administration Guide
 Published: 11 March 2004