차원이력관리기법(SCD) - Type 4

✨ Type 4 : 미니 차원 추가

[기법 설명] 괴물 디멘션(rapidly changing monster dimension)이라고도 불리는 기법으로, 디멘션의 속성들이 빠르게 변할 때, 차원의 이력을 보다 효율적으로 관리하기 위해서 사용하는 기법이다. 원천 마스터 테이블의 속성 값들의 특성을 파악하여, DW 에서 해당 차원테이블을 SCD-4로 관리할지에 대한 여부를 판단하여야 한다.
이전까지 살펴본 회원 마스터 테이블의 속성들은 이름(Name), 이메일(Email)과 같이 자주 바뀌는 속성 값이 아님을 직관적으로 파악할 수 있다. 하지만, 만약 아래와 같이 회원 마스터 테이블에 생년월일이(Birth_day) 추가되어 DW의 차원테이블에 나이(Age)라는 속성이 이력으로 관리되어야 하는 경우라면 어떻게 해야할 것인가. 적어도 원천테이블의 모든 회원은 1년에 1번씩 이력이 업데이트되어야 한다는 것을 의미한다. 나이(Age)라는 속성이 아닌, 매달 변하는 회원의 등급 속성의 경우에는 1달에 1번씩 이력이 업데이트 되는 경우가 발생한다. 이러한 경우, 아무리 대용량 데이터를 적재하는데 특화된 DW라고 해도 모든 이력을 관리하기에는 비용적인 측면에서 부담스러울 수 밖에 없다. 저장용량 문제 뿐만 아니라, 팩트테이블을 회원차원으로 분석하는 경우 팩트테이블 트랜잭션 시점의 나이 또는 회원등급을 구하기 위해 매번 이력 시점을 기준으로 Join을 해야하는 불필요한 쿼리비용이 발생한다.
이러한 문제점들을 보완하기 위해서 사용되는 차원이력관리 기법이 Type-4이고, 이를 잘 활용한다면 DW 저장용량과 쿼리비용을 모두 절감할 수 있을 것이다.

[스키마 설명] Type 4에 대한 이해를 돕기 위해, 기존 원천마스터테이블에 생년월일이라는 속성 값을 추가하였다. 생년월일 속성은, DW 회원차원에서는 나이(Age)라는 속성으로 변환되어 분석에 활용하고자 한다. 그리고 나이(Age)속성은 위에서 언급한 바와 같이 차원테이블에서 속성으로 관리하지 않고, 회원속성내역테이블(Member_Att_P)이라는 미니 차원을 통해 관리하고자 한다.
회원속성내역테이블(Member_Att_p)은 회원속성번호(Member_Att_no)라는 가상의 대체키 (Surrogate Key)가 PK역할을 수행한다. 해당 대체키는 회원차원(Member_Dim)에도 존재하고, 분석하고자 하는 팩트 테이블(Order_Fact)에도 존재한다. 해당 대체키와 회원차원(Member_Dim)의 PK인 Member_No 만 있으면, 특정 회원의 해당하는 시점의 나이를 알 수 있다.
예를 들어, 2020년 01월 01일 (Order_Fact.Ord_Ts)에 발생한 주문트랜잭션에 특정 회원번호과 해당하는 시점의 회원의 속성번호(Memeber_Att_no)가 있다면 분석하는 시점에 관계 없이 주문이 발생한 시점의 회원의 나이로 주문팩트를 분석할 수 있다.
반면, 회원차원(Member_Dim)에 존재하는 회원 속성번호(Member_Att_No)는 현재 시점 기준의 회원의 속성번호가 저장된다. 즉 회원차원(Member_Dim)테이블 하나만 본다면, SCD Type 1으로 (최신값 유지) 관리된다는 것이다. 다만, 회원차원에 저장되어 있는 회원속성번호는 팩트 테이블의 여러 시점에 맞게 해당하는 번호가 저장되어 있음으로 과거 시점의 회원속성분석이 가능한 것이다.
만약 위의 예시에서, 2020년 01월 01일에 발생한 주문트랜잭션을 회원의 현재나이로 분석하고 싶다면 주문팩트테이블에 저장된 회원속성번호가(Order_Fact.Member_Att_No) 아닌 회원차원에 저장되어 있는 회원속성번호로(Member_Dim.Member_Att_No) 분석하면 되는 것이다.
그렇다면, 팩트 테이블에 명시된 시점도 아니고 현재 시점도 아닌 사용자가 원하는 무작위의 특정 시점에 대한 회원의 나이 속성을 분석하고자 한다면 어떻게 해야 하는가. 위의 스키마에서는 회원속성번호는 팩트테이블에서 모든 시점에 적재되지 않는 이상 휘발되는 데이터이다. 만약, 모든 시점의 회원 나이를 분석하고자 하는 요구사항이 있다면 회원차원을 년/월 스냅샷으로 관리하는 방법이 있다. 년 단위 또는 월 단위로 회원의 속성이 변하는 것을 해당하는 주기 단위로 스냅샷 데이터를 저장한다면 모든 시점에 대한 회원속성번호를 적재하는 것이 가능해지기 때문이다. (물론, 위의 나이 속성은 시점을 기준으로 추산이 가능한 속성이기 때문에 굳이 스냅샷이 필요 없어 보이지만 나이 속성 이외에도 여러 속성이 회원속성내역테이블에서 속성으로 관리될 수도 있다.)

/* Ansi SQL (PostgreSQL 11) */
/* 1. 초기 적재 쿼리 */
/* 1.1 회원속성테이블(미니디멘션) 생성 */
DROP TABLE IF EXISTS Member_att_p;
CREATE TABLE Member_att_p
(
      member_att_no int GENERATED ALWAYS AS IDENTITY
      (START WITH 1000 INCREMENT BY 1),
	  age           int not null
)
;
INSERT
  INTO Member_att_p (age)
SELECT generate_series(1,100) AS age
;

/* 1.2 회원차원테이블 초기 적재 */
INSERT
  INTO Member_dim            -- 차원 테이블
SELECT M.Member_No         AS member_no
     , M.Name              AS name
     , M.Email             AS email
     , ATT.Memberr_att_no  AS member_att_no
     , sysdate             AS load_ts
  FROM Member_m            M -- 원천 마스터 테이블
  LEFT JOIN Member_att_p ATT -- 회원 속성 테이블
    ON EXTRACT(year from age(Member_m.birth_day))::int = ATT.age
;

/* 2. 변경분 적재 쿼리 */
/* 변경분 적재 쿼리 생략 */

/* 3. 주문팩트 적재 쿼리 */
INSERT
  INTO Order_Fact        -- 팩트 테이블
SELECT M.Order_No
     , M.Order_Qty
     , M.Order_Amount
     , M.Order_Ts
     , M.Member_No
     , DIM.Member_att_no
     , Sysdate           AS load_ts
  FROM Order_M           M -- 원천 마스터 테이블
  LEFT JOIN Member_dim DIM -- 차원 테이블
    ON M.Member_No = DIM.Member_No
;

[쿼리 설명] Type 4에서는 회원속성내역테이블이라는 별도의 미니 디멘션 테이블을 생성해야 한다. 해당 테이블에는, 기존과 달리 대체키 (Surrogate Key)가 추가되었다. 대체키는 회원 속성을 Unique하게 구별해줄 수 있는 Key의 역할을 하며, 회원차원과 분석하고자 하는 팩트테이블에 해당 대체키가 적재된다.
회원차원의 경우, 원천마스터테이블의 속성값과 함께 회원속성테이블에서 관리하는 나이(Age) 속성을 명시하기 위해 회원속성번호(Member_Att_No)가 적재된다. 각 회원의 생년월일에 일치하는 나이(Age)속성 값을 저장하기 위해서, 원천테이블의 생년월일 속성에서 추출된 나이(Age)값으로 회원속성내역테이블(Member_Att_P)과 Join한다. 만약, Age외의 다른 속성 값들이 회원속성테이블에서 관리한다면 Join조건에 그에 맞는 조건들이 추가되어야 한다.
회원차원테이블은 최신값의 회원속성번호가 적재되어 있다. 변경분 적재 쿼리는 원천마스터테이블의 변경여부와 관련 없이, 매년 초 회원의 속성번호를 갱신하기 위해서 전체 갱신이 이루어져야 한다. 이는 배치 프로세스에서 별도로 실행되는 트랜잭션이기 때문에 해당 쿼리에서는 제외하였다.
이제 주문팩트에 적재쿼리를 살펴보면, 주문의 원천마스터테이블(Order_M)의 속성 외에 회원속성번호(Member_Att_No)가 추가된 것을 확인할 수 있다. 위의 쿼리는 주문팩트가 생성되는 시점이 주문이 발생한 시점(Order_Ts)과 동일한 년도라는 가정을 하고, 주문팩트에 회원속성번호를 추가하는 쿼리로직이다. (주문일시(Order_Ts)와 배치시점이 상이하다면, 회원의 연력 값이 다를 수 있기 때문이다) 즉, 적재되는 회원의 속성번호는 주문이 발생하는 시점을 기준으로 회원의 나이(Age)를 알려주는 회원 속성번호가 적재된 것이다.
이제 이러한 스키마 구조에서는 주문시점의 회원의 연령과 현재시점의 회원의 연령으로 두가지 차원에서 주문팩트 분석이 가능해진다.

/* Ansi SQL (PostgreSQL 11) */
/* 1. 주문시점의 회원 연령별 주문 분석 */
SELECT sum(F.Order_Qty)  AS Order_Qty_Sum
     , P.Age
  FROM Order_Fact        F -- 팩트 테이블
  LEFT JOIN Member_Att_P P --회원 속성 테이블
    ON F.member_att_no = P.member_att_no
 GROUP BY P.Age
;

/* 2. 현재시점의 회원 연령별 주문 분석 */
SELECT sum(F.Order_Qty)  AS Order_Qty_Sum
     , P.Age
  FROM Order_Fact        F   -- 팩트 테이블
  LEFT JOIN Member_Dim   DIM -- 차원 테이블
    ON F.Member_No = Dim.Member_No
  LEFT JOIN Member_Att_P P   --회원 속성 테이블
    ON DIM.member_att_no = P.member_att_no
 GROUP BY P.Age
;