はじめに
本記事では、株式会社ソウルウェアが提供するクラウド型勤怠管理・交通費精算ソリューションkinconeの勤怠情報や交通費精算情報をCData ODBC Driver for RESTを利用してPowerBI Desktopでビジュアライズする方法について説明します。
実現イメージ
今回はAPIの連携対象BIツールとして、Power BI Desktopを使用しますが、各APIは単純にBIツールに接続することはできません。各BIツールがどのようにWeb API側へリクエストを投げるべきかの判断材料やメタデータの情報が無いためです。そこで、CData ODBC Driver for RESTを用いて、このボトルネックを解決します。
まず、各BIツール（Tableau、QlikSence、PowerBI）からはODBCインタフェースでSQL（Select文）をCData ODBC Driver for RESTに向けて発行してもらいます。そのリクエストを受け取った、CData ODBC Driver for RESTは、kinconeのWebAPIのエンドポイントに対して、SQL文を解釈し、HTTPリクエスト形式に変換して、Getメソッドを発行します。リクエスト後、Jsonフォーマットで返ってきたデータセットはCData ODBC Driver for RESTがODBCインタフェースのResultsetに変換してBIツールに返します。これにより、BIツールからアドホックにSQLによるリクエストがあったタイミングで最新のデータをkinconeから取得することが可能です。
前提
- インターネットに接続可能なWindowsマシン
- kinconeのアカウント ※無料トライアルあり
- CData ODBC Drivers for REST ※30日間無料トライアルあり
- Microsoft Power BI Desktop
kinconeのWebAPIを利用するための準備
kinconeの設定画面「外部連携 > APIトークン」からAPIトークンを取得します。
kinconeのWebAPI定義はOpenAPISpec(旧Swagger)形式でこちらのドキュメントからご覧頂けます。
kinconeのスキーマ定義ファイルの準備
続いて、kinconeのWeb APIをCData ODBC Drivers for RESTがSQLとして解釈できるように設定ファイルを作成します。
サンプルとしてkinconeの下記のWaeAPIエンドポイントのデータを取得するための設定ファイルを作成していますので、以下のGitHubから保存して、利用してみてください。
| WebAPIエンドポイント名 | ファイル名 | 備考 |
|---|---|---|
| GET/users 従業員一覧取得 | Users.rsd | |
| GET/departments 部署・店舗一覧取得 | Departments.rsd | |
| GET/attendances 勤怠一覧取得 | Attendances.rsd | |
| GET/attendance/workflows 勤怠申請一覧取得 | AttendanceWorkflows.rsd / AttendanceWorkflowsDetails.rsd | 内訳はDetailesテーブル |
| GET/expense/workflows 交通費申請一覧取得 | ExpenseWorkflows.rsd / ExpenseWorkflowsDetails.rsd | 内訳はDetailesテーブル |
保存先フォルダは後ほど使用するので「C:\CData_REST\kincone」といった書き込み可能な任意のフォルダに配置してください。
CData 製品のインストール、および、初期設定
CData ODBC Drivers for REST のビルドをダウンロードします。ダウンロードすると、インストールファイル（.exe）がダウンロードされます。インストーラーを起動してソフトウェア使用許諾契約書を確認してデフォルトのままインストールします。
（注意） オンラインアクティベーションのためインターネットへの接続が必要となります
インストールが完了すると、ODBCのDSN設定のウィンドウが立ち上がりますので以下の項目をセットします。
| 項目 | 値 | 備考 |
|---|---|---|
| Custom Headers | Authorization:Bearer ***** | *****部分はAPIトークン |
| Location | C:\CData_REST\kincone | RSDファイル群のパス |
ODBCのDSN構成画面の「OK」ボタンをクリックして設定内容を保存して閉じます。
Microsoft Power BI Desktopでのデータ可視化
Microsoft Power BI Desktopを起動します。起動すると下記のダイアログが表示されるので「データを取得」をクリックします。
「データの取得」ダイアログが表示されるので、「その他 > ODBC」を選択して「接続」をクリックします。
「ODBCからインポート」ダイアログが表示されるので、「データソース名（DSN）」に「CData REST Source」を選択します。
認証情報が求められる場合は、既にODBCのDSN設定にてクレデンシャル情報を登録済みのため登録は不要なのですが、「ユーザー名」が必須項目となっているため適当な文字列を登録して「接続」ボタンをクリックします。
「ナビゲータ」画面が表示されます。左側の階層を開いていくと、テーブルのリストが表示されます。今回は、勤怠申請一覧の詳細である「AttendanceWorkflowsDetails」と交通費申請一覧取得の詳細である「ExpenseWorkflowsDetails」テーブルを選択してみます。選択すると右側にデータのプレビューが表示されますので「読み込み」ボタンをクリックします。
レポートのキャンパスの画面が表示されます。右側のフィールドにテーブル内の各項目がリストとして表示されることを確認します。
「レポート」ページに戻り、「テーブル」を２つと「円グラフ」を配置して、勤怠申請一覧の詳細である「AttendanceWorkflowsDetails」と交通費申請一覧取得の詳細である「ExpenseWorkflowsDetails」テーブルの各種情報をフィールドに配置します。勤怠申請の一覧と交通費申請の一覧のビジュアライズが出来ました。
テストデータは、１ユーザのデータのみの表示となりますが、部門に所属する各ユーザの勤怠情報や交通費を管理者がモニタリングするような画面が作成できると思います。
必要に応じて左上の保存ボタンをクリックして作成したレポートを「Power BI ファイル（*.pbix）」として保存してください。
Microsoft PowerBI Desktopからkinconeのデータを可視化することが出来ました。
補足
勤怠、交通費のデータを取得する際には、emailとtermをSQLのwhere句で指定する事で、該当ユーザや取得する年月を指定する事が可能です。サンプルのSQLは以下の通りです。
| テーブル名（論理） | テーブル名（物理） | SQLサンプル | 備考 |
|---|---|---|---|
| 従業員一覧 | Users | select * from Users | |
| 部署・店舗一覧 | Departments | select * from Departments | |
| 勤怠一覧 | Attendances | select * from Attendances where email = '*@*.co.jp' and term = '201805' | emailは必須、 termは任意（指定しない場合は当月） |
| 勤怠申請一覧 | AttendanceWorkflows | select * from AttendanceWorkflows where email = '*@*.co.jp' and term = '201805' | email、 term共に任意（指定しない場合は当月） |
| 勤怠申請一覧（詳細） | AttendanceWorkflowsDetails | select * from AttendanceWorkflowsDetails where email = '*@*.co.jp' and term = '201805' | email、 term共に任意（指定しない場合は当月） |
| 交通費申請一覧 | ExpenseWorkflows | select * from ExpenseWorkflows where email = '*@*.co.jp' and term = '201805' | email、 term共に任意（指定しない場合は当月） |
| 交通費申請一覧（詳細） | ExpenseWorkflowsDetails | select * from ExpenseWorkflowsDetails where email = '*@*.co.jp' and term = '201805' | email、 term共に任意（指定しない場合は当月） |