다운로드 Oracle Database 10g Release 2 (10.2) Enterprise Edition Oracle Warehouse Builder 10g Release 2 (10.2) 샘플 데이터 (오라클 익스포트 파일)

TAGS bi, datawarehousing, 10g, All

Data Profiling and Automated Cleansing Using Oracle Warehouse Builder 10g Release 2를 이용한 데이터 프로파일링 및 자동 정제

Oracle Warehouse Builder 10g Release 2를 이용하여 비주얼 프로파일을 작성하고 데이터 웨어하우스의 데이터를 자동으로 수정하는 방법을 배워 보십시오.

게시일: 2006년 9월

오늘날 대부분의 기업들이 통합적, 안정적이고 일관성 있는 "단일화된 정보원(single source of truth)"를 사용자들에게 제공하기 위한 목적에서 데이터 웨어하우스를 구현하고 있습니다. 데이터는 다수의 시스템으로부터 추출되며 따라서 따라서 사용자가 원하는 정보를 추출, 정제, 통합하는 과정이 필요합니다.

하지만 데이터 웨어하우스에 로드되는 데이터의 품질은 천차만별이며, 소스 데이터의 프로파일링에 많은 시간의 수작업이 필요한 경우가 대부분입니다. 이를 위해 SQL*Plus에 대한 심층적인 지식이 필요할 뿐 아니라 고가의 써드 파티 툴을 구입해야 할 수도 있습니다.

Oracle Warehouse Builder 10g Release 2는 데이터 프로파일링 기능을 기본적으로 제공할 뿐 아니라 SQL*PLus에 대한 지식을 전혀 요구하지 않습니다. 또 Oracle Warehouse Builder를 통해 구현한 데이터 프로파일을 이용하여 데이터를 자동적으로 수정하는 것 또한 가능합니다. 본 문서에서는 이 중요한 신기능에 대해 상세히 살펴보기로 하겠습니다.

Oracle Warehouse Builder 10g를 이용한 데이터 프로파일링 및 수정

데이터 웨어하우스에 저장된 데이터는, 그 일관성과 안정성이 보장되는 경우에만 가치 있는 정보로서 활용될 수 있습니다. 데이터 웨어하우스에 데이터를 가져오려면, 먼저 그 구조와 데이터의 의미를 이해하고 품질을 평가한 뒤 어떤 정제, 변환 작업을 수행해야 하는지 파악해야 합니다. 어떤 작업이 필요한지 파악한 다음에는, 데이터에 대한 수정 작업을 진행하고 또 향후 로드 작업 과정에서 발생할 수 있는 에러를 감지, 해결할 수 있는 방안을 마련해야 합니다. Oracle Warehouse Builder 10g Release 2는 이러한 작업을 쉽고 간단하게 수행할 수 있게 하는 세 가지 새로운 기능을 제공하고 있습니다.

1. Graphical Data Profiler—데이터에 존재하는 구조, 시맨틱, 컨텐트, 이상 현상(anomaly), 외부 데이터(outlier) 등을 이해하고, 데이터 웨어하우스에서 활용할 수 있는 데이터 룰을 생성하는 기능을 지원합니다.
2. Correction Wizard—데이터 룰을 데이터에 적용하고, 데이터의 정제 및 변환을 위한 매핑 프로세스를 자동으로 생성해 줍니다.
3. Data Auditor—데이터 룰을 기준으로 향후 데이터 로드 작업의 품질을 모니터합니다.

Oracle Warehouse Builder는 복잡한 SQL*Plus 스크립트 또는 써드 파티 툴을 이용하지 않고도 데이터 프로파일링 및 수정 작업을 수행할 수 있다는 장점 이외에도 여러 가지 중요한 이점을 제공합니다. 데이터 품질에 관련하여 생성되는 메타데이터는 다른 메타데이터와 함께 디자인 리포지토리에 저장됩니다. 또 데이터 수정을 위해 사용되는 매핑은 정규 Oracle Warehouse Builder 매핑으로, 다른 ETL(extract, transform, load) 프로세스와 함께 모니터, 관리될 수 있습니다. Oracle Warehouse Builder를 이용한 데이터 정제, 프로파일링 환경을 구현하는 경우 단 하나의 도구 사용법만 익히는 것으로 충분할 뿐 아니라, 다른 ETL 작업과 이 프로세스를 통합함으로써 데이터 품질과 데이터 정제 작업이 데이터 웨어하우스 구현 프로세스의 핵심 과정으로 실행됨을 보장할 수 있습니다.

Oracle Warehouse Builder를 처음 접하거나 툴의 초기 버전만을 사용해 본 사용자라면, 최신 버전의 패키징, 라이센싱 방법이 변경되었음을 참고하시기 바랍니다. 향후 Oracle Warehouse Builder의 "코어 ETL" 기능(주로 툴의 이전 버전에서 제공되던 기능들)은 데이터베이스 라이센스의 일부로서 무료로 제공될 예정입니다. 또 엔터프라이즈 환경을 지원하는 추가적인 기능들은 Oracle Database Enterprise Edition의 옵션으로서 제공됩니다. 본 문서에서 설명되는 기능들을 이용하기 위해서는 Oracle Database 10g의 Warehouse Builder Data Quality Option 라이센스가 필요합니다. (보다 자세한 정보는 OTN의 “Oracle Database 라이센싱 문서” 를 참고하시기 바랍니다.)

Oracle Warehouse Builder 10g Release 2를 이용하여 데이터의 프로파일링, 수정, 감사 작업을 수행하는 방법을 설명하기 위해, 데이터 웨어하우스에 저장된 제품 데이터의 프로파일링, 정제 작업이 필요한 상황을 예로 들어 보겠습니다.

제품 데이터의 프로파일링 및 정제

이 예제에서는 Oracle Warehouse Builder 10g Release 2를 이용하여 일차적으로 프로파일링을 수행하고, 그런 다음 데이터의 수정 작업을 진행합니다. 이를 위해, Oracle Warehouse Builder의 데이터 프로파일링 기능을 이용하여 데이터의 구조와 특성을 이해하고, 데이터에 적용하게 될 데이터 룰을 자동으로 추출하게 될 것입니다. 그런 다음, 이 데이터 룰을 이용하여 소스 테이블에 대한 일련의 데이터 수정 작업을 자동으로 수행함으로써 수정된 테이블을 얻게 됩니다.

프로파일링 대상이 되는 데이터는 PRODUCT라는 이름의 테이블에 저장되어 있습니다. 테이블은 아래와 같이 정의되었습니다.

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Nov 11 15:52:17 2005

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> desc products
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PROD_ID                                            NUMBER
 PROD_NAME                                          VARCHAR2(50)
 PACK_COUNT                                         VARCHAR2(10)
 AVAILABLE_DATE                                     DATE
 MARKET_SEGMENT                                     VARCHAR2(50)
 MANUF_COUNTRY                                      VARCHAR2(50)
 REORDER_YN                                         VARCHAR2(1)

샘플 코드 다운로드페이지에서 제공되는 오라클 익스포트 파일을 이용하여 샘플 데이터를 설치하실 수 있습니다. 데이터의 가져오기를 위해 데이터 소유할 사용자(예: PRODUCT_CATALOG)를 생성하고, 사용자 권한을 설정하고, 아래와 같이 데이터 가져오기를 실행합니다.

Imp product_catalog/password@connect_string 
file=product_catalog.dmp fromuser=product_catalog 
touser=product_catalog

Oracle Warehouse Builder를 이용하여 테이블의 데이터를 조회할 수 있습니다.

데이터에 몇 가지 문제가 있음을 확인할 수 있습니다. 먼저, "market segment" 중 하나의 철자가 잘못되어 있습니다. 또 "manufacturing country" 중 하나가 "UK"가 아닌 "England"로 잘못 입력되어 있습니다. 이제 Oracle Warehouse Builder 10g Release 2를 이용한 프로파일링 및 정제 작업을 수행해 보기로 하겠습니다.

데이터 프로파일의 생성

Oracle Warehouse Builder를 이용하여 프로젝트를 생성하고 PRODUCT 테이블을 위한 메타데이터의 가져오기를 완료한 다음, Project Explorer의 Data Profiles 노드를 마우스 오른쪽 버튼으로 클릭하고 New… 를 선택하여 Data Profiler 마법사를 시작합니다. 마법사에서 프로파일링 대상으로 PRODUCT 테이블을 선택하고 비동기식 처리를 위한 프로파일링 작업을 설정하고 나면, 아래와 같이 Data Profile Editor가 표시됩니다. 데이터 프로파일을 처음으로 생성한 경우라면, Oracle Warehouse Builder는 결과를 저장할 프로파일 모듈을 새로 생성할 것을 사용자에게 요구합니다. 프로파일링 대상이 되는 데이터와는 다른 위치에 저장하고자 하는 경우, 데이터베이스 사용자와 테이블스페이스를 지정하면 됩니다.

Data Profile Editor는 몇 가지 패널을 제공합니다. 패널을 통해 프로파일 작업이 완료된 오브젝트와 프로파일링 작업 결과를 확인할 수 있습니다.

Data Profile Editor의 왼쪽에는 프로파일링 작업이 완료된 오브젝트(테이블, 뷰, Materialized View, 외부 테이블, 디멘션, 팩트 등)들과 생성된 모듈들에 대한 상세 정보를 표시하는 패널이 위치합니다. 프로파일링 된 오브젝트와 수정된 모듈의 목록 아래에는 데이터 프로파일과 관련된 속성의 목록이 표시됩니다. 이 속성 목록을 이용하여 데이터 프로파일을 위한 매개변수들을 세부적으로 설정할 수 있습니다. 특정 데이터 프로파일링 컴포넌트를 활성화 또는 비활성화하거나 특정 테이블에 대한 데이터 룰 프로파일링을 활성화하는 것이 가능합니다. 가장 하단 Monitor Panel이 위치합니다. 이 패널은 프로파일링 작업의 현재 진행 상황을 표시하고 있습니다. 데이터 프로파일링 작업은 완료하는데 많은 시간이 걸릴 수 있으며, 작업을 백그라운드에서 실행하면서 다른 Oracle Warehouse Builder 작업을 수행하는 것이 가능합니다. Oracle Warehouse Builder는 작업이 완료되면 자동으로 메시지를 표시합니다.

Data Profile Editor의 오른쪽에는 데이터 프로파일 작업의 결과를 표시하는 일련의 패널들이 표시됩니다. 가장 상단의 패널은 프로파일링 결과를 요약한 탭들이 표시되는 Profile Results Canvas를 위해 사용됩니다.

Data Type 탭은 오브젝트의 각 컬럼에 대한 데이터 타입, 최소/최대 길이 등의 상세 정보를 보여 주고 있습니다.

Unique Key 탭은 유니크 키 또는 프라이머리 키 제약 조건이 사용되는 컬럼을 보여 주고 있습니다. 또 유니크하지 않은 일부 키를 삭제 또는 수정하는 경우 유니크 키로 지정할 수 있을 만큼 충분한 수의 유니크 값을 포함하고 있는 컬럼들도 함께 표시됩니다. 각 컬럼의 Six Sigma 점수를 주목하시기 바랍니다. 이 점수는 1에서 7사이의 값으로 표시되며, 오브젝트에서 유니크 제약조건을 위반하는 로우의 수를 "1,000개 당 결함 수"로서 보여 주고 있습니다.

Profile Object 탭은 프로파일링 된 오브젝트 내의 레코드를 쿼리하는데 사용됩니다. SQL*Plus 대신 Where Clause, Execute Query, Get More 등의 버튼을 이용하여 오브젝트 내의 데이터를 조회할 수 있습니다.

Domain 탭은 도메인에서 프로파일링 된 오브젝트의 각 컬럼과, 데이터가 도메인의 규칙을 준수하는 수준을 보여줍니다. 도메인(domain)은 컬럼에 허용되는 일련의 값들로 정의되며, Oracle Warehouse Builder는 적어도 2번 이상 사용된 컬럼 값을 모두 도메인에 포함시킵니다.

특정 컬럼에 대해 도메인을 정의했다면, 이 도메인을 이용하여 데이터에 적용할 데이터 룰을 추출할 수 있습니다. Oracle Warehouse Builder는 컬럼에 대한 제약 조건으로서 데이터 룰을 구현하고, 데이터베이스 레벨에서 데이터 룰의 준수를 보장합니다. 데이터 룰을 위반하는 컬럼 값이 발견되는 경우, Oracle Warehouse Builder에서 데이터를 자동으로 수정하도록 설정할 수도 있습니다.

데이터 수정 작업의 생성

PRODUCTS 테이블의 데이터 프로파일을 확인하는 과정에서 Oracle Warehouse Builder는 Canada, USA, UK 등의 값을 포함하는 MANUF_COUNTRY 컬럼의 도메인을 자동으로 제시합니다. 이 국가들은 데이터셋에서 2회 이상 사용되고 있기 때문에 추천 도메인 값에 기본적으로 포함됩니다. England와 Mexico는 한 번씩 밖에 사용되지 않았기 때문에 추천되는 값에서 제외됩니다.

비즈니스 사용자들과의 인터뷰 과정에서, 관리자는 이 컬럼의 도메인이 Not Known, Canada, USA, UK, Mexico로 설정되어야 한다는 사실을 알게 됩니다. 또 England로 표시된 값은 모두 UK로 수정되어야 합니다. 이제 관리자는 Oracle Warehouse Builder를 이용하여 입력되는 소스 데이터를 수정하고 데이터 웨어하우스의 테이블 사본에 도메인을 적용하기 위한 제약 조건을 설정할 수 있습니다.

이를 위한 작업의 첫 단계로, 제시된 도메인의 하단에 위치한 Derive Data Rule 버튼을 눌러 마법사를 실행합니다. 마법사는 제시된 도메인을 검토하고, 필요한 수정을 가하고, 데이터 룰을 생성하는 전체 과정을 안내합니다. 예제에서는 먼저 “Mexico”를 Selected values 목록으로 옮기고, 목록 하단에 “Not Known”을 추가해 줍니다.

이것으로 데이터 룰을 생성하는 작업을 완료했습니다. 다음으로 수정(correction) 작업을 생성할 차례입니다. 수정 작업을 생성하는 과정에서, Oracle Warehouse Builder는 먼저 기존 타겟 모듈을 선택하거나 올바른 버전의 오브젝트를 포함하는 새로운 모듈을 생성할 것을 요구합니다. 새로운 모듈은 기본적으로 기존 오브젝트와 동일한 정의를 갖지만, 추가적으로 적용된 새로운 데이터 룰을 사용한다는 점에서 차이가 있습니다(데이터 룰은 제약 조건, 유니크 키 등을 이용하여 구현합니다).

타겟 모듈이 결정되면, 다음 단계에서는 수정 작업을 생성하기 위해 어떤 데이터 룰을 사용할 것인지 선택해야 합니다(아래 참조). 예제에서는 앞에서 정의된 데이터 룰과, 또 별도로 정의된 두 개의 룰(REORDER_YN 값이 "N'인 모든 레코드를 제외하는 룰과 MARKET_SEGMENT 테이블의 모든 값이 "Economy" 또는 "Executive" 중 하나의 값을 가짐을 보장하는 룰)을 사용합니다.

이것으로 데이터 수정 작업을 위한 룰의 설정을 완료하였습니다. 다음으로는 액션(action)과 수정 작업을 위한 정제(cleanse) 전략을 설정해야 합니다.

액션을 설정할 때 선택할 수 있는 옵션이 다음 과 같습니다.

• Ignore—어떤 조치도 취하지 않습니다.
• Report—테이블의 ERR$$$ 컬럼에 데이터 룰의 위반 로그를 저장합니다.
• Cleanse—데이터 룰을 위반하는 데이터를 수정하기 위한 데이터 정제 전략을 적용합니다.

데이터 정제 대상으로 선택한 컬럼에 대해 아래의 4가지 정제 전략 중 하나를 적용할 수 있습니다.

• Remove—오브젝트에서 데이터 룰을 위반하는 로우를 제외시킵니다. 예제에서는 REORDER_YN = “Y” 룰을 위반하는 로우에 대해 이 룰을 사용하게 됩니다.
• Similarity Match—Oracle Warehouse Builder에 내장된 Match-Merge 기능을 사용하여, 오류가 있는 값을 컬럼 도메인과 가장 일치하는 값으로 대체합니다.
• Soundex Match—SOUNDEX 함수를 사용하여 오류 값을 발음상 가장 일치하는 도메인 내의 값으로 대체합니다. 이 전략은 예제의 MARKET_SEGMENT 컬럼의 정제를 위해 사용됩니다.
• Custom—컬럼 정제를 위한 로직이 좀 더 복잡한 경우, 즉 다른 PL/SQL 함수가 사용되거나, 조건부 로직이 사용되거나, 커스텀 PL/SQL 함수의 생성이 필요한 경우에 사용됩니다. 예제에서는 MANUF_COUNTRY 컬럼의 정제를 위해 이 전략이 사용됩니다.

정제 액션과 전략의 선택을 완료하고 나면, 마법사는 사용자가 선택한 값을 이용하여 타겟 모듈 내에 수정 작업을 구현하는 매핑을 생성합니다.

이제 Design Center로 돌아오면 수정된 데이터를 포함하는 새로운 타겟 모듈이 생성된 것을 확인할 수 있습니다. 모듈 내에서, 수정된 데이터를 포함하는 테이블 정의와 데이터 수정 작업을 구현한 매핑 및 전환이 구현되어 있음을 확인할 수 있을 것입니다.

수정 작업을 구현한 매핑을 검토해 보면, 먼저 기존 PROUDUCT 테이블에서 데이터를 읽어 온 후 테이블의 스테이징용 사본에 로드한 후 데이터 룰을 적용하고 있음을 알 수 있습니다.

데이터 룰을 만족하는 로우들은 수정된 테이블로 복사됩니다. 룰을 위반하는 로우들은 "Pluggable Mapping"을 통해 정제됩니다. 이 기능은 Oracle Warehouse Builder 10g Release 2에서 새로이 제공되는 것으로 일련의 매핑 단계들을 다른 하나의 매핑으로 "플러그인" 처리할 수 있게 합니다. 사용자는 Pluggable Mapping의 컨텐트를 통해 그 구현 방식을 파악할 수 있습니다. 하지만 이 매핑을 수정하기 위해서는 (현재로서는) 매핑을 삭제하고 다시 생성하는 방법 밖에는 없습니다.

다시 Project Explorer로 돌아가 보면, Correction Wizard가 생성한 두 개의 변환 작업을 확인할 수 있습니다. 먼저 CUS_MANUF_COUNTRY 함수는 커스텀 정제 로직을 구현하는 "쉘(shell)" PL/SQL 함수입니다. 두 번째로 마법사가 자동으로 구현한 SIM_MARKET_SEGMENT 함수가 있습니다.

커스텀 정제 액션을 구현하는 프로그램 로직을 추가하려면 CUS_MANUF_COUNTRY 함수를 더블 클릭한 다음 필요한 로직을 추가하면 됩니다.

마지막으로, 데이터 수정 작업을 테스트하기 위해 Control Center를 통해 수정 오브젝트, 변환, 매핑을 적용하고 수정 매핑(correction mapping)을 실행합니다.

이제 수정된 PRODUCT 테이블의 컨텐트를 검토하고 기대한 결과를 얻었는지 확인합니다.

모든 MARKET_SEGEMENTS 컬럼이 "Executive" 또는 "Economy" 중 하나의 값을 가지고 있으며, REORDER_YN 컬럼이 "N'인 로우가 제거되었으며, MANUF_COUNTRY의 "England" 값은 "UK"로 수정되었습니다.

이것으로 Oracle Warehouse Builder을 이용하여 생성한 수정 작업이 정상적으로 동작함을 확인하였습니다. 마지막으로 Oracle Warehouse Builder의 Data Auditor를 설정하여 향후 입력되는 데이터의 품질을 모니터하도록 합니다.

Data Auditor는 임의적으로 실행될 수도 있고, 또는 하나의 프로세스 플로우 내에서 매핑, 서브프로세스, 변환 작업에 포함되어 스케줄 기반으로 실행될 수도 있는 프로세스입니다. Data Auditor는 자동으로 추출하거나 수동으로 정의한 데이터 룰을 이용하며, 데이터의 품질에 대한 통계 리포트를 제공하고 이를 감사 및 에러 로깅을 위한 테이블에 저장합니다. Data Auditor는 프로그램 가능합니다. 예를 들어 품질이 임계치보다 낮은 경우 경고를 발생시키거나, 관리자의 승인 하에 데이터 정제를 위한 매핑을 실행할 수 있습니다. 정제 매핑의 실행이 완료된 다음에는 Data Auditor의 프로그래밍을 통해 감사 결과가 일정 수준의 Six Sigma 점수를 만족하는 경우에만 나머지 ETL 프로세스를 실행하도록 할 수 있습니다. 이와 같이 하여 데이터 웨어하우스에 낮은 품질의 데이터를 로드하고 이를 제거하는 수고를 덜 수 있습니다.

결론

Oracle Warehouse Builder는 데이터 웨어하우스에 저장된 데이터 품질의 프로파일링, 수정, 모니터링 과정을 한 차원 개선해 주는 솔루션입니다. Oracle Warehouse Builder의 그래픽 데이터 프로파일링 기능을 이용하여 솔루션이 제공하는 기능들을 쉽고 편리하게 활용할 수 있습니다. 이 기능을 통해 데이터의 구조, 의미, 품질을 확인하고 Correction Wizard를 이용하여 프로파일 결과를 적용하여 데이터 수정을 위한 자동 매핑을 생성하는 것이 가능합니다. 관리자는 Data Profiler를 이용하여 데이터의 품질을 확인한 후, 생성된 데이터 룰을 이용하여 Data Auditor를 생성하여 데이터 웨어하우스에 입력되는 신규 데이터의 품질을 정기적으로 모니터할 수 있습니다.