[HSKIM] 05. 권한 관리

05. 권한 관리

1. 인증 방법

- Windows 인증     : 로그인 계정에 대한 인증을 Windows 담당함

- SQL SERVER 인증 : SQL SERVER 자체 계정으로 인증 함.

2. 인증 모드

- Windows 인증 모드 : Windows 인증 방법만 허가함. 권고되는 인증 모드
- 혼합 인증 모드       : Windows, SQL SEVER 인증 방법을 모두 허가.

* 인증 모드는 설치 할때 설정하고 운영 중에도 변경은 가능하지만 서비스 재시작을 해야 반영이 된다.


  - 개체 탐색기에서 변경

 - 레지스터 에서 변경  ( 1 :  Windows 모드 , 2 : 혼합 모드)

3.역할

고정 서버 수준 역할

다음 표에서는 고정 서버 수준 역할과 해당 기능을 보여 줍니다.

준 역할	설명
sysadmin	sysadmin 고정 서버 역할의 멤버는 서버에서 모든 작업을 수행할 수 있습니다.
serveradmin	serveradmin 고정 서버 역할의 멤버는 서버 차원의 구성 옵션을 변경하고 서버를 종료할 수 있습니다.
securityadmin	securityadmin 고정 서버 역할의 멤버는 로그인 및 해당 속성을 관리합니다. 서버 수준 사용 권한을 GRANT, DENY 및 REVOKE할 수 있습니다.데이터베이스에 액세스할 수 있는 데이터베이스 수준 사용 권한도 GRANT, DENY 및 REVOKE할 수 있습니다. 또한 이 역할의 멤버는 SQL Server 의 로그인 암호를 다시 설정할 수 있습니다. 중요: 보안 관리자는 데이터베이스 엔진에 대한 액세스 권한을 부여하고 사용자 권한을 구성할 수 있으므로 대부분의 서버 사용 권한을 할당할 수 있습니다. securityadmin 역할은 sysadmin 역할과 동일하게 처리되어야 합니다.
processadmin	processadmin 고정 서버 역할의 멤버는 SQL Server의 인스턴스에서 실행 중인 프로세스를 종료할 수 있습니다.
setupadmin	setupadmin 고정 서버 역할의 멤버는 Transact-SQL 문을 사용하여 연결된 서버를 추가하거나 제거할 수 있습니다. ( Management Studio를 사용할 때 sysadmin 멤버 자격이 필요합니다.)
bulkadmin	bulkadmin 고정 서버 역할의 멤버는 BULK INSERT 문을 실행할 수 있습니다.
diskadmin	diskadmin 고정 서버 역할은 디스크 파일을 관리하는 데 사용됩니다.
dbcreator	dbcreator 고정 서버 역할의 멤버는 데이터베이스를 생성, 변경, 삭제, 복원할 수 있습니다.
public	모든 SQL Server 로그인은 public 서버 역할에 속합니다. 서버 보안 주체에게 보안 개체에 대한 특정 사용 권한이 부여되지 않았거나 거부된 경우 사용자는 해당 개체에 대해 public으로 부여된 사용 권한을 상속 받습니다. 모든 사용자가 개체를 사용할 수 있도록 하려는 경우에만 개체에 public 권한을 할당해야 합니다. public의 멤버 자격은 변경할 수 없습니다. 참고: public은 다른 역할과 다른 방식으로 구현되며 public 고정 서버 역할에서 사용 권한이 부여, 거부 또는 취소될 수 있습니다.

고정 데이터베이스 역할

역할 이름	Description
db_owner	db_owner 고정 데이터베이스 역할의 멤버는 데이터베이스에서 모든 구성 및 유지 관리 작업을 수행할 수 있고 SQL Server에서 데이터베이스를 삭제할 수도 있습니다. SQL 데이터베이스 및 SQL 데이터 웨어하우스에서 일부 유지 관리 작업은 서버 수준 권한이 필요하여 db_owners으로 수행할 수 없습니다.
db_securityadmin	db_securityadmin 고정 데이터베이스 역할의 멤버는 역할 멤버 자격을 수정하고 사용 권한을 관리할 수 있습니다. 이 역할에 보안 주체를 추가하면 원하지 않는 권한 상승이 설정될 수 있습니다.
db_accessadmin	db_accessadmin 고정 데이터베이스 역할의 멤버는 Windows 로그인, Windows 그룹 및 SQL Server 로그인의 데이터베이스에 대한 액세스를 추가하거나 제거할 수 있습니다.
db_backupoperator	db_backupoperator 고정 데이터베이스 역할의 멤버는 데이터베이스를 백업할 수 있습니다.
db_ddladmin	db_ddladmin 고정 데이터베이스 역할의 멤버는 데이터베이스에서 모든 DDL(데이터 정의 언어) 명령을 실행할 수 있습니다.
db_datawriter	db_datawriter 고정 데이터베이스 역할의 멤버는 모든 사용자 테이블에서 데이터를 추가, 삭제 또는 변경할 수 있습니다.
db_datareader	db_datareader 고정 데이터베이스 역할의 멤버는 모든 사용자 테이블의 모든 데이터를 읽을 수 있습니다.
db_denydatawriter	db_denydatawriter 고정 데이터베이스 역할의 멤버는 데이터베이스 내의 사용자 테이블에 있는 데이터를 추가, 수정 또는 삭제할 수 없습니다.
db_denydatareader	db_denydatareader 고정 데이터베이스 역할의 멤버는 데이터베이스 내에 있는 사용자 테이블의 데이터를 읽을 수 없습니다.

4. 로그인 계정

- Windows 계정 만들기
CREATE LOGIN 윈도우계정
  FROM WINDOWS
  WITH DEFAULT_DATABASE = master

- SQL SEVER 자체 계정 만들기
CRATE LOGIN 계정
 WITH PASSWORD = 'DKVAH!!' ,
         DEFAULT_DATABASE = master

5. 사용자 계정 만들기

CREATE USER <사용자계정>
   FOR LOGIN <로그인계정>

6. 스키마

데이터베이스 안의 스키마 안의 테이블.
데이터베이스 개체에 대한 네임스페이스이다. 즉, 데이터베이스 개체가 갖는 고유한 이름을 결정 짓는다.

 - 이점
1) 개체의 소유자와는 상관없이 스키마를 이용해 개체를 그룹화 하여 관리에 효율을 꾀할 수 있습니다.

2) 권한 관리의 경우 개체 개별 적으로 권한을 허용하지 않고 스키마에 권한을 허용하면 스키마에 포함된 모든 개체에 대해 자동으로 권한을 줄 수 있어 권한 관리가 효율적입니다.

3) 소유자 계정을 삭제하기 위해 소유자가 소유한 개체의 소유자를 변경하거나 개체를 삭제할 필요가 없습니다. SQL 서버 2000에서는 개체를 소유한 소유자의 계정을 삭제 할 수 없었습니다.

- dbo 스키마
 dbo 스키마는 모든 데이터베이스에 기본적으로 포함되어 있는 스키마다.
 개체 이름 앞에 스키마를 붙이지 않으면, 기본 스키마가 붙은 것으로 간주한다.

- 스키마 만들기
CREATE SCHEMA hyunsoo

- 스키마

- 스키마 속성

7. 활용

-로그인 계정 확인
SELECT name, type_desc, create_date, is_disabled
FROM sys.server_principals
WHERE type_desc IN ('SQL_LOGIN','WINDOWS_LOGIN')

-데이터베이스 사용자 확인
SELECT name, type_desc, create_date
FROM sys.database_principals
WHERE type_desc IN ('WINDOWS_USER', 'SQL_USER')

AND sid IS NOT NULL

     

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

2장 킴벌 다차원 모델링 기법 개요

우리는 여러분이 이 장을 한 번에 처음부터 끝까지 읽을 것을 기대하지 않는다. :D
대신 이 장이 우리 기법들에 대한 참조 역할을 하길 바란다. :D
각각의 기법들에 관련해서는 활용 사례에 기반한 더 자세한 설명과 그림이 포함된 이후 장들의 위치를 추가하였다. :D

기본개념

비즈니스 요구 사항과 데이터 현황 수집

• 비즈니스 요구 사항과 원천 데이터의 실상을 이해할 필요가 있다.
• 현업 담당자와 회의를 통해 핵심성과지표(KPI), 당면한 비즈니스 이슈, 의사결정프로세스, 분석 요건 등을 들을 수 있고, 비즈니스 목표를 이해할 수 있다.
• 원천 시스템 전문가와 협의하여 면밀한 데이터 프로파일링을 수행하고, 이를 통해 원천 데이터의 실상을 확인하여 데이터 가용성을 평가할 수 있다.

다차원 모델링 워크숍

• 다차원 모델은 현업 업무 담당자와 데이터 거버넌스 담당자가 공동으로 참여하여 설계해야한다.
• 다차원 모델은 비즈니스와 분석 요건을 전체적으로 이해하지 못한 사람들만으로 설계되어서는 안 된다. 
• 협업은 핵심적인 요소이다.

4단계 다차원 설계 프로세스

• 다차원 모델링에서 네 가지 핵심 의사결정 항목을 아래와 같다

1. 비즈니스 프로세스를 선택하라.
2. 그레인을 확정하라.
3. 디멘션을 식별하라.
4. 팩트를 식별하라.

• 위의 질문들의 대한 대답은, 공동 모델링 회의에서 비즈니스 요구사항과 원천 데이터 실상을 고민하면서 결정된다.
• 비즈니스 프로세스, 그레인, 디멘션, 팩트가 확정된 이후 설계팀은 테이블과 칼럼명, 샘플 데이터와 비즈니스 규칙을 결정하게 된다.

비즈니스 프로세스

• 조직에서 수행되는 운영 업무 활동이며 예제로는 주문 접수나, 보험 클레임 처리, 수강신청, 매월 전 계좌를 결산하는 일 등이 있다.
• 비즈니스 프로세스 이벤트는 팩트 테이블에 팩트들로 변환될 실적 수치를 생성하고 기록한다.
• 팩트 테이블은 하나의 비즈니스 프로세스 결과에 초점을 둔다.
• 프로세스 선택은 중요하다. 설계 대상을 정하는 일이고, 그에 따른 그레인, 디멘션, 팩트들의 확정이 뒤따르기 때문이다.
• 각 비즈니스 프로세스는 전사 데이터 웨어하우스 버스 매트릭스에서 하나의 행에 해당된다.

그레인

• 그레인 확정은 다차원 설계에서 중요한 단계이다.
• 그레인은 팩트 테이블의 로우 하나가 정확히 무엇을 나타내는지를 정의한다.
• 모든 후보 디멘션과 팩트가 그레인에 일관성을 가져야 하므로, 디멘션과 팩트를 선별하기 전에 그레인이 확정되어야만 한다.
• 이러한 일관성은 BI애플리케이션 성능과 사용 편의성을 중시하는 다차원 설계가 전체적으로 동질성을 가지게 한다.
• 최소단위 그레인은 비즈니스 프로세스에서 발생하는 데이터 중 가장 낮은 레벨을 의미한다.
• 그레인 확정은 최소단위 그레인 데이터부터 시작하기를 권장한다.

상황을 설명하는 디멘젼

• 디멘션 테이블은 설명 속성을 포함하며, bi애플리케이션에서 팩트의 그룹핑이나 값에 대한 필터링에 사용된다.
• 디멘션 : "누가, 언제, 어디서, 무엇을, 어떻게, 왜"의 비즈니스 프로세스 이벤트를 둘러싼 배경을 설명한다.
• 언제든지 하나의 팩트 로우에 연결하는 디멘션은 하나이 값이어야 한다.

측정을 위한 팩트

• 팩트 : 비즈니스 업무 처리에서 발생하는 측정값으로 거의 대부분 숫자이다.
• 하나의 팩트 테이블 로우는 팩트 테이블 그레인으로 기술되는 값 측정 처리와 일대일 관계를 갖는다.
• 팩트 테이블 안에는 확정된 그레인과 부합하는 팩트들만이 허용된다.
• 예를들면, 소매업 업무처리에서 판매된 제품의 수량과 가격은 정상적인 팩트이지만, 매장관리자의 월급은 팩트 테이블에 포함 되어서는 안된다.

스타 스키마와 OLAP 큐브

• 스타 스키마 : 
• RDBMS에서 구현되는 다차원 구조이다. 
• 스타 스키마는 팩트 테이블과 관련된 디멘션의 키 참조를 통해 연결되는 것이 특징이다.
• OLAP큐브 : 
• 다차원 데이터베이스에서 구현되는 다차원 구조이다. 
• OLAP큐브는 관계형 스타스키마로부터 파생되거나 동등할 수 있다.
• OLAP큐브는 차원 속성과 팩트를 포함하지만 SQL보다 분석 기능과 성능이 뛰어난 XMLA, MDX같은 언어를 통해 접근한다.
• OLAP큐브는 다차원 DW/BI시스템 구현의 마지막 단계에 해당되며, 최소 단위 관계형 스타스키마에 기초한 요약구조로서 존재할 수 있다.

다차원 모델의 유연한 확장성

• 다차원 모델은 데이터 관계가 변화될 때 탄력적이다.
• 이후에 오는 모든 변화는 기존 bi쿼리나 애플리케이션의 변경 없이 반영될 수 있고, 쿼리 결과도 영향을 받지 않는다.

• 기존 팩트 테이블의 그레인에 부합하는 팩트는 신규 칼럼 생성을 통해 추가할 수 있다.
• 디멘션은 기존 팩트 테이블에 참조 키 칼럼을 생성함으로써 추가 가능하다. 단, 팩트 테이블의 그레인을 훼손하지 않아야 한다.
• 속성은 기존 디멘션 테이블에 신규 칼럼을 생성함으로써 추가할 수 있다.
• 팩트 테이블의 그레인은 기존 디멘션 테이블에 속성을 추가함으로써 더욱 상세해질 수 있으며, 팩트와 디멘션 테이블에 기존하는 칼럼명을 유지하면서 더 낮은 그레인으로 팩트 테이블을 재정의할 수 있다.

팩트 테이블 기본 기법

팩트 테이블

• 팩트 테이블은 실세계의 운영 업무 처리에 의해 생성되는 숫자로 된 측정값을 포함한다.
• 숫자 측정값 외에도 팩트 테이블에는 항상 연계된 디멘션의 참조 키를 포함하고, 퇴화 디멘션 키와 날짜 및 시간정보도 포함한다.

• 가장 낮은 그레인 레벨에서 팩트 테이블 로우는 운영 업무 처리 하나에 대응된다.
• 그래서 팩트 테이블의 기초 설계는 결과적으로 생성될 리포트의 영향이 아닌, 물리적인 업무 활동에 기초해야 한다.

합산 가능, 부분 합산 가능, 합산 불가 팩트

• 합산 가능한 팩트 : 
• 가장 유연하고 유용한 팩트
• 팩트 테이블과 연관된 어떤 디멘션으로도 합산 가능한다.
• 부분 합산 가능 팩트 : 
• 일부 디멘션에 대해서만 합산 가능하다. (예: 잔고금액, 재고스냅샷의 보유 재고량)
• 재고 스냅샷 스키마에서 보유 재고량은 제품이나 지점 축을 통해 요약되어, 유효한 총계를 나타낼 수 있다.
• 그러나 재고량은 어느 한 시점에서의 잔고 또는 재고의 스냅샷을 표현하기 때문에 시간에 걸쳐 합산할 수 있는 속성은 아니다.
• 날짜를 통해 계좌 잔고나 재고 수준을 집계하는 가장 유용한 방법은 평균을 구하는것이다. 
• 시간에 걸친 평균을 계산하기 위해 SQL문의 AVG함수를 사용할 수 없다.
• 합산 불가 팩트 : 비율과같은 측정값은 완전히 합산 불가능한 것이다.

팩트 테이블의 널 값

• 널 값이 포함된 측정값 칼럼은 팩트 테이블에서 아무런 문제가 없다.
• 널 값은 팩트 테이블의 참조 키 칼럼에서는 피해야 한다. 
• 참조 키 칼럼의 널 값은 자동적으로 참조 무결성 위배를 일으킨다.

표준 팩트

동일 측정값 칼럼이 서로 다른팩트 테이블에 나타난다면 팩트의 기술적 정의가 동일한지 확인해야한다.

트랜잭션 팩트 테이블

트랜잭션 팩트 테이블은 운영 업무 처리가 발생할 때만 로우가 생성되기 때문에 로우 수가 많을 수도 있고 적을 수도 있다.

주기적 스냅샷 팩트 테이블

• 로우는 일 단위, 주 단위, 월 단위와 같은 일정 기간 동안 발생한 많은 측정 이벤트를 요약한다.
• 그레인은 개별 트랜잭션이 아닌 기간이다.

점진적 스냅샷 팩트 테이블

• 프로세스의 처음부터 끝까지 예측 가능한 단계마다 발생하는 측정 이벤트를 요약한다.
• 주문 처리나 클레임 처리와 같은 파이프라인 또는 워크플로우 프로세스는 시작점, 중간단계, 종료가 정의되어 있으며, 이 단계들이 팩트 테이블 설계에 반영된다.
• 팩트 테이블에는 프로세스 각 주요 단계에 해당하는 일자 참조 키들이 있다.
• 점진적 스냅샷 팩트 테이블의 각 로우는 하나의 주문 라인에 해당되며, 주문 라인이 생성될 때 처음으로 추가된다.
• 일련의 처리가 딘행되면서 점진적 스냅샷 팩트 테이블 행은 다시 처리되어 업데이트 된다. 
• 점진적 스냅샷 팩트 테이블 로우의 지속적인 업데이트 과정은 세 가지 팩트 테이블 유형 중 유일하다.

팩트 없는 팩트 테이블

• 많은 측정 이벤트들이 숫자 값으로 기록되지만 어떤 이벤트는 단지 그 시점에 관련된 디멘션 집합만으로도 기록할 수 있다.
• 예를 들면, 학생의 강의 출석 처리는 숫자를 기록하지 않지만 팩트의 로우는 달력 일자, 학생, 강사, 장소 그리고 강좌의 참조 키 만으로도 잘 정의 될 수 있다.
• 고객과의 의사소통은 이벤트이지만 관련된 측정값은 없을 수 있다.
• 팩트 없는 팩트 테이블은 발생하지 않은 부분을 분석하는데 사용될 수 있다.
• 이런 질의는 항상 두 개의 영역이 있는데 하나는 전체 대상 테이블로 모든 발생 가능한 이벤트를 포함하고,
• 다른 하나는 활동 기록 테이블로 발생한 이벤트만을 포함한다.
• 전체 대상 테이블에서 활동 기록 테이블을 빼면, 그 결과는 발생하지 않은 이벤트의 집합이 된다.
• 프로모션 대상이지만 판매 되지 않은 제품은 무엇인가?
• 판매 팩트 테이블은 실제 판매가 된 SKU를 기록한다. SKU가 판매되지 않았을 경우 0으로 판매 수량을 관리하는 팩트 로우는 생성되지 않는데, 만약 판매 수량이 0인 로우를 관리한다면 팩트 테이블의 크기가 너무 커지게 되기 때문이다.
• 관계형 모델에서, 위와 같은 일어나지 않은 일에 대한 질문에 대답하기 위해 프로모션의 범위나 이벤트 관리를 위한 팩트 테이블이 필요하게 된다.

집계 팩트 테이블과 OLAP 큐브

• 집계 팩트 테이블은 오직 쿼리 성능을 향상시키기 위해서, 간단하게 최소단위 팩트 테이블 데이터의 수치 값 만을 롤업한 것이다.
• 이런 집계 팩트 테이블은 최소단위 팩트 테이블과 더불어 BI어플리케이션에 이용되며, BI툴은 쿼리 수행 시 자연스럽게 적절한 집계 레벨을 선택할 수 있다.
• 집계 내비게이션으로 알려진 이 처리 방식은 모든 리포트 작성자, 쿼리 툴, BI애플리케이션이 동일하게 성능의 이점을 활용할 수 있도록 제공되어야 한다.
• 적절하게 설계된 집계 테이블들은 쿼리 성능을 향상시키는 데이터베이스 인덱스처럼 활용되지만, BI애플리케이션이나 비즈니스 사용자가 직접 활용하는 것은 아니다. 
• 요약된 측정값을 포함하는 집계 OLAP큐브는 관계형 집계 테이블과 동일한 방법으로 만들어지지만, OLAP큐브는 비즈니스 사용자가 직접 접근하기 위한 것이다.

통합 팩트 테이블

• 팩트들이 동일한 그레인으로 표현된다면, 여러 프로세스로부터 나온 팩트들을 하나의 통합 팩트 테이블에 함꼐 묶어 넣는 것이 편리할 수 있다.
• 예를 들어, 판매 실적은 판매 예측과 통합하여 하나의 팩트 테이블에 통합해서 계획 대비 실적 분석 작업을 쉽고 빠르게 할 수 있다.
• 통합 팩트 테이블은 ETL처리에 부담을 주지만, BI어플리케이션의 분석 부담은 완화시켜준다.

디멘션 테이블 기본 기법

디멘션 테이블 구조

• 모든 디멘션 테이블은 PK 하나를 갖는다.
• 이 기본 키는 관련 팩트 테이블에 FK로 삽입되어 디멘션 로우를 설명함으로써 팩트 테이블 로우의 의미를 분명하게 해준다.
• 디멘션 테이블은 보통 카디널리티가 낮은 텍스트 속성 칼럼이 많은 반정규화 테이블이다.
• 운영 코드와 분류 값이 속성이긴 하지만 텍스트 형태의 풍부한 설명을 포함해야 최상의 디멘션 속성이 된다.

디멘션 대체 키

• 디멘션 테이블은 유일한 기본 키로 칼럼 하나를 이용한다.
• 이 기본 키는 운영 시스템의 원천키를 그대로 사용하지 않는다.
• 오랜 시간이 지난 후 어떤 변화를 추적할 떄 원천 키에 대한 여러 디멘션 로우가 발생할 수 있고, 하나의 디멘션의 원천 키가 여러 원천 시스템에서 생성될 때 불일치하거나 관리가 어려울 수 있다.
• 모든 디멘션의 기본 키를 의미 없는 정수로 만들어야 한다.
• 단 일자 디멘션은 이 대체 키 규칙에서 예외이다.

원천 키와 영속성 보장 대체 키.

• 운영 원천 시스템에서 생성되는 원천 키는 DW/BI의 관리 밖의 비즈니스 규칙에서 적용된다.
• 예를 들면, 사원이 퇴직한 후 재입사 한다면 사원번호(원천 키)는 변경될 수 있다.
• 이런 직원에 대해 데이터 웨어하우스가 한 개의 키를 갖고자 할 때, 새로운 영속성 보장 대체 키가 생성될 수 있다.
• 이 키는 비즈니스 프로세스에 독립적인 형태를 가지며, 1부터 시작하여 순차적으로 할당되는 간단한 정수여야 한다.
• 한 사원에게 오랜 시간 프로파일 변경으로 많은 대체 키가 생기는 반면, 영속성이 보장 대체 키는 변하지 않는다.

드릴 다운

• 드릴다운은 간단히 말하면 기존 쿼리에 로우 헤더를 추가하는 것이다.
• 새로운 로우 헤더는 SQL GROUP BY 절에 추가되는 디멘션 속성이다.

퇴화 디멘션.

• 때로는 디멘션에 기본 키를 제외하고 다른 내용이 없는 경우가 있다.
• 예를 들면, 송장이 여러 라인 항목을 가질 때, 라인 항목 팩트 로우는 송장의 모든 디멘션 외래 키를 상송하고, 송장에는 다른컨텐츠가 없게 된다. 그러나 송장번호는 라인 항목 레벨의 팩트 테이블에서 유효한 디멘션 키로 남게 된다.
• 퇴화 디멘션은 명시적으로 관련 디멘션 테이블이 없음을 인정한 채, 팩트테이블에 존재한다.
• 퇴화 디멘션은 트랜잭션과 점진적 스냅샷 팩트 테이블에 흔하게 나타난다.

반정규화 디멘션

• 다대일 관계의 고정 레벨 계층을 한 개의 로우에 여러 속성으로 반정규화 해야 한다.
• 디멘션 반정규화는 다차원 모델링에 있어 간단하면서도 쿼리 속도가 빨라야 한다는 두 가지 목표를 달성하도록 해준다.

다중 계층을 가지는 디멘션

• 많은 디멘션이 하나 이상의 계층을 갖는다.
• 여러 계층은 동일 디멘션 테이블 내에 있을 수 있다.

플래그와 분류 값

• 애매한 약어, 여부 플래그, 업무의 분류 값은 그 자체로도 의미가 있도록 원래의 전체 용어로 보완하여 디멘션 테이블에 담아야 한다.
• 코드 안에 여러 의미를 갖는 운영 코드들은 각 코드를 분리하고, 각각의 딤네션 설명 속성으로 변경해야 한다.

디멘션의 널 속성

• 디멘션 속성에 널 값은 데이터베이스마다 그룹핑과 제한 조건 시 처리하는 방식이 다르기 때문에 피해야 한다.

달력 디멘션

• SQL 내에서 계산되기를 기대해서는 안 되며, 달력 디멘션에서 그것을 찾을 수 있도록 하는 편이 낫다.
• 파티셔닝을 위해서, 일자 디멘션의 기본 키는 순번 형태의 대체 키 보다는 정수로 YYYYMMDD와 같은 형태로 표현하는 것이 좀 더 의미 있다.
• 그러나 일자 디멘션 테이블은 '미정'이거나 '알 수 없음'을 표현할 수 있는 특별한 로우가 필요하다.
• 일자/타입스탬프는 디멘션 테이블에 대한 연결 키가 아니라 팩트 테이블에 별도로 존재하는 칼럼으로 보는 것이 바람직한다.

롤 플레잉 디멘션

• 한 개의 디멘션 물리 테이블이 하나의 동일 팩트 테이블에서 여러 번 참조될 수 있으며, 각 참조는 논리적으로 구별된 디멘션 역할로 인식한다.
• 예를 들어, 팩트 테이블은 여러 일자를 가질 수 있다.

정크 디멘션

• 트랜잭션 비즈니스 프로세스는 대체로 잡다하과, 카디널리티가 낮은 플래그나 구분자를 많이 만들어 낸다.
• 이 각각의 플래그와 속성을 별도의 디멘션으로 만드는 것보다는 하나의 정크 디멘션에 모든 정보를 포함하여 생성하는 것이 좋을 것이다.
• 모든 속성에 대한 전체 값의 조합으로 만들 필요는 없고, 원천 데이터에서 실제 발생하는 값들의 조합만 포함시키면 된다.

스노우플레이크 디멘션

• 스노우플레이크가 계층 데이터를 정확히 보여주는 장점이 있지만 비즈니스 사용자의 이해가 어렵고, 스노우플레이크 구조상의 연결을 따라 다녀야 하는 불편함이 있다.
• 더구나 쿼리 성능에 좋지 못한 영향을 준다.
• 스노우플레이크가 아니어도 반정규화 디멘션 테이블에 동일한 정보를 정확히 포함시킬 수 있다.

아웃리거 디멘션

• 디멘션은 또 다르디멘션의 참조를 포함할 수 있다.
• 예를 들어, 은행계좌 디멘션은 계좌 개설 일자를 위해 별도의 일자 디멘션을 참조할 수 있다.
• 이때, 이 두번째 참조 디멘션을 아웃리거 디멘션이라고 부른다.
• 아웃리거 디멘션은 활용 가능하지만 가급적 활용하지 말아야 한다.
• 많은 경우, 디멘션 간의 상관관계는 팩트 테이블로 내려서, 팩트 테이블에 두 디멘션의 기본키가 외래 키로 반영되어야 한다.

표준 디멘션을 통한 통합

다차원 모델링의 성공적인 접근 방법 중 하나는 여러 비즈니스의 데이터를 통합하는 간단하면서도 강력한 레시피를 정의하는 것이다.

표준 디멘션

• 디멘션 테이블들은 서로 다른 디멘션 테이블의 속성이 동일한 칼럼명과 동일한 내용을 가질 때 표준 디멘션이 된다.
• 두 팩트 테이블로부터의 정보는 양쪽 모두 포함된 표준 디멘션의 속성을 사용함으로써 하나의 리포트에 합칠 수 있다.
• 표준 속성이 로우 헤더에(SQL쿼리의 그룹핑 칼럼으로) 사용될 떄, 두 팩트 테이블의 결과는 드릴 어크로스를 통해 하나의 로우에 나란히 표현될 수 있다.
• 이것이 전사 DW/BI시스템에서 통합의 본질이다.

축소 디멘션

• 축소 디멘션은 표준 디멘션으로 기본 디멘션의 로우 또는 칼럼의 부분집합 디멘션이다.
• 축소 디멘션은 집계 팩트 테이블을 만들 때 필요하며, 그래뉼래러티 레벨이 높은 데이터를 양산하는 비즈니스 프로세스에도 당연히 필요하다.
• 그 예로는 월별 브랜드별(판매 데이터에 포함되는 일자, 제품의 상세 레벨 대신) 에측 실적과 같은 것이다.
• 표준 디멘션의 부분집합으로 또 다른 형태는 두 개의 디멘션이 동일한 상세 레벨이나 로우 데이터의 일부만을 가지는 경우이다.

드릴 어크로스

• 드릴 어크로스는 동일한 표준 속성의 로우 헤더를 포함하는 두 개 이상의 팩트 테이블에 대해서 분할된 쿼리를 생성하는 것을 의미한다.

가치 사슬.

• 가치사슬은 조직의 중심이 되는 비즈니스 흐름을 말한다.
• 예를들어, 소매 판매의 가치 사슬은 구매, 재고, 판매로 구성된다. 회계 원장 가치 사슬은 예산, 집행, 지불로 이루어진다.
• 운영 원천 시스템은 보통 가치 사슬의 각 단계마다 트랜잭션이나 스냅샷을 발생시킨다.
• 각 프로세스마다 고유한 그래뉼래러티와 데이터 레벨, 고유한 시간 간격으로 발생하는 수치를 갖기 때문에, 각 프로세스는 적어도 하나의 최저 팩트 테이블에 데이터를 발생시킨다.

전사 데이터 웨어하우스 버스 아키텍쳐

• 전사 데이터 웨어하우스 버스 아키텍처는 전사 DW/BI 시스템을 위한 점진적 구축방법을 제공한다.
• 이 아키텍처는 비즈니스 프로세스 단위의 관리 가능한 범위로 DW/BI시스템을 구축할 수 있도록 도와주는 반면, 재사용되는 표준 디멘션을 통해 프로세스간 통합을 꾀한다.
• 버스 아키텍처는 기술이나 데이터베이스 플랫폼에 종속되지 않으며, 관계형 DB나 OLAP다차원 구조에 둘 다 적용 가능하다.

전사 데이터 웨어하우스 버스 매트릭스

• 전사 데이터 웨어하우스 버스 매트릭스는 전사 데이터 웨어하우스 버스 아키텍처를 설계하고, 의사소통할 떄 유용한 도구이다.
• 매트릭스의 행은 비즈니스 프로세스이고, 열은 디멘션이다.
• 매트릭스의 색칠해진 셸은 해당하는 행과 열에 해당하는 비즈니스 프로세스와 디멘션의 관련 여부를 나타낸다.

상세 구현 버스 매트릭스

• 상세 구현 버스 매트릭스는 각 비즈니스 프로세스 행을 팩트 테이블이나 OLAP큐브에 레벨을 상세화한 버스 매트릭스다.
• 이 상세 레벨에서, 각 팩트들의 정확한 그레인이 기술될 수 있다.

기회 및 이해관계자 매트릭스

• 전사 데이터 웨어하우스 버스 매트릭스 행이 식별된 후 디멘션 열 대신 마케팅, 영업, 재무와 같은 비즈니스 기능으로 바까ㅜ고, 비즈니스 프로세스 행과 비즈니스 기능이 연관 있을 시 해당하는 셀을 칠하면 다르매트릭스를 그릴 수 있다.
• 이 매트릭스는 각 프로세스 행에 해당하는 비즈니스 그룹을 알 수 있어 공동 설계 회의가 필요할 경우 참석 대상 비즈니스 그룹을 식별하는데 유용하다.

---------------------------------------------------------------------------- 여기까지 :ㅇ

디멘션 이력 관리 속성 다루기

타입0 : 최초값 유지
타입1 : 덮어쓰기
타입2 : 신규 로우 추가
타입3 : 신규 속성 추가
타입4 : 미니 디멘션 추가
타입5 : 미니 디멘션 타입과 타입 1 아웃리거 추가
타입6: 타입1 속성을 타입2 디멘션에 추가
타입7 : 타입1과 타입2 디멘션 병행

디멘션 계층 다루기

고정 레벨 계층
소규모 불규칙/가변 레벨 계층
브리지 테이블을 활용한 불규칙/가별 레벨 계층
전체 경로 속성을 가지는 불규칙/가변 레벨 계층

고급 팩트 테이블 기법

팩트 테이블 대체 키
지네 팩트 테이블
애트리뷰트와 팩트로 쓰이는 숫자 값
시간차이(Lag)/소요시간 팩트
헤더/라인 팩트 테이블
배부 팩트
배부를 활용한 손익 팩트 테이블
다중 통화 팩트
다중 측정단위 팩트
년 누적 팩트
팩트 테이블간 조인을 피아히 위한 다중 경로(multi pass) SQL
팩트 테이블의 시간 범위 추적
지연 처리 팩트

디멘션 고급기법

디멘션 테이블 간 조인
다주아 값 디멘션과 브리지 테이블
시간에 따라 변하는 다중 값 브리지 테이블
행동 태그 시계열
행동연구그룹
디멘션 속성으로서의 집계 팩트
동적 값 구간
텍스트 주석 디멘션
다중시간대
측정값 분류 디멘션
진행 단계 디멘션
핫 스와퍼블 디멘션
추상화 범용 디멘션
감사 디멘션
지연 처리 디멘션

특수 목적 스키마

이기종 상품을 위한 슈퍼타입과 서브타입 스키마
실시간 팩트 테이블
오류 처리 스키마

[HSKIM] 03. 데이터 베이스

1. 데이터베이스 정보 확인



SELECT name                          AS DB이름
     , create_date                   AS 생성일
     , compatibility_level           AS 호환성수준
     , collation_name                AS 정렬방식
     , user_access_desc              AS 액세스허용
     , state_desc                    AS 상태
     , recovery_model_desc           AS 복구모델
     , snapshot_isolation_state_desc AS 스냅샷격리수준
     , log_reuse_wait_desc           AS 로그재사용대기
FROM sys.DATABASES 

1-1 호환성수준
버전이 바뀌면서 이전 버전에서 제공하던 기능이나 쿼리문을 더 이상 사용하지 못하게 되는 경우가 있다. 호환성 수준을 낮추면 이전 버전에서 사용하던 기능을 계속해서 사용할 수 있다.

1-2 상태(OFFLINE | ONLINE | EMERGENCY)

OFFLINE으로 설정하면 데이터베이스가 닫히고 종료되어 사용할 수 없다. ONLINE으로 설정하면 데이터베이스가 열려 데이터베이스를 사용할 수 있다. EMERGENCY로 설정하면 데이터베이스가 읽기 전용으로 표시되면, sysadmin 고정 서버 역할의 맴버만 연결할 수 있다. 로그 파일이 손상되어 '주의 대상'으로 표시된 데이터베이스를 EMERGENCY로 설정하면 읽을 수 있다. 기본값은 ONLINE이다.

1-3 엑세스 제한(SINGEL_USER | RESTRICTED_USER | MULTI_USER)

SINGLE_USER로 설정하면 한 번에 한 명의 사용자만 데이터베이스에 연결할 수 있다. RESTRICTED_USER로 설정하면 db_owner 고정 데이터베이스 역할, dbcreator 와 sysdamin 고정 서버 역할의 멤버만 연결 할 수 있으며, 연결할 수 있는 멤버의 수에는 제한이 없다. MULTI_USER는 적합한 권한을 가진 모든 사용자가 연결 할 수 있다. 

1-4 복구모델(RECOVERY)

전체(FULL)로 설정하면 데이터베이스가 손상된 경우 트랜잭션 로그 백업을 사용하여 완벽하게 복원할 수 있다. 특정 시점의 복원도 가능하여 중요 데이터베이스에 적합한 복구 모델이다. 대량 로그(BULK_LOGGED)로 설정하면 특정 대량 작업에 대해 로그 공간을 최소로 사용하여, 대량 작업에 대한 성능을 높일 수 있다. 하지만 대량 작업이 발생한 경우 특점 시점 복원을 할 수 없다. 단순(SIMPLE)으로 설정하면 최소한의 로그 공간을 사용하며, 로그 백업을 수행할 수 없다. 개발용이나 읽기 전용 데이터베이스에 적합한 복구 모델이다.

2. 데이터베이스 파일 크기 정보

EXEC sp_helpfile

-- 사용 중인 디스크 크기
SELECT(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;

-- 전체 디스트 크기
SELECT SUM(size)*1.0/128 AS [size in MB]

FROM sys.database_files

-- DB 사이즈 줄이기(MDF, NDF)
DBCC SHRINKDATABASE ([DB명], TRUNCATEONLY)

-- 로그 사이즈 줄이기(LDF)

DBCC SHRINKFILE(tempdev, TRUNCATEONLY);

3. 파일 그룹

3-1 주 파일 그룹(Primary File Group)
주 파일 그룹, 즉 PRIMARY 파일 그룹은 주 데이터 파일(mdf 확장자를 가진 파일)을 포함한다. 물론 보조 데이터 파일(ndf 확장자를 가진 파일)도 Primary 파일 그룹에 추가할 수 있다.
PRIMARY  파일 그룹만 사용하는 경우에는 어떤 테이블은 주 데이터 파일에 저장하고, 어떤 테이블은 보조 데이터 파일에 저장하는 형태를 갖출 수는 없다.

3-2 사용자 정의 파일 그룹(User-defined File Groups)
PRIMARY 파일 그룹 외에 사용자가 마든 파일 그룹이 사용자 파일 그룹이며, 여러 개 만들 수 있다. 사용자 저의 파일 그룹을 사용하면 테이블을 역할별로 구분하여 서로 다른 위치에 저장할 수 있다. PRIMARY 파일 그룹은 하나만 존재할 수 있지만, 사용자 정의 파일 그룹은 여러개 존재할 수 있다.


--파일 그룹 추가
ALTER DATABASE HRDB2
 ADD FILEGROUP UF01

--파일 그룹에 파일 추가
ALTER DATABASE HRDB2
 ADD FILE (
        NAME = 'UF01',
        FILENAME = 'E:\SQLData\UF01.ndf',
        SIZE = 512MB,
        FILEGROWTH = 128MB
 ) TO FILEGROUP UF01

 -- 기본 그룹 변경

 ALTER DATABASE HRDB2

   MODIFY FILEGROUP UF01 DEFAULT

-- 특정 파일 그룹에 테이블 생성

CREATE TABLE [dbo].[GUNDAM_2](

[ID] [int] NULL,

[ID2] [int] NULL

) ON [UF01]

4. tempdb 데이터베이스 이동

데이터 파일과 트랜잭션 로그 파일이 물리적으로 서로 다른 디스크에 위치하는 것이 성능을 위해 좋다고 했다. 성능을 위해 또 하나의 고려할 것이 tempdb 데이터베이스 위치이다.

tempdb 데이터 베이스는 사용자가 의도하지 않더라도 SQL Server 내부적인 필요 때문에 사용되는 경우가 많다. 수많은 임시 테이블들이 만들어지고 제거되는 tempdb 데이터베이스다 보다, tempdb 데이터베이스를 다른 디스크에 두는 것이 성능에 좋다. 사용자 데이터베이스는 분리한 후 새로운 위치로 파일을 옮긴 후 연결하면 된다. tempdb 데이터베이스는 SQL Server 운영 중에 절대로 분리하거나 연결할 수 없는 데이터베이스다.

 tempdb 데이터베이스를 옮길려면, SQL Server 운영 중에 ALTER DATABASE 문으로 모든 파일의 새로운 위치를 저장한 후 SQL Server 서비스를 재시작하면 된다. tempdb 데이터베이스는 SQL Server 서비스가 시작되면서 다시 만들어지므로, 기존 파일을 새로운 위치로 옮길 필요가 없다. 파일들이 새롭게 만들어질 폴더만 정확히 존재하고 있으면 된다.

-- 데이터 파일 옮기기

ALTER DATABASE tempdb

 MODIFY FILE (

     NAME = tempdev,

     FILENAME = 'E:\SQLData\tempdb.mdf'

     )

ALTER DATABASE tempdb

 MODIFY FILE (

     NAME = temp2,

     FILENAME = 'E:\SQLData\tempdb_mssql_2.ndf'

     )

ALTER DATABASE tempdb

 MODIFY FILE (

     NAME = temp3,

     FILENAME = 'E:\SQLData\tempdb_mssql_3.ndf'

     )

ALTER DATABASE tempdb

 MODIFY FILE (

     NAME = temp4,

     FILENAME = 'E:\SQLData\tempdb_mssql_4.ndf'

     )

-- 로그 파일 옮기기

ALTER DATABASE tempdb

 MODIFY FILE (

     NAME = templog,

     FILENAME = 'F:\SQLLog\templog.ldf'

     )

4. 데이터베이스 이름 변경

EXEC sp_renamedb 'HRDB2' 'GUNDAM'

ALTER DATABASE HRDB2

  MODIFY NAME = GUNDAM

(참고로 데이터베이스 옵션에서 액세스 허용을 SINGLE_USER로 변경해야 함)

Dimension Processing Commands (Analysis Services Performance Guide for SQL Server 2012 and SQL Server 2014)

4.Tuning Processing Performance

4.2 Tuning Dimension Processing

4.2.2 Dimension Processing Commands

차원처리명령를 통해 차원을 처리합니다.

각 처리 명령은 하나 이상의 job을 생성하고 operation을 수행합니다.

성능관점에서 차원처리 명령은 중요합니다

l  ProcessData

l  ProcessFull

l  ProcessUpdate

l  ProcessAdd

ProcessFull

ProcessFull, ProcessData 명령은 차원의 모든 저장소 내용을 삭제하고 다시 작성합니다.

ProcessFull은 모든 차원 처리 작업을 실행하고 모든 종속 파티션에서 암시적 ProcessClear를 수행합니다.

즉, 차원의 ProcessFull 작업을 수행할때는 종속파티션에 대해 ProcessFull 작업을 수행하여 큐브를 다시 온라인으로 가져와야합니다. (-> 팩트에 영향)

ProcessFull은 차원 데이터 자체에 대한 인덱스도 작성합니다 (파티션의 인덱스는 별도로 작성됩니다).

차원에서 ProcessData를 수행하는 경우 나중에 차원 쿼리에서 이러한 인덱스를 사용할 수 있도록 ProcessIndexes를 수행해야합니다.

ProcessUpdate

ProcessFull과 달리 ProcessUpdate는 차원 저장 내용을 삭제하지 않습니다.

대신 종속 파티션을 유지하기 위해 지능적으로 업데이트를 적용합니다.

특히 ProcessUpdate는 SQL 쿼리를 보내 전체 차원 테이블을 읽은 다음 변경 내용을 차원 저장소에 적용합니다.

ProcessAdd

ProcessAdd는 new members를 삽입하기만 하면 되는 시나리오에 적합합니다.

기존 members를 삭제하거나 업데이트 하지 않습니다.

ProcessAdd의 성능 이점은 source 차원 테이블의 행이 new rows만 return하도록 제한하는 쿼리라는 다른 source table이나 data source view를 사용할 수 있다는 것입니다.

이렇게하면 모든 소스 데이터를 읽을 필요가 없습니다.

또한 ProcessAdd는 모든 인덱스와 집계를 유지합니다.

ProcessUpdate 및 ProcessAdd에는 사용자가 알아야 할 몇 가지 특별한 동작이 있습니다.

이러한 동작은 다음 섹션에서 설명합니다.

4.2.2.1 ProcessUpdate

ProcessUpdate는 특성관계유형(rigid / flexible)에 따라 insert, update, delete를 처리할 수 있습니다.

특성관계 > 속성 > RelationshipType

• Rigid : 일자차원의 2017년 1월은 2017년에 속한다. 계속..
• Flexible : 직원은 부서 이동이 가능하다.

Aggregations, indexes를 생성하므로 조회 성능을 유지 보수하기 위해 집계를 다시 작성해야합니다.

그러나 변경이 감지 된 경우에만 유연한 집계가 drop됩니다.

ProcessUpdate가 실행되면 차원에 종속 된 파티션을 통과해야합니다.

각 파티션에 대해 모든 인덱스와 집계를 점검하여 업데이트가 필요한지 확인해야합니다.

많은 파티션, 인덱스 및 집계가있는 큐브에서는 매우 오랜 시간이 걸릴 수 있습니다. 

4.2.2.2 ProcessAdd

ProcessAdd는 Type2 변경 차원을 관리하는 기본 방법입니다.

Analysis Services는 기존 인덱스를 무효화 할 필요가 없다는 것을 알고 있기 때문에 일반적으로 ProcessAdd는 ProcessUpdate보다 훨씬 빠르게 실행됩니다.

Analysis Services의 기본 구성에서 ProcessAdd는 실행시 처리 오류를 트리거하고 중복 키 값을보고합니다.

이 오류는 이미 차원에 있는 키가 아닌 속성의 "추가"로 인해 발생합니다.

이 경우 해결 방법은 차원 처리 명령에서 <KeyDuplicate>를 IgnoreError로 설정하는 것입니다.

Empty차원에서 ProcessAdd를 실행할 수 없습니다. 

4.3 Tuning Cube Dimension Processing

4.3.1 Reduce Attribute Overhead

Attribute의 수가 많으면 차원처리의 많은 비용이 발생합니다.

차원처리의 시간이 제한적인 경우, 성능향상을 위해선 attribute를 변경해야 합니다.

최종 사용자가 사용하지 않는 Attribute는 삭제하십시오.

4.3.1.1   Remove Bitmap Indexes

기본키 속성을 처리하는 동안, 모든 관련 속성에 대해 비트맵인덱스가 만들어집니다.

높은 카디널리티를 갖는 속성은 비트맵인덱스를 작성하는데 시간이 걸릴 수 있습니다.

예를 들어, 고객차원의 기본키는 계좌번호별로 각 고객을 고유하게 식별합니다.

그러나 사용자는 고객의 주민등록번호를 기준으로 데이터를 검색할 수 있습니다.

각 고객계좌번호는 주민등록번호와 1:1 관계입니다.

주민등록번호의 비트맵생성을 제거하는 것을 고려할 수 있습니다.

또한 매우 선택적인 비트맵인덱스가 있는 다른 속성과 함께 항상 질의되는 속성에서 비트맵인덱스를 제거하는 것도 고려할 수 있습니다.

다른 속성에 충분한 선택성이 있는 경우 다른 비트맵인덱스를 추가하여 필터링해도 큰 이점이 없습니다.

예를 들어 sales 팩트테이블을 만들고 사용자 쿼리가 항상 날짜 및 영업점 차원을 사용한다고 가정합니다.

영업점점원를 사용하여 필터를 적용하는 경우도 있습니다.

그러나 영업점에서 이미 필터링했기 때문에 영업점점원에 비트맵을 추가하면 성능에 큰 이점은 없습니다.

이 경우 영업점점원 특성에서 비트맵인덱스를 사용하지 않도록 설정할 수 있습니다.

Attribute 속성 >AttributeHierarchyOptimizedState > Not Optimized

4.3.1.2 Optimize Attribute Processing Across Multiple Data Sources

계단식 속성 관계를 사용하여 여러 데이터 소스에서 차원을 가져 오는 경우 시스템은 데이터 소스에 따라 처리하는 동안 특성을 분할 할 수 있습니다.

속성의 키, 이름 및 속성 관계가 동일한 데이터베이스에서 제공된 경우, 시스템은 하나의 데이터베이스 만 조회하여 해당 속성에 대한 SQL 조회를 최적화 할 수 있습니다.

계단식 특성 관계를 사용하지 않으면 여러 원본의 데이터에 액세스하는 메서드를 제공하는 SQL Server OPENROWSET 함수가 데이터 스트림을 병합하는 데 사용됩니다. 
이 상황에서는 여러 OPENROWSET 파생 테이블에 액세스해야하기 때문에 특성 처리가 매우 느립니다.

옵션이있는 경우 ETL을 수행하여 차원에 필요한 모든 데이터를 동일한 SQL Server 데이터베이스로 가져와야합니다. 
이를 통해 관계형 엔진을 활용하여 쿼리를 튜닝 할 수 있습니다.

4.3.2 Tuning the Relational Dimenstion Processing Queries

파티션 당 하나의 쿼리만 서버에 보내는 팩트파티션과 달리 차원 프로세스 작업은 여러 쿼리를 보냅니다.

그 이유는 차원테이블과 팩트테이블이 다른 경향이 있기 때문입니다

l  차원은 작아지려고하고, 변화가 적으려하고,,.

l  팩트는 간단하지만 많은 변화가 있습니다.

차원의 특성을 갖는 테이블은 시스템에 대한 삽입 / 업데이트 성능 오버 헤드가 거의없이 과도하게 인덱싱 될 수 있습니다.

이를 처리하는 동안 이점을 이용하고 관계형 인덱스를 자유롭게 사용할 수 있습니다

차원 처리에 사용되는 관계형 쿼리를 신속하게 조정하려면 차원 처리의 Profiler 추적을 캡처하고 데이터베이스 엔진 튜닝 관리자를 사용하여 추적을 기반으로하는 권장 사항을 생성합니다.

작은 차원 테이블의 경우 제안 된 모든 인덱스를 추가 할 수있는 가능성이 있습니다.

큰 테이블의 경우, 가장 긴 실행 쿼리로 인덱스를 지정하십시오.

대규모 차원 테이블에 대한 자세한 조정 방법은 SQL Server 2008 R2 Analysis Services 운영 가이드를 참조하십시오.

 4.3.2.1 Using ByTable Processing

Dimension의 ProcessingGroup 속성을 ByTable 값으로 설정하여 차원 처리 중에 Analysis Services의 동작 방식을 변경할 수 있습니다.

이 변경 작업을 수행하면 여러 SELECT DISTINCT 쿼리를 보내는 대신 처리 작업에서 하나의 쿼리를 사용하여 전체 테이블을 요청합니다.

처리 중에 모든 새 차원데이터를 보관할 만큼 충분한 메모리가있는 경우이 옵션을 사용하면 처리를 신속하게 최적화 할 수 있습니다.

각 특성에 대해 SELECT DISTINCT가 실행되지 않고 처리 중에 동일한 멤버가 반복적으로 발생하기 때문에 ByTable 처리로 인해 중복 키 (KeyDuplicate) 오류가 발생합니다.

따라서 사용자 정의 오류 구성을 지정하고 KeyDuplicate 오류를 비활성화 해야합니다.

Expert Cube Development with SSAS - 3장

3장 Designing More Complex Dimensions

. Grouping

차원 중에서 속성도 별로 없으면서 키값이 엄청 많은 차원들에 사용하면 유용

DiscretizationMethod와 DiscretizationBucketCount 속성을 사용하면 자동으로 Grouping을 지원하며 아래와 같은 모양이 나온다.

적당하게 그룹핑을 해주는데 사실 이게 맘에 안들면 걍 SQL을 이용해서 별도 속성을 만들면 원하는대로 만들 수 있다.

CASE WHEN Weight IS NULL OR Weight<0 THEN 'N/A' WHEN Weight<10 THEN '0-10Kg' WHEN Weight<20 THEN '10-20Kg' ELSE '20Kg or more' END

이런 식으로...

. Banding

Grouping은 차원에서의 그룹화라면 Banding은 Fact를 건드려서 만들어내는 그룹화 방법이다. 그래서 자연스럽게 Grouping은 차원의 키의 개수나 값은 변화가 없지만 Banding은 변화가 온다.

Banding은 Band를 묶는 조건을 고객이 바꾸길 원할때 팩트 테이블을 전부 다시 말아야 하는 불상사가 생길 수 있는데 다음과 같은 경우다.

왼쪽이 팩트고 오른 쪽이 디멘전인데 OR값을 그냥 Surrogate키로 의미없이 땄을때 만약 9815값이 MEDIUM이었다가 LOW라고 정의를 바꾸려면 팩트테이블의 OR컬럼을 모두 2에서 1로 UPDATE가 들어간다.

이 때 디멘전의 Surrogate키를 만약 의미를 가지고 따게 되면 (가령 Order Value/100) 대리키 RANGE정의가 바뀌더라도 차원만 UPDATE하게 되므로 간편하다!


. Modeling Slowly Changing Dimension
차원의 속성이 변하지 않는다면 좋겠지만... 그런일은 일어나지 않는다. 속성이 변하는 차원을 관리하기 위한 대표적인 모델링 기법을 SCD I~III 이라고 부른다. 실제로는 한 차원안에서도 속성 별로 1~3유형이 달라지도록 모델링해야 할 수 있다.

. Type I SCDs
가장 흔하고 가장 간단하고 별로 할일이 없다. 속성이 변하면 그냥 단순히 과거의 값을 UPDATE하고 끝난다. RDB의 집계테이블이라면 모든 집계테이블을 재집계해야겠지만 Analysis Services는 그럴 필요가 없이 차원만 UPDATE해주면 된다.
항상 현재기준의 속성값으로 과거 팩트가 제공된다.
(다만 집계디자인을 사용하는 경우에는 집계테이블과 마찬가지 원리로 집계값들이 DROP된다.)

만약 해당 팩트가 발생한 시점의 차원의 속성값을 알고 싶다면 Type I을 사용해선 안된다.
가장 흔한 예로 주문한 "고객의 주소 별 주문액" 값이라면 고객차원의 주소는 항상 현재 주소를 가지고 있으므로 주문 당시의 주소별 주문액은 Type I으로는 알수가 없는 것이다.

Type I의 속성값들은 MDX쿼리에서 함부로 사용해서는 안된다. 값이 변경되면 무의미해질 수 있다.

. Type II SCDs
차원의 속성이 변하는 이력을 모두 관리하는 모델링 기법이다. Surrogate키가 필수적으로 요구되고 다음 3가지 컬럼이 일반적으로 항상 요구된다.

1. Current/Pervious 여부컬럼
2. 유효시작일시
3. 유효종료일시

Surrogate키는 고객이 보기에는 의미없는 컬럼이므로 AttributeHierarchyVisibleState속성은 False로 둔다. 그리고 특성관계에서 실제 의미를 가지는 키값으로 집계구조를 생성한다.

그리고 시점별로 달라지는 속성들을 위해서 또 고객에게 노출되지 않는 중간집계컬럼을 둬서 쿼리 속도를 향상시킬 수 있다.

. Type III SCDs
현재 속성 값과 직전(또는 최초) 속성 값 2가지만을 가지는 차원인데 실제로는 쓸일이 거의 없다. 특성관계만 보고 넘어가자

. Modeling junk dimension
속성이 달랑 1개이고 속성값도 몇개가 안되는 자잘한 차원들이 설계하다보면 많이 나오는데 이를 모두 별도의 차원으로 할 경우... 성능에도 안좋고 고객도 사용하기 어렵다.
이런 차원들을 모아서 1개의 차원으로 만드는 것을 junk dimension이라고 한다.

보통 팩트테이블을 가지고 가능한 조합을 추출해내고 Surrogate키를 따서 하나의 차원테이블을 만들어서 구현한다. Surrogate키는 역시 고객에게는 Visible False이다.

junk 차원에서 일부 차원이 분리되어 나가거나 할때는 기존 팩트값에 꽂힌 Surrogate키가 충돌이나거나 하지 않게 세심하게 작업해야 한다.

. Modeling ragged hierarchies
레벨깊이가 일정하지 않은 부모/자식구조를 갖는 차원을 말한다.
재귀적으로 부모/자식 관계를 갖는 테이블이 대표적인데 정해진 level depth가 정해져 있지 않기 때문에 골치 덩어리다.

- Usage 속성값이 Parent인 컬럼이 부모컬럼이다.
- NamingTempate속성값으로 레벨이름을 명명할 수 있다. (예: 대;중;소;세)

사용자계층에 대해서 HideMemberIf 속성을 사용하면 재귀테이블이 아니더라도 Ragged hierarchies를 표현할 수 있는데 테이블 구조는 다음과 같다.

테이블 구조

특정 레벨 이하의 리프가 상위 값을 가진다.

생성된 계층

위와 같이 상위값이 다른 경우 Level이 하나씩 당겨지는 방식이다.

두번째 방법이 조금 낫긴 하지만 ragged hierarchies는 최대한 피해야 한다.
쿼리속도, 처리속도, 설계난이도, MDX개발 난이도 어느 것 하나 좋지 않다.
집계디자인도 필수적으로 IsAggregateable: False처리하라.