다차원 모델링(1/2)

OLTP에서 가장 성능 좋은 DB를 만드는 방법에는 무엇일까요? 하드웨어의 성능을 올리거나, 확장하는 방법 또는 인덱스 생성 등 여러 가지 방법이 있겠지만, 가장 좋은 건 적합하고 합리적인 설계에 따른 DB모델링이 가장 중요하다고 생각 됩니다. OLAP도 마찬가지 입니다.

이번 시간에는 다차원모델에 대해서 알아볼 시간인데요, 처음부터 말씀 드리자면, OLTP와는 너무나 다른 설계 방법입니다. 또는 완전히 반대입니다. OLTP에서는 왼쪽으로 가야 한다면 OLAP에서는 오른쪽으로 가면 되겠네요~, 이러한 이유는 목적이 서로 틀리기 때문입니다. OLTP에서는 트랜잭션 단위로 처리하는 것에 중점이 있다면, OLAP은 최적화된 조회에 목적이 있기 때문입니다.

OLTP에는 관계형 데이터베이스(RDB)가 있다면, OLAP에서는 다차원DB(MDB or MDDB)가 있습니다. 비즈니스 영역, 또는 주제별 관점으로 구축되는 다차원DB를 설계하기 위해서는 다음의 구성요소가 필요 합니다.

 

가장 가운데 있는 다차원 모델은 별(Star), 눈꽃송이(Snowflake), 복합(Composite) 스키마로 구성됩니다. 별은 Star스키마, 눈꽃송이는 Snowflake스키마 라고 하겠습니다. 분석관점과 데이터 양에 따라서 모델을 선택하게 되는데, 대용량에서 가장 많이 사용되는 Star스키마부터 알아 보겠습니다.

 

 

하나의 Fact(사실)테이블과 다수의 Dimension(차원)테이블로 구성되어 실제 사용자가 쉽게 이해할 수 있도록 단순하게 모델링 되며,원하는 보고서를 만들기 위해 필요한 조인의 횟수를 줄임으로써 사용자 질의에 빠른 속도로 응답할 수 있다는 장점을 가졌습니다. 각 차원은 기본 키 - 외래 키 관계에 따라 Fact테이블에 직접 연결되어 있는 단일 Dimension테이블을 기반으로 합니다. 

예를 들어 위에 그림처럼 Dimension테이블을 성별과 연령으로 각각 만들어서 Fact테이블에 외래키로 연결하는 것입니다. OLTP에서는 회원에 대한 Dimension테이블을 하나만 만들어서 성별과 연령을 속성으로 가져가겠지만 Star스키마에서는 이렇게 역정규화를 하게 됩니다.

Star스키마는 대용량에서 많이 사용됩니다. 분석관점이 명확하고 데이터 건수가 많은 경우에 유리하지만, 다른 관점으로 분석 요구 시 Dimension테이블이 추가 되고 Fact테이블에 대해서 변경이 일어 날 경우 처음부터 다시 쌓거나 다른 사실테이블을 만들어야 하는 불편함이 있습니다. 그러다 보니, 비슷한 테이블들이 여러 존재하고 너무 많아 지면 ETL에서 시간이 늘어나는 점이 있으니 유의 해야 합니다. 경험상으로는 분석관점이 명확할 때 사용해야 하는데 그렇지 않을 경우에는 Snowflake스키마가 더 적합할 때가 있습니다.

Snowflake스키마는 다음과 같은 구조를 가집니다.

 

 

Star스키마의 Fact테이블 구조와 동일하게 유지하면서 Dimension테이블이 정규화된 구조를 말합니다. 

위에 Star스키마와 예와 비교한다면 회원에 대한 Dimension테이블을 하나를 만들어서 다시 여기서 성별 Dimension,연령Dimension을 생성한 후 회원Dimension과 연결하는 구조 입니다. 매출 Fact테이블에는 회원번호와 같이 식별할 수 있는 속성을 가지게 됩니다.

Snowflake스키마는 그래서 확장에 용이 합니다. 회원에 대한 속성에서 직업이 추가 된다고 해도 매출 Fact에는 변화가 없고 회원Dimension에 직업속성과 직업이라는 Dimension이 추가만 되면 됩니다. 하지만 Star스키마에 비해 많은 데이터를 보유 할 수 있으므로, 성능과 집계에 영향을 미칠 수 있습니다. 그리고 데이터 중복을 피할 수 있으나, 이해하기 어려운 단점이 있을 수 있습니다.이렇게 중간에 회원 Dimension과 같이 참조되는 테이블을 Outboard 또는 Outrigger 라고도 합니다.

Star스키마와 Snowflake는 각각 장단점을 가지고 있으므로, 실제 프로젝트에서는 획일적으로 설계하지 않고 복합적으로 설계합니다. 이러한 스키마 구조를 복합스키마 라고 합니다.

 

 

데이터의 무결성(Integrity)을 유지하고 데이터의 중복 성을 줄이기 위한 정규화와 성능향상을 위한 비정규화가 일정한 수준에서 조합된 형태를 취하게 됩니다. 여기서 두 가지를 항상 염두 해야 합니다. 첫 번째는 집계 상세수준을 고려해서 정의하는 것과 두 번째는 사용자 질의 유형과 질의 응답성능을 고려하여 적절한 수준에서 테이블을 비정규화 필요성 여부를 고려하는 것입니다.

알맞은 설계 방법을 선택하기 위하여 Fact테이블을 설계하기 전에 데이터의 차원 별 분포도를 먼저 파악하기도 합니다. 

그 외에 여러 개의 Star스키마의 집합관계를 가지고 있는 Constellation(별자리)스키마도 있습니다. 각기 다른 Fact테이블들이 동일한 Dimension테이블과 조인되는 구조입니다. 대게 Star스키마로만 이루어진 DM에서 나타납니다.

 

이렇게 다양한 스키마 구조들에 대해서 알아 보았습니다. 다시 한번 말씀 드리면, 한 스키마 구조에만 얽매이지 않고 데이터 양과 활용되는 관점에서 다양하고 자유로운 설계가 전체적인 BI프로젝트를 건강하게 합니다.

위에서 자주 언급된 Fact테이블에 대해서 구체적으로 함 알아 볼까요? Fact테이블은 정규화된 테이블로써 스키마에 중심에 위치하며 설계 시 가장 큰 테이블입니다, 수억 개의 행을 가질 수도 있을 정도로 엄청 큰 Big 테이블 입니다.

 

 

생성시 유의해야 할 점은 중복되는 데이터가 없어야 하며, 너무 큰 테이블의 경우에는 수직, 수평분할 고려해야 합니다. 또한 SQL 2005부터 지원하는 데이터 압축, 파티션 등을 적용하여 구성해야 합니다. 그리고 Fact테이블은 아래와 같은 특징을 가지고 있습니다.

l Dimension테이블과 연관된 키 구조를 가짐

l Dimension테이블과의 연관된 키 구조를 제외하고 숫자 형태의 포맷으로 주로 구성 (측정값)

l 측정값이 없는 Fact테이블은 Fact less Fact Tale(사실 없는 사실테이블)이라 하며, 카운트가능

 

 

그러면 측정값 집계 시 유형에는 3가지가 있다고 했습니다. 가산, 비가산, 반가산입니다. 각 집계유형에 따른 타입은 해당표를 참고 하시면 됩니다.

Aggregation	Type
Sum	Additive
Count, Min, Max	Semiadditive
FirstChild, LastChild	Semiadditive
AverageOfChildren	Semiadditive
FirstNonEmpty, LastNonEmpty	Semiadditive
ByAccount	Semiadditive
DistinctCount, None	Nonadditive

Fact테이블을 구성하면서 가장 많이 고민하는 문제는 Dimension테이블과 조인되기 때문에 인덱스 설계와, 참조 무결 성을 유지하기 위해서 ETL시 유의해야 하는 점입니다. 그리고 일반적으로 SUM할 수 있는 숫자 포멧의 값을 측정값이라고 합니다. 이러한 측정값은 가급적 가산형태로만 하는 것이 좋습니다. 반가산, 비가산 등의 측정값은 사용자에게 혼란 등을 야기할 수 있으며 데이터의 왜곡을 초래할 수도 있습니다. 또한 가급적 산신을 넣지 않습니다. 산식이 변경될 경우 재계산해서 UPDATE가 발생할 수 있는데, 이때UPDATE는 대용량에서는 바람직하지 않습니다. 그러면 UPDATE가 필요한 경우에는 병렬로 처리하는 CTAS가 오히려 유리할 수 있습니다. 

Fact테이블의 왜곡되는 현상을 아래 그림으로 살펴보면

 

 

인당 매출액을 평균값으로 Fact테이블에 적재할 경우, Type A: Sum(매출액)/Sum(고객수) = 인당평균매출액으로 구할 수 있지만, Type B: Sum(Avg(매출액)) / Count 로는 왜곡된 데이터가 나올 수 있으므로 유의해야 합니다.

많은 분들이 Fact테이블 구성 시 의문을 가지고 계시는 것이 바로 Dimension테이블과 FK를 만들어야 하는 가? 입니다. FK는 데이터의 정합성을 유지하는 Constraint로서 중요 합니다. 하지만 Fact테이블 적재하는 경우에 FK로 인하여 적재시간이 지연될 수 있는데, FK를 생략해서 나중에 테이블간의 정합성 체크를 하는 비용을 생각한다면 사전에 하는 것이 더 바람직해 보입니다.

일반적인 OLTP 모델링에서도 논리 명을 설계할 때 마찬가지 이겠지만, BI에서는 의사소통을 위해 더욱 강조 되는 부분이 명확한 명명 법 입니다. 예를 들면 ‘액’, ‘금액’ 등의 명명보다는 가급적 ‘매출액’, ‘판매액’ 등의 자세한 명명을 사용하여, 전사적으로 이해하는 용어를 사용하여 혼란을 야기 하지 않도록 합니다. 예를 들어 매출액 = 판매액 – 반품액, 매출액 = 판매액 등 ‘매출액’에 대한 이해가 부서마다 용어에 대한 이해가 다르다면 전사적으로 용어를 통일 시켜야 합니다. 

Fact테이블의 Row수는 차원의 상세수준에 따라 결정되는데, 이에 대해서 알아보겠습니다. 예를 들어 매장차원(20개 매장)과 상품차원(10개 상품)만으로 구성이 되어 있을 때, Fact테이블의 Row수는 20 X 10 가지의 경우의 수를 쉽게 예상할 수 있습니다. 또한 Dimension테이블의 수는 동일하나 상세수준으로 조절이 가능한데, 그림으로 표현하면 다음과 같습니다.

 

 

일반적으로 Dimension테이블의 수가 적을 수록 상세수준이 낮으며, 연관된 Dimension테이블들의 키(FK)의 경우의 수라고 보면 됩니다. 필요하다면 상세수준을 낮춰서 성능의 향상을 높일 수 있으며, 다양한 분석을 원한다면 높이는 것도 하나의 방법입니다. 이를 활용하여 실무자에게는 주문번호 별 매출 데이터를 보이고, CEO에게는 날짜 차원 별 데이터를 보여서 각각 리포트에서 성능을 조절하는 것입니다. 그러나 상세수준이 높은 Fact 테이블에서 상세수준이 낮은 Fact 테이블을 생성할 수 있으나, 반대로 상세수준이 낮은 Fact 테이블에서 상세수준이 높은 Fact테이블을 생성할 수 없으니 처음에 만들 테이블의 상세수준을 합리적으로 결정해야 합니다.

OLTP에서는 판매가 이루어질 때만 저장하므로, 10개의 매장에서 서로 다른 제품을 각각 1개씩팔았다면 10 Row가 되지만 다차원 모델에서는 10개 매장 * 10개 상품으로 Fact가 구성되므로 100 Row가 만들어 집니다. 이처럼 오히려 Fact테이블의Row수가 더 많게 되는데, 집계의 상세수준이 차원에 따라 달라진다는 점을 명심해서 집계의 효율성(성능)과 분석의 다양성 이라는 두 마리 토끼를 다 잡을 수 있게 고민의 고민을 거듭하게 됩니다. 

BI 프로젝트가 거의 마무리가 될 지점에서 테이블의 정보를 취합해 보면, 비슷한 Fact테이블들이 많이 존재하게 되는데, 이때 동일 상세수준과 동일Dimension테이블을 가진 Fact테이블간에 병합이 가능합니다. 이렇게 병합하게 되면 ETL 시간도 줄어 들고, 저장 공간도 절약되니 일석이조의 효과를 볼 수 있습니다. 단 업무적으로 상이한 Fact테이블 이라면, 분리되어 각각 존재하는 것이 향후 변경 Point가 적어 지므로 더 낳을 수 있습니다.

출처 - http://www.sqler.com/499404#0