【Sisense Deta Modeling】ディメンションの値が変動するSCDに対処する

シャローム！Sato-Gです。
先週、Slackで「夏休みを取得していない人はきちんと取得するように」と指示が飛んでいた。
僕は２日休んでGo Toキャンペーンでキロロリゾートに行ってきた。北海道の真夏のリゾートは涼しくて最高！！
えっ？なぜ使えるのかって？
だって、僕は北海道民なので...
住民票は札幌でたまたま東京にいるだけだし、「たまたま」がだんだん長くなってるだけです！
東京で今月中に1日休むよ～！

さて、今回はSCD(Slowly Changing Dimension)の解決方法を１つご紹介する。
まあ、あるあるだから読んでみて～

1. SCD(Slowly Changing Dimension)とは

SCD（Slowly Changing Dimension）は、BI製品が世の中に登場した1990年代から存在する命題だ。
BI製品の多くはデータモデリングでディメンション - メジャーの関係を定義する。
BI製品のテーブル構成でいうと、ディメンションテーブル - ファクトテーブルをキーで固定的に結合した静的なデータモデルを作成するのが一般的だ。
その時にディメンションの値が（ゆるやかに）変化する場合、それをSCDと呼び、正しく集計を行うためには何らかの対策が必要となる。

例えば、企業の組織改編で、２つの部門が統合され、さらにそのうちの一部が別部門として独立したとしよう。
時系列のあらゆる時点で、
・常に当時の部門で正しく集計されるようにしたい
・部門を引き継いだのであれば、前年対比が引き継いだ元の部門の実績も含めて比較したい
というようなビジネス要求はあたり前のようにあるだろう。
むしろ「それはできません」なんていうことがあったら、ビジネスの大きな足枷となるから、「あなたビジネスわかっていないね」と言われておしまい。
こんな要求にも柔軟に対応していこうね。

2. 有給休暇取得データのSCD対応策

2.1 データの説明

従業員の有給休暇取得データを集計するために以下のデータを準備した。
ダウンロードはこちら

■データソース
・Excel
・ワークシート名
- 有休取得実績
- 従業員マスタ
- 従業員マスタ_異動あり

取得実績データは従業員個人に紐付いているため、従業員単位のでの取得日数を集計するのは容易だ。
また各部門毎の取得日数を求める場合、従業員の所属部門をを基に集計することになるだろう。
しかし、従業員は異動するから１年前の集計を行う場合は、その時点に各従業員がどの部門に所属していたのかがわかなければいけない。
しかも人事異動は年度の途中で行われるし、いつ発生するかわからない。

今回の場合
・Steve：2019/7/1に第一営業部から第二営業部に異動
・Jimmy：2019/9/30で退職
・Sam：2019/8/1に入社
という人の動きがあり、特にSteveの対応が重要となる。

2.2 データを取り込んでみる

ExcelコネクタではSQL編集はできないため、どっちみち一旦は取り込まなければいけない。
上記のExcelデータを一旦取り込んで見る。

Web ECMでは上記のように表示されるはずだ。
ここでは[従業員マスタ]と[従業員マスタ_異動あり]という２つの従業員マスタがある。
今回は[従業員マスタ]は現在の所属部門をベースに集計するために残すが、異動を考慮し、時系列で当時の所属部門で集計を行えるように[従業員マスタ_異動あり]からカスタムテーブル[従業員マスタ_重複あり]をSQLで作成する。

2.3 SCD対応の従業員マスタ作成

異動を考慮した従業員マスタを「従業員マスタ_重複あり」というテーブルで実現してみる。最終形はこんな感じ。

在籍期間FROM , 在籍期間TOは入社、退職、人事異動などが発生した際のみ値が入る。よって値のない場合は以下のルールで値をセットする。
在籍期間FROM　　2019/4/1
在籍期間TO　　　 データ中の休暇取得日の最大値
これらは全体で値を１つずつしか持たないため、以下のようにCROSS JOINしてもレコード数が増えることはない。

SELECT ...
FROM
[従業員マスタ_異動あり] e
CROSS JOIN
(
SELECT CreateDate(2019,4,1) AS DateMin,
MAX(年月日) AS DateMax
FROM [有休取得実績]
) d

従業員マスタ_異動ありはDateMin, DateMaxが付与され、在籍期間FROM, 在籍期間TOがNULLの場合はDateMin, DateMaxをセットするようにする。また従業員IDは部門コードとの組み合わせでユニークとなるため、この２つを組み合わせてユニークになるよう結合して従業員KEYを作成する。

[従業員マスタ_重複あり]テーブルを作成するクエリは以下のとおりとなる。

SELECT  従業員ID,
        従業員名,
        部門コード,
        部門,
        ToString(従業員ID) + '-' + ToString(部門コード) AS 従業員KEY,
        CASE
            WHEN 在籍期間FROM IS NULL THEN d.DateMin
            ELSE 在籍期間FROM
            END AS 在籍期間FROM,
        CASE
            WHEN 在籍期間TO IS NULL THEN d.DateMax
            ELSE 在籍期間TO
            END AS 在籍期間TO
FROM [従業員マスタ_異動あり] e
    CROSS JOIN
    (
    SELECT  CreateDate(2019,4,1) AS DateMin,
            MAX(年月日) AS DateMax
    FROM [有休取得実績]
) d

以上でSCD対応の従業員マスタは完成。

2.4 SCD対応のファクトテーブル[有休実績]の作成

有休取得実績テーブルに従業員マスタ_重複をLEFT JOINする。

SELECT ...
FROM [有休取得実績] h
LEFT JOIN [従業員マスタ_重複] e

JOINするときのキーは従業員ID、さらに取得日がその在籍期間FROM以上、在籍期間TO以下である必要があるので、SQL全体は以下のようになる。

SELECT  年月日,
        h.[従業員ID],
        従業員KEY,
        有休取得日数
FROM [有休取得実績] h
LEFT JOIN [従業員マスタ_重複] e
    ON h.[従業員ID] = e.[従業員ID]
    WHERE h.[年月日] >= e.[在籍期間FROM]
    AND h.[年月日] <= e.[在籍期間TO]
 

以上、[有休実績]テーブルでは、その当時の従業員KEY（従業員IDと部門コードの組み合わせ）が取得されるようになる。

2.5 Calendarテーブル

[有休取得実績]テーブルは37行あるので、このテーブルを利用して[Calendar]テーブルを作成する。
CROSS JOINしているのは37×37で3年分の年月日を取得できるからで、このようにテーブルを利用してCalendarを作成する方法は【Data Modeling】カレンダーテーブルを自動生成するで紹介しているので、今回は割愛することにする。

SELECT  CreateDate(2019,4,1) + t3.Cnt *(60*60*24) AS 年月日,
        GetYear( CreateDate(2019,4,1) + t3.Cnt *(60*60*24))*10000 + GetMonth( CreateDate(2019,4,1) + t3.Cnt *(60*60*24))*100 + GetDay( CreateDate(2019,4,1) + t3.Cnt *(60*60*24)) AS 年月日KEY,
        1 AS FAKE_KEY,
        0 AS ZERO
FROM
(
    SELECT RANK()-1 AS Cnt
    FROM [有休取得実績] t1
    CROSS JOIN
    (
    SELECT RANK() AS Cnt1
    FROM [有休取得実績]
    ) t2
) t3
WHERE CreateDate(2019,4,1) + t3.Cnt *(60*60*24)
<=
(SELECT
    MAX(年月日)
FROM [有休取得実績]
)
ORDER BY CreateDate(2019,4,1) + t3.Cnt *(60*60*24)

以上でこのデータモデルに必要なテーブルが完成した。

2.6 データモデル

以上で作成したから以下ののようにリレーションを設定し、データモデルを完成させる。

有休実績.従業員ID - 従業員マスタ.従業員ID
有休実績.従業員KEY - 従業員マスタ_重複.従業員KEY
有休実績.年月日 - Calendar.年月日

3. ダッシュボードで確認

以上で作成したElastiCubeでダッシュボードを作成して検証してみる。
左は部門別の年度別取得日数、右はさらに部門->従業員までドリルダウンした取得日数である。
このデータモデルでは時系列のあらゆる時点で当時の所属部門で正しく集計できていることがわかる。

4. まとめ

SCDの対策として、FROM～TOの期間をWHERE句で指定して、その時点での正確なデータを取得する方法をご紹介した。
理屈を追うと「なるほど！」と思うんだけど、実際の場面でゼロベースから考えようとすると思いつかないもんだよね。
うーん、「ディメンションの値が変動する、困ったなー」と思ったら、あまり難しく考えずに"sisense","scd"でググッてみよう。
このページにたどり着くはず（そうあってほしい！）

ではまた！