저번 시간에 이어서 Dimension테이블에 대해서 알아보겠습니다. 비즈니스를 잘 이해해야 Dimension테이블 설계를 올바르게 할 수 있으며, 차원과 연결된 Fact테이블까지 영향을 미치게 됩니다. 첫 단추를 잘 채워야 하는 것과 같은 이치 입니다. 그러므로 Dimension테이블 설계가 중요하다는 것은 더 이상 강조하지 않겠습니다.

 

01.png

 

그럼 Dimension테이블의 형태에 대해서 알아 보겠습니다.

대부분의 Dimension테이블에서 키는 정수형 기반이며, 명칭 또는 설명을 나타내는 문자형태로 되어 있으며, 질의의관점과 관련이 되어 있습니다.정수형 기반인 tinyint, int, bigint으로 키를 생성하는 것이 좋은데, 왜냐하면 Bitmap인덱스를 사용할 수 있기 때문에 성능상 이점이 있습니다. 문자열로 키를 잡는 것은 권장하지 않습니다.

데이터 형태는 비정규화로 존재하게 되며, 효율적인 쿼리에 목적이 있습니다. 또한 다양한 계층형식이 존재하는데 예를 들어 대--소 분류의 상품 카테고리가 될 수도 있으며 Recursive형태의 관계를 가질 수도 있습니다. 이러한 계층형태는 Drill-Down 또는 Drill-Up형태로 리포트를 만들 수 있습니다.

 

 

02.png

 

다차원모델링 할 때 가장 이해하기가 어려운 부분이 바로 특성 별 집계입니다.Dimension테이블을 아래와 같은 구조가 되어 있다고 가정하면,

무비키

무비명

장르

형식

제작국가

1

세 얼간이

코미디

단편영화

인도

2

타이타닉

멜로/애정/로맨스

단편영화

미국

3

그대를 사랑합니다.

드라마

단편영화

한국

4

울지마 톤즈

다큐멘터리

단편영화

한국

5

레옹

액션

단편영화

프랑스

6

트루맛쇼

다큐멘터리

단편영화

한국

장르별 영화 수를 구하는 쿼리는 다음과 같습니다.

SELECT 장르, COUNT(*)

FROM F_판매 A

INNER JOIN D_무비 B ON A.무비키 = B.무비키

GROUP BY 장르

형식별 영화 수는 다음과 같습니다.

SELECT 형식, COUNT(*)

FROM F_판매 A

INNER JOIN D_무비 B ON A.무비키 = B.무비키

GROUP BY 형식

위에 쿼리와 같이 장르별, 형식별로 분석하고자 하는 관점이 GROUP BY 절에 들어 오면 됩니다 이처럼 특성 별로 집계되어 다차원으로 분석할 수 있기 때문에, 차원에서 속성을 어떻게 구성하느냐가 분석관점에서 중요한 의미가 있는 것입니다.

Dimension테이블의 데이터 양은 Fact테이블 보다는 일반적으로 Row수가 적으나, 간혹 Big Dimension테이블(대형차원 테이블)이 생성 될 수 있는데, 성능에 영향이 있을 수 있습니다. 이러한 테이블은 Fact와 함께 축약해서 해보는 것이 성능상 이점이 있습니다. 예를 들면 우편번호를 차원 키로 하는 것 보다는 시, , 구 또는 , , 동 레벨에서 집계를 해서 Dimension테이블의 RowFact테이블의 Row 수를 줄이는 것입니다.

 

03.png

 

차원을 구성하는 것, 각각의 데이터를 차원항목(Member, Element) 라고 합니다. 성별 차원에서 차원항목은 ’, ‘가 되는 것이고 아래 그림에서 국가차원일 경우에는 미국, 일본, 중국, 한국이 차원항목이 되는 것입니다.

 

04.png

 

예를 들어 매출Fact테이블이 있다고 가정할 때, 상품Dimension테이블에서 처음에는 10개의 차원항목으로 구성한 후에, 신상품이 출시가 되어 신제품명으로 차원항목이 추가될 때, 기존 Fact테이블에는 변경이 일어나지 않고, 신규로 들어오는 데이터에 대해서만 신제품명으로 집계됩니다.

그런데 차원항목이 추가가 아니라 변경이 일어나면 어떻게 될까요? 해당 제품명으로 판매가 부진하자 마케팅 부서에서 상품명을 변경해서 판매가 이루어 지는 것입니다. 그럼 이때 명칭을 바꾸기 전과 후를 비교해야 하는데 이처럼 차원항목의 변경이력내역을 가져 갈지, 아니면 그냥 명칭만을 변경할지 고민하게 되는데, 다차원모델링에서 이러한 것을 느린변경차원(Slowly Changing Dimension, SCD)라고 합니다. 이러한 느린변경차원에는 무려 6가지의 종류가 있지만 가장 많이 사용하는 SCD Type 0 ~ Type 3까지 4가지 종류에 대해서 살펴 보도록 하겠습니다.

SCD Type

설명

비고

Type 0

변하지 않음

, / Yes, No

Type 1

데이터 업데이트

아이패드 à 뉴아이패드

Type 2

SK(Surrogate Key)를 이용하여 추가

01, 01, 아이패드 / 02, 01, 뉴아이패드

Type 3

변경전명칭 과 변경후명칭을 함께 가져감

아이패드, 뉴아이패드, 2012-01-01

SCD Type 0은 불변인 것이므로 더 이상 설명이 필요 없을 것 같습니다.

SCD Type 1은 차원항목에 데이터를 업데이트하는 것입니다. 이전 차원 명이 중요하지 않을 경우와 잘못 지정된 경우에 변경하게 됩니다.

 

 

 

05.png

 

업데이트만 하면 되므로, 간편하지만 이력관리가 되지 않으므로 상품명이 변경되었을 때 매출의 영향은 얼마나 있었는지? 와 같은 질의는 할 수 없습니다.

SCD Type 2는 대체키(Surrogate Key)가 추가 되어, 데이터의 변경하지 않고 새로 입력하는 방법입니다. 차원에 대해서 이력관리가 가능하므로, 차원 이력에 따른 질의를 할 수 있습니다.

 

06.png

 

주로 많이 사용하는 것은 Type 2 Type 1입니다. 처음에 협의가 중요합니다. 이력관리가 필요한지 필요가 없는지 잘 정해야 합니다. 나중에 변경하고자 하면 Fact테이블을 다시 쌓아야 하는 상황이 발생 할 수도 있으며,Type 2로 차원데이터를 관리 하려면 차원이관 시 1.5배의 노력이 필요 합니다. SSIS에서 SCD에 대해서 마법사를 지원하기도 하지만, 이관 시 대체 키를 관리해야 하는 어려움이 있습니다. 하지만 이러한 고통에도 차원에 대한 이력에 따른 영향도를 분석 할 수 있는 이점 또한 있습니다.

마지막 SCD Type 3에 대해서 알아 보겠습니다. 해당 SCD Type 2처럼 데이터를 추가하지 않고, 변경 전후 데이터를 한 Row에서 관리하는 것입니다.

 

07.png

 

이전과 현재를 동시에 관리함으로써, Row가 추가 되지 않으므로 대체 키를 관리하지는 않지만 차원이 많이 변경되면, 사용자에게 혼란을 줄 수 있습니다.

느린차원변경에 대해서 알아 보았습니다. 비즈니스를 잘 파악한 후에 어떠한 Type을 사용할 지 합리적으로 선택하는 것이 가장 중요합니다.

대체키(Surrogate Key)에 대해서 좀더 자세히 알아 보도록 하겠습니다. 대체키는 PK로 지정되면 인공적으로 만든 식별자라고도 할 수 있습니다. 대부분 정수 형으로 정의 됩니다.

 

08.png

 

영화 중에 써로게이트(2009) 라는 영화를 보면 사람들은 대부분 집에 누워서 뇌에서 생각하는 정신력만으로 로봇을 조정하고 자신이 할 수 있는 일을 대신 시키는 장면이 나옵니다.대체키도 마찬가지 입니다. 이러한 자연키(Natural Key) 또는 비즈니스 키(Business Key)라고 불리 우는 원본 테이블에서 Dimension테이블로 이관하면서 대체키가 생성 되는 것입니다. 대체키를 만들었을 때의 이점으로는 자연키는 복합키일 수 있다는 것입니다 그래서 하나의 정수형 기반의 키로 대체될 수 있으므로 조인시 성능을 보장할 수 있습니다. 다만 비매핑에 대한 처리가 필요할 수 있으며, 누락된 차원멤버 추가 시 Fact테이블을 재적재 또는 업데이트가 필요할 수 있습니다.

Fact테이블에서 측정값 중에 단가와 같은 값은 합계를 내어도 무의미 하고, 차원으로 만들면은 단가가 너무 많아 비효율적일 수 있습니다. 이럴 때 구간차원을 만들 수 있습니다.

 

09.png

 

이렇게 구간을 만들어서 차원을 관리하고 그 기준에 맞게 Fact테이블을 적재 하게 되면 여러 가지 이점이 있습니다. 다양한 값을 분석하고자 하는 비즈니스관점에서 분석 할 수 있다는 이점입니다. 아래처럼 나이구간처럼 5, 10, 마케팅부서구간으로 나누어서 분석하고자 하는 관점으로 분석 할 수 있다는 이점이 있습니다. 이러한 구간차원도 마찬가지 이지만 구간에 기준이 변경된다면 다시 Fact테이블을 재적재해야 하는 만큼, 비즈니스에 따라서 적합한 구간을 설정하는 것이 중요 합니다. 너무 세분화 되지도 않고, 반대로 너무 집계 되지 않도록 해야 합니다.

 

10.png

 

차원에 대한 설명으로 마지막으로 정크차원입니다. 일반적으로는 Yes/No 또는 True/False 와 같이 간단명료하지만 자체적으로 의미가 없고, Fact테이블과 연계해야지만 의미가 있는 차원입니다. 예를 들어 결혼유무라는 Fact테이블의 속성에서는 사용가능 합니다 이렇나 정크차원은 이외로 많이 사용하게 됩니다. 입고여부, 매출발생여부 등등 셀 수 도 없이 많은 항목에서 사용됩니다. 그리고 이러한 차원에서도 사용됩니다

 

11.png

 

수신여부를 각각 두게 되면 가운데 교차 엔티티가 발생하면서 중간에 Fact테이블이 하나 더 생성되어야 합니다. 이러한 구성도 물론 OLAP에서 표현할 수 있지만, 복잡한 비즈니스나 가운데 Fact테이블이 너무 커질 경우 성능에 영향을 줄 수 있습니다. 이때 정크차원을 만들어서 이러한 어려움을 해결 할 수 있습니다.

차원에 대해서 모두 알아 보았습니다. 그럼 마지막 정리로 다차원 DB구축과정에 순서는 다음과 같습니다.

1. 주제영역 설정(매출, 고객, 재고)

2. 측정값 설정(매출액, 수량, 평균매출가격)

3. 구분차원의 결정(매장별, 상품별, 광역별)

4. 데이터의 구체성 결정(데이터의 상세수준, 집합 정도 결정)

5. 계층구조, 속성정의

6. 관계식 정의

7. 차원수의 결정(집계성능 및 희박 성 상관관계)

8. 다차원모델의 변화

9. 데이터 정합성 체크

1. 주제영역 설정으로 시작하게 됩니다. 예를 들면 매출, 고객, 재고라는 고유주제영역을 설정하게 됩니다. 이러한 주제영역을 설정하지 않고 추가하게 된다면 필요한 테이블을 다시 이관해야 하므로 처음부터 시작해야 하는 어려움이 발생합니다.

2. Fact테이블기준으로 측정값그룹을 지정한 후 측정값을 설정하게 됩니다. 매출액, 수량, 평균 매출 가격 등을 분석의 주체를 결정하게 됩니다.

3. 어떠한 관점으로 분석할 것인가를 결정하는데 이러한 과정을 구분차원의 결정이라 합니다. 예를 들면 매장별, 상품별, 광역별로 분서관점을 지정합니다.

4. 데이터의 구체성을 결정입니다.데이터를 얼마만큼의 구체성을 가지고 집계를 한 것인가 입니다. 예를 들면 어떤 회사는 일별로 결산을 하기 때문에 일별 축적된 데이터만 필요로 하는 반면에 어떠한 회사는 시간도 중요하기 때문에 시간에 대해서까지 집계를 원할 수도 있습니다. 또한 어떠한 회사는 광역 별로만 집계를 할 수도 있고 어떠한 회사는 매장의 판매자 까지 집계를 할 수도 있습니다. 이러한 데이터의 상세수준즉 집합 정도를 결정하게 되는데 이때 중요한 이슈 중에 하나는 데이터 양에 대한 문제와, 응답 속도 입니다. 아무래도 상세 정도가 깊을 수록 많은 데이터를 분석해야 하기 때문에 응답속도가 느려지고, 데이터 양이 많아 질것 입니다.

5. 계층구조에서 상품의 경우는 대중소, 시 계열에서는 년월분기 등을 지정하게 됩니다. 속성은 상품의 색깔, 단가그룹별(천원 대, 만원 대)등이 될 수 있습니다. 그리고 일반적으로 단가 금액으로는 매우 틀리기 그 특성으로 집계하지는 않습니다만, 참고적으로 사용할 수도 있습니다. 이러한 차원에 대해서 세세한 속성들을 결정하는 과정이 되겠습니다.

6. 다음은 관계식 정의 입니다. 가장 간단한 예는 바로 금액입니다. 단가 * 수량을 금액으로 계산하는데 이때 계산된 컬럼으로 측정값과 측정값 사이의 관계를 공식으로 나타낸 것입니다. 판매율 같은 경우에도 판매 수/전체재고 수*100으로 계산되는 것입니다.

7. 차원수의 결정은 아까 말씀 드렸던 집계의 희박 성을 고려하여 집계 정도를 결정하는 것입니다. 집계상세를 올리면 집계성능은 떨어지지만 조회속도가 빠르고, 내리면 집계성능은 좋아지지만 조회속도가 느리게 됩니다.

8. 다음은 모델의 변화 입니다. 어제까지만 해도 상품의 카테고리는 3가지였는데 오늘부터 기간 계에서 4가지로 변경하기로 하였습니다. 카테고리를 추가하고 집계해야 하는 상황이 발생하게 되는 것입니다. 이러한 변화를 고려하는 설계가 중요합니다.

9. 데이터가 맞지 않으면, 아무 소용이 없습니다. OLTP에서 어제의 매출이 1,000만원이었다면, OLAP에서도 1,000만원이 나와야 합니다. 이러한 값의 정합성은 단계별로 주요 측정값 별로 미리 체크해 두는 것이 좋습니다. 그래야 중간에서라도 원인을 파악하고 조정이 가능한 것입니다.

다차원모델링에 대해서 두 번에 걸쳐서 포스팅 하였습니다. 사실 다차원모델은 책이 한권일 정로도 방대한 분량인데 지면상 여기서 정리해야겠습니다. 좋은 설계에서 출발해야 미연의 사고를 방지 할 수 있습니다. 또한 성능에도 영향을 미치기 됩니다. 느리고 문제가 있는 BI프로젝트에서 찬찬히 뜯어보면 대부분이 올바르지 않은 설계에서 출발 합니다. 대부분의 BI개발자들은 다차원모델링에 대한 재설계를 두려워한 나머지 OLTP에서 일어나는 비즈니스 모델 그대로 옮겨 놓기를 원합니다. 물론 빠른 결과물 때문일 수도 있겠지만 대용량인 DW시스템에서는 성능에 악영향을 미치기 때문입니다. 이러한 곳에서 완벽한 해결은 재설계(Redesign)이 아니고서는 없습니다. 임시방편으로 조치를 취할 수 있는 것으로 몇 가지 셋팅을 조정하는 것도 한계가 있기 때문입니다.

<목차>

  1. BI, Microsoft BI 소개
  2. DW, DM, OLAP의 이해
  3. 다차원모델링(1/2) – 스키마, Fact테이블
  4. 다차원모델링(2/2) - Dimension테이블, 다차원DB구축 과정 정리
  5. MSSQL 2012 DW 셋팅
  6. 압축
  7. 파티션
  8. 컬럼스토어 인덱스
  9. MSSQL 2012 Semantic Model 소개
  10. MSSQL 2012 Power Pivot
  11. MSSQL 2012 Tabular Model(1/2) – 기본편
  12. MSSQL 2012 Tabular Model(2/3) – 고급편
  13. MSSQL 2012 Tabular Model(3/3) - DAX
  14. MSSQL 2012 Dimension Model(1/5) - 차원
  15. MSSQL 2012 Dimension Model(2/5) - 측정값그룹, 큐브
  16. MSSQL 2012 Dimension Model(3/5) - 파티션
  17. MSSQL 2012 Dimension Model(4/5) - 집계 디자인
  18. MSSQL 2012 Dimension Model(5/5) - 계산된 메져, MDX
  19. MSSQL 2012 DQ
  20. MSSQL 2012 MDS




profile

안녕하세요, SQLER의 바쉬*^^*, 김상수입니다. 행복 하세욤~. ^.^
위세아이텍 연구소 책임 / SQLER 시샵 / BI Developer
E-Mail: bash1130@hotmail.com / Mobile: +82 10-6231-7672 / NateOn:bash1130@nate.com