Re:dashでRedmineのチケットを可視化する

日々優先タスクをこなすことに追われ、分析や改善が後回しになってしまうことはありがちです。
RedmineではチケットをCSVでダウンロードできるのでExcelワークで分析することはできますが、
Excelでの分析は時間がかかるためKPIとして常に監視するのは難しいです。
チームやプロジェクトの状態を把握し改善箇所を見つけるためにも定量的なデータの分析とKPIの監視が大切です。
最近、Re:dashを使ってRedmineのチケット分析を行ったのでその事例を紹介したいと思います。

これはre:dash Advent Calendar 2016の8日目の記事です。
本記事ではRedmineとRe:dashの連携方法を説明し、
Redmineのチケット分析に必要になるテーブル構成やSQLクエリのサンプルを紹介したいと思います。

こんな感じのダッシュボードが簡単に作れました。

RedmineとRe:dashの連携方法

Re:dashとRedmineの連携にはRe:dashからRDBに直接接続します。

Redmineのアーキテクチャ

RedmineはRuby on Railsで構築されており、データの永続化にはRDBを使います。
MySQL,PostgreSQL,SQL Server, SQLiteなどがサポートされているので、
連携しようとしているRedmineでどのDBを使っているか確認してください。

Redmineの設定

一切不要です。

MySQLの設定

連携したいRedmineが接続しているRDBの接続情報を用意しましょう。
外部からの接続を想定していない場合、RDBやfirewallの設定変更が必要になることがあります。
新たに接続用のユーザーを作成する場合、SELECT権限だけあれば大丈夫です。

Re:dashの構築と設定

Re:dashの構築

Re:dashの構築はこちらの記事(「Re:dash」を「Docker for Mac」で動かしてみた)を参考にさせてもらいました。

接続情報の追加

まず歯車のアイコンから接続情報を追加します。

DATA SOURCE タブを選択し、New Data Sourceをクリックします。

Typeで使用しているRDBを選択すると、必要な接続情報の入力フィールドが出現するので、必要項目を入力して保存してください。

クエリとグラフの作成

クエリの作成

左上のメニューからNews Queryを選択します。

エディタが表示されるので分析したいデータを取得するSQLを書きましょう。
すぐに使えるSQLのサンプルは後の章で紹介します。

画面中央のData Sourceに先ほど作成したDB接続情報が表示されるので選択します。
左側にDBのテーブルリストが表示されます。テーブルをクリックするとフィールドを見れます。
SQLを書いたらExecuteボタンでクエリを実行(Command+EnterもOK)
Saveボタンでクエリを保存します。

データが取得できたらNEW VISUALIZATIONをクリックするとグラフのエディタが開きます。
あとは画面を見ながら適当に設定すると綺麗なグラフが出来上がります。

Redmineのチケット分析

ここからは分析に使うクエリの作り方を紹介します。

時系列の生成

DBにはヒストリカルにデータが蓄積されているので、ある時点の合計をまとめて取得するには起点となる日付のシーケンスが必要になります。

1~100くらいまでの連番が入ったテーブルがあると楽です。

(MySQLの例)連番が入ったSEQ_TEBALEから作成

select DATE_ADD(NOW(), INTERVAL - num MONTH) as date 
from SEQ_TABLE limit 10;

SQLだけでも作れます。

(MySQLの例)クエリだけで生成する方法

select DATE(date) date from (
select DATE_ADD(NOW(), INTERVAL -1 MONTH)date 
union select DATE_ADD(NOW(), INTERVAL -2 MONTH) date
union select DATE_ADD(NOW(), INTERVAL -3 MONTH) date
-- 中略
union select DATE_ADD(NOW(), INTERVAL -23 MONTH) date
union select DATE_ADD(NOW(), INTERVAL -24 MONTH) date
) A

MONTHをWEEKやDAYに変えると、それぞれ月次、週次、日次のシーケンスが作れます。
(*変数を使う方法はうまく行きませんでした。複数の文があると先頭の文だけ実行した結果を表示しようとしてしまうようです。)

起票/累積チケット

作成した日付を元に起票/累積チケットを集計して見ましょう。
チケットはissueテーブルにあり、作成日はcreated_on、終了日はclosed_onに入っています。

[MySQL]起票数の集計

select D.date, count(A.id) cnt from (
-- 時系列クエリ
select DATE_ADD(NOW(), INTERVAL -1 MONTH)date 
union select DATE_ADD(NOW(), INTERVAL -2 MONTH) date
) D
cross join issues A
where A.created_on between D.date and DATE_ADD(D.date, INTERVAL 1 MONTH)
group by date

起票数は作成日の期間で集計すればいいので簡単です。

[MySQL]累積チケット数の集計

select D.date, count(A.id) cnt from (
-- 時系列クエリ
select DATE_ADD(NOW(), INTERVAL -1 MONTH)date 
union select DATE_ADD(NOW(), INTERVAL -2 MONTH) date
) D
cross join issues A
where A.created_on < D.date 
  and (closed_on is null or closed_on > D.date)
group by date

累積チケット数は起点となる日付より以前に作成されたチケットのうち、
終了していないものと、未来の日付で終了しているものを集計します。

プロジェクト単位の集計

プロジェクトはprojectテーブルに入っています。
他のテーブルにあるプロジェクトのIDはproject_idで統一されているようです。

[MySQL]プロジェクト単位の累積チケット数の集計

select P.name type, D.date, count(A.id) cnt from (
-- 時系列クエリ
select DATE_ADD(NOW(), INTERVAL -1 MONTH)date 
union select DATE_ADD(NOW(), INTERVAL -2 MONTH) date
) D
cross join issues A
left join projects P on (A.project_id = P.id) 
where A.created_on < D.date 
  and (closed_on is null or closed_on > D.date)
group by date

優先度(列挙項目)別の集計

チケットの優先度は列挙項目です。
列挙項目はenumerationsテーブルにあり、type = IssuePriorityのレコードが優先度になっています。

[MySQL]優先度単位の累積チケット数の集計

select P.name type, D.date, count(A.id) cnt from (
-- 時系列クエリ
select DATE_ADD(NOW(), INTERVAL -1 MONTH)date 
union select DATE_ADD(NOW(), INTERVAL -2 MONTH) date
) D
cross join issues A
left join (select id,name from enumerations where type = 'IssuePriority') P on (A.project_id = P.id) 
where A.created_on < D.date 
  and (closed_on is null or closed_on > D.date)
group by date

色々な項目の表示

カスタム属性やチケットに関わったユーザなども表示することができます。
また、urlリンクなども表示することができます。
SQLを直接使うことでRedmineのチケット一覧ではできないような絞り込みやソートが可能になります。

[MySQL]カスタム属性、関係者、URLリンクなどを組み合わせて表示

SELECT concat('<a href="http://redmine.xxxxxx.lan/redmine/issues/',cast(issue_id AS CHAR),'" target="_blank">',cast(issue_id AS CHAR) ,'</a>') issue_id,
       subject,
       ifnull(group_concat(CASE custom_field_id WHEN 74 THEN value ELSE NULL END, ''), '') AS '発生箇所',
       ifnull(group_concat(CASE custom_field_id WHEN 76 THEN value ELSE NULL END, ''), '') AS '原因',
       ifnull(group_concat(CASE custom_field_id WHEN 78 THEN value ELSE NULL END, ''), '') AS '発生プロジェクト',
       member AS '関係者',
       DATE(created_on) AS '起票日',
       DATE(updated_on) AS '更新日',
       DATE(closed_on) AS '終了日'
FROM
  (SELECT id issue_id,
          project_id,
          tracker_id,
          subject,
          status_id,
          priority_id,
          created_on,
          updated_on,
          closed_on
   FROM issues) I
LEFT JOIN
  (SELECT id custome_value_id,
          customized_id issue_id,
          custom_field_id,
          value
   FROM custom_values
   WHERE custom_field_id IN (74, 76, 78)) CV 
   USING (issue_id)
LEFT JOIN
  (SELECT id custom_field_id, name FROM custom_fields) CF 
  USING (custom_field_id)
INNER JOIN
  (SELECT journalized_id issue_id,
          group_concat(DISTINCT LASTNAME SEPARATOR '<br/>') member
   FROM journals J
   LEFT JOIN journal_details D ON (J.id = D.journal_id)
   LEFT JOIN users U ON (J.user_id = U.id)
   GROUP BY journalized_id) M 
   USING (issue_id)
GROUP BY issue_id

おわり