--- title: 【Sisense Deta Modeling】ディメンションの値が変動するSCDに対処する description: SCD(Slowly Changing Dimension)は古くからBIでは避けて通れない課題です。Sisenseでデータモデリングで人事異動を模した集計をSCDとして正しく集計するモデリングを習得します。 image: https://knowledge.insight-lab.co.jp/hubfs/Imported_Blog_Media/06_model-1024x730-2.png --- ![INSIGHT LAB_文字のみ_WHT-1](https://knowledge.insight-lab.co.jp/hs-fs/hubfs/INSIGHT%20LAB_%E6%96%87%E5%AD%97%E3%81%AE%E3%81%BF_WHT-1.png?width=400&height=110&name=INSIGHT%20LAB_%E6%96%87%E5%AD%97%E3%81%AE%E3%81%BF_WHT-1.png) ![INSIGHT LAB_文字のみ](https://knowledge.insight-lab.co.jp/hs-fs/hubfs/INSIGHT%20LAB_%E6%96%87%E5%AD%97%E3%81%AE%E3%81%BF.png?width=400&height=110&name=INSIGHT%20LAB_%E6%96%87%E5%AD%97%E3%81%AE%E3%81%BF.png) [![お問い合わせ](https://hubspot-no-cache-na2-prod.s3.amazonaws.com/cta/default/5935783/a4a0266e-14ad-4388-b83f-681a6f35b8a7.png)](https://hubspot-cta-redirect-na2-prod.s3.amazonaws.com/cta/redirect/5935783/a4a0266e-14ad-4388-b83f-681a6f35b8a7) - [Sisenseとは](https://service.insight-lab.co.jp/sisense) - [Sisenseナレッジ](https://knowledge.insight-lab.co.jp/sisense) - [セミナー](https://event.insight-lab.co.jp/sisense-seminar) - [トレーニング](https://service.insight-lab.co.jp/data-analytics-dojo/training_course) - [お役立ち資料](https://download.insight-lab.co.jp/sisense/download) ###### 3 分で読むことができます。 # [【Sisense Deta Modeling】ディメンションの値が変動するSCDに対処する](https://knowledge.insight-lab.co.jp/sisense/data-modeling/slowly_changing_dimension) 執筆者 [Turtle](https://knowledge.insight-lab.co.jp/sisense/author/turtle) 更新日時 2020年10月16日 ###### Topics: [Data Modeling](https://knowledge.insight-lab.co.jp/sisense/tag/data-modeling) ![【Deta Modeling】ディメンションの値が変動するSCDに対処する](https://knowledge.insight-lab.co.jp/hubfs/Imported_Blog_Media/06_model-1024x730-2.png) 目次 シャローム！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 データの説明 従業員の有給休暇取得データを集計するために以下のデータを準備した。 ダウンロードは[**こちら**](https://knowledge.insight-lab.co.jp/hubfs/SISENSE/Data/scd_data.zip) **■データソース** ・Excel ・ワークシート名 - 有休取得実績 - 従業員マスタ - 従業員マスタ\_異動あり [![](https://knowledge.insight-lab.co.jp/hs-fs/hubfs/Imported_Blog_Media/01_excel-2.png?width=760&height=1019&name=01_excel-2.png)](https://knowledge.insight-lab.co.jp/hubfs/Imported_Blog_Media/01_excel-2.png) 取得実績データは従業員個人に紐付いているため、従業員単位のでの取得日数を集計するのは容易だ。 また各部門毎の取得日数を求める場合、従業員の所属部門をを基に集計することになるだろう。 しかし、従業員は異動するから１年前の集計を行う場合は、その時点に各従業員がどの部門に所属していたのかがわかなければいけない。 しかも人事異動は年度の途中で行われるし、いつ発生するかわからない。 今回の場合 ・Steve：2019/7/1に第一営業部から第二営業部に異動 ・Jimmy：2019/9/30で退職 ・Sam：2019/8/1に入社 という人の動きがあり、特にSteveの対応が重要となる。 ### 2.2 データを取り込んでみる ExcelコネクタではSQL編集はできないため、どっちみち一旦は取り込まなければいけない。 上記のExcelデータを一旦取り込んで見る。 [![](https://knowledge.insight-lab.co.jp/hs-fs/hubfs/Imported_Blog_Media/02_data_import-1024x488-2.png?width=1024&height=488&name=02_data_import-1024x488-2.png)](https://knowledge.insight-lab.co.jp/hubfs/Imported_Blog_Media/02_data_import-2.png) Web ECMでは上記のように表示されるはずだ。 ここでは\[**従業員マスタ**\]と\[**従業員マスタ\_異動あり**\]という２つの従業員マスタがある。 今回は\[従業員マスタ\]は現在の所属部門をベースに集計するために残すが、異動を考慮し、時系列で当時の所属部門で集計を行えるように\[従業員マスタ\_異動あり\]からカスタムテーブル\[従業員マスタ\_重複あり\]をSQLで作成する。 ### 2.3 SCD対応の従業員マスタ作成 異動を考慮した従業員マスタを「**従業員マスタ\_重複あり**」というテーブルで実現してみる。最終形はこんな感じ。 [![](https://knowledge.insight-lab.co.jp/hs-fs/hubfs/Imported_Blog_Media/03_table_target-2.png?width=884&height=246&name=03_table_target-2.png)](https://knowledge.insight-lab.co.jp/hubfs/Imported_Blog_Media/03_table_target-2.png) 在籍期間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 ``` [![](https://knowledge.insight-lab.co.jp/hs-fs/hubfs/Imported_Blog_Media/04_sql-1024x515-2.png?width=1024&height=515&name=04_sql-1024x515-2.png)](https://knowledge.insight-lab.co.jp/hubfs/Imported_Blog_Media/04_sql-2.png) 以上で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と部門コードの組み合わせ）が取得されるようになる。 [![](https://knowledge.insight-lab.co.jp/hs-fs/hubfs/Imported_Blog_Media/05_sql2-1024x355-2.png?width=1024&height=355&name=05_sql2-1024x355-2.png)](https://knowledge.insight-lab.co.jp/hubfs/Imported_Blog_Media/05_sql2-2.png) ### 2.5 Calendarテーブル \[**有休取得実績**\]テーブルは37行あるので、このテーブルを利用して\[**Calendar**\]テーブルを作成する。 CROSS JOINしているのは37×37で3年分の年月日を取得できるからで、このようにテーブルを利用してCalendarを作成する方法は【[**Data Modeling】カレンダーテーブルを自動生成する**](https://knowledge.insight-lab.co.jp/sisense/data-modeling/calendar-table)で紹介しているので、今回は割愛することにする。 ``` 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.年月日** [![](https://knowledge.insight-lab.co.jp/hs-fs/hubfs/Imported_Blog_Media/06_model-1024x730-2.png?width=1024&height=730&name=06_model-1024x730-2.png)](https://knowledge.insight-lab.co.jp/hubfs/Imported_Blog_Media/06_model-2.png) ## 3. ダッシュボードで確認 以上で作成したElastiCubeでダッシュボードを作成して検証してみる。 左は部門別の年度別取得日数、右はさらに部門->従業員までドリルダウンした取得日数である。 このデータモデルでは時系列のあらゆる時点で当時の所属部門で正しく集計できていることがわかる。 [![](https://knowledge.insight-lab.co.jp/hs-fs/hubfs/Imported_Blog_Media/07_dashboard-1024x325-2.png?width=1024&height=325&name=07_dashboard-1024x325-2.png)](https://knowledge.insight-lab.co.jp/hubfs/Imported_Blog_Media/07_dashboard-2.png) ## 4. まとめ SCDの対策として、FROM～TOの期間をWHERE句で指定して、その時点での正確なデータを取得する方法をご紹介した。 理屈を追うと「なるほど！」と思うんだけど、実際の場面でゼロベースから考えようとすると思いつかないもんだよね。 うーん、「ディメンションの値が変動する、困ったなー」と思ったら、あまり難しく考えずに"sisense","scd"でググッてみよう。 このページにたどり着くはず（そうあってほしい！） ではまた！ Sisenseを体験してみませんか？ INSIGHT LABではSisense紹介セミナーを定期開催しています。Sisenseの製品紹介や他BI製品との比較だけでなく、デモンストレーションを通してSisenseのシンプルな操作性やプレゼンテーション機能を体感いただけます。 [![詳細はこちら](https://hubspot-no-cache-na2-prod.s3.amazonaws.com/cta/default/5935783/ff8d86bf-1184-4d92-8926-6a31a20de4b0.png)](https://hubspot-cta-redirect-na2-prod.s3.amazonaws.com/cta/redirect/5935783/ff8d86bf-1184-4d92-8926-6a31a20de4b0) - [Tweet](https://twitter.com/share) ![Turtle](https://knowledge.insight-lab.co.jp/hubfs/Sunflower.jpg) #### 執筆者 [Turtle](https://knowledge.insight-lab.co.jp/sisense/author/turtle) 可視化領域を中心とした業務に従事しています。 ###### [前の投稿](https://knowledge.insight-lab.co.jp/sisense/administration/ssl_lets_encrypt) [![【Sisense Server】無料のSSL証明書](https://knowledge.insight-lab.co.jp/hubfs/Imported_Blog_Media/10_login-1024x567-2.png)](https://knowledge.insight-lab.co.jp/sisense/administration/ssl_lets_encrypt) ###### [【Sisense Administaration】無料のSSL証明書"Let's Encrypt"でSSL化してみる](https://knowledge.insight-lab.co.jp/sisense/administration/ssl_lets_encrypt) ###### [次の投稿](https://knowledge.insight-lab.co.jp/sisense/formula/stdevp) [![【Formula】STDEVP関数を使って偏差値を算出する](https://knowledge.insight-lab.co.jp/hubfs/Imported_Blog_Media/2aff692155e1fa929a7842ecd2d2e20a-2.png)](https://knowledge.insight-lab.co.jp/sisense/formula/stdevp) ###### [【Sisense Formula】STDEVP関数を使って偏差値を算出する](https://knowledge.insight-lab.co.jp/sisense/formula/stdevp) RECOMMEND こちらの記事も人気です。 ###### 4 分で読むことができます。 #### [【Sisense Administaration】無料のSSL証明書"Let's Encrypt"でSSL化してみる](https://knowledge.insight-lab.co.jp/sisense/administration/ssl_lets_encrypt) ###### 2020年 10月 16日 ###### 8 分で読むことができます。 #### [【API連携】Spotify音楽データの分析①【Sisense】](https://knowledge.insight-lab.co.jp/sisense/information/spotify_music_data_1) ###### 2020年 10月 16日 ###### 2 分で読むことができます。 #### [【Sisense Widget】折れ線グラフを階段状に表示する](https://knowledge.insight-lab.co.jp/sisense/widget/step_line_chart) ###### 2020年 10月 16日 ###### 3 分で読むことができます。 #### [【Sisense Widget】Radar Chartを使ってポケモンの種族値を表示してみた](https://knowledge.insight-lab.co.jp/sisense/widget/radarchart) ###### 2020年 10月 16日 ###### 3 分で読むことができます。 #### [【Sisense Formula】CASE関数を使って条件分岐する](https://knowledge.insight-lab.co.jp/sisense/formula/case) ###### 2020年 10月 16日 ###### 3 分で読むことができます。 #### [【Sisense Widget】折れ線グラフにおける欠落値の対処法](https://knowledge.insight-lab.co.jp/sisense/widget/line-graph) ###### 2020年 10月 16日 ##### 運営会社 運営会社 - [会社概要](https://www.insight-lab.co.jp/about/company.html) - [お問い合わせ](https://contact.insight-lab.co.jp/main) ##### メニュー メニュー - Sisenseとは - [Sisenseナレッジ](https://knowledge.insight-lab.co.jp/sisense) - [セミナー](https://event.insight-lab.co.jp/sisense-seminar) - [お役立ち資料](https://download.insight-lab.co.jp/sisense/download) [![INSIGHT LAB_文字のみ_WHT-1](https://knowledge.insight-lab.co.jp/hs-fs/hubfs/INSIGHT%20LAB_%E6%96%87%E5%AD%97%E3%81%AE%E3%81%BF_WHT-1.png?width=200&height=55&name=INSIGHT%20LAB_%E6%96%87%E5%AD%97%E3%81%AE%E3%81%BF_WHT-1.png "INSIGHT LAB_文字のみ_WHT-1")](https://www.insight-lab.co.jp/) - [プライバシーポリシー](https://www.insight-lab.co.jp/privacy.html) Copyright © 2020 INSIGHT LAB, Inc.