1 개요

2 테스트 환경 만들기

3 시나리오 테스트

4 SQL Server 2008의 Merge를 이용한 SCD2

5 참고자료

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 컬럼에는 '김재학'을 넣는 것이다. 간혹 Type2, Type3를 혼합하여 사용하는 경우도 있다. Microsoft SQL Server 2005의 SSIS에서는 Type1, Type2 그리고 '유추차원맴버'라는 것을 지원하는데(개념이 골때린다. 반나절만에 노가다로 개념을 익혔다), 그 뜻은 소스 테이블에는 차원 맴버가 존재하는데, 차원 테이블에는 차원 맴버가 존재하지 않고, 팩트 테이블에는 존재하는 것을 맴버를 말한다. 결국은SSIS는 끈적함을 명시적은 아니더라도 묵시적으로는 유지시켜주자는 생각에도 도입된 듯하다. 차원과 팩트 테이블이 끈적거리게 관계를 맺어 놓았으면 이 따위 것이 필요 없는데, 아마도 ETL과정에서 클린징과정을 거쳤으므로, 참조무결성을 DW에서 걸어 놓는다는 것이 무의미하다는 것에서 비롯된 것으로 보인다. 어찌되었건 SSIS를 이용해서 해보자.

• 운영계: 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 원본]을 끌어다 놓고, 위 스크립트를 실행한 데이터베이스를 선택하여, 아래와 같이 편집한다.

도구상자에서 [느린 변경 차원]을 끌어다 놓고, 아래의 그림과 같이 연결한다.

[느린 변경 차원]을 더블클릭하여 마법사를 띄운다. [다음]을 클릭한다.

[차원 테이블 및 키 선택] 단계에서 아래 그림과 같이 차원 테이블을 선택하고, 입력열과 키 유형을 편집한다.

[느린 변경 차원 열] 단계에서 아래와 같이 편집한다. (혹시 아무것도 나타나지 않는다면 차원 열 바로 밑의 그리드를 조낸 클릭질하라)

[고정 및 변경 특성 옵션] 단계에서 특성에 따라 편집한다.(여기서는 모두 체크한다.)

[기록 특성 옵션]에서는 아래와 같이 편집한다. 앞에서 테스트를 위해 아래와 같이 설정하기 위해서 테이블을 만들었으므로 걍 고고싱하자. (만약 언제부터 언제까지 값이 쓰였는지가 중요하다면 차원 테이블의 설계를 변경해야 한다. )

드뎌 반나절 걸려서 이해했던 [유추 차원 맴버] 단계이다. 아래와 같이 설정하고, [다음]을 클릭한다.

설정이 모두 끝나으므로 [마침]을 클릭한다.

마법사가 아래와 같이 만들어 줄 것이다.

이것을 이해하기 위해서는 각각의 단계의 작업들이 마법사에 의해 어떻게 설정되었는지 볼 필요가 있다. 유추맴버의 경우는 다음과 같은 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 테이블을 확인한다.

시나리오1: 최로 로딩, 소스 -> 타겟으로 데이터 추출

1. SSIS 패키지 수행
   
2. 차원특성
   
   • 고정특성: cust_name, family_name
     
   • 변경특성: gender
     
   • 기록특성: home_addr
     

시나리오2: 고객이 추가되었다.

1. insert customer values(2, 'han', 'ro sa', 1, '서울 거시기구 기시기동', 0);
   
2. SSIS 패키지 수행
   

시나리오3: 로사가 이사를 했다. (customer.keys = 2)

1. update customer set home_addr = '서울 관악구 신림1동' where keys = 2
   
2. SSIS 패키지 수행
   

시나리오4: 로사가 성전환수술을 하여 성(gender)이 바뀌었다.

1. update customer set gender = 0 where keys = 2
   
2. SSIS 패키지 수행
   

시나리오5: 재학이도 성전환 수술하고, 부산으로 이사했다.

1. update customer set gender = 0, home_addr = '부산중구중앙동' where keys = 1
   
2. SSIS 패키지 수행
   

시나리오6: dim_customer에는 현재 keys의 값이 1, 2 이렇게 2개의 행만 존재한다. 하지만 fact 테이블을 로딩해보니 keys = 3인 데이터가 존재하는 것을 확인하였다. 다음과 같은 상태다.

(관계가 걸려있지 않은 상태에서만 가능한 시나리오다. 공식적으로 관계를 끊는 것을 말하는 것과 같다.)

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행을 넣어줬다.
   

1. insert customer values(3, 'kim', 'tae hun', 1, '경기도파주시무슨동', 1); --이제서야 원본(customer)에 입력되었다.
   
2. SSIS 패키지 수행
   

--이 예제는 하다가 말은거 같다.

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
*/

• http://en.wikipedia.org/wiki/Slowly_changing_dimension
  
• https://www.sqlservercentral.com/articles/slowly-changing-dimensions-using-t-sql-merge