|
Siebel Customer-Centric Enterprise Warehouse Installation and Configuration Guide > Configuring Common Components of the Siebel Customer-Centric Enterprise Warehouse > Configuring Slowly Changing Dimensions >
Enabling Type II Slowly Changing Dimensions (SCDs)
You can configure the Siebel Customer-Centric Enterprise Warehouse to maintain history down to the column level. For example, if you are loading the IA_ACTIVITY_COSTS table and you only want to maintain history for the AVG_ACTV_DURATION column, you can set the Type II flag to Y for this column and N for all other columns. A brief summary is provided in Table 20.
Table 20. Summary of Types of Slowly Changing Dimensions
Slowly Changing Dimensions Type |
|
|
Type I Slowly Changing Dimension |
N
|
Overwrites the data with the latest value. |
Type II Slowly Changing Dimension |
Y
|
Creates a new record for the updated records and, if applicable, updates the effective dates and current flag for any existing record. |
If you want to use Type II SCDs, you need to set the value of the Type II Flag. By default it is set to N, but you can enter a conditional statement that sets the flag to Y. For example, you may only want to create new records if particular columns change values. In this case, you can set this up in your conditional statement. You can configure the Type II Flag in the Source Adapter mapplet by modifying the port EXT_TYPE2_FLAG in the Expression transformation. Use the following procedure. To configure the Type II flag
- In PowerCenter Designer, open the applicable source system configuration folder.
- Open the applicable Source Adapter mapplet.
- Double-click the Expression transformation to open the Edit Transformations box.
- In the Ports tab, edit the expression for the
EXT_TYPE2_FLAG port.
The default for the Type II Flag is N. Here you can enter Y to enable Type II functionality for all columns in the table. You can also enable only certain columns to maintain history. To enable some columns, but not others, you need to insert a conditional statement. For example, if you want to maintain history only for the Channel Point Name column, then you could write the following conditional statement:
IIF(EXT_CHNL_POINT_NAME!= OD_CHNL_POINT_NAME, 'Y', 'N')
In this case, only the Channel Point Name column has historical values—all other columns do not.
- Validate and save your changes to the repository.
|
