freee の取引データをMac版 Tableau Desktop でビジュアライズしてみよう

はじめに

本記事では、freee株式会社が提供するクラウド会計ソフトfreeeの取引データをCData ODBC Drivers for RESTを利用してMac版Tableau Desktopでビジュアライズする方法について解説します。

実現イメージ

今回はてMac版Tableau Desktopでからfreee APIに接続しますが、Tableauは単純に　freeeと接続することができません。それはTableauがどのようにfreee API側へリクエストを投げるべきかの判断材料やメタデータの情報が無いためです。

そこで、CData REST ODBC Driverを用いて、このボトルネックを解決します。まず、TableauからはODBCインタフェースでSQL（Select文）をCData REST ODBC Driverに向けて発行してもらいます。そのリクエストを受け取った、CData REST ODBC Driverは、freeeのWebAPIのエンドポイントに対して、SQL文を解釈し、HTTPリクエスト形式に変換して発行します。リクエスト後、Jsonフォーマットで返ってきたデータセットはCData REST ODBC DriverがODBCインタフェースのResultsetに変換してTableauに返します。

これにより、TableauからアドホックにSQLによるリクエストがあったタイミングで最新のデータを freee から取得することが可能です。それでは実際に、Tableauからfreee内の取引データを取得してみたいと思います。

前提

• インターネットに接続可能なMacOSマシン
• iODBC Driver Manager
• クラウド会計ソフトfreeeのアカウント ※３０日間の無料試用版あり
• CData ODBC Drivers for REST ※３０日間の無料試用版あり
• Tableau Desktop ※無料試用版あり

freeeのAPIを利用するための準備

freee スタートガイド の「はじめに」の以下手順を実施して、AppIDとSecretを取得します。

CData ODBC Drivers for REST 製品のインストール、および、初期設定

CData ODBC Drivers for RESTMac版のビルドをダウンロードします。ダウンロードすると、ディスクイメージファイル（.dmg）がダウンロードされるので、管理者権限で中のパッケージファイル（.pkg）のインストーラーを起動してソフトウェア使用許諾契約書を確認してデフォルトのままインストールします。

ターミナルで次のコマンドを実行して、本製品のライセンスを取得します。評価版をアクティベートするには、<key> の入力を省略してください。

cd "/Applications/CData ODBC Driver for REST/bin"
sudo ./install-license <key>

（注意） オンラインアクティベーション時にはインターネットへの接続が必要となります

iODBC アドミニストレータを昇格権限で起動します。それには、次のコマンドをターミナルに入力します。

sudo /Applications/iODBC/iODBC\ Administrator.app/Contents/MacOS/iODBC\ Administrator

iODBC アドミニストレータが起動したら、「System DSN」タブを開き、「Add」ボタンをクリックします。

「CData ODBC Driver for REST」を選択します。

Data Source Name(DSN)をセット（例：CData REST　Source freee）して、下記の項目をセットします。

項目	値	備考
OAuthVersion	2.0
OAuthAccessTokenURL	https://api.freee.co.jp/oauth/token
OAuthAuthorizationURL	https://secure.freee.co.jp/oauth/authorize
OAuthClientId	***********************************	freee側のアプリケーション登録時のApp ID
OAuthClientSecret	***********************************	freee側のアプリケーション登録時のSecret
InitiateOAuth	GETANDREFRESH
Callbackurl	http://localhost
Location	/Applications/CData/freee/	スキーマファイルの格納パス
Format	JSON
Other	CheckPromptMode=False
logfile	/Applications/CData/CdataLog/Freee_ODBC.log	(任意)CDataのログファイルを出力する場合、出力パスを指定
verbosity	3	(任意)CDataのログファイルを出力する場合、出力レベルを指定（3）

「OK」ボタンをクリックして、「iODBC Data Source Administrator」の画面に戻り、「Test」ボタンをクリックします。

ブラウザが起動して、freeeのログイン画面が表示されるので、freeeへのユーザIDおよびパスワードを入力してログインします。すると下記のような認証が出来た旨の画面が表示されます。ブラウザを閉じます。

下記のダイアログが表示されれば、freeeへの一覧のOAuthによる認証認可のプロセスが完了したことになります。

本ダイアログを閉じて、作成したDSN「CData REST Source freee」を開きます。項目内に「persist」が追加されていることを確認します。本項目にAccessTokenが格納されています。

「OK」ボタンをクリックして、「iODBC Data Source Administrator」の画面も「OK」ボタンをクリックして閉じます。

スキーマ定義の配置

Githubから「.rsd」ファイル群をダウンロードしてローカルディレクトリに配置します。

配置パスの例
/Applications/CData/freee/

Tableau Desktopからの接続

事業所の確認

TableauDesktopの「その他のデータソース（ODBC）」をクリックして、上記手順で作成したDSN（例：CData REST Source freee）を選択して「サインイン」ボタンをクリックします。

「データソース」に移動するので、「データベース」から「CData」を選択して、表示された表の中から「_companies（事業所）」テーブルを選択して右上のペインにドラッグ＆ドロップして配置します。「今すぐ更新」を行い、事業所一覧が取得されることを確認します。id(事業所ID)は後の手順で使用するので手元に控えておきます。

（注意）「Xcompanies」のように、アンダーバーが「X」に変換されてしまう場合は、右上のペインにドロップしたテーブル名を右クリックして「テーブル名のリセット」をしてください。

同じ手順で、今度は「_deals（取引）」テーブルを選択してデータを取得してみましょう。すると、「アクセス権がありません。存在しないかすでにさ削除された事業所です。」といったエラーメッセージが表示されます。

これは「_deals（取引）」テーブルに該当するAPIエンドポイントには、リクエスト時のパラメータとして事業所IDが必要です。今回は本事業所IDを指定していないため本エラーが発生しました。

（ご参考）freee API （収入／支出）一覧の取得

そこで、上記で取得した事業所IDをiODBCのDSNにセットします。iODBC アドミニストレータを昇格権限で起動して、作成したDSN（例：CData REST Source freee）を開いて、下記のパラメータを追加します。

追加する項目	値	備考
CustomUrlParams	company_id=******	上記手順で取得した事業所ID

DSNを保存して、再度、開いてみて上記パラメータの保存が出来ていないような場合は、下記のコマンドで直接、「odbc.ini」ファイルを書き換えることも可能です。

sudo vi /Library/ODBC/odbc.ini

項目の追加が出来たら「iODBC Data Source Administrator」の画面を閉じます。

取引データ、および、各種マスターデータの確認

再度、Tableauを起動して、「_deals（取引）」テーブルにアクセスします。今度は、正常に「_deals（取引）」データが取得出来ました。

続いて、各マスターテーブル群も右上のペインにドラッグ＆ドロップで配置して、以下の通り、結合キーを指定します。

結合タイプ	テーブル（左）	結合キー（左）	テーブル（右）	結合キー（右）
左	_deals（取引）	detailes_account_item_id	_account_items（勘定科目）	id
左	_deals（取引）	conpany_id	_companies（事業所）	id
左	_deals（取引）	partner_id	_partners（取引先）	id
左	_deals（取引）	details_tax_id	_taxes（税区分）	id
左	_deals（取引）	payments_from_walletable_id	_walletables（口座）	id

実際にどのようなSQLが発行されるのか確認してみます。Tableau Desktopのメニューバーから「データ > カスタムSQLに変換」を開きます。下記のように、「LEFT JOIN」で「_deals（取引）」テーブルをベースに、各マスターテーブルが「LEFT JOIN」されているSQLが生成されていることを確認出来ます。確認したら、「カスタムSQLに変換」ウィンドウは「キャンセル」して閉じてください。

SELECT "_deals (REST._deals)"."amount" AS "amount",
  "_walletables (REST._walletables)"."bank_id" AS "bank_id",
  "_account_items (REST._account_items)"."categories" AS "categories",
  "_deals (REST._deals)"."company_id" AS "company_id",
  "_partners (REST._partners)"."company_id" AS "company_id__Xpartners_",
  "_account_items (REST._account_items)"."default_tax_code" AS "default_tax_code",
  "_account_items (REST._account_items)"."default_tax_id" AS "default_tax_id",
  "_deals (REST._deals)"."details_account_item_id" AS "details_account_item_id",
  "_deals (REST._deals)"."details_amount" AS "details_amount",
  "_deals (REST._deals)"."details_description" AS "details_description",
  "_deals (REST._deals)"."details_entry_side" AS "details_entry_side",
  "_deals (REST._deals)"."details_item_id" AS "details_item_id",
  "_deals (REST._deals)"."details_section_id" AS "details_section_id",
  "_deals (REST._deals)"."details_tax_code" AS "details_tax_code",
  "_deals (REST._deals)"."details_tax_id" AS "details_tax_id",
  "_deals (REST._deals)"."details_vat" AS "details_vat",
  "_companies (REST._companies)"."display_name" AS "display_name",
  "_deals (REST._deals)"."due_amount" AS "due_amount",
  "_deals (REST._deals)"."due_date" AS "due_date",
  "_deals (REST._deals)"."id" AS "id",
  "_account_items (REST._account_items)"."id" AS "id__Xaccount_items_",
  "_companies (REST._companies)"."id" AS "id__Xcompanies_",
  "_partners (REST._partners)"."id" AS "id__Xpartners_",
  "_taxes (REST._taxes)"."id" AS "id__Xtaxes_",
  "_walletables (REST._walletables)"."id" AS "id__Xwalletables_",
  "_deals (REST._deals)"."issue_date" AS "issue_date",
  "_account_items (REST._account_items)"."name" AS "name",
  "_companies (REST._companies)"."name" AS "name__Xcompanies_",
  "_partners (REST._partners)"."name" AS "name__Xpartners_",
  "_taxes (REST._taxes)"."name" AS "name__Xtaxes_",
  "_walletables (REST._walletables)"."name" AS "name__Xwalletables_",
  "_companies (REST._companies)"."name_kana" AS "name_kana",
  "_deals (REST._deals)"."partner_id" AS "partner_id",
  "_deals (REST._deals)"."payments_amount" AS "payments_amount",
  "_deals (REST._deals)"."payments_date" AS "payments_date",
  "_deals (REST._deals)"."payments_from_walletable_id" AS "payments_from_walletable_id",
  "_deals (REST._deals)"."payments_from_walletable_type" AS "payments_from_walletable_type",
  "_deals (REST._deals)"."ref_number" AS "ref_number",
  "_companies (REST._companies)"."role" AS "role",
  "_account_items (REST._account_items)"."shortcut" AS "shortcut",
  "_partners (REST._partners)"."shortcut1" AS "shortcut1",
  "_partners (REST._partners)"."shortcut2" AS "shortcut2",
  "_account_items (REST._account_items)"."shortcut_num" AS "shortcut_num",
  "_deals (REST._deals)"."type" AS "type",
  "_walletables (REST._walletables)"."type" AS "type__Xwalletables_"
FROM "REST"."_deals" "_deals (REST._deals)"
  LEFT JOIN "REST"."_account_items" "_account_items (REST._account_items)" ON ("_deals (REST._deals)"."details_account_item_id" = "_account_items (REST._account_items)"."id")
  LEFT JOIN "REST"."_companies" "_companies (REST._companies)" ON ("_deals (REST._deals)"."company_id" = "_companies (REST._companies)"."id")
  LEFT JOIN "REST"."_partners" "_partners (REST._partners)" ON ("_deals (REST._deals)"."partner_id" = "_partners (REST._partners)"."id")
  LEFT JOIN "REST"."_taxes" "_taxes (REST._taxes)" ON ("_deals (REST._deals)"."details_tax_id" = "_taxes (REST._taxes)"."id")
  LEFT JOIN "REST"."_walletables" "_walletables (REST._walletables)" ON ("_deals (REST._deals)"."payments_from_walletable_id" = "_walletables (REST._walletables)"."id")

ビジュアライズ

Tableauワークブックを保存して、「シート１」に移動します。ドラッグ＆ドロップ操作で積み上げ棒グラフを選択して以下の通り、ディメンションおよびメジャー内の項目を右側ペインに配置します。

ディメンション・メジャー	項目	配置	備考
ディメンション（_account_items）	category	フィルタ	資本、差引損益計算、営業損益、経費
ディメンション（_account_items）	name	列
ディメンション（_walletables）	name	色
ディメンション（メジャー）	ammount	行

取引データを、経費の勘定科目と口座毎に集計した積み上げグラフが作成出来ました。

（補足）freee用スキーマ定義の生成方法

上記手順「スキーマ定義の配置」で使用したスキーマファイル「．rsd」ファイルは下記のストアドプロシージャを実行して生成することも可能です。ストアドプロシージャを実行できるツール（iODBC Demo Unicodeなど)から実行ください。

EXEC CreateSchema @TableName='companies', @URI='https://api.freee.co.jp/api/1/companies', @XPath='/companies', @Format='JSON', @FileLocation='/Applications/CData/freee/', @FileName='companies.rsd';
EXEC CreateSchema @TableName='deals', @URI='https://api.freee.co.jp/api/1/deals', @XPath='/deals', @Format='JSON', @FileLocation='/Applications/CData/freee/', @FileName='deals.rsd';
EXEC CreateSchema @TableName='partners', @URI='https://api.freee.co.jp/api/1/partners', @XPath='/partners', @Format='JSON', @FileLocation='/Applications/CData/freee/', @FileName='partners.rsd';
EXEC CreateSchema @TableName='account_items', @URI='https://api.freee.co.jp/api/1/account_items', @XPath='/account_items', @Format='JSON', @FileLocation='/Applications/CData/freee/', @FileName='account_items.rsd';
EXEC CreateSchema @TableName='taxes', @URI='https://api.freee.co.jp/api/1/taxes', @XPath='/taxes', @Format='JSON', @FileLocation='/Applications/CData/freee/', @FileName='taxes.rsd';
EXEC CreateSchema @TableName='walletables', @URI='https://api.freee.co.jp/api/1/walletables', @XPath='/walletables', @Format='JSON', @FileLocation='/Applications/CData/freee/', @FileName='walletables.rsd';

まとめ

本記事では、freee内取引および各マスターデータをCData Drivers for RESTを利用してMac版 Tableau Desktop から参照する方法について説明しました。前提として必要なサービスやソフトウェアは試用版があるので、是非、お試しください。