Jedox(Palo)（ExcelベースBI） の小ネタいろいろ

※2012/02/28追加 Paloは商用版の名称を会社名のJedoxに変更しました。よってJedox(Palo)と併記しています。
◯RDBMSのDM/DWHからCubeへの変換
単なるひとりごとです。これってTalend ETLを使う場合tMapで条件分岐を作らなくちゃいけないのかなと思っていたんですが、矢印ひっぱりまくるだけでいけました。と、文字だけでは意味分からんと思うので（笑）、簡単に変換構造を書いておきます。

元テーブル
AAA1 BBB1 CCC1
AAA2 BBB2 CCC2

変換先１
AAA1 ”Datatype1" BBB1
AAA2 ”Datatype1" BBB2

変換先２
AAA1 ”Datatype2" CCC1
AAA2 ”Datatype2" CCC2

変換先１,２はそれぞれtPaloOutputMultiを置いてもいいですし、tUniteで結んでもいいかもしれません。

◯RDBMSのDM/DWHからCubeへの変換２
ちなみにNullはエラーになるのでクエリの時点でNull撲滅させておいてください。

◯ユーザインターフェースの日本語化
日本語に翻訳したいな～ってJedox社の人に言ってみたらあっさり翻訳アカウントもらえました。いやー、ホントに良いソフトなので日本でももっと広まってもいいですね。要翻訳は3000エントリ。一ヶ月ぐらいかかりそうだなあ。

◯コミュニティエディションのインストール
試用版のPremiumはバージョン3.2で、Jedox(Palo) for excel と Jedox(Palo) web を同時にインストールしてくれますが、コミュニティ版は現在のところ3.1が利用できます。Jedox(Palo) for excel と Jedox(Palo) web をインストールするのですが、標準オプションのままインストールするとそれぞれがLocalなデータベースをインストールしようとするので注意が必要です。データベースを統一するには、Jedox(Palo) for excel のほうをカスタムインストールにして、DBをインストールしないってオプションをオンにします。そしてExcelでJedox(Palo)を使用時にOLAPサーバのポートを7777->7921に変更すればJedox(Palo) Webと同じOLAPサーバを使用することができます。

タグ：Palo Excel ETL Talend Jedox

Telend から Jedox(Palo)（ExcelベースBI） にデータを投入する

※2012/02/28追加 Paloは商用版の名称を会社名のJedoxに変更しました。よってJedox(Palo)と併記しています。

業務の合間を縫ってJedox(Palo)をいじってます。たのしー。(*ρ*)

テストDBを作成するためにExcelからキューブにデータを登録する関数（PALO.SETDATAとかなんとか）を使用してみましたが、ロードが遅く（おそらく数百/s）数百万件以上の読み込みには耐えられないと判断、早速Talendで読み込ませる設定を試してみました。ちなみにJedox(Palo)にも付属のETLツールがありますが試していないので使用感は不明です。

なんか若干コツがあります。
・Talend Open Studio 4.1.1における受け側コンポーネントとしては tPaloOutputMulti と tPaloOutput がありますが、tPaloOutputはhttp://www.talendforge.org/forum/viewtopic.php?id=2354 に書かれている通り、なんかいろいろ設定が必要っぽいのでtPaloOutputMulti を使用しました。
・自分がうまくいったスキーマはキューブのdimensionと同順に並べて、最終行にMeasureを並べるスキーマです。順番変えてもコラム名合わせたらいけるかなと思ったんですがなんか失敗しました(--;;
・日付を食らわせる場合は”2010-11-11”などの形式を利用し、スラッシュなどは使わない。一応Jedox(Palo)側ではエレメントの命名規則としてスラッシュ禁止になっています。
・同一dimensionを使用するMeasureが複数ある場合はDatatypeのようなdimensionを用意してそれで値を切り替えたほうがDB容量の圧縮に効くかもしれません。今はまだ試してみませんが、その場合ｔMapで振り分ければコンポーネントを大量にコピペすることなくできると思います。
・Talendにて3万件/sのスピードは出ています

早くもクセが分かってきました。ちゃんとしたDMができたら即公開できそうです。

タグ：Palo Excel Talend Jedox

Jedox(Palo)～ Excel OLAPで実現するセルフサービスBI

※2012/02/28追加 Paloは商用版の名称を会社名のJedoxに変更しました。よってJedox(Palo)と併記しています。
社内のBI（セルフサービス版）をどうしようか、いろいろな製品をあたってみましたがようやく結論がでました。日本ではかなり「どマイナー」っぽいんですが、Jedox(Palo)というオープンソースBIです。Excel上でOLAPキューブにアクセスする関数が使用できるアドインを利用してOLAPができるようになります。Excelでデータをいじるとどうしてもデータクオリティが下がるという問題がありますが、キューブは専用DBで管理され、consolidationされたデータの管理ができるため上記の懸念を払拭した上でExcelをBI的に使うことができます。そしてWebインターフェースがまたすばらしく、表計算ソフトを使用したことがある人なら使うことができそうなもので優しいUIです。試験的に使い始めたところ、割とすんなり馴染めたのでこれから本格的にデータをのっけていきたいと考えています。

○メリット
Cubeからデータを取り出した後のレポート化がExcelなためとっても楽。
Webへの発行がとても楽。名前付けるだけ。
Webでのスプレッドシートインターフェースが機能的にOffice2010互換を実現している。
（インポート、エクスポートが完璧なわけではなく、あくまで機能的に）
TalendにJedox(Palo)のコンポーネントがあるため、Talend使いの人ならETLでデータ投入が可能。

○デメリット
インターフェースが英語。
マニュアルが英語。
ネット上も日本語情報全く無し。
ってことで翻訳でもするかぁ～（笑）

タグ：Palo BI Jedox

Windows7 64bit で Talend Open Studio を使用する

環境移行で久しぶりにハマりました…。

Windows7 64bit を使用した場合以下のプログラムの32bit、64bitの選択が求められます。
・Office2010
・ODBCドライバ
・データソース管理
・Java
・MySQL
・Talend（64bitも探すとあった気がします）

結論としてはこの中で
Office2010－データソース管理－ODBCドライバ－Javaは32か64bitに統一する必要があります。

◯いろいろ起こる面倒な現象
・32か64bitの制約がつくものとしてはOffice2010です。MSは互換性の問題から64bit Officeをあまり推奨していません。
・64bit Officeがインストールされていないと64bitのODBCドライバがインストールされません。
・一方でTalendは現在Windows向けは32bitですが、この場合32bitのJavaを利用するため、データソースは32bitのドライバがあるものしか使用できません。
・データソース管理はコントロールパネルから立ち上げると64bitドライバのデータソースしか利用できません。一方で32bitのOfficeからODBC接続を使用すると32bitのデータソース管理画面が出てきます。
・バッチファイルを吐き出してそれを実行する場合、デフォルトのJavaが64bitになっていると、32bit Officeの場合64bit ODBCドライバが入っていないので、tAccess何がしを実行しようとしてもエラーになります。

ということで、若干妥協して32bit版で統一しました(--#。そこそこVBAを使用するのでOfficeを64bitにするのはちょっと怖い…。

タグ：Talend Windows7 64bit

Access 2007 VBA を VBS から実行する

ダーティーな方法ですがとりあえず何とかしたい時に役立つかもしれませんｗ
ちなみに以下のマクロはクエリを発行したのちExcelに吐き出すスクリプトです。

-----Access VBAの内容始まり-----
Sub マクロ名()
Dim strac As String
Dim varxls As Variant
Dim strmsg As String

strac = "クエリ名"
varxls = "C:\tmp\エクセルファイル.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strac, varxls, True

End Sub
-----Access VBAの内容終わり-----

-----VBSの内容始まり-----
Set objArgs = Wscript.Arguments
runXLmacro

Function runXLmacro()
Dim AcApp
Set AcApp = CreateObject("Access.Application")
AcApp.visible = true
AcApp.OpenCurrentDatabase "C:\Documents and Settings\ユーザ名\デスクトップ\XXX.accdb"
AcApp.Run "マクロ名"

End Function
-----VBSの内容終わり-----

この内容を書いたVBSを実行すればAccess内のマクロが実行されます。

タグ：access VBA VBS

OSS DB（データウェアハウス、データマート）の Infobright を試す

よく使うソフト（Pentaho,Jasper,Talend）からリンクされているオープンソースっぽいものがあったので見てみました。Infobright というDB（データウェアハウス、データマート）です。MySQL互換で、ツールや設定類がMySQL環境そのままで通ります。
http://www.infobright-partner.jp/

なんかコラム志向とやら圧縮が強いやら書いてあります。自分はDB素人なので難しい解説は本家に譲るとして、試した結果を書いておきたいと思います。

・インストール
　カンタン。
・設定
　特にしてません
・インターフェース
　MySQLそのままです。つーかデーモン名もmysqldだし…。
・データ読み込み
　今までの設定のまま（ポートを3306から5029に変えたぐらい）でいけました。Talendでロードしましたが、速さ的には同程度かと思われます。
・クエリ
　MySQL互換で通りました。集計クエリをいくつか食らわせて比較してみましたが平均5倍程度速いです。びっくり。

ちなみにコミュニティエディションでは一部のDML（INSERT、DELETE、UPDATE）が通りません。この制限が無ければすぐにでもReplaceするのにな～。左記の制限があるので固定されたデータに対するデータマートとしては有効に働くと思います。ということでなかなかオススメできるDBです。

Talend Open Studio (ETLツール) で業務の自動化～Excel→PDF→メール送信

Talend Open Profilerを使っているといろいろ面白そうなコンポーネントがあったりします。今回はその中のコンポーネントを組み合わせたりすることでレポート業務を自動化した取り組みを紹介します。

○Excelで作ったファイルをPDFで吐き出して送ることってあるよね
掲題の行為を全て自動化することを考えてみます。データの取得までは以前紹介した通常のETLのフロー・テクニックを作ることでできると思います。さて、それから先の業務が今まで
Accessクエリの結果をExcelに貼り付け→Excel上で整形しレポートとして公開
という手順でした。しかしこれだと全て手作業なので、欲しいデータを休日も見たいとかいった要望への対応ができません。そもそも毎日同じことをするのに同じ時間使うのは馬鹿らしいですよね。ということで自動化です。

○障壁1～Excel VBAのVBS化
とりあえず単純にマクロを呼べるVBSをネットで探します。引数を渡したりすることもできますが、まずはシンプルにやりたいので単純な関数実行にします。マクロの内容としては「他のブックの内容をデータシートに表示（="c:\xxx\[file.xls]sheet1!RC'）」して、コピーして値で貼り付けします。この際コピー先のファイルは貼り付けの際一回開く必要があります。

○障壁2～VBAでExcelからPDFを吐き出す
COMで扱えるPDF仮想プリンタドライバを使用するという方法がいろいろ公開されています。ですが、そこそこ面倒そうだし、フリーのドライバだと表示が崩れることもあります。なので自分はOffice2007のPDF出力アドインを使う、という方向で対処しました。ちなみにOffice2010だとアドインすら必要ありません。

○障壁3～コマンドライン実行
TalendでtSystemというコンポーネントを使用します。これは使い方簡単なようで1点だけ引っかかるところがあるので注意してください。コマンドの実行はWindowsの場合
”cmd /c 実行したいコマンド”
で行います。VBSの場合は実行したいコマンドだけ記述してもエラーになるので注意してください。

○障壁4～メール送信
tSendMailというコンポーネントを使用します。うちの会社はGoogle Appsを使用していますが、STARTTLSの設定やドメイン、ユーザ名など適切に設定することで送信することができました。

○障壁5～セキュリティ
まあいろいろあると思うので十分注意しましょうf(^^;;

ということでレポートに関す定型業務の自動化スキームが完成しました。これでいろいろなレポーティングの要望に対してクイックに対応することができるようになりました（祝）。やっぱりちゃんとしたシステムとアドホック対応のシステムの両輪で業務サポートできると強いですよね。

JasperServer Community Edition を日本語表示にする

あまりおすすめはしませんが、JasperServer Community Edition を日本語表示にするやり方を書いておきます。

(1)ファイルをダウンロード
http://jasperforge.org/jasperbabylon/download/4/
から Bundle_ja.properties をダウンロード

(2)配置
C://Program Files (x86)/jasperserver-ce-3.7.0/apache-tomcat/webapps/jasperserver/WEB-INF/bundles/
にダウンロードしたBundle_ja.propertiesをjasperserver_messages_ja.propertiesにリネームし保存します。

(3)再起動

これでなんとなく日本語が出るようになります。

しかしこれをインストールしたところでまだ不完全です。訳しきれていない（多少は翻訳してcommitしておきましたが、まだ完全ではありません。）のと、文字列のリソース整合性が取れていないのが原因です。

タグ：Jasperserver BI

Talend Open Studio (ETLツール) でデータ統合（４）

おまけの話です。

どうも64bitバージョンのTalend Open Studioが動かないと思ったら、インストールされているJREが32bitでした…。しかし日本語のJavaのサイトに行ってもWindows版の64bitが選択できない。英語のサイトに行って入手してようやく動いたわけですが、これってOracleのGoogle Chromeユーザーへの嫌がらせだったりするんでしょーか？（笑）

Talend Open Studio (ETLツール) でデータ統合（３）

○あんなことこんなことできたらいいなとそのTips
・データのコラムを減らしたりする
スキーマで行います。ここで*OutputBulkExecというコンポーネントは入出力をいっぺんにできるのですが、それを使ってしまうとコラム調整ができないので、入力コンポーネントと出力コンポーネントの間のスキーマで定義するか、tMapで抜くかのどちらかにしたら良いと思います。

・コラムの演算をしたいとき
例えば売上が税込になっているのを、税抜に変更したい時とかは、tMapをはさみます。マップエディタからコラムを開いてFIX(row1.xxx +0.49 / 1.05)のように行ないたい演算や関数を使って記述していきます。
# ちなみに四捨五入（FIX()）はありますが、切り上げ・切り下げは無いので上記のようにすることで
# 切り上げを実現することになります。切り下げの場合は-0.5してください。

・項目数を数えたい時
OLAPの際にMDXで記述することも可能らしいんですが、OLAP時の処理に時間がかかってしまうので予め処理しておくとよいです。そんな時はtAggreagteRowを使います。このコンポーネントはSUMやAVE、COUNTといった集計以外にも、集計したあとCount（Distinct-Count）などの関数も用意されています。

・0で始まる文字列の扱い
演算をする必要がない場合は最後のスキーマの直前まで文字列のままで、最後に変換をかけると良いです（コードのように00001のような文字列がある場合、下手にやると途中で1になってしまう）

・複数ファイルの連続処理
tFileListをｔFileInputDelimitedの前に配置し、コネクタをIterationにします。そして、ｔFileInputDelimitedで入力ファイル名のところでCtrl-Spaceを押して、tFileListのCURRENT_FILEPATH的な項目を選択します。当然ですがテーブルをDrop AND Createにすると最後に読み込んだファイルしか残らないので注意しましょう…（←これで1時間ハマってた人）。globは"*.txt"のように指定します。

とりあえず自分がしたい範囲のことに関しては大体できることが確認できました。今のところMDXの記述よりETLのほうが簡単そうなのでETLで出来る限りやり切ることを考えています。
もう少し日本語情報・コミュニティが増えていくといいですね。

タグ：Talend TOS ETL