Edited at
re:dashDay 8

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

More than 1 year has passed since last update.

日々優先タスクをこなすことに追われ、分析や改善が後回しになってしまうことはありがちです。

RedmineではチケットをCSVでダウンロードできるのでExcelワークで分析することはできますが、

Excelでの分析は時間がかかるためKPIとして常に監視するのは難しいです。

チームやプロジェクトの状態を把握し改善箇所を見つけるためにも定量的なデータの分析とKPIの監視が大切です。

最近、Re:dashを使ってRedmineのチケット分析を行ったのでその事例を紹介したいと思います。

これはre:dash Advent Calendar 2016の8日目の記事です。

本記事ではRedmineとRe:dashの連携方法を説明し、

Redmineのチケット分析に必要になるテーブル構成やSQLクエリのサンプルを紹介したいと思います。

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

dashboad.png


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」で動かしてみた)を参考にさせてもらいました。


接続情報の追加

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

スクリーンショット 2016-12-08 09.03.38.png

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

スクリーンショット 2016-12-08 09.05.02.png

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

スクリーンショット 2016-12-08 09.05.26.png


クエリとグラフの作成


クエリの作成

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

スクリーンショット 2016-12-08 09.16.49.png

エディタが表示されるので分析したいデータを取得するSQLを書きましょう。

すぐに使えるSQLのサンプルは後の章で紹介します。

スクリーンショット 2016-12-08 09.19.48.png

画面中央のData Sourceに先ほど作成したDB接続情報が表示されるので選択します。

左側にDBのテーブルリストが表示されます。テーブルをクリックするとフィールドを見れます。

SQLを書いたらExecuteボタンでクエリを実行(Command+EnterもOK)

Saveボタンでクエリを保存します。

データが取得できたらNEW VISUALIZATIONをクリックするとグラフのエディタが開きます。

あとは画面を見ながら適当に設定すると綺麗なグラフが出来上がります。

スクリーンショット 2016-12-08 09.28.00.png


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


おわり