DWHの作り方 - RDBで構築する場合に設計開発で考えるべきこと

DWHの構築方法や、設計開発について本やネットで調べてもなかなかみつからないため、私が新人の頃苦労しました。５年たった今でもその状況は変わってなさそうなので、備忘録としてまとめます。

DWHという名前がついてますが、データベースの使い方について名前を付けただけです。一度わかれば後はルーチンワークみたいなものです。

前提

DWHにはいろんな意味で使う人がいますが、ここではデータを蓄積するテーブルという意味でのDWHを指しています。

DWHを構築するために利用するRDBMSは、特に想定しているものはありませんが、大体なんでも大丈夫です。

MySQL、Oracle、PostgreSQL、SQLServerあたりを想定しています。

データベースの容量の見積もりは含んでません、そこは各種データベースソフトが出しているであろう見積もりの仕方を参考にするしかないと思います。

DWH（データウェアハウス）とは

この見出しでは本やネットで調べたらすぐに出てくることを書いてます。そんなこと知ってるよって人は、次の見出しから具体的に記載していますので、そこまで読み飛ばしてください。

DWHの定義

あえて専門的な記載方法しますと、DWHは以下と定義されています。

1. サブジェクトごとに編成されていること(subject oriented)
2. データが統合されていること(integrated)
3. 時系列データを持つこと(time variant)
4. データが永続すること(non-volatile)

要するに、売上データや配送データなど、種類の違うデータは別に保管しており(1)、テーブル結合などを用いず１つのテーブルにまとめ(2)、日時がわかるデータ(3)、更新削除はされない(4)ということになります。

スタースキーマ

DWHではデータの中身を第２正規系にまでとどめておいて、ファクト表とディメンション表、いわゆるトランザクションとマスタに分ける方法をとります。

これは大量にデータが保存されているテーブルを参照する際のクエリ速度を早めるためにあります。

DWHの作り方

DWH作り方 - テーブル設計

目的ごとに蓄積するためのテーブル(DWH)を１つずつ用意する

蓄積したいトランザクションデータを、目的１つにつき１テーブルに分けましょう。

例えば、売上データと配送データは基本的に同じテーブルには入れられません。

配送データの中にも金額があるかもしれませんが、返品等を考えると処理が複雑になるため、売上を分析するためのデータの集合体は、売り上げが確定している元データを利用し、配送データは配送関連の分析をするために蓄積すべきです。

テーブルに保存するデータの粒度を合わせる

売上用の蓄積テーブルが用意できたら、そこに保存するための元データを用意するわけですが、場合によっては売上を定義するための情報が散在しており、それぞれ粒度が違う場合があります。

（例えば、Aファイルは月毎の情報しか持っていないが、Bファイルは日毎の情報しかないなど）

この場合は、月毎の売上データDWHと、日毎の売上データDWHの２つを作ることも検討します。

どちらか１つは必要ない運用にすることも検討しますが、少なくとも２つをまとめて１つのDWHを作ることはあり得ません。

横並びでデータを持っても容量の無駄ですし、速度低下にもつながります。集計する際にも変な数値が起きてしまいますので使い道がありません。

マスタとの結合方法

速度を高めるため、マスタとの結合には２つ以上のカラムを使いません。基本的に１つです。

そこで問題になってくるのが、マスタの過去情報管理です。

人の所属などの人事情報は毎年編成が変わったりするため、マスタが更新されてしまうと過去の情報も書き換えられてしまいます。

通常の設計であれば、年月日の開始日終了日などを利用するところですが、１つのキーでは実現できません。

そこは、どのようにデータを見たいかで決めていくべきです。

通常のリレーショナルデータベースと同様開始日終了日をみて結合するようにするか、キーの値自身にID＋年月を設定する方法もあります。（ただしこの方法だと年月ごとに変更がなくてもマスタを更新しないといけない。年月日だと現実的ではない。）要件によっては過去のデータも含めてマスタが変わってもよい場合もあります。（同じ軸でデータを見たい場合）

シンプルなのは、マスタには更新せず、常に追記することでしょうか。システム上は助かりますが、運用的に難しいケースが多いです。工数を使って名寄せと呼ばれる作業をETL上で行うこともあります。様々なシステムのマスタを取得し、IDを振りなおしたり、名称の記載方法を統一したりします。この際にマスタの更新内容を読み取ってIDを振りなおす仕組みを組み込みますが作るのは大変です。

DWH作り方 - 運用設計

更新タイミング

DWHは大量にデータがあるのでほとんどの場合、処理に時間がかかります。なので夜間処理が通常になると思います。

リアルタイム性を求めるのであれば、別途リアルタイム専用のテーブルと処理を設計するか、差分更新にてDWHの処理を高速化する検討が必要です。

差分・全件洗い替え

大量にデータがあるDWHで全件洗い替えは行いたくないですが、どうしてもごみデータが出るなどの要件はよくあるため、その場合はごみデータを判別して削除すると逆に時間がかかるため全件洗い替えを選択する場合もあります。または、一か月分のみ洗い替えるなどの設計もあります。

理想は、追記のみ行うことです。ただしその場合、２重実行された場合にどちらも登録されてしまわないようユニーク値の設定はしっかり行います。

障害時のデータ更新方法

データを間違って全部消してしまったり、基幹システムからよくわからないデータを取り込んでいたりなど、実はよくあります。

そんなときの場合にどのように解消するか設計しておくとよいでしょう。

データレイクに取り込みデータを蓄積しておき、全件ETLを動かしなおせば元に戻る。といった運用をすることが一番楽です。

マスタの更新方法

マスタの更新は手動か、自動か、手動の場合は、どのように取り込ませるか？直接DBか、専用にアプリ作るかなど。

さいごに

今回はここまで。

DWHを作る際に考えておきたいことをまとめました。

この後はデータマートを作成します。