crmprogrammer38の日記

主にDWHやSalesforceのプログラムメモです。

【DWH】データモデリング (5.ディメンションを大きくし過ぎない。)

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

 

crmprogrammer38.hatenablog.com

 

この辺りからは、ゆるくモデリングしようよという内容になっています。

ディメンションと結合するのはファクトだけで、ディメンションからさらにディメンションに結合はしないのがスタースキーマモデリングですが、ディメンションの項目が増えすぎて逆に性能が落ちてしまう場合があるので注意。

 

企業のデータで、ディメンションになることが多いのが、顧客軸、商品軸、組織軸だと思います。顧客軸でも、顧客の販売店、その上の支社、そして本社などの階層を持っていたり、商品、組織も5階層ぐらいのところが多いと思います。

仮に商品の属性が30項目あって、その項目を階層分に1つのディメンションに定義してしまうと、30項目×5階層で150項目のディメンションになってしまいます。ディメンションなので名称項目などを持つためサイズも大きくなります。

[大きなディメンション構造]

f:id:crmprogrammer38:20170222234125p:plain

 

そんなディメンションを用意するぐらいならば、商品の階層情報だけを持つ商品階層ディメンションと、その商品階層ディメンションと商品ディメンションを結合させて使った方がいいと思います。

商品階層も、全階層で見る頻度は低く、5階層あったうちの2つぐらいをチョイスするような使い方であればデータベースの処理コストも下がります。

f:id:crmprogrammer38:20170222234656p:plain

ディメンションが大きくなりすぎる前に考えたいものです。

ちなみに、階層だけでなく、例えば店舗などでも、店舗に様々な分析軸を持たせてデータを持たせたい場合もあると思います。主キー項目が店舗コードのディメンションだからといって無理に統合せず、店舗コードが主キーのディメンションが用途別に複数あっても大した問題ではないと思います。

 

 

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

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

 

crmprogrammer38.hatenablog.com

 

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

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

具体的には、

・CHARではなく、VARCHAR

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

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

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

 

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

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

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

 

【DWH】データモデリング (3.ヘッダと明細の構成のファクトは1つのファクトに結合する。)

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

 

crmprogrammer38.hatenablog.com

 

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

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

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

f:id:crmprogrammer38:20170222203116p:plain

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

f:id:crmprogrammer38:20170222203529p:plain

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

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

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

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

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

 

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

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

 

crmprogrammer38.hatenablog.com

 

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

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

 

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

 

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

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

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

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

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

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

 

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

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

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

f:id:crmprogrammer38:20170222195653p:plain

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

f:id:crmprogrammer38:20170222200016p:plain

 

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

[売上ファクトのデータ(加工後)]

f:id:crmprogrammer38:20170222200221p:plain

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

f:id:crmprogrammer38:20170222200301p:plain

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

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

f:id:crmprogrammer38:20170222200818p:plain

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

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

 

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

 

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

f:id:crmprogrammer38:20170222235508p:plain

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

f:id:crmprogrammer38:20170222235705p:plain

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

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

 

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

f:id:crmprogrammer38:20170222235856p:plain

 

 

[最後に]

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

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

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

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

 

crmprogrammer38.hatenablog.com

 

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

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

 

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

 

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

 

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

(年月日と店舗でユニークです)

f:id:crmprogrammer38:20170222111655p:plain

 

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

f:id:crmprogrammer38:20170222111706p:plain

[結合条件] これは1:Nでの結合条件

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

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

 

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

f:id:crmprogrammer38:20170222111725p:plain

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

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

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

 

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

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

f:id:crmprogrammer38:20170222111743p:plain

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

 

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

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

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

 

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

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

 

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

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

f:id:crmprogrammer38:20170208123750p:plain

 

 

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

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

3.ヘッダと明細の構成のファクトは1つのファクトに結合する。

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

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

6.ファクトは極限まで小さくする。

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

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

9.1から8にとらわれすぎない。

 

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

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

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

 

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

 

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

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

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

 

2.BIツールの良さに反する定型帳票

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

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

 

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

 

3.作り終わった後の性能問題

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

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

 

[所感]

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

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

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

 

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