Redmine の DB を Metabase で解析して必要な工数を予測する

13342 ワード
Metabase Redmine SQL 統計学 Redmine テキストリンク

Redmine の準備

Redmine には Agile プラグインをインストールしてチケットにストーリーポイントを付与できるようにしておきます。ストーリーポイントは標準フィールドの予定工数でも代替可能なので必須ではありません。

Redmine で Agile プラグイン( Light 版)を使う - Qiita

Redmine の DB に読み取り専用のユーザーを作っておくと良いです。

Metabase の準備

Metabase と Redmine を連結しておきます。
Metabase から Redmine へは読み取り専用のユーザーでアクセスすると良いです。

OpenProject に Metabase をドッキングしたら最強のダッシュボードが作れるかも - Qiita
DIYな業務システムのビューに Metabase はいかが - Qiita

クエリを作成する

下記の記事で述べた考えを用いて、終了したチケットの見積もり(=ストーリーポイント)と実績(=入力した作業時間)の相関を解析し、未完了チケットの見積もりから必要な工数を予測します。

99%の確率で納期を守るには - Qiita

Metabase のネイティブクエリで下記を入力します。
ビジュアライゼーションの設定をすると結果をグラフ化できますね。

-- 二次集計
SELECT
    `closed_on_date`,
    `消化ポイント`,
    `残ポイント`,
    `消費時間`,
    `平均` * `残ポイント` AS `期待値`,
    SQRT( `分散` * `残ポイント` ) AS `標準偏差`
FROM
    (
        -- 一次集計
        SELECT
            `issues3`.`closed_on_date`,
            SUM(CASE WHEN `issues3`.`was_closed` THEN `issues3`.`story_points` ELSE 0 END) AS `消化ポイント`,
            SUM(CASE WHEN `issues3`.`was_closed` THEN 0 ELSE `issues3`.`story_points` END) AS `残ポイント`,
            SUM(CASE WHEN `issues3`.`was_closed` THEN `issues3`.`hours_sum` ELSE 0 END) AS `消費時間`,
            AVG(CASE WHEN `issues3`.`was_closed` THEN `issues3`.`hours_par_point` ELSE NULL END) AS `平均`,
            VARIANCE(CASE WHEN `issues3`.`was_closed` THEN `issues3`.`hours_par_point` ELSE NULL END)  AS `分散`
        FROM
            (
                -- 動きがあった日付において終了していたか判定する
                SELECT
                    `issues1`.`closed_on_date`,
                    `issues2`.`is_closed` AND `issues1`.`closed_on_date` >= `issues2`.`closed_on_date` AS `was_closed`,
                    `issues2`.`story_points`,
                    `issues2`.`hours_sum`,
                    `issues2`.`hours_sum` / `issues2`.`story_points` AS `hours_par_point`
                FROM
                    (
                        -- 動きがあった日付のリストを生成する
                        SELECT
                            DATE(`issues`.`closed_on`) AS `closed_on_date`
                        FROM
                            `issues`
                        GROUP BY
                            `closed_on_date`
                    ) `issues1`
                    INNER JOIN
                        (
                            -- 下記のデータを動きがあった日付ごとに集計する
                            SELECT
                                `projects`.`name`,
                                -- 終了したチケットか
                                `issue_statuses`.`is_closed`,
                                -- 工数合計
                                `time_entries_sum`.`hours_sum`,
                                -- ストーリーポイント(予定工数で代替可能)
                                `agile_data`.`story_points`,
                                -- 終了日
                                DATE(`issues`.`closed_on`) AS `closed_on_date`
                            FROM
                                `issues`
                                LEFT JOIN
                                    (
                                        -- 工数入力をチケット別に集計する
                                        SELECT
                                            `issue_id`,
                                            SUM(`hours`) AS `hours_sum`
                                        FROM
                                            `time_entries`
                                        GROUP BY
                                            `issue_id`
                                    ) `time_entries_sum`
                                ON  `issues`.`id` = `time_entries_sum`.`issue_id`
                                LEFT JOIN
                                    `agile_data`
                                ON  `issues`.`id` = `agile_data`.`issue_id`
                                LEFT JOIN
                                    `issue_statuses`
                                ON  `issues`.`status_id` = `issue_statuses`.`id`
                                LEFT JOIN
                                    `projects`
                                ON  `issues`.`project_id` = `projects`.`id`
                                LEFT JOIN
                                    `versions`
                                ON  `issues`.`fixed_version_id` = `versions`.`id`
                            -- フィールドフィルター
                            WHERE {{project_name}}
                                [[AND {{version_name}}]]
                        ) `issues2`
            ) `issues3`
        GROUP BY
            `issues3`.`closed_on_date`
    ) `issues4`

フィールドフィルターは Metabase 固有の記述です。ネイティブクエリとして入力するだけでなく変数の値タイプなどを設定する必要があります。

クエリをダッシュボードに追加する

作成したクエリをダッシュボードに追加する際にフィルターを追加してフィールドフィルターと紐づけることで、任意のプロジェクト、任意のバージョンについて予測を表示することができます。

プロジェクト名のフィールドフィルターは必須項目なので、紐づける前はクエリをダッシュボードに追加してもエラー表示となります。