【SSRS】データドリブンサブスクリプションのメール件名と本文
やっぱり、３日坊主になってた・・・（笑）
久々にはまったので備忘録的にメモ・
SSRSのただのサブスクリプションだと、件名と本文は
固定文を設定することになる。
（@ExecutionTime と @ReportName は自動置換してくれる）
そして、本文に複数の行数を登録することができる。
（入力欄がTEXTAREAになっている）
しかしデータドリブンサブスクリプションでは、
本文の冒頭であて先の人の名前など「メールごとに文面を変える」のが
普通だ。
このような場合にどうするかというと、
「SQLで本文の内容をひとつの項目として作成し、サブスクリプションの
設定では、本文（コメント）を”データベースから値を取得”に変えて、
本文項目の内容を使用する。
データ取得のイメージは
SELECT
社員コード
,メールアドレス
,社員名+ N'様'+
N'今週のXXXXをご報告いたします。'+
N'ご対応のほどをよろしくお願いします。'+
AS 本文
FROM 社員テーブル
てな感じ。
ところが・・・ここからが嵌ったところ。
このままでは、文字列が連結されて１行になってしまう。
改行はどうやって入れればいいのか？
普通にSQLで改行を入れるため NCHAR(13)+NCHAR(10) を入れても
OUTLOOKでは、無視されていた（というかメールデータで既に削除されていた）
で、答え・・・<BR>を文字として入れる
SELECT
社員コード
,メールアドレス
,社員名+ N'様<BR>'+
N'今週のXXXXをご報告いたします。<BR>'+
N'ご対応のほどをよろしくお願いします。<BR>'+
AS 本文
FROM 社員テーブル
でOKだった。
というか、このメール、「HTMLメール」として送られている。
気づくまで時間が掛かったorz
情けない・・・
【SSIS】特定時点で最新だったデータを集める
アーキテクチャや製品紹介しか書いていなくて、ブログ趣旨からずれてる・・・
ってことで、ちょっとした「技」編
最初は、「特定時点で最新だったデータを集める」です。
テーブルのデータが、1つのIDに1つのレコードとなっていればいいのですが、
主キーなしや、日付・履歴番号が主キーに入っていて、同じデータの中でも最新のものだけを取り出したい時ってありますよね。
マスタの変更履歴が残したいから、変更するときは新しいデータを追加して、古いデータは「削除フラグを立てておく」ってゆーシステムは多いかと。
（履歴がほしいのですから、当然、作成日も登録しますね。）
このとき、最新の有効なマスタは、もちろん削除フラグが立っていないレコードだけを集めればいいのですが、たとえば「先月末時点のマスタ」が必要なときはどうしましょうか？
まず、先月末時点なので作成日が今月以降のものを除外します。
次に、マスターのキー（例えば商品番号）ごとに最新のものを集める必要があります。
SQLServerのSQLでは、ROW_NUMBER()関数というのがあって、これを使えばSQLでも実現できます。
でも今は、SSISでの実現方法です。
この時に使えるのが参照コンポーネントです。
参照コンポーネントは、「参照データの中に一致したデータが見つかると、それ以降に一致するものがあっても無視される。」という特性があります。
SQLのJOINとは違うんです。
やり方はこうです
- まず、「OLE DB ソース」で「一意なキーのリスト（Distinctしておく）」を取得します。（今月初めて登録されたキーを除外するため、「WHERE条件：作成日<今月1日」をつけておきましょう）
- このソースを入力とする「参照」を作成します。
- 「参照」の参照ソースにSQLを指定します。
SQLは、「WHERE条件：作成日<今月1日」「ORDER BY：キ−,作成日 DESC」とします。
ORDER BY に「作成日 DESC」をつけるのが味噌です。
こうすることで、参照データは「キーごとに最新のものが最初に見つかる」ようになります。
＊＊＊注意点＊＊＊
「参照」では、参照データをメモリー上に読み込んでから処理が走ります。
フローの実行前に、この読み込み処理が実行されます。
そのため、参照データが多すぎるとメモリーを圧迫して処理が遅くなる場合があります。
できるだけ、「必要な項目だけ」を参照データとして取り込むようにしたほうがいいです。
それでも多すぎる場合・・・メモリを増設するか・・・そのとき考えよう。
じゃーねー
SQLServerBIの構成形態 OLAPサーバー編
先日の続きで、KAYANOの薦める構成の話です。
KAYANOの推奨構成 ②OLAPサーバー編
今回は、DWHとOLAPサーバーです。
DataBase ※SQLServer
↓
（OLAP DataBase） ※SSAS
OLAPって？
DWH(DB Server)では「テーブル単位」でデータを扱うのに対し、
OLAPサーバーは「テーブルとマスターの纏まり」として扱います。
なんでかというと、データを集計してレポートを作るときには、
「テーブルとマスター」を組み合わせて使うので予め纏まりとして
定義しておけば「レポートが作りやすい」からです。
こういう纏まりでデータを定義するのがOLAP Databaseです。
つまりOLAP Databaseというのは、レポートを作るためのサーバーです。
注意してほしいのですが、ここでいう「レポート」は定型レポートだけじゃないことです。
簡単にレポートを作れるということは、とっても簡単に変更できるということです。
営業部署別のレポートを変更して、顧客別にとか、地域別にとかと変更する（ダイシング）ことも、全社集計レポートを特定営業所のみに変更する（スライシング）ことも、営業所別のレポートを担当者明細に変更する（ドリルダウン）ことも簡単にできるってことです。
この簡単に変更できるという点から「自由分析レポート」と呼ばれるものが
実現されています。
ということで
- 定型レポートしかいらない
- そもそもデータが単純だからOLAPでなくても簡単
- 簡単じゃなくていい
という場合は、OLAPは必ずしも必要ありません。
※必ずしもというのは、SSASの場合、集計検索がDBより早いので
データ件数が多い（数百万件以上）の場合は、メリットがあるからです。
だから、KAYANOの推奨としては、SSASは必ずしも使わなくていい。ということになります。
でも、SSASは使いだすと、とっても面白い・便利な製品です。
データに対する考え方を理解しないと、どうやって作るのかよく解からない、とーーーっても難しいものなんですが、細かなところで色々な定義や機能を設定することができて、とても奥が深くて楽しい製品なので、ぜひトライしてほしいところです。
ちなみに、次で触れる、レポートサーバーのSSRSとは相性があまり良くないので、SSASとSSRSを組み合わせるとちょっと苦労するかもしれません。
KAYANOが感じる中でSQLServerBIの最大の弱点です。
OLAPサーバーは、レポートを作りやすくする為のサーバーなのに・・・TT）
SSRSは定型レポートを作るためのサーバーだってことで許してちょんまげ（古っ)
じゃーねー
SQLServerBIの構成形態 ETL編
SQLServerでBIやデータ分析をするときには、DBは使うとして
SSAS・SSIS・SSRSをどのように組み合わせるかはいろいろなパターンがあります。
ソリューションとして、
- SQLServerだけで組む
- 他の製品と組み合わせて使う
というのが前提として考える必要があります。
また、フロントツール（ユーザーインタフェース）を
- Webにする
- 専用ソフトを組む
- 他の専用ソフトを使う
- EXCELにする
というのでも変わります。
ただ、基本的な構成は、DWH・OLAPのソリューションモデルに
集約されるのではないかと思っています。（BI屋だからそう思うのかも）
基本構成は、データの流れで言うと下記のようになります。
（外部データソース）
↓
ETL ※SSIS
↓
DataBase ※SQLServer
↓
（OLAP DataBase） ※SSAS
↓
FromtTool ※SSRS or EXCEL※は、SQLServerのみで組んだ場合の製品
（）は、無い構成もアリ！というところ。
いくつかのエントリに分けて、KAYANOの推奨構成を書いていきます。
KAYANOの推奨構成 ①ETL・DWH編
今回は、
の部分。
もともと、SQLServerを使っているシステムの場合、データは
SQLServerの中に格納されています。というか、常時更新されています。
こういう場合、ETLなしでいいんじゃないか？と思われるかもしれませんが、
KAYANOの推奨は、「今あるDBを外部データソースとして扱う」ということです。
BI用のデータベースは、アプリケーションで使っているデータベースとは
別のものを作成します。
理由は３つ
①データの範囲・対象が違う
アプリケーションで使用しているデータは、大抵の場合、BIで使用する
データと範囲や対象が異なっています。
BIでは時系列で分析するため、過去すべてとか数年のデータを
「生きたデータ」として使います。
しかし、オンラインでは過去データはあくまで参照用のデータとして
保持します。
逆に、アプリケーションで必要となる一時的なワークデータや未確定の
データなどは、BIでは不要なケースが多いです。
②性能の出し方が違う
インデックス計画だけでなく、パーティションなども含め、
BIとオンラインでは性能要件が全く違います。
オンラインでは、「今必要な部分」のデータが取り出せればいいのですが
BIでは、「任意のキーで絞った全データ」が必要になります。
①同様、過去すべてが生きたデータだからです。
これは、オンラインへの影響を最小限に抑えるためにも重要なポイントになります。
③整合性のレベルが違う
オンラインでは、業務を優先するので若干のマスタ不整合を許容する
ケースがあります。マスタ未登録でも仮登録できるとか、NULLでもいいとか。
BIでは、極力このような不整合状態をなくすようにします。
未登録はNULLでもいいのですが、「NULLは未登録の状態」と
はっきりしていれば、「未登録用のコード」を割り振つけて
しまう方が扱いやすくなります。
ということで
（外部データソース）
↓
DataBase ※SQLServer
という部分は必須ということになります。
プログラミングに慣れている人や、SQLに慣れている人であれば、VBやC#,スクリプト、SQLなど色々な手法を考えると思います。
私も、ここをストアドプロシージャで作ったり、VIEW+SQLで作ったりすることもあります。
もちろんScriptingもあります。
でも、推奨は、SSISです。
理由は２つ。
①わかりやすい
GUIでの開発で、データの流し方がわかりやすくなります。
特に別の担当者に引き継いだり依頼したりするときに、非常に有効です。
②速い
SSISに限らずETLは、アーキテクチャ的に（性能を向上させる為のコツが
必要になりますが）他の手法に比べて処理速度が格段に速くできます。
特に数１００万レコード以上のデータを扱う場合は、重要なポイントです。
と書いたものの、
ETLには得手不得手な処理があり、性能を出すために、不得手な部分は、
他の手法・ツールと上手く組み合わせる必要があります。
ですので「GUIだからSQLを全く知らなくても使える」とか
「プログラミング言語をわからなくても大丈夫」とかということはありません。
それでも、ほとんどのことは、GUI上でできるので、
設計書をそのまま走らせている
という感じで開発ができます。
絶対にお勧めです。
おまけ
と書きましたが、DWHではDataBaseの前にもう一つDataBase（ODSと呼んでます）を置くのがご作法です。ODSは、外部データソースのコピーであり、かつ、必要な過去データも保持します。
ODSを置く場合に、外部→ODSの部分をどういう方式にするかは、悩ましいです。
が、私は、ここも、SSISを使います。
ま、ODSや、外部取り込みの話はそのうち。
ってことで、じゃーねー
SQLServer のBI機能とは・・・
とりあえず、宣伝ね。
SQL Server はエンジニアなら大概は知ってるマイクロソフトのデータベースエンジンです。
データベースは、、、、説明が面倒だからWikiPediaでもググってね。
ところがSQL Serverは、上位Editionになると、Business Inteligence の機能が
おまけでついてくる。
おまけだよ。おまけ・・・BIエンジニアのKayanoにとっては、なんじゃそりゃですわ。
このおまけ。昔は、「なんちゃってBI」くらいの感じで、「ま、おまけだね」だったんだけど、SQLServer2005からかなりまともになってきて、2008R2では、「おお！しっかりしたBI製品じゃん。しかも”安い！”」
BIは、
ETL(Extract Tranceform Loading)
DWH(Data Wear House)
Reporting(& 自由分析)
の３（４？）要素からできるソリューションなんだけど、SQLServerは
これを全部提供しちゃってんだ。おまけで。。。
ETL
SQLServer Intgration Services(SSIS)
こいつは、そこそこ使える。でも、まだまだ、成長・拡張の余地が大きいです。
メジャーな製品だと、DataStage,Sagent（共にフル機能を導入すると１０００万円以上はする）とか、DataSpider,WAHA（１００〜３００万くらいかな）とか
ほかにもいろいろある。
高額な製品だと、技術的にも成熟した感があるけど、ま、SQLServerはやすいからさ。
DWH
SQLServerDBエンジン自体もDWHなんだけど、多次元データベースとしては
SQLServer Analysis Services(SSAS)
BIは、大量のデータを分析のために集計（SELECT）するから、
普通のオンラインDBとは違った性能が要求されるのね。
これに特化したのがDWH用のデータベース。
メジャーな製品だと、SybaseIQとかNeteezaとかかな。
この値段も高いけど、多次元データベース程複雑じゃないから
大規模データのユーザーには有効な製品なんだな。
それほど大規模でない場合は
DBエンジン自体もDWHとして使えるけど、データの意味づけや関連性を
BI的に定義できるデータベースの方がいい。
この点で、SSASのような多次元データベースは先に行ってる感がある。
ほかのメジャーな製品だと、Essbaseとか CognosTM1とかかな。
Reporting
SQLServer Reporting Services(SSRS)
これもいろんな製品があるし、SSRSが優れているっていうわけでもない。
でも、必要十分なことはかなりできる。「使える！」って感じが強いね。
自由分析
EXCEL2007、2010（Pivot）
なんつっても、誰がどういおうが、BIの最大のメリットは自由分析！
これを便利にするために他の機能があるといっても過言ではない。
で、EXCEL2007以降のBI連携機能は、めちゃくちゃ強力。
他のBIツールを一蹴するほどの力を持っているね。
ライバルは、BusinessObjectとかHyperionとかいろいろあるけど、
「Webじゃない」という点を除けば、最強だとKayanoは思ってる。
最後に、このエントリの主眼。つーか、本音。
SQLServerの DBエンジン・SSIS・SSRS・SSASは、それぞれ全くの別製品なんだわ。これが。そのほかのいろんなものも同じく。
別製品だからか、それぞれの親和性が”低い”。これが頭が痛い。
ただ、SSASとEXCELの組み合わせは例外。滅茶苦茶 強力。
SQLServerでBIを組むとき、フロントをEXCELにしないのは間違い。
つーか、Reportingできれいなレポートをたくさん作るより、
SSASを充実させて、EXCELで好き勝手使わせるのが正しい選択だと、ここで断言したい。。。強く。
って、ことでじゃーねー
