【DWH】データモデリング (４．結合するキーは極力サイズを小さくする。)

この記事は、前回書いたデータモデリングの９つのことの４つ目です。

crmprogrammer38.hatenablog.com

件数が多ければ多いほど結合の処理時間がかかってきます。

なので、物理的に問題がないなら結合するキー項目のサイズを極力小さくします。

具体的には、

・CHARではなく、VARCHAR

・文字型のキー項目があって、その項目はただ、連番に、左に0詰めしているだけだったら、数値項目

・日付項目で時刻を含まないなら、数値(YYYYMMDDの数値にする)

・もちろん桁数も短くできるなら削ります（業務的に必ず10バイトなのに、30バイトなどで定義しない）

純粋に格納するバイト数が小さくなるように定義します。

だからといって、全てのディメンションのキー項目を数値型のサロゲートキーにする必要はありません。個人的にはナチュラルキーをベースとして、どうしてもというところだけサロゲートキーにするというのが良いと思っています。

性能が良いデータベースを買ったのだから気にしないなんてのは勿体無いです。データベースの性能を引き出しましょう。早くて困る人はいませんし、何より開発者も幸せになれます。

DWH: データモデリング (３．ヘッダと明細の構成のファクトは１つのファクトに結合する。)

この記事は、前回書いたデータモデリングの９つのことの３つ目です。

crmprogrammer38.hatenablog.com

ヘッダテーブルと明細テーブルの構成でトランザクションを構成する場合は多いと思います。

そういう時は、ヘッダテーブルと明細テーブルを結合して１つのファクトにします。

[ファクトを結合する前]

[ファクトを結合した後]

こんな感じで、スタースキーマになりました。

ですが、赤字の箇所の項目に注目して欲しい。

結合する前はヘッダに持っていた数値項目を、明細単位に展開すると集計した際におかしくなります。

なので、明細単位に展開する際にヘッダテーブルのみに持っている数値項目をどう明細に持たせるかの検討が必要です。

上の場合、ヘッダに値引額があって、明細テーブルの合計金額に対してまとめて値引をしていますが、ファクトを結合した後では、値引額を明細単位で分割しています。（分割ロジックは均等割りであったり、金額の割合に応じて割ったりなどになると思います）

DWH: データモデリング (２．ディメンションとファクトは、内部結合にする。)

この記事は、前回書いたデータモデリングの９つのことの２つ目です。

crmprogrammer38.hatenablog.com

安定した性能にするなら内部結合です！ 

ＤＷＨだと、ファクトの件数が多くて、ディメンションの項目に指定された検索条件を元に、一番最適なファクトの絞込みを行って欲しい。

でも、データ内容によっては外部結合にせざるを得ない時があって、そういう時は強制的に内部結合にできるようにデータを加工をします。

【内部結合にするためのデータの加工１】

ファクトの結合キーがnullの場合、でもデータは落としたくない

これはよくありますよね。nullであることが意味がある時があります。

でも結合キーがnullは外部結合にせざるをえないので、次のような工夫をします。

・ディメンションにnullの時のレコードを用意（例えばキー項目"00000000"で登録）

・ファクトの項目がnullの場合、nullを"00000000"に変換してセット

例えば、次のような売上ファクトで、次の業務データがあったとします。

(顧客は、顧客登録している場合は、顧客コードがセットされているけど、顧客登録されていない場合はnullになる。)

[売上ファクトのデータ]

[顧客ディメンションのデータ]

そしてこれをデータ加工すると次のようになります。

[売上ファクトのデータ（加工後）]

[顧客ディメンションのデータ（加工後）]

これなら顧客がnullだったレコードも内部結合することができます。

でも、顧客がnullの時に"00000000"と表示するのが違和感がある場合は、次のように顧客ディメンションにさらに「顧客 結合用」列を追加すれば、表示する顧客をnullにすることができます。

【内部結合にするためのデータの加工２】

ファクトの結合キーがディメンションにない場合、でもデータは落としたくない

データの打ち間違えか、マスタの名寄せをしたかでファクトの顧客が、ディメンションにない場合があります。そんな時はディメンションに足りない分を追加します。

[売上ファクトのデータ]

[顧客ディメンションのデータ]

売上ファクトの顧客"A000000"に該当する顧客が顧客ディメンションにありません。

なので、顧客ディメンションに追加します。

[顧客ディメンションのデータ（加工後）]

[最後に]

外部結合を多用してとても苦労した経験があって、内部結合にして安定させようという気持ちで書いています。

もちろん、外部結合を選択するケースもあると思います。その都度一番いい方法を選択していくことが重要です。

DWH: データモデリング (１．ディメンションとファクトは、物理的に１：Nで結合する。)

この記事は、前回書いたデータモデリングの９つのことの１つ目です。

crmprogrammer38.hatenablog.com

まず、物理的に１：Nで結合するという点について以下を示しています。

・ディメンションとファクトで結合する際に、ディメンション側の結合キーはユニーク制約があること

ポイントはユニーク制約なので、いわゆるPK（ユニーク＋not null）である必要はありません。データベース側が結合条件の項目がユニークであると認識できることが必要です。（要するに、PKか、Unique Indexのどちらかが必要。TERADATAであればUPIも使えます）

当たり前のように感じますが、データが複雑になればなるほど見落としがちになります。

例えば、次のような店舗毎に営業日が異なるカレンダを考えます。 

（年月日と店舗でユニークです）

結合するファクトに、年月日と店舗の要素があれば、上記のカレンダとファクトは、１：Nで結合できています。

[結合条件]　これは１：Nでの結合条件

       店舗別カレンダ.年月日 = ファクト.年月日

and 店舗別カレンダ.店舗    = ファクト.店舗

ですが、ファクトに店舗の要素がなく、店舗別カレンダの代表店舗を指定する場合の結合は、データとして考えれば１：Nですが、結合条件としては物理的に１：Nでありません。

[結合条件]　データとしては１：Nだが、結合条件としては１：Nではない。

       店舗別カレンダ.年月日 = ファクト.年月日

and 店舗別カレンダ.店舗    = '固定値の条件'

データベースのオプティマイザ次第ですが、結合が１：Nであることを明確にしたほうが性能も安定します。

 そのため結合条件に代表店舗を指定して結合するのではなく、代表店舗のカレンダレコードで新規にカレンダを物理的に作成して、そちらを利用します。

テーブルが増えていくことだけが難点ですが、これで性能が安定するなら大したことではないです。

DWH: スタースキーマをベースにあらためて考えてみたデータモデリングの９つのこと

DWHのデータモデルでとても有名な"スタースキーマ"。

でも、アプライアンスDBを導入したんだからきちんと正規化した方が後々使いやすいといった意見もあったりします。

私は、スタースキーマはとてもシンプルでわかりやすく、そして結合が少なくて使いやすい、何より性能が安定するモデルと考えています。

そこで、自分の思いを踏まえてスタースキーマをベースにしたデータモデリングについて書いていこうと思います。

[いわゆるスタースキーマの絵]　※以前の記事にも同じものを貼っています

真ん中の売上データのテーブルを一般的にファクトテーブル（以下、ファクト）そして、周りのカレンダ、商品、店舗、顧客のテーブルをディメンションテーブル（以下、ディメンション）という名前で呼びます。

１．ディメンションとファクトは、物理的に１：Nで結合する。

２．ディメンションとファクトは、内部結合にする。

３．ヘッダと明細の構成のファクトは１つのファクトに結合する。

４．結合するキーは極力サイズを小さくする。

５．ディメンションを大きくし過ぎない。

６．ファクトは極限まで小さくする。

７．先週との比較値、先月との比較値、前年との比較値などは、BIツールの機能を利用する。（機能がないならデータマートを作ろう）

８．ナチュラルキーを基本とし、サロゲートキーはピンポイントで使う。

９．１から８にとらわれすぎない。

次からの記事で１つ１つを細かく記載します。

DWH: BI/DWHのプロジェクトの作業で思うこと

私の経験として、BI/DWHのプロジェクトは他の業務パッケージのプロジェクトに比べて難しくて失敗しやすいなーと考えています。

明確に、なぜ失敗しやすいのかといった分析結果があるのかもしれませんが、個人的に思うことをつらつらと書いていこうと思います。

１．データの種類と、データ量の多さ、そして複雑さ

まずこれですよね、何するにしてもデータ量が多くて、軽く環境つくって試してみることができない。

あと、同じようなマスタが乱立していたり、同じ項目名なのに意味が違ったり、本当に混乱してきます。

２．BIツールの良さに反する定型帳票

BIツールは、使う人によって見る切り口が違ったり、その時々で見たい粒度が違ったりする場合に、さくっと自分のみたい項目を選んでデータを見る機能があります。

仮説の裏づけをしてみてもいいし、数値だけから客観的に物事をとらえてもいい、そんな便利なツールだと思っています。（使う人の操作は項目選んで表示するだけですが、データを見ることはとても重要です）

でも、既存にある複雑な定型帳票を作るには向いていなかったりします。今ある定型帳票を廃止するのはとても難しく、無理やりBIツールで定型を出力できるように、専用のデータマートを用意する必要がでてきて、スケジュールは逼迫、設計書も陳腐化していきます。

３．作り終わった後の性能問題

開発フェーズが終わり、本番相当のデータで動かすと発覚する性能の問題。

数万件のデータがの単体テスト環境では問題なく動作してるけど、数億件のデータが入った環境では動かなくてプロジェクトは混乱を極めます。

[所感]

みんな一生懸命作業してるのに、やっぱり大変な状況になるんですよねー。

ほんとつらい、とりあえず統計情報をとったり、SQLの見直しをしたりなど対応してみますが、先の見えない作業はいやなものです。

油断していると、日次のバッチ処理時間が24時間を越え始めたり

うまく行ったときは大変なだけにとても充実感があります。それだけが支えです。。

雑記: DWHのアプライアンスDBについて思うこと

TERADATAについての記事を書いてきましたが、他のDWHのアプライアンスDBはどうなんだろうなと思いました。

ただ、調べると、Oracle exadataとTERADATA以外は情報が少ないので、この２つの争いになってるように思います。

TERADATAからOracle exadataに移行したり、その逆もあったりしてますが、Oracleは情報が多いのと、技術者が多いのがいいなと思います。もちろんexadataを扱う上での作法はあると思いますが、使い馴染んだOracleのSQL、使いやすい関数がそのまま使えるのは魅力的です。

TERADATAは数々の実績に裏打ちされた実力があって、多くの企業に採用されています。ただ、情報が少ないのと、技術者がOracleより少ない。この部分のハードルが高いと感じています。

どちらを選んでもとてもいいDWHが作れると思いますが、最近の時代の流れは情報が沢山あるOracle exadataの方が有利なんだろうなーとか意味も無く考えています。