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 Fact Table in Siebel Data Warehouse

Example: Bringing in activity data from external sources.

To add new data as a whole row into a standard fact table

  1. Identify and understand the existing structure of staging tables. Refer to Siebel Data Warehouse Data Model Reference for the table structure.

    Fact tables contain foreign keys to existing dimension tables. These dimensional key columns in the fact table need to be populated with the correct ROW_WID values from the dimension tables. For dimensions that are populated with Siebel data, identify the correct ROW_WID by looking up the INTEGRATION_ID in the dimension table. For example, to load Order Item details from SAP, the Order Item Fact staging table has the PROD_ID column that needs to be resolved from the main Siebel data source. Looking up the ROW_WID of the S_PROD_INT table later resolves the foreign key to product dimension.

  2. Develop custom SIL mappings to handle the specific situation required. The reference SIL mapping can be copied to the Custom folder and customized for specific requirements. If the SIL mappings were copied as a base from the standard folder, it is possible to leverage the existing logic for ROW_WID generation, update strategy, and so on. It may be easier to trim an existing SIL mapping than to create a new one. Apply discretion in creating a custom SIL mapping.
  3. While joining with the dimensions in the SIL process, use the DATASOURCE_NUM_ID and INTEGRATION_ID to resolve foreign key values. Note that for dimensions populated entirely from Siebel, DATASOURCE_NUM_ID will be 1.
  4. For the dimensions populated entirely from other external sources, use the appropriate DATASRC_NUM_ID (not 1) and the corresponding INTEGRATION_ID. This needs to be hard coded in the custom mapping.
  5. Use an update strategy that would update the existing records and insert new ones. Inspect SDE and SIL mappings provided as reference to understanding the programmatic logic.
    • Scenario 1. All the dimensions get data from the external source as the fact table. In this case it is not necessary to change the existing standard SIL mappings. The standard SIL process assumes that the dimension rows that a fact needs to join are from the same data source as the fact rows. (See Figure 12 for an example.)
    • Scenario 2: Some dimensions are populated from external sources and some from the Siebel OLTP. In this case, standard SIL mappings need to be modified so that the appropriate DATASRC_NUM_ID is used when joining to a dimension table. This scenario needs to be custom designed and developed. As in any customization, it will require ongoing maintenance. (See Figure 13 for an example.) Copy the mapping over to the Custom folder and do the modifications. Create a workflow that points to the mapping in the Custom folder. In the DAC repository, search for the task that uses this workflow as a command and change the folder to Custom folder. Change the name as necessary.

NOTE:  Changing the standard mapping is an exception. It is important to document changes as updates to standard mapping will be lost during the Informatica repository process. Reapply changes to the standard mappings after the upgrade process. Prior to modifying copy the mapping as-is from OTB folder to the Custom folder, and make changes in the Custom folder. In the DAC repository, find the task that is using the workflow name and change the folder to point to the Custom folder. Adding new data as a whole row into the standard Fact table: Scenario 1.

Figure 12.  Adding New Data as a Whole Row into the Standard Fact Table - Scenario 1
Click for full size image
Figure 13.  Adding New Data as a Whole Row into the Standard Fact Table - Scenario 2
Click for full size image

In the Figure 13 because the contact data and product data are from different data sources, it is necessary to modify the SIL session and the SQL override in the following manner:

SELECT

ACT.INTEGRATION_ID,

P.ROW_WID,

C.ROW_WID

FROM

W_ACTIVITY_FS ACT,

W_CONTACT_D C,

W_PRODUCT_D P

WHERE

ACT.PRODUCT_ID = P.INTEGRATION_ID

AND P.DATASRC_NUM_ID = 1 /* Product is from Siebel Source having datasrc_num_id = 1

ln dimension */

AND ACT.CONTACT_ID = C.INTEGRATION_ID

AND ACT.DATASRC_NUM_ID = C.DATASRC_NUM_ID

/* The rows in Contact dimension that the fact refer to are from the same external datasrc, 2 */


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