피봇 변환 – 데이터 행렬 변환

피봇 변환 – 데이터 행렬 변환

 

안녕하세요 강성욱 입니다.(www.sqltag.org, www.sqler.com, http://blog.naver.com/jevida)

이번 시간에는 [피벗 변환]에 대해서 알아 보도록 하겠습니다.

 

SSIS에서 [피벗 변환]은 테이블 데이터 뿐만 아니라 다양한 데이터 형태의 입력 데이터데 대해서도 피벗 변환을 수행 할 수 있습니다.

 

[SSMS]를 실행하여 오늘 실습에 사용할 데이터를 생성 합니다.

오늘 실습은 성별에 대하여 피봇을 진행 하도록 합니다.

BEGIN TRY     drop table Member END TRY BEGIN CATCH END CATCH GO   create table Member( ID nvarchaR(100), Gender nvarchar(2), Age int, Cash int) GO   INSERT INTO Member VALUES ('A', 'M', 35, 100) INSERT INTO Member VALUES ('B', 'F', 25, 200) INSERT INTO Member VALUES ('C', 'M', 25, 100) GO

 

 

 

[BIDS를 실행]하여 [Integration Services 프로젝트]를 생성 합니다.

 

[제어 흐름] 탭에서 [데이터 흐름 태스크]를 드래그 앤 드롭으로 추가 합니다.

[피벗 변환] 이름을 지정하여 태스크를 생성 합니다.

 

[데이터 흐름]탭에서 그림과 같이 [원본 데이터(OLE DB 원본)]와 [대상 데이터(OLE DB 대상)]을 끌어다 놓습니다. 그 사이 [피벗]작업을 추가하여 데이터 흐름선을 연결 합니다.

 

[원본 데이터]에서 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다.

 

그림과 같이 편집 창이 나타나면[OLE DB연결 관리자]에서 원본 DB의 연결 관리자를 선택하고 해당 테이블을 선택하여 [미리보기]를 이용하여 정상적으로 데이터가 읽어오는지 확인 합니다.

(본 실습에서는 위에서 생성한 예제 데이터를 이용합니다.)

 

[피벗] 작업에서 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다,

 

아래 그림과 같이 [고급 피벗 편집기]창이 나타납니다.

[구성 요소 속성]탭에서는 피벗의 이름 및 LocalID를 지정할 수 있습니다. 또한 유효성 체크 유무를 변경 할 수 있습니다.

 

[입력열] 탭에서는 피벗에 사용할 입력 열을 선택 합니다.

 

[입/출력] 속성 탭에서는 입력 받은 데이터를 피벗 데이터로 출력할 것인지 설정 합니다.

이때 주의 사항이 아래 그림처럼 입력 열에 대한 [LeneageID]값이 매핑 값이 됩니다.(중요함)

또한 [PivotUsage]값에 따라 행 및 열, 값 속성으로 분류 됩니다.

PivotUsage값	설명
0	열이 피벗 연산에 참여하지 않고 바로 출력
1	행 속성을 가지는 열
2	이 열의 값이 열의 속성을 가짐.(컬럼으로 갈 속성)
3	값 속성을 가지는열.

 

실습에서는

ID : 0

Gender : 2

Age : 1

Cash : 3

으로 PivotUsage를 지정 하였습니다.

 

[출력 열]에서는 [열 추가] 버튼을 이용하여 [출력 열]을 생성 합니다.

여기서 주의할 점이 출력 열에서 나타낼 데이터의 입력열을 지정해 주어야 하는데 이때 사용하는 것이 위에서 설명한 [LeneageID]값 입니다. [SourceColumn]값에 입력 열의 [LeneageID]을 입력하면 [DataType] 타입 또한 입력열의 데이터 타입으로 자동으로 변경 됩니다.

 

오늘 실습에서는 성별에 대한 피봇을 진행 합니다. 여기서도 주의할 점이 값에 대한(PivoUsage : 3) [LeneageID]를 입력해야 합니다. 그리고 필터 조건으로 [PivotKeyValue] 항목에 (PivotUsage : 2) 성별에 대한 값을 입력해 주어야 합니다.

 

 

• ComprasionFlags – 그룹핑 작업을 수행 할 때 문자열에 대한 비교 처리 방법, 대소문자 구분이나 문자 너비, 기호 무시등의 문자열 비교 속성을 설정할 수 있습니다.
• PivotKeyValue – SQL 쿼리에서 [CASE WHEN 컬럼명 = '조건값' THEN…] 형태에서 '조건 값'에 해당하는 값을 지정합니다.
• SourceColumn – 연산을 수행할 값의 [LeneageID]값 입니다.

 

 

[대상 데이터] 작업에서 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다.

 

[OLE DB 연결 관리자]에서 대상 DB의 연결 관리자를 선택 합니다. 그리고 [새로 만들기] 버튼을 클릭하여 대상 테이블을 새로 만들어 봅니다. (이미 대상 테이블이 존재하는 경우 [테이블 또는 뷰 이름]에서 대상 테이블을 선택 합니다.)

[새로 만들기]를 선택하면 피봇 데이터 형태의 컬럼이 생성 됩니다.

 

[매핑] 탭으로 이동하여 대상이 적절히 매핑 되었는지 확인 합니다.

 

편집이 완료 되었으면 패키지를 실행 합니다.

늘 그러듯!

녹색 : 정상 실행

빨간색 : 오류 발생

 

패키지 작업이 완료 되었으면 SSMS를 실행하여 데이터를 확인 합니다.

 

데이터를 확인하면 성별에 따라 Cash 값을 피봇된 것을 확인 할 수 있습니다.

 

이 작업은 SSIS를 이용하여 여러곳에서 데이터를 가져올 때 피벗 작업을 진행하여 데이터를 적재할 때 매우 유용할 듯 합니다. (통계 쿼리 대신 사용할까 고민 중입니다.)