🤓 데이터 품질관리 관점의 CTQ 정의
현재 프로젝트의 업무 중 하나가 데이터 품질관리이다. 그리고 CTQ 대상을 선정해 달라는 요청이 왔다. 그런데...
🤔 CTQ가 뭐지?
핵심품질항목(CTQ, Critical To Quality)는 6 Sigma에서 유래한 용어다.
- 6 Sigma에서의 CTQ
- 기대 품질에 치명적인 영향을 주는 요인
- 예를 들어 어느 화학 공정에서 특정 프로세스의 수율이 매우 낮아 전체 수율에 큰 영향을 미치고 있다고 할 때, 이 프로세스가 개선 대상이 된다.
- 데이터 품질관리 관점에서의 CTQ
- 데이터의 품질에 치명적인 영향을 주는 요인
데이터 품질관리를 위해 CTQ를 정의함으로써 전사적으로 영향을 미칠 수 있는 핵심이 되는 항목을 선정하여 개선 활동에 자원을 집중하고, 이를 통해 데이터 자산의 가치를 향상시키는 것을 목적으로 한다.
😅 CTQ 대상 선정하기
다음과 같은 절차로 CTQ 대상 선정을 진행하였다.
- CTQ 후보 도출
- 우선순위 평가
- 타당성 검토
- CTQ 확정
절차별로 자세히 알아보자!
① CTQ 후보 도출
우선 데이터베이스에서 테이블과 컬럼의 정보를 수집했다. 현재 프로젝트에서는 ORACLE을 사용하므로 (표준화가 잘 되어있다는 가정하에) 다음과 같은 쿼리를 이용하여 후보 항목들을 추출하였다.
- 제외 항목
- 시스템 컬럼(최초등록일시, 최종수정일시 등)
- 텍스트 컬럼(내용 도메인)
- DATE, TIMESTAMP 컬럼
- 일련번호, 식별번호, 순번, ID 등
- 미사용, 임시, 백업 테이블
/* CTQ 후보 도출 */
SELECT A.COLUMN_NAME
, B.COMMENTS
, A.DATA_TYPE
, A.DATA_LENGTH
/* (기관별 수정)테이블 명명규칙 참고하여 주제영역 별 건수 카운트 */
, SUM(DECODE(SUBSTR(C.TABLE_NAME,0,3),'주제영역1',1,0)) AS 주제영역1_CNT
, SUM(DECODE(SUBSTR(C.TABLE_NAME,0,3),'주제영역2',1,0)) AS 주제영역2_CNT
, SUM(DECODE(SUBSTR(C.TABLE_NAME,0,3),'주제영역3',1,0)) AS 주제영역3_CNT
/* 해당 항목 사용 테이블 리스트 */
, LISTAGG(C.TABLE_NAME,',') WITHIN GROUP (ORDER BY C.TABLE_NAME ASC) AS TABLE_LIST
FROM ALL_TAB_COLUMNS A
, ALL_COL_COMMENTS B
, ALL_TABLES C
, ALL_TAB_COMMENTS D
WHERE 1=1
AND A.OWNER = B.OWNER
AND A.COLUMN_NAME = B.COLUMN_NAME
AND A.TABLE_NAME = B.TABLE_NAME
AND A.OWNER = C.OWNER
AND A.TABLE_NAME = C.TABLE_NAME
AND A.OWNER = D.OWNER
AND A.TABLE_NAME = D.TABLE_NAME
AND D.TABLE_TYPE = 'TABLE'
/* 스키마명 */
AND A.OWNER = '스키마명' -- (기관별 수정)스키마명 입력
/* 주제영역 */
AND SUBSTR(C.TABLE_NAME,0,3) IN ('주제영역1', '주제영역2', '주제영역3') -- (기관별 수정)테이블 명명규칙 참고하여 주제영역 특정
/* 제외조건 */
AND A.COLUMN_NAME NOT IN ('최초등록일시','최종수정일시', '삭제여부', '사용여부') -- (기관별 수정)시스템 컬럼 제외
AND NOT REGEXP_LIKE(A.COLUMN_NAME, '_CN$|_EXPL$|_RMRK$') -- (기관별 수정)텍스트 컬럼 제외
AND NOT REGEXP_LIKE(A.COLUMN_NAME, '_ID$|_SN$|_SQNO$') -- (기관별 수정)인조식별자, 번호 컬럼 제외
AND A.DATA_TYPE NOT IN ('DATE', 'TIMESTAMP') -- DATE, TIMESTAMP 타입 제외
AND C.NUM_ROWS > 0 -- 미사용 테이블 제외
AND NOT REGEXP_LIKE(C.TABLE_NAME, '^TMP_|^BAK_|_TMP$|_BAK$|_TMP_|_BAK_|[0-9]') -- (기관별 수정)백업, 임시 테이블 제외
GROUP BY A.COLUMN_NAME, B.COMMENTS, A.DATA_TYPE, A.DATA_LENGTH
ORDER BY COLUMN_NAME, COUNT(C.TABLE_NAME) DESC
;
② 우선순위 평가
우선순위 평가기준은 기관별로 다르다. 보통 시스템 관점, 업무적 관점으로 구분하여, 상/중/하로 점수를 매긴다.
- 시스템 관점: 시스템의 수치 데이터를 근거한 객관적 평가(시스템 분포, 테이블 분포, 컬럼 호출 빈도 등)
- 업무적 관점: 정보시스템 담당자 혹은 업무 담당자의 주관적 평가가 필요한 항목
시스템 관점에서의 평가는 시스템 분포, 테이블 분포, PK 사용여부, PK컬럼 수, PK 아닌 컬럼 수, Matser 테이블에서의 사용 여부 등의 세부항목 정보를 추출했다. 업무적 관점의 평가는 임의대로 진행할 수 없기에, 정보시스템 담당자에게 중요도 산정을 요청드렸다.
/* 시스템 관점 평가 */
SELECT A.COLUMN_NAME -- 컬럼 영문명
, MAX(C.COMMENTS) LCOL_NAME -- 컬럼 한글명
, COUNT(DISTINCT SUBSTR(A.TABLE_NAME,0,3)) AS SYS_CNT -- (기관별 수정)시스템(주제영역)분포수
, COUNT(*) AS COLUMN_CNT -- 테이블분포수
, MAX(CASE WHEN B.COLUMN_NAME IS NULL THEN 'N' ELSE 'Y' END) AS PK_YN -- PK사용여부(1개라도 PK)
, SUM(NVL2(B.COLUMN_NAME,1,0)) AS PK_CNT -- PK컬럼수
, SUM(NVL2(B.COLUMN_NAME,0,1)) AS NOT_PK_CNT -- PK아닌컬럼수
, MAX(DECODE(SUBSTR(A.TABLE_NAME,-1,1),'M','Y','N')) AS MST_YN -- (기관별 수정)MASTER여부
FROM ALL_TAB_COLUMNS A
, (SELECT A.OWNER
, A.TABLE_NAME
, B.COLUMN_NAME
FROM ALL_CONSTRAINTS A
, ALL_CONS_COLUMNS B
WHERE 1=1
AND A.CONSTRAINT_TYPE = 'P'
AND A.OWNER = B.OWNER
AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND A.CONSTRAINT_NAME NOT LIKE 'BIN$%'
) B
, ALL_COL_COMMENTS C
, ALL_TABLES D
, ALL_TAB_COMMENTS E
WHERE 1=1
AND A.OWNER = B.OWNER (+)
AND A.TABLE_NAME = B.TABLE_NAME (+)
AND A.COLUMN_NAME = B.COLUMN_NAME (+)
AND A.OWNER = C.OWNER (+)
AND A.TABLE_NAME = C.TABLE_NAME (+)
AND A.COLUMN_NAME = C.COLUMN_NAME (+)
AND C.COMMENTS IS NOT NULL
AND A.OWNER = D.OWNER
AND A.TABLE_NAME = D.TABLE_NAME
AND A.OWNER = E.OWNER
AND A.TABLE_NAME = E.TABLE_NAME
AND E.TABLE_TYPE = 'TABLE'
/* 스키마명 */
AND A.OWNER = 'S_MIG' -- (기관별 수정)스키마명 입력
/* 주제영역 */
AND SUBSTR(D.TABLE_NAME,0,3) IN ('주제영역1', '주제영역2', '주제영역3') -- (기관별 수정)테이블 명명규칙 참고하여 주제영역 특정
/* 제외조건 */
AND A.COLUMN_NAME NOT IN ('최초등록일시','최종수정일시', '삭제여부', '사용여부') -- (기관별 수정)시스템 컬럼 제외
AND NOT REGEXP_LIKE(A.COLUMN_NAME, '_CN$|_EXPL$|_RMRK$') -- (기관별 수정)텍스트 컬럼 제외
AND NOT REGEXP_LIKE(A.COLUMN_NAME, '_ID$|_SN$|_SQNO$') -- (기관별 수정)인조식별자, 번호 컬럼 제외
AND A.DATA_TYPE NOT IN ('DATE', 'TIMESTAMP') -- DATE, TIMESTAMP 타입 제외
AND D.NUM_ROWS > 0 -- 미사용 테이블 제외
AND NOT REGEXP_LIKE(D.TABLE_NAME, '^TMP_|^BAK_|_TMP$|_BAK$|_TMP_|_BAK_|[0-9]') -- (기관별 수정)백업, 임시 테이블 제외
GROUP BY A.COLUMN_NAME
ORDER BY 1 ASC
;
③ 타당성 평가
우선순위 평가가 완료된 후 기관에 맞는 총점 산식으로 점수를 계산했다. 그 후 결과를 바탕으로 정보시스템 담당자에게 검토를 요청했다.
- 검토 요청 내용
- 시스템 관점 평가 확인
- 측정 가능 여부 확인
- 선정(삭제) 여부 확인
④ CTQ 확정
정보시스템 담당자의 검토 후 최종적인 CTQ 항목들을 도출하였고, 해당 항목들을 데이터 품질관리 시스템에 등록하였다.
✍️ 결론
각 기관별로 중요시 하는 기준은 모두 다르다. 위에서 설명한 방법과 절차가 모든 기관에 적용할 수 있는건 아니다. 업무 담당자, 정보시스템 담당자와 충분한 커뮤니케이션 후 CTQ 정의에 대한 객관적인 평가 기준을 도출해야 할 것 같다.
Data Architect🌱