INFORMATICA提供三种SCD Dimension抽取Mapping Wizard:
(个人认为Mapping Template Wizards比Wizards更先进更简练)
Type1不保存历史数据,当某些重要字段发生变化,直接更新维度表
Type2保存所有历史维度变更,三种方案:
Type3只保存当前和最近一次的被替换的维度变更,Target表通常增加类似字段:(上次更新的字段值和上次更新时间)
比如:PM_PREV_INTEGRATION_ID,PM_PREV_DATASOURCE_NUM_ID,PM_EFFECT_DATE
TYPE3 图:
重点讨论下TYPE2的三类抽取:
1)Creating a Type 2 Dimension/Version Data Mapping
When you use this option, the Designer creates two additional fields in the target:
PM_PRIMARYKEY. The Integration Service generates a primary key for each row written to the target.
PM_VERSION_NUMBER. The Integration Service generates a version number for each row written to the target.
当前最新的业务记录是PM_VERSION_NUMBER最大的那条记录
每当维度指定的重要属性发生变化就插入一行新数据,业务键相同的一组记录中最大的PM_VERSION_NUMBER+1,原来的记录保持不变,所以mapping只有插入操作。
NewOrChangedFlag: IIF( ISNULL(PM_PRIMARYKEY), 1, IIF( ( POSITION_NAME != PM_PREV_POSITION_NAME OR DIVN_NAME != PM_PREV_DIVN_NAME ) , 2, 0) )
Filter : IIF(NewOrChangedFlag=0,FALSE,TRUE)
NEW_PM_VERSION_NUMBER: IIF( NewOrChangedFlag = 1,0, PM_VERSION_NUMBER + 1)
2) Creating a Type 2 Dimension/Flag Current Mapping
When you use this option, the Designer creates two additional fields in the target:
PM_CURRENT_FLAG. The Integration Service flags the current row “1” and all previous versions “0.”
PM_PRIMARYKEY. The Integration Service generates a primary key for each row written to the target.
NewOrChangedFlag: IIF( ISNULL(PM_PRIMARYKEY), 1, IIF( ( POSITION_NAME != PM_PREV_POSITION_NAME OR DIVN_NAME != PM_PREV_DIVN_NAME ) , 2, 0) )
新插入的 PM_CURRENT_FLAG=1 ,旧的更新为 PM_CURRENT_FLAG=0
3)Creating a Type 2 Dimension/Effective Date Range Mapping
When you use this option, the Designer creates the following additional fields in the target:
PM_BEGIN_DATE. For each new and changed dimension written to the target, the Integration Service uses the system date to indicate the start of the effective date range for the dimension.
PM_END_DATE. For each dimension being updated, the Integration Service uses the system date to indicate the end of the effective date range for the dimension.
PM_PRIMARYKEY. The Integration Service generates a primary key for each row written to the target.
PM_PRIMARYKEY | ITEM | STYLES | PM_BEGIN_DATE | PM_END_DATE |
---|---|---|---|---|
4325 | Sock | 13 | 9/1/98 | 6/1/99 |
5401 | Boot | 20 | 10/1/98 | 6/1/99 |
6345 | Sock | 18 | 6/1/99 | |
6346 | Boot | 25 | 6/1/99 |
Mapping流程图和Creating a Type 2 Dimension/Flag Current Mapping一样,更新字段如图: