Contents

1
2 ろ 蟆 襷り鍵
3 襴 ろ
4 SQL Server 2008 Merge襯 伎 SCD2
5 谿瑚襭


1 #

Slowly Changing Dimension(危 SCD) 谿 覲蟆曙 伎企. 螳朱 企ゼ 襷 螳 螻, 蠏碁 谿 覲蟆 覦覯 襦襷 豢 , 襴蠍 譬 れ 襴襯 企慨 螳 3螳讌螳 る 蟆伎. 蠏碁 襴襦 Type1, 2, 3手 螻糾概壱 企 覿覯碁. Microsoft SQL Server 2005 襷 Type1, Type2朱 襷 覓企 る (願 覈 谿城? <;) 蠏碁 企. 企 覈轟~

  • Type1: 覲蟆
  • Type2: 蠍磯
  • Type3: 豢螳(Old, New 貉殊螳)

覲 伎 . Type1 '伎' '蟾'朱 蠏碁 谿 螳 覲蟆渚 蟆 覩誤螻, Type2 '伎' 螳(襯 れ CurrentYN=0)螻 螳 蠏碁ゼ 螻豺螻, '蟾'企朱 襦 豢螳(CurrentYN=1) 蟆 襷覃, Type3 貉殊 豢螳/覲蟆渚, OldName 貉殊 '伎' NewName 貉殊 '蟾' k 蟆企. 螳 Type2, Type3襯 狩 蟆曙磯 . Microsoft SQL Server 2005 SSIS Type1, Type2 蠏碁Μ螻 '豢谿襷企'朱 蟆 讌(螳 螻襴磯. 覦襷 瑚る 螳 牛), 蠏 詞 企 谿 襷企螳 譟伎, 谿 企 谿 襷企螳 譟伎讌 螻, 企 譟伎 蟆 襷企襯 襷. 蟆郁記SSIS 覈 朱 覓旧朱 讌貅譯殊 螳 . 谿螻 企 蟇磯Μ蟆 蟯螻襯 襷碕 朱 一 蟆 , 襷 ETL螻殊 企Π讌螻殊 蟇一海朱襦, 谿語^覓願屋煙 DW 蟇語 る 蟆 覓伎覩誤る 蟆 觜襦 蟆朱 覲伎碁. 伎蟇 SSIS襯 伎伎 企慨.

2 ろ 蟆 襷り鍵 #


  • 伎螻: dbo.customer
  • 覲願: dbo.dim_customer

use master
go

create database ssis_demo
go

use ssis_demo
go


--1. ろ 一危
if object_id('dbo.customer') is not null
	drop table dbo.customer
go

create table dbo.customer(
	keys int
,	family_name varchar(20)
,	cust_name varchar(20)
,	gender bit
,	home_addr varchar(100)
,	inferred_member_yn bit default(0)
);

insert dbo.customer values(1, 'lee', 'jae hak', 1, ' ″蟲 豐', 0);
go

if object_id('dbo.dim_customer') is not null
	drop table dbo.dim_customer
go

create table dbo.dim_customer(
	surrogatekey int identity(1,1) primary key
,	keys int 
,	family_name varchar(20)
,	cust_name varchar(20)
,	gender bit
,	home_addr varchar(100)
,	current_yn bit
,	inferred_member_yn bit default(0)
);
go

SSIS 襦碁ゼ 燕螻, れ [危襴] [一危 襴 ] 企 . れ朱 [一危 襴 ] 觚 企Ν蟇磯, [一危 襴] 朱 企, 蟲[OLE DB 覲] 企 螻, ろ襴渚碁ゼ ろ 一危磯伎るゼ , 螳 ク讌.
scd01.jpg

[襴 覲蟆 谿] 企 螻, 蠏碁手骸 螳 郁屋.
scd02.jpg

[襴 覲蟆 谿] 觚企Ν 襷覯襯 企. [れ] 企Ν.
scd03.jpg

[谿 企 覦 ] 螻 蠏碁手骸 螳 谿 企 螻, レ願骸 ク讌.
scd04.jpg

[襴 覲蟆 谿 ] 螻 螳 ク讌. (轟 覓願 讌 る 谿 覦襦 覦 蠏碁Μ襯 譟磯 企Ν讌)
scd05.jpg

[螻 覦 覲蟆 轟 旧] 螻 轟煙 磯 ク讌.(蠍一 覈 豌危.)
scd06.jpg

[蠍磯 轟 旧] 螳 ク讌. ろ碁ゼ 螳 れ蠍 伎 企 襷れ朱襦 蟇 螻螻燕. (襷 語覿 語蟾讌 螳 一讌螳 譴る 谿 企 り襯 覲蟆渚伎 . )
scd07.jpg

覦 蟇碁れ 危危 [豢 谿 襷企] 螻企. 螳 れ螻, [れ] 企Ν.
scd08.jpg

れ 覈 朱襦 [襷豺] 企Ν.
scd09.jpg

襷覯螳 螳 襷れ 譴 蟆企.
scd10.jpg

願 危危蠍 伎 螳螳 螻 れ 襷覯 企至 れ讌 覲 螳 . 豢襷企 蟆曙磯 れ螻 螳 SQL覓語 ろ 蟆企. (螳螳 螻襯 企Ν讌 企慨.)

UPDATE [dbo].[dim_customer] 
SET [cust_name] = ?,
[family_name] = ?,
[gender] = ?,
[home_addr] = ?,
[inferred_member_yn] = '0' --- 譯朱
WHERE [keys] = ? AND [inferred_member_yn] = '1' --- 譯朱

谿瑚: 襷 れ螻 螳 蠍一螻 . (豌願 譟一襷 企蟆 企給る <^)
豢 谿 覃る 襯 豢 覃る襯 蠍 旧 讌 給. 豢 覃る 企 讌 襦讌 谿 覃る襯 谿語^ 譟伎. 豢 覃る 一危郁 襦覃 貊襯 襷 蠍一ヾ 貊襯 一危誤 給.

.. 伎 SSIS れ襯 襷れ. 伎 企至 讌 ろ碁ゼ 企慨襦 . れ SQL 襴1,2,3, 襦 襴り 覃 customer, dim_customer 企 誤.

3 襴 ろ #

襴1: 豕襦 襦, -> 蟆朱 一危 豢豢
  1. SSIS れ
  2. 谿轟
    • 螻轟: cust_name, family_name
    • 覲蟆渚轟: gender
    • 蠍磯轟: home_addr
scd11.jpg

襴2: 螻螳 豢螳.
  1. insert customer values(2, 'han', 'ro sa', 1, ' 蟇一蠍郁規 蠍一蠍磯', 0);
  2. SSIS れ
scd12.jpg

襴3: 襦螳 伎襯 . (customer.keys = 2)
  1. update customer set home_addr = ' 蟯蟲 襴1' where keys = 2
  2. SSIS れ
scd13.jpg

襴4: 襦螳 煙 (gender) 覦.
  1. update customer set gender = 0 where keys = 2
  2. SSIS れ
scd14.jpg

襴5: 企 煙 螻, 覿一朱 伎.
  1. update customer set gender = 0, home_addr = '覿一蟲譴' where keys = 1
  2. SSIS れ
scd15.jpg

襴6: dim_customer keys 螳 1, 2 企蟆 2螳 襷 譟伎. 讌襷 fact 企 襦企慨 keys = 3 一危郁 譟伎 蟆 誤. れ螻 螳 .
scd16.jpg
(蟯螻螳 蟇碁れ讌 襷 螳ロ 襴る. 螻旧朱 蟯螻襯 蟆 襷 蟆螻 螳.)

  1. insert dbo.dim_customer (keys, family_name, cust_name, gender, home_addr, current_yn, inferred_member_yn) values(3, 'kim', 'tae hun', 1, '蟆所鍵譯殊覓伎', 1); --fact襯 襦覃伎 dbo.dim_customer 螳襦 1 l伎が.
scd17.jpg

  1. insert customer values(3, 'kim', 'tae hun', 1, '蟆所鍵譯殊覓伎', 1); --伎 覲(customer) ル.
  2. SSIS れ
scd18.jpg

4 SQL Server 2008 Merge襯 伎 SCD2 #


-- り 襷蟇 螳.
drop table #source
drop table #target

create table #source (id int, gb int)
insert #source values(1, 1)
insert #source values(2, 2)

create table #target(seq int identity(1,1), id int, gb int, bdt date, edt date)

--豐蠍一
declare 
	@begin_dt date
	
if not exists (select * from #target)
	set @begin_dt = '20000101'
else 
	set @begin_dt = getdate()			

merge #target a
	using #source b
		on a.id = b.id
		and a.gb = b.gb
	when matched and a.edt = '99991231' and a.id = b.id then
		update set
			a.edt = @begin_dt
	when not matched by target then
		insert(id, gb, bdt, edt) 
		values(b.id, b.gb, @begin_dt, '99991231')
output $action, inserted.*, deleted.*; 


			
--id=1 gb螳 4襦 覲蟆暑る?
update #source
set gb = 4
where id = 1	

/*
select * from #source

id	gb
1	4
2	2
*/	

declare 
	@begin_dt2 date
	
if not exists (select * from #target)
	set @begin_dt2 = '20000101'
else 
	set @begin_dt2 = getdate()			

merge #target a
	using #source b
		on a.id = b.id
		and a.gb = b.gb
		and a.edt = '99991231'
	when not matched by target then
		insert(id, gb, bdt, edt) 
		values(b.id, b.gb, @begin_dt2, '99991231')
	when not matched by source and a.edt = '99991231' then
		update set
			a.edt = @begin_dt2
output $action, inserted.*, deleted.*; 


/*
select * from #target

seq	id	gb	bdt	edt
1	1	1	2000-01-01	2010-11-05
2	2	2	2000-01-01	9999-12-31
3	1	4	2010-11-05	9999-12-31
*/


--id=1 gb螳 5襦 覲蟆暑る?
update #source
set gb = 5
where id = 1	


declare 
	@begin_dt3 date
	
if not exists (select * from #target)
	set @begin_dt3 = '20000101'
else 
	set @begin_dt3 = getdate()			

merge #target a --襷 minimal logging 螻 苦朱.. with (TABLOCK) a  螳 企 曙 ′譴.
	using #source b
		on a.id = b.id
		and a.gb = b.gb
		and a.edt = '99991231'
	when not matched by target then
		insert(id, gb, bdt, edt) 
		values(b.id, b.gb, @begin_dt3, '99991231')
	when not matched by source and a.edt = '99991231' then
		update set
			a.edt = @begin_dt3
output $action, inserted.*, deleted.*; 

/*
select * from #target

seq	id	gb	bdt	edt
1	1	1	2000-01-01	2010-11-05
2	2	2	2000-01-01	9999-12-31
3	1	4	2010-11-05	2010-11-05
4	1	5	2010-11-05	9999-12-31
*/

5 谿瑚襭 #