LookupDemo.dtsx이번 글에서는 SSIS의 여러 다양한 기능 들 중에서 DW 환경에서 많이 이용될 수 있는 작업으로 조회 변환 작업(Lookup)에 대한 간단한 예를 설명하겠습니다. 아래의 [따라하기]를 수행하기 위해서는 먼저 사전 테이블을 만들어야 하기 때문에, 본문 아래쪽에 실린 테이블 생성 스크립트를 먼저 수행해야 합니다.
따라하기
1. [연결 관리자]에서 OLE DB 연결을 하나 추가 – 이미 작업을 위해 만들어 놓은 TempDB로 데이터베이스를 선택합니다.
2. [제어 흐름 영역]에 데이터 흐름 작업을 추가 – [도구 상자]에서 데이터 흐름 작업을 선택한 후, 드래그 해서 작업 영역에 갖다 놓습니다.
3. 데이터 흐름 영역에서 OLE DB 원본을 추가한 후, 이미 만들어 놓은 데이터 원본 테이블인 SourceTable을 선택합니다.
4. [도구 상자]의 [데이터 흐름 변환] 부분에서 조회 작업을 선택하여 추가한 후, OLE DB 원본과 연결시킵니다.
5. [조회 변환 작업]의 속성을 다음과 같이 지정합니다.
아래에 있는 매개 변수(P)를 눌러 다음과 같이 매개 변수를 설정합니다.
6. 오류 출력 구성을 클릭하여 다음과 같이 설정합니다.
7. 매핑된 결과가 저장될 테이블 지정 – [도구 상자]의 [데이터 흐름 대상] 부분에서 OLE DB 대상을 선택하여 추가한 후, 조회의 녹색 라인과 연결 시킵니다.
8. OLE DB 대상의 속성에서 대상 테이블을 [dbo].[TargetTable]로 지정한 후, 매핑 탭을 눌러 정상적으로 칼럼들이 매핑 되었는지 확인합니다.
9. 매핑에 실패한 결과가 저장될 테이블 지정 – [도구 상자]의 [데이터 흐름 대상] 부분에서 OLE DB 대상을 선택하여 추가한 후, 조회의 적색 라인과 연결 시킵니다.
10. OLE DB 대상1의 속성에서 대상 테이블을 [dbo].[ErrorTable]로 지정한 후, 매핑 탭을 눌러 정상적으로 칼럼들이 매핑 되었는지 확인합니다.
11. 확인을 누른 후, 실행합니다.
12. 만약, 매핑이 안된 데이터를 별도로 보관할 필요가 없다면, 6단계에서 ‘행 리디렉션’ 대신 ‘오류 무시’로 설정하고, 9, 10 단계를 생략하면 됩니다.
조회 작업의 장점
언뜻 보기에는 조인 작업과 별 차이가 없어 보일지도 모릅니다. 하지만, 다음과 같은 장점들이 있습니다.
디멘젼 테이블 역할을 하는 조회 테이블을 메모리에 캐싱을 시켜서 빠른 속도로 참조 작업을 할 수 있습니다.
일반적으로, DW 환경에서는 원본 테이블에 비해 디멘젼 테이블의 사이즈가 작기 때문에 캐싱을 시킨 후, 바로 매핑 작업을 수행한다면 상당한 성능 효과를 볼 수도 있습니다. 참고로, 외국의 블로그에 실린 성능 차이를 설명하면, 2,500만 건의 테이블을 이용하여 30개의 디멘젼 테이블로 매핑을 시키는 Merge Join 작업을 SSIS의 조회 작업으로 변환한 결과, 수행 시간이 2일에서 45분으로 단축이 되었다고 합니다.
다음과 같은 기능도 생각할 수 있습니다. 만약, 원본의 데이터에 대해 1:n 으로 매핑이 되는 경우가 있을 수도 있습니다. 즉, 매핑이 되는 디멘젼 값이 고유하지 않아서, 한 개 이상의 대리키 값으로 매핑이 되는 경우, JOIN을 이용한다면 원본 데이터가 중복으로 들어가게 될 것입니다. 조회 작업을 이용한다면, 이러한 점을 막을 수도 있다..테스트 해 보기 바랍니다.
이 외에도, 앞 서 데모에서 보인 대로, 디멘젼이 매핑 안될 때 처리 방법을 지정할 수 있습니다. 에러를 발생시켜 매핑 작업을 중단시킬 수도 있으며, 에러를 무시할 수도 있고, 데모에서와 같이 별도의 대상으로 저장시킬 수도 있습니다.
따라 하기를 수행하기 위한 임시 테이블 생성 스크립트
|
/*--------------------------------------------------------------------------- SSIS 조회(Lookup)작업데모를위한사전테이블생성스크립트 -----------------------------------------------------------------------------*/ USE TEMPDB GO --데이터테이블생성및데이터입력 CREATE TABLE dbo.SourceTable ( KeyColumn VARCHAR(32), ModifyDate SMALLDATETIME ) GO --정상적으로매핑되어야할데이터 INSERT INTO dbo.SourceTable (KeyColumn, ModifyDate) VALUES ('a',' GO --디멘젼값의유효시작일이전의데이터: 매핑되서는안됨 INSERT INTO dbo.SourceTable (KeyColumn, ModifyDate) VALUES ('a',' GO --매핑되는디멘젼이없는데이터: 매핑되서는안됨 INSERT INTO dbo.SourceTable (KeyColumn, ModifyDate) VALUES ('b',' GO --매핑된데이터가저장될테이블 CREATE TABLE dbo.TargetTable ( KeyColumn VARCHAR(32), ModifyDate SMALLDATETIME, SurrogateKey INT ) GO --매핑실패한데이터가저장될테이블 CREATE TABLE dbo.ErrorTable ( KeyColumn VARCHAR(32), ModifyDate SMALLDATETIME ) GO --디멘젼테이블생성및데이터입력 CREATE TABLE dbo.LookupDimension ( KeyColumn VARCHAR(32), -- 디멘젼값 BeginDate SMALLDATETIME, -- 디멘젼값의유효시작일 ExpireDate SMALLDATETIME, -- 디멘젼값의종료일 SurrogateKey INT -- 대리키 ) GO INSERT INTO dbo.LookupDimension (KeyColumn, BeginDate, ExpireDate, SurrogateKey) VALUES ('a', ' GO --다음과같은결과가출력되어야한다 --Case1) 디멘젼으로매핑이되는경우만출력(TargetData에저장될것이다.) SELECT S.KeyColumn, S.ModifyDate, D.SurrogateKey FROM dbo.SourceTable S LEFT OUTER JOIN dbo.LookupDimension D ON S.KeyColumn = D.KeyColumn AND S.ModifyDate >= D.BeginDate AND S.ModifyDate < D.ExpireDate WHERE D.SurrogateKey IS NOT NULL GO --Case2) 디멘젼으로매핑이안되는데이터를별도로출력(ErrorData에저장될것이다.) SELECT S.KeyColumn, S.ModifyDate FROM dbo.SourceTable S LEFT OUTER JOIN dbo.LookupDimension D ON S.KeyColumn = D.KeyColumn AND S.ModifyDate >= D.BeginDate AND S.ModifyDate < D.ExpireDate WHERE D.SurrogateKey IS NULL GO |
'연구개발 > DTS & SSIS' 카테고리의 다른 글
| 기본 강좌 5 - SQL 실행 작업 (0) | 2009.06.20 |
|---|---|
| 기본 강좌 4 - 외부 컴포넌트 등록 및 Foreach 루프 컨테이너 이용하기 (0) | 2009.06.20 |
| 기본 강좌 2 - 컨테이너 이해하기 (0) | 2009.06.20 |
| [실습강좌5] SSIS 실습5 - 오류 출력 추가하기 (0) | 2009.06.20 |
| [실습강좌4] SSIS 실습4 - 로깅 추가하기 (0) | 2009.06.20 |