关闭

缓慢变化维抽取方案

标签: SCD 缓慢变化维
108人阅读 评论(0) 收藏 举报
分类:

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

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:42130次
    • 积分:1079
    • 等级:
    • 排名:千里之外
    • 原创:58篇
    • 转载:71篇
    • 译文:0篇
    • 评论:0条
    文章分类