プログラマ38の日記

主にプログラムメモです。

Excel: VBAのround関数ではまったこと

まとまった計算をするのにVBAは便利なので、ちょっとした時に書きます。

 

ですが、がっつりVBAで開発すること自体は少ないので細かなところではまりました。

 

はまったのは round 関数

数式のround関数はround_half_upなのですが、

VBAのround関数はround_half_evenなのです。

 

VBA内で数式のroundと同じ計算をしたい場合は、WorksheetFunction.Roundを使うことになります。数式の間隔で関数を使う場合はWorksheetFunctionを常に使っていったほうがいいのかなと思いました。

[計算結果]

f:id:crmprogrammer38:20170302170608p:plain

vbaのroundは、roundする桁の左側の数値が偶数の場合は切り下げになります。

意外と気が付きずらいのがさらにつらいです。

TERADATA: BIツールではViewで定義しよう

まずTERADATAでは、次のようにViewを定義します。

REPLACE VIEW
SampleView (
field1
,field2
,field3
) as
LOCKING TABLE SampleTable FOR ACCESS
select
field1
,field2
,field3
from
SampleTable;

上の赤の太字の記載は、テーブルがロックされててもデータを読むという指定です。

いわゆるダーティリードですが、DWHでは、1つのレコードを頻繁に更新することはないので、ダーティリードで特に問題になるケースは少ないと思います。

どちらかというと、ロックされるとデータが読めない方が問題があって、日中に時間のかかる更新を行っていると、その間selectが実行できなくなります。

※TERADATAのロック制御はTERADATA社のサイトに詳しく書いてあります。

 

大体のBIツールではselect文は自動で生成されるため、select文の頭に、locking table ・・・ for access を指定できません。そのためテーブルに対応するViewを定義し、Viewの中でlocking table ・・・ for accessを指定します。

そのViewをBIツールで定義することでロックされててもテーブルを読むことができるようになります。

DWH: データモデル(9.1から8にとらわれすぎない。)

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

 

crmprogrammer38.hatenablog.com

 

思うことを書いてきましたが、データモデルのシンプルでわかりやすいガイドラインとしてスタースキーマモデルがあると思います。

 

1つ1つ場合分けをして、このときはこう、このときはこうという風に決めていくのではなく、まずスタースキーマモデルを作成して、その後まだ工夫の余地があるなら改善していくのがいいのではないかなと思います。

 

もちろん、最終的には業務要件を満たす(どうしても満たせない場合は要件の落としどころを探らないといけませんが)ことが目的なので、スタースキーマをベースにちょっとずつアレンジしていくことになります。

 

スタースキーマの定義から外れるからダメとか、完全に正規化してスノーフレークモデルにしないとダメとかそういうことではないので、ルールに縛られすぎずにモデリングしていきたいものです。

 

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

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

 

crmprogrammer38.hatenablog.com

 

DWHでは、サロゲートキーを使う場合があると思います。

f:id:crmprogrammer38:20170302145353p:plain

個人的にサロゲートキーをポイントを絞ったほうがいいように感じています。

というのは以下のような場合に保守しずらいと思うからです。

・金額がおかしいなどの調査の際に、ファクトはディメンションと結合しないとナチュラルキーが判断できない

・ディメンションの作成処理に誤りがあった。ファクトの作成処理に誤りがあった場合、データの復旧が難しい。

・ディメンション作成後、ファクト作成という順番のため、ディメンション作成でエラーがでると、そのエラーを解決するまでファクトが作成できない。

 

もちろんメリットもあるので、ポイントを絞って使うものだと思います。

例えば、サロゲートキーを使うことで、本来であれば10項目で結合するところが、1項目の結合ですむようになる場合など積極的に使っていきます。

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

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

 

crmprogrammer38.hatenablog.com

 

ハイエンドのBIツールは、複数のファクトの検索結果をBIツール側で結合する機能を持っています。

その機能を利用すると、データマートをたくさん用意する必要がなくなります。

 

ただし、ファクトの粒度が異なっている場合、値がどう表示されるかはBIツールの特性によるので注意が必要です。

(例えば、月別のファクトと、日別のファクトをBIツールで結合する場合、月別のファクトの値を、日別で見ると月別の値が日別に展開されてしまったり、値が表示できなかったりします)

 

ファクト毎のデータの粒度については、BIツールを使う側に理解を深めてもらうことも必要となります。

例えば、次のような3つのファクトデータを企業が持っているとします。

f:id:crmprogrammer38:20170302133200p:plainf:id:crmprogrammer38:20170302133401p:plainf:id:crmprogrammer38:20170302133803p:plain

・気温ファクトは日別・地域別に気温を持っています。

・売上ファクトは、日別・商品別・店舗別・地域別に売上金額を持っています。

・売上予算ファクトは、年月別・店舗別・地域別に売上予算額を持っています。

※地域は店舗の属性としてデータを持つ場合もありますが、あえてファクトに持たせています。

 データを見る人は、売上ファクトと売上予算ファクトを対比させて数値を見るには、年月別・店舗別・地域別でデータを見ることを理解しておく必要がありますが、業務を把握していれば当たり前のように理解してもらえると思います。

 気温ファクトと売上ファクトでデータを見る場合は、気温はあくまで参考とする数値なので、売上ファクトの粒度でデータを見つつ、気温と対比させるように使えると思います。(例年よりも寒いから、この商品が売れている、売れていないというデータの見方も可能になります)

 そして、これらのデータを前年、前々年と比較しながら営業活動に生かしていくことになります。

 

上記の、複数のファクトを定義できるBIツールではデータモデルは以下のようになります。

f:id:crmprogrammer38:20170302140803p:plain

1つのディメンションから複数のファクトにリレーションを張ることで、そのディメンションの粒度でそろえてBIツール側で結合してくれます。

あとはBIツールの利用者がディメンションから分析軸、ファクトから数値を選ぶことで自由にデータを見ることができるようになります。

 もし仮に、上記のデータモデルを満たす気温・売上・売上予算データマートを作ろうとすると、売上予算ファクトをさらに、日別・商品別に按分したり、気温ファクトは、商品別・店舗別に同じ値で展開したりすることが考えられます。が、難しさがあります。(もちろん必要ならデータマートを作成します。ただし、データを作る処理が必要なので、費用と時間がかかります。そして、そのデータマートにさらに別のファクトの数値を追加しようとするととても難しくなります)

【DWH】データモデリング (6.ファクトは極限まで小さくする。)

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

 

crmprogrammer38.hatenablog.com

 

 

ファクトテーブルは、DWHでは一番件数が多くなるテーブルなので、1レコードで数バイトの違いが、最終的に数ギガバイトの違いになって現れてきます。

 

なので、必要最低限の項目、限りなくサイズを小さくできる型でデータを保持するように設計します。

業務システムでよくある項目の、レコード作成日、レコード更新日、レコード作成者、レコード更新者などよくある項目は不要項目の筆頭です。

※でも、ディメンションと1:Nで結合するためにあえて冗長に持っている項目は必要です。

 

後、その時点の情報が必要という業務要件を満たすために、ファクトにその時点のマスタ属性を持つのも極力さけたいところです。ディメンション側で履歴管理を検討します。履歴管理する場合はファクトの項目に基準となる日付項目が必要です。

f:id:crmprogrammer38:20170302155308p:plain

 

もちろん業務要件を満たすことが一番なので、無理やりファクトの項目を少なく・小さくする必要はありませんが、業務要件を満たすことができるなら、どんどんファクトの項目を少なく・小さくしていきましょう!

 

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

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

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