ODBC
PowerBI
CData
kincone

kinconeの勤怠・交通費精算情報をPower BI Desktopでビジュアライズ

はじめに

本記事では、株式会社ソウルウェアが提供するクラウド型勤怠管理・交通費精算ソリューション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から取得することが可能です。

image.png

前提

kinconeのWebAPIを利用するための準備

kinconeの設定画面「外部連携 > APIトークン」からAPIトークンを取得します。

image.png

kinconeのWebAPI定義はOpenAPISpec(旧Swagger)形式でこちらのドキュメントからご覧頂けます。

kinconeのスキーマ定義ファイルの準備

続いて、kinconeのWeb APIをCData ODBC Drivers for RESTがSQLとして解釈できるように設定ファイルを作成します。

サンプルとしてkinconeの下記のWaeAPIエンドポイントのデータを取得するための設定ファイルを作成していますので、以下のGitHubから保存して、利用してみてください。

https://github.com/kuwazzy/CDataKincone/tree/master/kincone

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)がダウンロードされます。インストーラーを起動してソフトウェア使用許諾契約書を確認してデフォルトのままインストールします。

image.png

(注意) オンラインアクティベーションのためインターネットへの接続が必要となります

インストールが完了すると、ODBCのDSN設定のウィンドウが立ち上がりますので以下の項目をセットします。

image.png

項目 備考
Custom Headers Authorization:Bearer ***** *****部分はAPIトークン
Location C:\CData_REST\kincone RSDファイル群のパス

ODBCのDSN構成画面の「OK」ボタンをクリックして設定内容を保存して閉じます。

Microsoft Power BI Desktopでのデータ可視化

Microsoft Power BI Desktopを起動します。起動すると下記のダイアログが表示されるので「データを取得」をクリックします。

image.png

「データの取得」ダイアログが表示されるので、「その他 > ODBC」を選択して「接続」をクリックします。

image.png

「ODBCからインポート」ダイアログが表示されるので、「データソース名(DSN)」に「CData REST Source」を選択します。

image.png

認証情報が求められる場合は、既にODBCのDSN設定にてクレデンシャル情報を登録済みのため登録は不要なのですが、「ユーザー名」が必須項目となっているため適当な文字列を登録して「接続」ボタンをクリックします。

image.png

「ナビゲータ」画面が表示されます。左側の階層を開いていくと、テーブルのリストが表示されます。今回は、勤怠申請一覧の詳細である「AttendanceWorkflowsDetails」と交通費申請一覧取得の詳細である「ExpenseWorkflowsDetails」テーブルを選択してみます。選択すると右側にデータのプレビューが表示されますので「読み込み」ボタンをクリックします。

image.png

レポートのキャンパスの画面が表示されます。右側のフィールドにテーブル内の各項目がリストとして表示されることを確認します。

image.png

「レポート」ページに戻り、「テーブル」を2つと「円グラフ」を配置して、勤怠申請一覧の詳細である「AttendanceWorkflowsDetails」と交通費申請一覧取得の詳細である「ExpenseWorkflowsDetails」テーブルの各種情報をフィールドに配置します。勤怠申請の一覧と交通費申請の一覧のビジュアライズが出来ました。

image.png

テストデータは、1ユーザのデータのみの表示となりますが、部門に所属する各ユーザの勤怠情報や交通費を管理者がモニタリングするような画面が作成できると思います。

必要に応じて左上の保存ボタンをクリックして作成したレポートを「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共に任意(指定しない場合は当月)