BIツールのPentahoと、
BIでの利用に適したカラム指向データベースInfobrightでBI環境を構築し、
多次元データの分析を行ってみます。

Pentaho, Infobrightともに、
CommunityEditionというオープンソース版が存在するので、
ソフトウェアのライセンス費用をかけずに、試してみる事が出来ます。
ここではCommunityEditionを使って環境構築・分析を行ってみます。

Pentaho Community
 http://community.pentaho.com

Infobright
 http://www.infobright.org

サーバ環境構築は次の手順で行います。
1. Javaのセットアップ
2. Pentaho BI Serverのセットアップ
3. Infobrightのセットアップ
※この記事では、サーバOSはCentOS6を想定します。

データ分析は次の手順で行います。
1. 分析するデータのスキーマ設計
2. Infobrightへのデータロード
3. Pentaho Schema Workbenchでのキューブ設計
4. Pentaho BI Serverへの接続と簡単な分析

サーバ環境構築 1. Javaのセットアップ

以下のサイトからJavaSDKをダウンロードします。

Java SE Downloads
 http://www.oracle.com/technetwork/java/javase/downloads/index.html
ファイル名:jdk-6u37-linux-x64-rpm.bin (Linux x64の場合)

ダウンロードしたファイルをおいたディレクトリで、以下のコマンドを実行し、
実行権を付与した上でセットアップを実行します。

$ chmod 744 jdk-6u37-linux-x64-rpm.bin
$ ./jdk-6u37-linux-x64-rpm.bin

javaコマンドでバージョンを確認出来ればセットアップは成功です。

$ java -version
java version "1.6.0_37"
Java(TM) SE Runtime Environment (build 1.6.0_37-b06)
Java HotSpot(TM) 64-Bit Server VM (build 20.12-b01, mixed mode)

サーバ環境構築 2. Pentaho BI Serverのセットアップ

以下のサイトからPentaho BI Server Community Editionをダウンロードします。

Pentaho Business Analytics
 http://sourceforge.net/projects/pentaho/
 → Files → Business Intellignece Server → 4.5.0-stable → biserver-ce-4.5.0-stable.tar.gz
ファイル名:biserver-ce-4.5.0-stable.tar.gz

ダウンロードしたファイルをおいたディレクトリで、以下のコマンドを実行し、
ダウンロードしたファイルを展開します。

$ mkdir /usr/local/pentaho
$ tar zxf biserver-ce-4.5.0-stable.tar.gz -C /usr/local/pentaho

以下のコマンドを入力してPentaho BI Serverを起動します。

$ /usr/local/pentaho/biserver-ce/start-pentaho.sh

起動時が完了した後、ブラウザから以下のURLにアクセスし、
Pentahoのログイン画面が出ればセットアップは成功です。

http://<サーバのIPアドレス>:8080/pentaho/Login

# 停止は「/usr/local/pentaho/biserver-ce/stop-pentaho.sh」です。

サーバ環境構築 3. Infobrightのセットアップ

以下のサイトからInfobright Community Editionをダウンロードします。

Infobright.org
 http://www.infobright.org/
 → DOWNLOAD → Download ICE
ファイル名:infobright-4.0.7-0-x86_64-ice.rpm (Linux x64の場合)

ダウンロードしたファイルをおいたディレクトリで、以下のコマンドを実行し、
インストールを実行します。

$ rpm -ivh infobright-4.0.7-0-x86_64-ice.rpm

インストールが完了したら以下のコマンドでInfobrightを開始します。

$ service mysqld-ib start

Infobrightが開始したら、以下のコマンドでデータベースに接続できることを確認します。

$ mysql -u root -P 5029

ここまでサーバ環境の構築が完了です。

データ分析 1. 分析するデータのスキーマ設計

次に、分析するデータのスキーマを設計します。
次の「日時」「支店」「金額」の売り上げデータを、
「支店」「販売年月」の2軸から分析するスタースキーマに変換しておきます。
# ここでは、以下のデータをスクリプトなどで生成する想定とします。

◆売り上げデータ
2012-11-20 10:35:00 東京店 1000
2012-12-05 19:07:00 大阪店 1200
2012-12-07 12:42:00 京都店 2200
2012-12-10 13:05:00 東京店 1800

◆Dimentionテーブル(支店)
店舗キー 店舗名
1 東京店
2 京都店
3 大阪店

◆Dimentionテーブル(販売年月)
日付キー 年 月
201211 2012 11
201212 2012 12

◆Factテーブル(売り上げ)
日付キー 店舗キー 金額
201211 1 1000
201212 3 1200
201212 2 2200
201212 1 1800

# このようなデータの生成は、
# 通常はPentahoのDataIntegrationを用いて作ります。
# が、Infobright CommunityEditionでは
# レコードのInsert/Updateができず、
# DataIntegrationですんなりデータが作りにくいので、
# ここでは、別途スクリプト等でデータ生成するものとして話を進めます。

データ分析 2. Infobrightへのデータロード

次に、先ほど作成したデータをInfobrightにロードします。
CommunityEditionではInsert/Updateができないため、
LOAD DATAでテーブルのデータを一括でロードする手順になります。

最初にデータベースとユーザを作成しておきます。
ここではデータベース・ユーザともにbitestとしておきます。

create database bitest default character set utf8;
grant all on bitest.* to 'bitest'@'%' identified by 'bitest';
grant all on bitest.* to 'bitest'@'localhost' identified by 'bitest';

次に、MySQLコマンドから、以下のSQLを実行しテーブルを作成します。

create table dim_yearmonth (
key_yearmonth int,
year int,
month int
);
create table dim_shop (
key_shop int,
name varchar(128)
);
create table fact_sales (
key_yearmonth int,
key_shop int,
amount int
);

次に、MySQLコマンドから、以下のSQLでデータをロードします。
ロードするデータはタブ区切りで、それぞれ以下の名前で保存しておきます。
・Dimentionテーブル(支店) … /tmp/dim_shop.tsv
・Dimentionテーブル(販売年月) … /tmp/dim_yearmonth.tsv
・Factテーブル(売り上げ) … /tmp/fact_sales.tsv

LOAD DATA LOCAL INFILE '/tmp/dim_yearmonth.tsv' INTO TABLE dim_yearmonth
FIELDS TERMINATED BY '\t' ENCLOSED BY '\"' ESCAPED BY '\\'
LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE '/tmp/dim_shop.tsv' INTO TABLE dim_shop
FIELDS TERMINATED BY '\t' ENCLOSED BY '\"' ESCAPED BY '\\'
LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE '/tmp/fact_sales.tsv' INTO TABLE fact_sales
FIELDS TERMINATED BY '\t' ENCLOSED BY '\"' ESCAPED BY '\\'
LINES TERMINATED BY '\n';

次にPentaho BI Serverから作成したデータベースへの接続を設定しておきます。

Pentaho BI Serverを起動した状態で、
ブラウザから以下のURLにアクセスし、pentahoにログインします。
# セットアップ後なにも設定を変更していなければ、
# ユーザ「joe」、パスワード「password」でログインできます。
 http://<サーバのIPアドレス>:8080/pentaho/Login

ログイン後、「ファイル」→「新規作成」→「DataSource...」メニューを選択します。
Data Source Wizardで、
Source Typeのドロップダウンリストを「Database Table(s)」に切り替えます。
Connectionの一覧が表示された後、+ボタン(一覧右上の緑色アイコン)を押下します。

DatabaseConnectionに次のような設定を行いOKを押下します。
- Connection Name: 任意の名称、ここではbitestとしておきます
- Connection Type: MySQL
- Access: Native(JDBC)
- Host Name: InfobrightをセットアップしたホストのIPアドレスかホスト名
- Database Name: Infobrightに作成したデータベースの名前
- Port Number: 5029(設定を変えていなければ)
- User Name: Infobrightに作成したユーザの名前
- Password: UserNameに指定したユーザのパスワード

pentahoinfobright01

Data Source Wizardに戻るのでCancelボタン押下で終了します。

データ分析 3. Pentaho Schema Workbenchでのキューブ設計

次に、Schema Workbenchでキューブの設計を行います。
Schema Workbenchでキューブを設計し、
設計したキューブをPentaho BI Serverにpublishするという流れになります。

まず準備として、PentahoBIServer側にpublishのパスワードを設定します。
以下のように、次のファイルにパスワードを指定します。
 /usr/local/pentaho/biserver-ce/pentaho-solutions/system/publisher_config.xml
※この例では「password」というパスワードを設定しています。

変更前:

<publisher-password></publisher-password>

変更後:

<publisher-password>password</publisher-password>

パスワードの設定が終わったら、Pentaho Schema Workbenchをセットアップします。
ここからの作業はWorkbenchを利用するクライアントPC側で行います。
以下のサイトからPentaho Schema Workbenchをダウンロードします。

Mondrian
 http://sourceforge.net/projects/mondrian/
 → Files → schema workbench → 3.4.1-stable → psw-ce-3.4.1.1.zip
ファイル名:psw-ce-3.4.1.1.zip

ダウンロードしたファイルを展開します。
展開後のファイルから「workbench.bat」を実行すると、
Schema Workbenchが起動します。

但し、キューブ設計に日本語を含める場合は、
publish時にエラーが発生するので、
次のように、「workbench.bat」の起動処理部分に
「-Dfile.encoding="UTF-8"」を追記しておきます。

変更前:

"%_PENTAHO_JAVA%" -Xms100m -Xmx500m -cp "%CP%" -Dlog4j.configuration=file:///%ROOT%\.schemaWorkbench\log4j.xml mondrian.gui.Workbench

変更後:

"%_PENTAHO_JAVA%" -Xms100m -Xmx500m -Dfile.encoding="UTF-8" -cp "%CP%" -Dlog4j.configuration=file:///%ROOT%\.schemaWorkbench\log4j.xml mondrian.gui.Workbench

参考:よくある質問 - オープンソースBIのPentaho(ペンタホ)ブログ
 http://www.pentaho-partner.jp/blog/2011/10/post-43.html

次に、Schema Workbenchに先ほど作成したInfobrightへの接続設定をします。
Schema Workbenchを起動し、「Options」→「初期設定」メニューを選び、
DatabaseConnectionに次のような設定を行いOKを押下します。
- Connection Name: 任意の名称
- Connection Type: MySQL
- Access: Native(JDBC)
- Host Name: InfobrightをセットアップしたホストのIPアドレスかホスト名
- Database Name: Infobrightに作成したデータベースの名前
- Port Number: 5029(設定を変えていなければ)
- User Name: Infobrightに作成したユーザの名前
- Password: UserNameに指定したユーザのパスワード

pentahoinfobright02

それではいよいよキューブの設計に入ります。
「ファイル」→「新規作成」→「スキーマ」メニューを選び、新規スキーマを作成します。

左側のツリーペインの「schema」のコンテキストメニューから
「キューブ追加」を選択してキューブを追加します。

pentahoinfobright03

左側のツリーペインの「New Cube 0」のコンテキストメニューから
「Add Table」を選択してテーブルを追加、
「ディメンジョン追加」を2回選択してディメンジョンを2つ追加、
「メジャー追加」を選択してメジャーを追加します。

さらに、ツリーを開き「New Dimension 0」配下にある、
「New Hierarchy 0」のコンテキストメニューから
「レベル追加」を選択してテーブルを追加、
「Add Table」を選択してテーブルを追加しておきます。

ツリーを開き「New Dimension 1」配下にある、
「New Hierarchy 0」のコンテキストメニューから
「レベル追加」を選択してテーブルを2回追加、
「Add Table」を選択してテーブルを追加しておきます。

以上の操作で、ツリーを全て開くと以下画面の状態になっているはずです。

pentahoinfobright04

引き続き、ツリーの各項目に想定したスキーマの情報を指定していきます。
(「支店」「販売年月」の2軸から売上を分析するキューブ)

以下の通り、
ツリーから各項目を選択してAttributeに対するValueを指定していきます。
※primaryKey, foreignKey, columnなどの項目は、
 同じor配下のテーブル指定後でないと選択肢が表示されないので、
 行き来しながら指定して行ってください。

Schema:
 name: bitest

Schema/New Cube0:
 name: sales
 caption: 売上分析

Schema/New Cube0/Table:Table:
 name: fact_sales

Schema/New Cube0/New Dimension 0:
 name: shop
 foreignKey: fact_sales->key_shop
 caption: 支店

Schema/New Cube0/New Dimension 0/New Hierarchy 0:
 name: shop
 primaryKey: dim_shop->key_shop
 caption: 支店

Schema/New Cube0/New Dimension 0/New Hierarchy 0/New Level 0:
 name: shop
 column: dim_shop->key_shop
 captionColumn: dim_shop->name
 caption: 支店

Schema/New Cube0/New Dimension 0/New Hierarchy 0/Table: Table:
 name: dim_shop

Schema/New Cube0/New Dimension 1:
 name: yearmonth
 foreignKey: fact_sales->key_yearmonth
 caption: 販売年月

Schema/New Cube0/New Dimension 1/New Hierarchy 0:
 name: yearmonth
 primaryKey: dim_yearmonth->key_yearmonth
 caption: 販売年月

Schema/New Cube0/New Dimension 1/New Hierarchy 0/New Level 0:
 name: year
 column: dim_yearmonth->year
 captionColumn: dim_yearmonth->year
 caption: 販売年

Schema/New Cube0/New Dimension 1/New Hierarchy 0/New Level 1:
 name: month
 column: dim_yearmonth->month
 captionColumn: dim_yearmonth->month
 caption: 販売月

Schema/New Cube0/New Dimension 1/New Hierarchy 0/Table: Table:
 name: dim_yearmonth

Schema/New Cube0/New Measure 0:
 name: gross_sales
 aggregator: SUM
 column: fact_sales->amount
 caption: 売上合計

すべて入力が終わると以下のイメージのようなツリー表示になります。
入力が終わったら「ファイル」→「保存」メニューで、
適当に名前をつけてファイルを保存しておきます。

pentahoinfobright05

次は、Pentaho BI Serverに作成したキューブをPublishします。
「ファイル」→「Publish...」メニューを選びます。
「Repository Login」ダイアログに以下の内容を指定して「OK」を押下します。
 URL: http://<サーバのIPアドレス>:8080/pentaho/
 Publish Password: publisher_config.xmlに指定したパスワード
  (この記事の説明どおりの場合はpassword)
 User: Pentahoのユーザ(何も設定変更していなければjoe)
 Password: Userのパスワード(何も設定変更していなければpassword)

「Publish Schema」ダイアログに以下の内容を指定して「Publish」を押下します。
 Pentaho or JNDI Data Source: Connection Nameで指定した名称
  (この記事の説明どおりの場合はbitest)

Schema情報のサーバへのpublishが成功すると、
「Publish Successful」と表示されます。

データ分析 4. Pentaho BI Serverへの接続と簡単な分析

それでは、publishしたSchemaを使って分析をおこないます。

Pentaho BI Serverを起動した状態で、
ブラウザから以下のURLにアクセスし、pentahoにログインします。
# セットアップ後なにも設定を変更していなければ、
# ユーザ「joe」、パスワード「password」でログインできます。
 http://<サーバのIPアドレス>:8080/pentaho/Login

「ファイル」→「新規作成」→「アナリシスビュー...」メニューを選択します。
アナリシスビューダイアログで、以下を指定して「OK」を押下します。
 スキーマ:bitest
 キューブ:売上分析

以下のような画面が表示されるます。

pentahoinfobright06

支店・販売年月の+を開くと支店別・販売年月の売上合計を表示できます。

pentahoinfobright07

各項目の行列切り替えを行いたい場合ですが、
支店を行側に表示させる場合を例に手順を示します。
左上の「Open OLAP Navigator」ボタンからOLAP Navigatorを開き、
「Rows」の「支店」のColumnボタンを押下します。

pentahoinfobright08

その後、「OK」とすると次の画面のように行側に支店が表示されます。

pentahoinfobright09

表示する情報を絞り込みたい場合ですが、
販売年月を2012年12月だけに絞り込む場合を例に手順を示します。
左上の「Open OLAP Navigator」ボタンからOLAP Navigatorを開き、
「Rows」の「販売年月」リンクを押下し、
ここで「12」のみをチェックした状態にします。

pentahoinfobright10

その後、「OK」、「OK」と押下すると
次の画面のように2012年12月だけの情報が表示されます。

pentahoinfobright11

「Drill Through」を使うと、各集計値の明細をみることができます。
「Drill Through」ボタンを押下し、

pentahoinfobright12

表示したい集計値の横にあるShow source data(下向き矢印)ボタンを押すと
下側に明細が表示されます。

pentahoinfobright13

このようにして、
キューブ設計で想定したDimensionを切り口とした分析をすることが出来ます。
ここで紹介した以外にもいろいろな分析機能がありますが、
ひとまず、簡単なクロス集計を実現するための流れを紹介しました。

この方法だけでも、
Excelのピボットテーブルで扱いきれないサイズのクロス集計が出来るので、
データ分析の幅を少し広げることができるのではないかと思います。