缓慢变化维抽取方案

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一样，更新字段如图：