기본 강좌 33 - 조회 변환

조회변환.dtsx

조회 변환

 

입력 데이터에 대해 코드 테이블 또는 디멘젼 테이블과 같은 참조 테이블의 정보를 조회할 때 사용할 수 있는 변환입니다. 조회 변환은 SQL 2000 DTS의 Data Pump 작업이나 데이터 기반 쿼리 작업 에서 이미 있었던 기능이지만, SQL 2005 SSIS에서는 캐싱 옵션 설정이나 간단한 설정 방법 등 많은 점이 개선되었습니다.

 

다음과 같은 작업을 고려해 봅시다.

 

 

입력 데이터의 CustomerID를 이용하여 조회 데이터 테이블인 Sales.Customer 에서 CustomerID 열을 이용하여 AccountNumber 값을 찾아내어 결합하여 출력하는 형태의 작업입니다. 위의 그림과 같이 조회 변환은 조인 연산(INNER JOIN 또는 OUTER JOIN)과 유사합니다. 실제로, DW(Data Warehouse)환경에서 팩트 테이블과 디멘젼 테이블 간의 조인 작업 시 SQL문의 조인 연산이나 SSIS의 병합 조인 변환 대신, 조회 변환을 많이 사용합니다.

조회 변환이 조인 연산과 다른 점은 다음과 같습니다.

 

①       조회 데이터를 메모리에 캐싱 시킨 후 처리할 수 있습니다. 캐싱 방법도 전체 캐싱 및 부분 캐싱 등으로 설정할 수 있으며, 캐싱을 통해 조회 성능이 매우 향상될 수 있습니다.

②       입력 데이터에는 있는 값이 조회 데이터에 없는 경우에 대해 효율적으로 처리할 수 있습니다. 예를 들어 입력 데이터에서 999999라는 CustomerID 값이 있지만, 조회 테이블에는 이 값이 없는 경우, INNER JOIN 연산에서는 해당 원본의 값이 제외되어 처리가 되며, LEFT OUTER JOIN 의 경우에는 NULL로 결과가 출력됩니다. 조회 변환에서는 에러로 처리가 되며, 이러한 에러 데이터는 조회 변환 내의 오류 출력을 이용하여 다른 경로로 출력하여 처리할 수 있습니다.

③       하나의 입력 데이터 열에 두 개 이상의 조회 데이터가 매핑 되는 경우, 별도의 중복 제거 작업이 필요하지 않습니다. 예를 들어 입력 데이터의 676이라는 CustomerID 값이 조회 데이터에 두 개 이상 존재할 때, INNER JOIN 이나 LEFT OUTER JOIN 연산 모두 각각의 데이터에 대해 연산을 수행합니다. 결과적으로 입력 데이터의 수보다 출력 데이터의 수가 많아지게 되는 오류가 발생합니다. 조회 변환을 이용하면, 조회 데이터의 항목이 여러 개이더라도, 자동으로 처음 매핑이 되는 값만 반환합니다. DW 환경에서 디멘젼을 잘못 관리하여 발생할 수 있는 중복 데이터 문제 등을 제거할 수 있습니다.

 

조회 변환 편집기는 세 개의 탭으로 구성되어 있습니다.

 

참조 테이블 탭에서는 조회 테이블을 설정하게 됩니다. OLE DB 연결을 이용하여 테이블 또는 쿼리를 지정합니다. 직접 테이블을 지정할 수 있지만, 가급적 필요한 열만을 포함하는 SQL 쿼리로 작성하시기 바랍니다.

 

 

열 탭에서는 입력 데이터와 참조 데이터 간의 연결을 설정하며, 참조 데이터에서 출력할 열을 지정합니다.

 

 

고급 탭에서는 참조 테이블의 캐싱을 설정합니다.

 

 

①      메모리 제한 사용(M)을 체크하지 않으면 전체 캐싱으로 설정되어 참조 테이블 전체가 메모리에 캐싱됩니다.

②      메모리 제한 사용(M)을 체크한 후, 캐싱 사용(C)을 체크하지 않으면 캐싱을 하지 않습니다.

③      메모리 제한 사용(M)을 체크한 후, 캐싱 사용(C)을 체크하고, 캐시 크기를 설정하면 부분 캐싱을 수행합니다.

 

SQL 문 캐싱(S)을 설정하면, 참조 테이블 전체를 캐싱하는 대신 참조 테이블에서 조회 되는 데이터에 대해서만 캐싱 되도록 설정합니다. 이 때, 매개 변수를 이용한 SQL 문이 사용되며, 이 SQL 문 또한 캐싱이 되어 수행됩니다. 조회 테이블이 상당히 큰 경우에는 이 옵션을 사용하는 것이 전체 테이블을 캐싱하는 것에 비해 더 유리할 수 있습니다.

 

 

 

  기본적인 조회를 하면 inner join이 되는 것 같습니다. left join으로 할려고 하면, 어디서 설정을 하나요?
첫번째 그림의 AccountNumber가 NULL 이 나오는 것을 handling 하고자 합니다


->AccountNumber가 NULL로 나오는 것은 조회가 안되어 오류로 출력됩니다.
다음 예제에서 조회 오류에 대한 처리 방법을 참고하세요~^^
http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005SSIS&intSeq=682