Create percent complete by deliverable chart in Metabase

[widal001]

Summary

Create a chart in Metabase that shows the percentage of points complete by deliverable.

Acceptance criteria

• The chart is visible to anyone with access to Metabase
• The chart reads data from Postgres DB

[widal001]

Blocked by #2665

[widal001]

Here's the dashboard that is live on dev

Metabase dashboard

Sprinty McBurndown report

Notes

There are some limitations with the Metabase dashboard:

• We can't overlay percentages by default on top of the bars, the data tips are only visible on hover
• We have limited options for color choices

Also there appears to be a bug based on how we're tracking history:

• We capture a snapshot of the mapping between deliverables and epics and between epics and tickets
• If an epic gets moved from one epic to another we create a new mapping, but don't archive the old mapping (or indicate that it's expired, etc.)
• That means we either have to:
  • Create a mapping of every relationship every day and only use the previous day's mapping
  • Or we need to add a column to indicate when a given mapping "expired"

[widal001]

Creating a snapshot of the SQL used to create the underlying model for calculating percent complete by deliverable:

-- Isolate the mappings between epics and deliverables from the previous day 
WITH epic_map AS (
  SELECT DISTINCT
    epic_id,
    deliverable_id,
    d_effective
  FROM app.gh_epic_deliverable_map
  WHERE d_effective = CURRENT_DATE - 1
),
-- Isolate the mappings between quads and deliverables from the previous day
quad_map AS (
  SELECT DISTINCT
    quad_id,
    deliverable_id,
    d_effective
  FROM app.gh_deliverable_quad_map
  WHERE d_effective = CURRENT_DATE - 1
),
-- Isolate the issue metadata from the previous day
issue_history AS (
  SELECT DISTINCT
    issue_id,
    points,
    is_closed,
    d_effective
  FROM app.gh_issue_history
  WHERE d_effective = CURRENT_DATE - 1
),
-- Join the dimension and fact tables and select the fields for subsequent analysis
deliverable_tasks AS (
  SELECT
    deliverable.id AS deliverable_id,
    deliverable.title AS deliverable_title,
    epic.id AS epic_id,
    epic.title AS epic_title,
    epic_map.d_effective AS epic_effective_dt,
    quad.name AS quad_name,
    issue.title AS issue_title,
    -- The following fields are needed to calculate burndown
    issue.opened_date AS issue_open_date,
    issue.closed_date AS issue_closed_date,
    issue_history.points AS issue_points,
    -- The following fields help summarize number of points/issues closed by deliverable and day
    issue_history.is_closed AS issue_closed,
    CASE WHEN issue_history.is_closed = 1 THEN 0 ELSE 1 END AS issue_open,
    CASE WHEN issue_history.is_closed = 1 THEN issue_history.points ELSE 0 END AS points_closed,
    CASE WHEN issue_history.is_closed = 0 THEN issue_history.points ELSE 0 END AS points_open
  FROM app.gh_deliverable AS deliverable
  JOIN epic_map ON epic_map.deliverable_id = deliverable.id
  JOIN quad_map ON quad_map.deliverable_id = deliverable.id
  JOIN app.gh_quad AS quad ON quad.id = quad_map.quad_id
  JOIN app.gh_epic AS epic ON epic.id = epic_map.epic_id
  JOIN app.gh_issue AS issue ON issue.epic_id = epic_map.epic_id
  LEFT JOIN issue_history ON issue.id = issue_history.issue_id
)
SELECT *
FROM deliverable_tasks