Expert Cube Development with SSAS - 2장

Building Basic Dimensions and Cubes


Multidimensional and Tabular models

SQL Server 2012부터 SSAS는 Multidimensional 모델에 추가로 Tabular 모델이 추가됨.
Tabular 모델은 Excel Power Pivot엔진을 기반으로한 In-memory columnstore OLAP.
개발이 쉽고 distinct count에 강하다.
--> 그러나 2012까지는 partition들에 대한 병렬처리가 지원되지 않아서 속도가 급격히 느려져 우리 사이트에서는 걷어냈다. (2016에서 도입되어 테스트 필요)
Multidimensional 과 Tabular 모델 프로젝트는 서로간의 변환이나 마이그레이션이 불가.

Choosing an edition of Analysis Services

우린 그룹사 계약이 되어 있어서 모두 Enterprise Edition으로~

Setting up as new Analysis Services project

. svn과 같은 source control을 반드시 사용하십쇼.

. 프로젝트는 Project mode와 Online mode로 되어 있는데용.
  개발 중에는 Project mode로 하여 Deploy 방식으로 적용합니다.

  운영 중에 사소한 변경을 할때는 Online mode로 진행합니다.
  Online모드는 Save할때마다 반영이 되어버려서 위험하거든요.

. Project Properties -> Deployment -> Processing Option 은 '처리안함' 합니다.
왜냐하면 배포하면서 자동으로 Processing시작되면 망하거든요.

Creating data sources

. 소스가 SQL Server 일때는 항상 Provider를 "SQL Server Native Client"를 선택해야 최고의 성능을 얻을 수 있습니다. 다른 DBMS의 경우에는 OLEDB, ODBC, .NET provider 순으로 try해보세요.

. 처리를 위한 계정은 대부분 읽기권한만 있으면 충분합니다. (Fact에 일부데이터를 쓰는 Writeback 시나리오에서는 쓰기권한도 필요합니다.)

. 접속계정을 SQL계정대시 Windows계정을 사용하면 배포 후에 비밀번호를 재입력하는 번거로움을 줄일 수 있습니다.

Creating Data Source Views

. 줄여서 DSV라고 부릅니다. 모든 차원, 팩트 테이블들이 올라와야 하는 메타정보입니다. 차원과 팩트간의 join관계는 여기에서 모두 설정합니다.

. 외래키가 명시적으로 저장되어 있지 않더라도 열이름을 인식하여 자동join관계를 설정해주긴 합니다. (그래도 최종 체크는 해야겠죠? ^^)

. 테이블이나 뷰를 되도록 그대로 사용하고 DSV상에서 명명된 쿼리 등을 이용해서 복잡한 로직을 생성하지 마세요!! (정말 최후의 수단입니다.)

Using the new Dimension wizard

1. Create Method는 대부분 Use an existing table을 사용합니다.

2. Key와 Name컬럼을 선택합니다.

3. Snowflake라서 관련 테이블이 있는 경우 선택합니다.

4. Attributes 들이 있으면 선택합니다.
   이때 Enable Browsing을 끄면 OLAP조회 시 집계의 대상은 되지 못합니다.
  Attribute Type은 시계열, 통화 관련된 경우가 아니면 건들지 않습니다.

5. 완료 시 디멘전 이름이 그냥 ID가 되어 버리고 이는 XML을 직접 수정하기 전까지는 차원이름을 변경하더라도 바뀌지 않고 남습니다. 스크립트 자동화 등을 위해서는 명시적으로 XML을 수정해서라도 ID값은 표준화하는 것이 좋습니다.

Using the Dimension Editor

주요 속성들만 살펴 봅니다.

. AttributeHierarchyEnabled, AttributeHierarchyOptimizedSate: Processing 시에 집계의 대상에서 제외시킬지 여부와 포함하더라도 최적화할지 아니면 집계부담은 덜고 조회부하를 올릴지를 결정

. OrderBy, OrderByAttribute: 차원값들의 정렬순서를 결정

. AttributeHierarchyOrdered: 속성 값의 정렬 순서가 중요치 않을때 (너무 멤버가 많아서 뿌려지는 것이 의미가 없는 상품id같은 경우) Processing시간 감소를 위해 조정할 수 있다.

Configuring a Time dimension

. 시계열 차원은 타입을 속성 별로 지정해주세요. 그래요 Time관련 MDX함수를 사용할 수 있는 경우가 많습니다. (일자 키도 date형이 아닌 int형을 쓰도록 권고하기 때문에 Type지정은 꼭 필요합니다.)

Create user hierarchies

. 계층(hierarchy)를 만들면 초보 사용자에게는 편하지만 고급사용자에게는 제약을 줍니다. 권고로는 계층을 만들면 해당 속성은 visible:false시키십쇼. 복잡하면 풍성하지만 사용자는 헷갈리기만 합니다.

Configuring attribute relationships

. 특성 관계(attribute relationships)는 차원 내의 속성들 사이의 1:N관계를 계층적으로 표현합니다. 이를 정의해두면 하위 속성들(1:N) 기준의 팩트값들을 상위 속성(1:N) 기준으로 집계할 수 있습니다. 매번 하위 속성레벨로 다 긁어서 Sum하는 것이 아니라 Processing때 집계값을 생성할 수 있는 거죠. 아래는 복잡한 특성 관계 차원의 예입니다.

. 계층(hierarchy)는 필수적으로 특성관계 설정이 필요합니다.

. 특성관계는 유형이 있는데 유동(Flexible)과 고정(Rigid)가 있습니다. 절대 변하지 않는 날짜->월->년도 같은 고정값의 경우 고정으로 설정하면 차원 processing시에 많은 단계를 skip할 수 있어서 시간이 절약됩니다.

. 차원 디자인 하면서 BIDS Helper라는 도구를 위해서 Dimension Health Check는 꼭 돌려보세요.

Using the new Cube wizard

빈 큐브를 생성하고 필요한 팩트들을 추가해나가는 것이 일반적입니다.
이 절에서는 간단히 기존 테이블을 이용해 예제를 만드네요.

Database processing

. SSDT에서 하거나 SSMS에서 모두 processing(처리)가 가능합니다.

. 처리의 단위: 차원 / 측정값 그룹, 파티션
  큐브 = 측정값 그룹들의 총합 / 차원 = 속성 들의 총합

. 대표적 오류 유형 4가지

1. 스키마 변화
RDBMS 소스와 스키마가 틀어졌을때 DSV가 변경된 소스의 컬럼이름 변경 등을 반영해주지 않으면 계속 구스키마 기준으로 쿼리를 날린다.

2. Key Error
팩트에 존재하는 키가 드물게 차원에 존재하지 않게되었을 때 기본적으로는 오류를 뱉는다. 그래서 만일을 위해 '오류무시' 옵션을 켜두고 처리하고 있다.

3. 잘못된 오브젝트 처리 순서
아직 차원이 처리되지 않았는데 팩트가 처리되면서 새로운 키값을 인식하지 못할때 에러를 뱉는다. 차원을 먼저 처리해야 한다.

4. MDX Script Error
구조 변경을 하면 MDX스크립트 내의 오브젝트 이름들의 참조가 깨질 수 있다. 그래서 많은 구조변경이 있는 경우 MDX전체를 주석화 한 후에 전체 처리가 끝나면 주석을 풀어가면서 배포하는 것이 요령이 될 수 있다.

데이터 웨어하우스 툴킷 - 1장

1장 - 데이터 웨어하우징, 비지니스 인텔리전스 및 다차원 모델링 입문

이후 장들을 위해서 초석을 쌓는다.

상위 관점에서 데이터 웨어하우스 및 비즈니스 인텔리전스(DW/BI) 시스템을 살펴보는 것으로 시작한다.

1. 비즈니스 중심의 DW/BI 목표

2. 팩트와 디멘션 테이블을 포함하는 다차원 모델링 핵심 개념와 용어

3. 킴벌 DW/BI 아키텍처의 구성요소와 사상

4. 다른 DW/BI 아키텍처와의 비교, 각 아키텍처에서 다차원 모델링의 역할

5. 다차원 모델링에 대한 오해

데이터 수집과 데이터 분석이라는 서로 다른 세계

정보는 대부분 두가지 목적 1)운영상 기록의 보관과 2)의사결정을 위한 분석 으로 사용된다.

데이터 웨어하우스와 비즈니스 인텔리전스의 목표

- DW/BI의 목표는 회사 복도를 걸으면서 현업 관리자들이 하는 이야기를 들으면 쉽게 알아낼 수 있다.
   "우리는 엄청난 데이터를 수집하지만, 거기에 접근할 수가 없다"
   "우리는 데이터를 사방으로 자르고 쪼개서 분석하길 원한다"
   "비즈니스 사용자도 쉽게 데이터에 접근할 수 있어야 한다"
   "단지 나에게 중요한 것만 보여줘라"
   "우리는 의사결정 자체가 아니라, 누가 올바른 숫자를 갖고 있는 지에 대해 논쟁하느라 전체 회의 시간을 허비한다"
   "사람들이 정보 활용을 통해 좀 더 사실 기반으로 의사결정하기를 원한다"

- 정리해보면..
1. DW/BI 시스템은 정보에 쉽게 접근 가능하도록 만들어야 한다.

2. DW/BI 시스템은 일관된 정보를 제공해야 한다.

3. DW/BI 시스템은 변화에 유연해야 한다.

4. DW/BI 시스템은 정보를 적시에 제공해야 한다.

5. DW/BI 시스템은 정보자산을 보호하는 보안 요새가 되어야 한다.

6. DW/BI 시스템은 향산된 의사결정을 위해 권위 있고 신뢰할 수 있는 토대가 되어야 한다.
DW는 의사결정 지원을 위한 올바른 데이터를 가져야 한다. DW/BI 시스템을 통해 기대할 수 있는 갖강 중요한 성과물은 분석 결과로 제시된 증거에 기반한 의사결정이다. 이러한 의사결정은 DW/BI 시스템이 있기에 가능한 것이며, 비즈니스적으로 영향과 가치를 준다. DW/BI이전에 이름 붙여진 '의사결정 지원 시스템'이라는 말은 여전히 여러분이 설계하는 DW/BI에 대한 최고의 설명이다.

7. DW/BI 시스템이 성공적이라고 생각하기 위해서는 현업이 DW/BI 시스템을 받아 들어야 한다.

다차원 모델링 소개

- 다차원 모델링은 두가지 요구 사항을 동시에 충족하기 떄문에 분석 데이터 제공 시 선호하는 기법으로 널리 바다들여진다.
    ① 비즈니스 사용자에게 이해하기 쉬운 데이터를 제공
    ② 빠른 쿼리 성능을 제공

- 다차원 모델링은 데이터베이스를 단순하게 만드는 오래된 기법이다.
- 단순하게 시작한 데이터 모델은 설계 끝까지 단순할 가능성이 있다. 복잡하게 시작한 모델은 결국 복잡하게 끝나서 쿼리 성능은 느리고 현업 사용자에게는 거부 당할 것이다.

- "모든 것을 더 이상 간단해질 수 없을 만큼 가능한 간단하게 만들어라"

- 갱신 또는 삽입 트랜잭션이 데이터베이스의 한 부분만 건드리기 떄문에 3차 정규화 구조는 운영 업무 처리에 매우 유용하다. 그러나 정규화 모델은 BI쿼리를 처리하기에는 너무 복잡하다. 
- 사용자의 예측 불가한 쿼리의 복잡성은 데이터베이스 optimizer를 압도하여 결국 장애 수준의 쿼리 성능을 보이게 된다.

1) 스타 스키마 대 OLAP 큐브

- 관계형 DBMS에서 구현되는 다차원 모델은 별 모양 구조를 닮아 스타스키마라 불린다.
- 다차원 데이터베이스 환경에서 구현되는 다차원 모델은 OLAP큐브라 불린다.
- 스타 스키마와 OLAP큐브 모두 디멘션들로 구성된 공통적인 논리 설계를 갖지만 물리적인 구현은 다르다.

- 일반적으로 상세한 최소단위 정보는 스타 스키마에 적재하고, OLAP 큐브는 그 후에 부가적으로 스타 스키마로
부터 생성하는 것을 권장한다.

- 스타 스키마의 두 가지 핵심 요소를 살펴보자.

2) 성과 측정을 위한 팩트 테이블
- 다차원 모델에서 팩트 테이블은 조직의 비즈니스 프로세스 이벤트에서 발생하는 성과 측정값을 저장한다.

- 가장 유용한 팩트는 판매 금액처럼 숫자이면서 합산 가능한 팩트이다. -> 합산 가능여부는 매우 중요하다.

- 부분적 합산만 가능 : '계좌잔액' , '재고수량' 팩트는 시간 디멘션으로는 합산할 수 없다.

- 아예 합산이 불가능한 팩트 : '단가', '비율'은 어떤 디멘션으로도 합산할 수 없다.

- 팩트의 그레인 : 1)트랜잭션(일반적), 2)주기적 스냅샷, 3) 점진적 스냅샷

3) 배경을 설명하는 디멘션 테이블

- 50~100개의 속성을 갖는 경우가 일반적

- 디멘션 속성은 '~별'로 식별된다. 예를 들면, 사용자가 브랜드별 매출액을 보기 원할 때, 브랜드는 디멘션 속성으로 사용 가능해야 한다.

- 어떤 숫자가 팩트인지 디멘션 속성인지 결정해야 하는 설계자의 딜레마는 대부분의 경우 그리 어렵지 않다. 연속적인 숫자 값이 관찰된다면 거의 팩트일 것이다. 비교적 작은 목록 범위 내에서 이산적으로 끊어지는 숫자라면 대부분 디멘션 속성이다.

- 디멘션 테이블은 3차 정규화하지 않고, 한 테이블 안에 다대일 관계를 납작하게 펼치는 반정규화가 일반적이다.

4) 스타 시크마에서 조인되는 팩트와 디멘션

- 다차원 모델의 단순성은 성능 측면에서도 이점이 있다.

킴벌의 DW/BI 아키텍처

- DW/BI 환경에는 고려해야 할 네 가지 분리된 개별 구성요소가 있는데 이는 1)운영계 원천 시스템, 2)ETL시스템, 3)프레젠테이션 영역, 4)BI애플리케이션

1) 운영계 원천 시스템

- 비즈니스 트랜잭션을 포착하여 기록하는 운영 시스템

- 좋은 데이터 웨어하우스는 과거를 보여주어야 하는 원천 시스템의 많은 책임을 경감시켜준다. 원천 시스템은 다른 운영 시스템에 있는 제품, 고객, 지역, 기간 등 공통 데이터를 공유할 책임이 없는 특정 목적만을 위한 애플리케이션이다.

2) 추출, 변환 및 적재 시스템 (ETL)

- 운영계 원천 시스템가 DW/BI 프레젠테이션 영역 사이의 모든 것이다.

3) 비즈니스 인텔리전스 지원을 위한 프레젠테이션 영역

- 최소단위 데이터는 정규화 모델에 두고 다차원 모델에는 요약 데이터만 저장하는 것은 용납될 수 없다.

4) 비즈니스 인텔리전스 애플리케이션

대안적 DW/BI 아키텍처

- 아키텍처 성향과 관계없이 다차원 모델링은 항상 존재

1) 독립적 데이터 마트 아키텍처

- 아키텍처화 되지 않은 아키텍처

- 이런것도 있다..

2) 인몬의 허브-앤-스포크 기업 정보 공장(CIF) 아키텍처

- Data Acquisition : CIF에서 데이터는 운영원천시스템으로부터 추출되고 종종 데이터 획득이라고도 불리는 ETL시스템을 통해 처리된다. 
- EDW : 이 처리로부터 나온 최소단위 데이터는 3차 정규화 데이터베이스에 적재된다. 이 정규화된 최소단위 저장소는 CIF아키텍처에서 EDW라 불린다. 킴벌 아키텍처는 ETL처리를 지원하기 위해 선택적으로 정규화를 허용하지만, CIF에서 정규화된 EDW는 필수 구조이다.
- Data Delivery : CIF방식을 적용한 기업에는 상세 레벨 데이터 또는 가용 데이터 적시성 때문에 종종 EDW에 접근하는 현업 사용자가 있다. 그러나 이후의 데이터 전달 ETL프로세스는 전적으로 후방의 비즈니스 사용자를 지원하는 리포팅과 분석 환경을 생성한다. 분석용 데이터베이스는 다차원으로 구성되기도 하지만 일반적으로 킴벌 아키텍처의 프레젠테이션 영역 구조와 다르다. 그들은 주로 부서 중심적이고(비즈니스 프로세스 중심으로 구성되기 보다), 집계된 데이터를 제공한다(최소 단위 데이터보다는). 데이터 전달 ETL프로세스가 기본적인 요약을 넘어서는 비즈니스 규칙을 적용한다면(부서 차원에서 칼럼명 변경 또는 계산 로직 변경과 같은), EDW에 있는 최소단위 데이터와 분석용 데이터베이스를 서로 연관시키기는 매우 어려울 것이다.

- 킴벌 아키텍처처럼, CIF는 전사 차원의 데이터 중재와 통합을 주장한다. CIF는 정규화된 EDW가 이 역할을 수행하고, 킴벌 아키텍처는 표준 디멘션을 갖는 전사 버스의 중요성을 강조한다.
- 기술적으로 정규화 과정이 곧 통합을 말하는 것은 아니다. 정규화는 단지 다대일 관계를 구현하는 물리 테이블을 생성할 뿐이다. 통합은 서로 다른 원천에서 발생하는 불일치의 해결을 필요로 한다. 분리되어 호환되지 않는 데이터베이스 원천들도 통합과는 전혀 관계없이 정규화될 수 있다. 표준 디멘션에 기반한 킴벌 아키텍처는 이와 반대로, 정규화 여부에 상관없이 데이터 불일치 자체를 해결하는데 초점을 둔다.
- 순수 CIF아키텍처의 극단적인 형태는 데이터 웨어하우스로서 사용될 수 없다. 이런 아키텍처는 최소단위 데이터를 정규화 구조에 가두어 조회하기 어렵게 하고, 서로 다른 현업 사용자 그룹들에게 부서별로 호환되지 않는 데이터 마트를 제공한다.

3) 허브-앤-스포크와 킴벌 하이브리드 아키텍처

- 킴벌과 인몬 CIF 아키텍처의 결합

- CIF 중심의 EDW를 생성하되 분석과 리포팅을 원하는 비즈니스 사용자의 접근은 금지한다. → 프레젠테이션 영역을 생성하는 원천에 불과

- 프레젠테이션 영역의 데이터는 다차원적이고 최소단위이며, 프로세스 중심적으로 전사 데이터 웨어하우스 버스 아키텍처를 준수한다.
- 사용자 쿼리는 전적으로 다차원의 프레젠테이션 영역에서 처리하도록하여 3차 정규화 EDW의 성능과 활용성 문제를 해결하면서 기존에 투자된 통합 저장소를 활용할 수 있다. → 3차 정규화 EDW 생성에 이미 투자를 했는데, 이러한 EDW가 기대하는 만큼 빠르지 않고 유연한 리포팅과 분석을 제공하고 있지 않은 상태라면, 이 하이브리드 방식은 여러분의 기업에 적당할 수 있다.
- 하지만 아무것도 없이 처음부터 시작한다면.... 최소 단위 데이터가 중복적으로 저장되고 이동되기 떄문에 개바로가 유지보수 측면에서 시간과 비용이 많이 든다.

다차원 모델링에 대한 오해
오해1 : 다차원 모델링은 오직 요약 데이터를 위한 것이다.
- 비즈니스 사용자가 요청한 질문들을 모두 예측할 수 없기 때문에 그들이 가장 상세한 데이터를 조회할 수 있도록 해야 한다. 그래야 그들은 비즈니스 요청에 따라 데이터를 롤업할 수 있다. 

오해2 : 다차원 모델은 전사가 아닌 부서단위 모델이다.
- 다차원 모델은 조직의 부서에 기초해 경계를 그리는 것이 아니라 주문, 송장, 서비스 콜과 같은 비즈니스 프로세스를 중심으로 구성되어야 한다.

오해3 : 다차원 모델은 확장성이 없다.
- 정규화 모델과 다차원 모델 모두 동일한 정보와 데이터 관계를 포함한다. 논리적으로 내용은 동일하다.
- 한 모델로 표현된 모든 데이터 관계는 다른 모델로 정확히 표현될 수 있다.
- 난이도는 다르겠지만, 정규화 모델과 다차원 모델 모두 동일 지문에 정확히 대답할 수 있다.

오해4 : 다차원 모델은 예측 가능한 분석 패턴에만 유용하다.
- 다차원 모델은 사전에 정의된 리포트나 분석에 맞춰서 설계하지 말아야 한다.
- 설계는 측정 프로세스를 중심으로 해야 한다. 
- 분명히 BI애플리케이션의 조회조건과 레이블을 고려하는 것은 중요하다. 그러나 여러분은 Top 10 리포트 목록을 위해 설계하지는 말아야 한다. 이 목록은 계쏙 바뀌어 다차원 모델이 계쏙 변경되게 만든다.
- 핵심은 항샹 변화하는 분석 패턴이 아니라 안정된 조직이 측정 이벤트에 초점을 맞추는 것이다.
- 쿼리 유연성의 비밀은 팩트 테이블을 가장 낮은 레벨로 만드는 것이다.
- 다차원 모델을 위한 정확한 시작점은 유연성과 확장성을 극대화할 수 있도록 최하위 상세 데이터에서 데이터를 표현하는 것이다.

오해5 : 다차원 모델은 통합되지 않는다.
- 다차원 모델은 전사 데이터 웨어하우스 버스 아키텍처를 준수한다면 반드시 통합된다.
- 표준 디멘션은 ETL 시스템에서 중아아 집중적이고 지속적인 마스터 데이터로서 생성되고 유지된다. 그리고 데이터 통합과 의미론적 일관성을 위해 다차원 모델에서 재사용된다.

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배치를 잘 짜셔야 합니다. ㅎㅎ