Expert Cube Development with SSAS - 1장

The OLTP database

대부분 mainframe RDBMS가 많죠. 하지만 가끔은 flat 텍스트 파일일 수도 있어요.

DW/BI입장에서의 OLTP의 고찰점

1. BI어플리케이션에서 직접 접근하지 않는다. export/cleansing/integrating/loading을 통해 DW를 구성한 후 BI어플리케이션에서 사용한다.

2. OLTP는 많은 경우 복잡다단하고 무정지 시스템이기 때문에 DW에서 변경을 요구하기 힘들고 "AS-IS"로써 받아드리는 것이 속편하다.

3. 데이터나 코드값의 무결성 등이 운영계에서 보장되지 않는 경우도 많고 이미 저질러진 오류들을 수정하는 것도 쉽지 않고 문서화도 잘되어 있지 않은 경우가 많기 때문에 DW의 첫작업은 데이터무결성조사/데이터성격파악하여 문서화하는 것이다.

SSAS를 위한 별도의 data mart는 필요한가? 예!
만약 OLTP시스템을 바로 SSAS에서 바라본다면 빨리 개발할 수 있을지는 모르지만 지저분하고 무결성이 보장되지 않아 신용이 없을 뿐더러 cube processing 성능이 후진 시스템이 될 것이다.

The data warehouse

DW디자인은 크게 Ralph Kimball과 Bill Inmon의 방법론이 존재합니다.

Ralph Kimball : data mart들의 집합 = data warehouse

Bill Inmon : 정규화된 data warehouse을 먼저 구성 후에 data mart들을 만든다.

DW/BI하는 사람이라면 아래 2개 책은 꼭 책장에 있어야 한다네

- Building the Data Warehouse 4th Edition by W. H. Inmon
- The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling 3rd Edition by Ralph Kimball

SSAS입장에서는 어쨌든 data mart가 있는 것을 기본으로 합니다.

Data Modeling 

Dimension
BI에서 가장 중요한 다야한 관점을 통해 데이터분석을 풍성하게하는 역할을 하는 테이블입니당. 디멘전은 많은 속성들을 가질수록 그 역할을 잘 수행하지요.

natural key: 원천에서 key역할을 하는 컬럼들을 말합니다. (ex: 상품ID)
surrogate key: DW에서 unique integer key를 채번하여 만드는 대체키입니다.

디멘전들은 팩트들과 연결이 되는데요. 가끔은 다른 디멘전을 통해서 팩트와 연결되기도 합니다.

Fact
팩트는 말그대로 실제 발생한 사실 또는 측정가능한 값을 담고 있는 테이블을 말합니다.
팩트는 여러 디멘전들과 연결됩니다. 하지만 다른 팩트 테이블과는 직접적으로 연결되지 않습니다.
Kimball의 주장으로는 디멘전과 팩트는 surrogate key를 통해서만 연결하는 것을 권고합니다.

Star schema, Snowflake schema
스타 스키마는 차원들이 팩트에 직접 연결되는 것이고
스노우플래이크 스키마는 일부 차원이 다른 차원을 통해서 팩트에 연결되는 것을 말해요.

위의 2개의 스키마에서 DimGeography를 보면 됩니다.
아래 스키마가 스노우플래이크 입니다.

스노우플래이크는 해당 데이터의 성격을 정확히 알지 않으면 개발자와 OLAP모두 혼동을 일으킬 잠재적 문제가 있습니다.
DimSalesTerritory는 고객의 Territory입니까? 아니면 주문의 Territory입니까? 위의 예는 Snowflake로 만들기 전의 스키마가 있지만 처음보는 경우 알기가 힘들겠죵?

그래서 SSAS에서는 되도록 snowflake를 star스키마로 전환하기 위해서 snowflake에 참여한 테이블들을 join하여 VIEW를 만들고 이 VIEW를 팩트에 직접 연결는 방법을 권고합니다.

SSAS에서 snowflake를 그대로 Data Source View에 구현하게 되면 왕왕 차원이 팩트에 연결되지 않았다는 에러를 만날 수도 있습니다. 그리고 큐브처리(cube processing)시에 복잡한 join쿼리가 data mart로 날라가기 때문에 성능문제를 겪을 수가 있습니다. 그리고 SSAS소스의 가독성도 떨어지고요.. Keep it simple!

Junk dimensions

값이 몇개 안되는 차원들이 있습니다. (ex: 예/아니오 | 이마트/신세계 등)
이것들을 모두 별도의 차원으로 만들어도 되지만 사용자들은 차원이 너무 많아지면 혼란스럽겠죠? (30개 이내가 좋다고 하네요)

이것들을 모아서 1개의 차원으로 만드는 기법을 Junk dimension이라고 합니다.
. 팩트테이블의 차원키도 적어지고
. 쿼리할때 조인 테이블도 적어지고
. 큐브처리도 빨리지겠죠?

근데 이 Junk dimension을 나중에 다양한 사유로 쪼개거나 추가할때는 기존에 팩트에 따진 key값들이 흐트러지지 않도록 조심스럽게 작업해야하는 부담이 있습니다. 물론 전체처리를 다시 한다면 조금 낫겠죠?

Degenerate dimensions

퇴화디멘전이라고 하는데 잘모르겠어유.... 트랜잭션번호 즉 팩트에 있는 주문번호 등을 말하는데 주문건을 확인하기 위해서는 유용한데 이를 디멘전으로 만들면 팩트의 row수 만큼 거대한 디멘전이 만들어지기도 하고 속성이 딱히 있지 않기 때문에 만들지 않습니다.
우리는 주문번호를 8자리+6자리로 잘라서 cardinality를 확 줄여서 만들어 놓았죠.

SSAS에서 퇴화디멘전은
AttributeHierarchyEnabled속성은 False로 처리해야한다고 하고
Fact관계를 통해서 팩트자체로 디멘전으로 사용할 수도 있다고 하는데 이거 나중에 다룰때 좀더 이해를 해봐야 할 듯 해요.

Slowly Changing Dimensions

디멘전은 안변하면 좋지만 항상 조금씩 변하죠. 약간의 변형들이 있지만 이렇게 값들이 변하는 디멘전을 SCD라고 하고 Type1~3이 있습니다.

. Type 1
값이 바뀌면 그냥 Update해버립니다. 차원의 과거 상태를 알수 없습니다. 구현이 아주 간단해서 편하죠.

. Type 2
값이 바뀌면 새로운 행을 Insert하고 과거의 팩트 row들은 과거의 차원 row와 새로운 팩트 row들은 새로운 차원 row와 연결됩니다. 보통 시작일자~종료일자를 관리해서 값이 바뀌면 직전 row에 종료일자를 찍고 새로운 값을 Insert하면서 시작일자가 정해집니다.

. Type 3
마지막 과거 값만 필요할 때 사용합니다. 직전값을 차원내의 별도 속성으로 저장해두고 현재 값은 Update합니다. 잘 사용하진 않죠~

결국 Type 2방식이 문제인데 한 디멘전 내에서도 속성 별로 여건이 다릅니다. 고객 주소는 변경될 수 있지만 고객 생년월일은 변경 안되겠죠?

그래서 이런 경우 시점차원과 현재차원으로 분리하여 늘리기도 합니다.
하지만 차원수가 너무 많아지고 쪼개진 2개의 차원은 원래 한차원이었으니 서로간의 관계가 있겠죠? 차원2개+팩트1개 간의 join이 일어나게 되어 성능이슈도 발생합니다.

시점차원과 현재차원을 분리하지 않고 1개의 차원으로 구현하는 방법도 있습니다. ETL이 다소 복잡해지지만 ^^;; 성능으로 보나 사용자 친화적으로 보나 이 방법을 시도해보세요.

Bridge tables or factless fact tables

다대다 관계로 존재하는 속성이 하나도 없는 팩트를 말하는데요. 가끔 속성이 없더라도 row존재가 자체가 의미를 가지는 경우가 있습니다. 학생차원과 수업차원사이에는 수강이라는 Bridge table이 존재할 수 있고요. 이는 실제 발생한 수강행위가 있을때만 row가 발생하지용. 그럼 성능적으로도 좋고요. 다대다 관계를 유용하게 이용하는 것이죠.
"프랑스문화예술" 수업을 들은 1학년 학생들의 ssg 주문금액(생뚱맞죠?)을 구한다면 가운데 Bridge table이 훌륭한 다대다 관계 팩트이자 차원적 역할을 할수 있습니다.

물론 다대다 관계 남발은 두통을 유발합니다. Bridge table의 row수가 커지면 성능도 급격히 떨어지고요. 나중에 다룬다네요.

Snapshot and transaction fact tables

뭐 설명 안해도 되겠죠?
스냅샷을 뜰때 필요한 컬럼만 하거나 아니면 집계해서 스냅샷을 뜨면 그나마 공간을 많이 절약할 수 있지만 시간이 흐른뒤에 새로운 분석 니즈가 발생하면 또 대응이 안되는 단점이 있네요.

Updating fact and dimension tables

스키마 업데이트와 데이터 업데이트로 나눠서 생각해봐야 합니다.

스키마 업데이트
OLTP가 계속해서 변하기 때문에 자연스레 발생하게 됩니다.
차원의 경우에는 많은 속성의 추가가 일반적이고 비교적 쉽게 추가할 수 있습니다.
다만 SSAS입장에서는 차원의 전체처리가 필요할 수 있습니다.
팩트의 경우에는 추가되는 팩트가 기존의 저장구조를 흔들기 때문에 cube전체처리를 필요로 합니다.(UPDATE명령이 있지만 더 느립니다.)

데이터 업데이트
차원의 경우에는 특히 Type 2 SCD인 경우에 좀 복잡해집니다. 변경된 값들이 모두 팩트와 연결이 되어 있기 때문에 조심스럽게 Update해야 합니다.
팩트의 경우에는 주로 잘못된 값이 적재되었기 때문에 일어나는데요. 잘못된 시점부터 팩트를 모두 다시 적재해야 합니다.

간혹 팩트크기가 적다면 전체를 매일 새로 적재하는 큐브들도 있을 수 있는데요. 이 경우에는 데이터 업데이트 이슈로부터 자유로울 수 있는 장점도 있습니다. one shot data warehouse라고 하네요.

Natural and surrogate keys

Kimball은 대체키(surrogate key)를 꼭 쓰라고 하는데... 쉬운일은 아닙니다;;
key선택 기준들을 알아보겠습니다.

. 되도록 작은 크기의 integer계열의 타입을 사용한다. (tinyint, smallint, int, bigint)
. date형 대신 YYYYMMDD스타일의 integer를 사용하면 가독성도 좋고 integer 타입도 사용하게 됩니다.
. Range형태로 key를 만든다. 가령 금액대 차원의 경우 FLOOR(금액/10000) 수식을 활용해서 키값의 수도 줄일 수 있다.

Unkown Members, key errors, and NULLability

PK FK관계에 참여한 컬럼들은 절대로 NULLable하지 않아야 합니다.
NULLable컬럼은 NULL정보를 위한 추가적인 BYTE가 필요하고요.
키관계처리시 에러가 납니다. (우린 에러무시 옵션으로 processing하고 있죠? ㅜ.ㅜ)
물론 SSAS에서 키관계 에러가 나면 Unknown Member라고 해서 미매핑 처리해주는 로직이 있긴 하지만 그것보다는 사전에 DW에서 NULL처리를 하여 코드없음, 값없음 처리를 해주는 것이 좋습니다.

Physical database design for Analysis Services

Multiple data sources

Data Source View에서 모든 소스들의 테이블들을 하나로 모아서 보기 때문에 SSAS입장에서는 그냥 하나의 data source를 바라보는 것처럼 간주해도 됩니다.

Data types and Analysis Services

가장 빠른 데이터 타입 선택은 항상 중요합니다.
. date : yyyymmdd형태의 int를 사용
. numeric : smallmoney, money, real and float이 좋습니다. 의외로 decimal타입은 CPU를 더 필요로 합니다.
. distinct count : integer계열르 쓰기 위해서 char계열은 대체키를 만들거나 hashing을 사용하는 것을 고려해 볼 수 있습니다.

한가지 주의할 점은 가능한한 작은 데이터 타입을 선택한다고 해서 팩트 테이블 내의 가장 큰 값을 기준으로 선택하면 안되고 전체 sum값을 기준으로 선택해야 overflow가 안됩니다.

SQL queries generated during cube processing

SSAS내부의 star schema만을 볼 것이 아니라 큐브처리 시에 data source에 날라가는 SQL쿼리의 성능최적화를 항상 고려해야 합니다.

. 차원처리
병렬로 DB로 쿼리가 날라가기 때문에 캐싱, 인덱스 등의 도움을 받게 됩니다.
차원VIEW가 많은 JOIN이나 복잡한 WHERE절을 가지고 있고 성능적인 문제가 있다면 ETL배치를 리뷰해볼 필요가 있습니다. 쉽고 단순한 VIEW가 되도록 ETL에서 물리테이블을 구성하는 것을 고려해야 합니다.

단순한 star schema가 아니라 snowflake schema구성 시 SSAS는 JOIN쿼리를 data source에 날리게 됩니다. 그래서 되도록 star schema로 구성하는 걸 추천합니다.

. 참조관계차원
팩트 차원 연결시에 참조관계로 구성할 수 있습니다. 이때 주의할 점은 참조되는 차원이 팩트 테이블 SQL쿼리 수행시 JOIN형태로 같이 수행된다는 것입니다. 그래서 참조되는 차원의 row수가 많은 경우 DB에는 많은 부담을 주게 됩니다.

. fact dimensions
팩트 차원 연결 시 팩트관계로 설정하면 팩트가 곧 차원이 됩니다. 이때 차원은 항상 unique값을 필요로 해서 DISTINCT 쿼리가 팩트 테이블에 날라가게 됩니다. 팩트는 row수가 어마어마 할 수 있으므로 주의가 필요합니다.

Distinct Count Measures

distinct count 팩트값은 해당 팩트테이블에 "SELECT DISTINCT ... ORDER BY ..."형태의 쿼리가 날라가게 됩니다. 해당 쿼리가 DB에서 최적화되도록 파티셔닝, 인덱스 설정 등의 방법을 고려하여야 합니다.

Indexes in data mart

. 디멘전 테이블
팩트와 연결되는 키(대체키 추천)에 인덱스가 있으면 좋겠죠?
원천키에도 unique인덱스를 설정해서 개발 시에 중복을 예방하고 운영환경에서는 성능을 고려해서 disable하는 것도 방법입니다.

. 팩트 테이블
팩트 테이블이 커질 수록 파티셔닝이 인덱스보다 더 중요합니다. DISTINCT COUNT 팩트 때문에 CLUSTERED INDEX가 있으면 큐브처리 시 ORDER BY 이 좋아집니다.

. one shot data warehouse에서는 매일 테이블을 새로 생성하므로 구지 파티셔닝, 인덱스를 만들지 않는 것도 고려하셔요

. ROLAP을 사용할 때는 인덱스를 다양하게 활용해서 조회쿼리 수행의 성능을 높여주세요

Usage of schemas

SQL Server의 schema객체를 활용하면 영역별로 테이블을 모아서 볼 수 있어 편합니다.

Naming convention

data mart도 물론 표준화는 중요합니다. 컬럼명이 일치화되면 자동으로 차원과 팩트 테이블을 연결해주기 때문에 개발할 때 손쉬워요.

Views vs Data Source View

SSAS의 DSV에서 할 수 있는 많은 일을 DB의 VIEW에서 처리할 수 있고 강력히 DB의 VIEW를 사용하고 SSAS의 DSV는 최대한 단순하게 star schema를 구성하는 것을 추천합니다.

SQL VIEW사용의 장점을 나열해 보겠습니다.
. db내에 소스가 존재하기 때문에 로직 확인이 쉽다.
. 컬럼명 수정이 용이하다.
. 간단한 계산식도 처리할 수 있다.
. 간단한 텍스트로 소스가 되어 있어서 SSAS소스의 xml을 까보지 않아도 된다.
. 수정이 용이하다.
. 컬럼 노출이나 숨김이 용이하다.
. 기본값 설정도 쉽다.
. DW상의 복잡한 snowflake schema도 간단한 star schema로 변환이 가능하다.
. DB의 보안, schema객체를 활용할 수 있다.
. 성능 최적화가 용이하다.

주의할 점은 가끔 ETL배치를 건너뛰고 VIEW를 사용해서 복잡한 쿼리를 만들고 성능이슈를 발생시킬 수 있다는 것이다. ETL배치를 잘 짜셔야 합니다. ㅎㅎ