Jira — Sources — Binom Governance

Copy formulas into the KPI Definition Card, then adapt grain and filters.

Example

Issues created

How many issues were created in the period?

COUNT(*) FROM issue WHERE created IN period

Grain: Issue
Fields: Issue.created, Issue.project_key, Issue.issuetype
Dimensions: project, issuetype, priority, status_category

created from REST fields.created — JQL created >= … for increments.

Adapt: Optionally exclude subtasks and epics from intake KPIs.

Example

Issues resolved

How many issues were resolved in the period?

COUNT(*) FROM issue WHERE resolutiondate IN period

Grain: Issue
Fields: Issue.resolutiondate, Issue.project_key, Issue.issuetype
Dimensions: project, issuetype, sprint, priority

Use resolutiondate — not only status Done without a date.

Adapt: Treat reopens and Duplicate resolution separately.

Cycle time (days)

How long from created to resolved?

AVG(DATEDIFF(day, created, resolutiondate)) FROM issue WHERE resolutiondate IS NOT NULL

Grain: Resolved issue
Fields: Issue.created, Issue.resolutiondate
Dimensions: project, issuetype, priority, sprint

Optionally use status changelog (In Progress) for lead vs cycle time.

Adapt: Lock median vs average and outlier caps.

Open bugs

How many bugs are currently still open?

COUNT(*) FROM issue WHERE issuetype = 'Bug' AND resolutiondate IS NULL

Grain: Open issue
Fields: Issue.issuetype, Issue.resolutiondate, Issue.status_category
Dimensions: project, priority, status_category

Map issuetype names locally (Bug vs Defect).

Adapt: Clarify snapshot date and backlog filter (Won't Do).

Story points completed

How many story points were completed in the sprint?

SUM(story_points) FROM issue WHERE sprint_id = :sprint AND status_category = Done

Grain: Issue in sprint
Fields: Issue.story_points, Issue.sprint_id, Issue.status_category
Dimensions: sprint, project, issuetype

Map story points custom field id per site (REST field id).

Adapt: Lock committed vs completed and scope-change rules.

Formula overview

Same formulas as a table — copy into KPI cards or SQL drafts.

Label	Formula	Grain	Fields
Issues created	`COUNT(*) FROM issue WHERE created IN period`	Issue	Issue.created, Issue.project_key, Issue.issuetype
Issues resolved	`COUNT(*) FROM issue WHERE resolutiondate IN period`	Issue	Issue.resolutiondate, Issue.project_key, Issue.issuetype
Cycle time (days)	`AVG(DATEDIFF(day, created, resolutiondate)) FROM issue WHERE resolutiondate IS NOT NULL`	Resolved issue	Issue.created, Issue.resolutiondate
Open bugs	`COUNT(*) FROM issue WHERE issuetype = 'Bug' AND resolutiondate IS NULL`	Open issue	Issue.issuetype, Issue.resolutiondate, Issue.status_category
Story points completed	`SUM(story_points) FROM issue WHERE sprint_id = :sprint AND status_category = Done`	Issue in sprint	Issue.story_points, Issue.sprint_id, Issue.status_category

Id	Label	Grain	Notes
`project`	Project	project_key	Join via fields.project.key; filter archived projects.
`issuetype`	Issue type	issuetype.name / id	Separate bug vs story for open bugs and throughput.
`status_category`	Status category	To do / in progress / done	More stable than status names — survives workflow variants.
`sprint`	Sprint	sprint_id	Clarify active vs closed sprint for story points completed.
`priority`	Priority	priority.id / name	Normalize across schemes (Highest/High/…).

PII hotspots

Inventory direct and quasi identifiers for this source — tag, restrict RAW, hash or drop before marts.

Entity	Fields	Classification	Stage treatment	Treatment
User / Atlassian account	`accountId, displayName, emailAddress, avatarUrls`	Workforce	RAW: workforce policy; Curated: separate from customer PII; Mart: aggregates only	Jira users — emailAddress only with legal basis; keep accountId as key.
Issue reporter / assignee	`reporter, assignee, creator, watches`	Workforce	RAW: workforce policy; Curated: accountId only; Mart: aggregates only	Reporter/assignee as accountId — no cleartext names in default marts.
Issue fields (custom)	`customfield_* email/phone, Customer Request Type contact fields`	Direct	RAW: restrict access; Curated: hash/tokenize; Mart: no cleartext	Inventory custom fields and JSM contacts — often a second PII surface.
Issue description / comments	`description, comment.body, worklog.comment`	Quasi	RAW: rarely load; Curated: drop/redact; Mart: never	Free text can identify persons — default skip for analytics.
Attachments / media	`attachment.filename, attachment.content, thumbnail`	Quasi	RAW: rarely load; Curated: drop/redact; Mart: never	Attachments and filenames — expensive and potentially PII.

DSDR discovery

Where personal data can hide beyond obvious contact fields — warehouse copies included.

Focus	Notes
Match keys	accountId, emailAddress, issue key, project key, custom external id fields.
Primary objects	Issue, user, project, comment, worklog + custom fields.
REST / JQL exports	JQL exports and /rest/api/3/* duplicate user and issue PII into stages.
Sandbox / site copies	Do not mix sandbox issues with prod delivery marts.

Source-specific guidance: what goes wrong, how to prevent it, what staff must know — plus where metadata lives and how to access it.

Data quality guides 6

Prevent issues in the source first. Warehouse checks are a safety net — not a substitute for staff habits.

High Open issue without assignee

Problem: Open issues without assignee cannot slice workload and cycle time by owner.
Prevent: Workflow: assignee required before In Progress; board policy.
Staff must know: Delivery: Do not start without assignee — clarify ownership.
Checks: assignee IS NULL AND statusCategory != Done
Fix in source: Workflow validator; required assignee on transition.
Warehouse safety net: Unknown-assignee dim; exclude from owner KPIs.

High Issue without project

Problem: Issues without projectKey break portfolio and team reports.
Prevent: Create screen: project always set; validate API create.
Staff must know: Jira: Every issue belongs to a project.
Checks: projectKey IS NULL OR projectId IS NULL
Fix in source: Required project on create; block API without project.
Warehouse safety net: Quarantine rows; exclude from portfolio KPIs.

High Resolution before created

Problem: resolved < created makes lead/cycle time unusable.
Prevent: No manual date overrides without process; document migrations.
Staff must know: Ops: Never set resolution date before created.
Checks: resolutiondate < created
Fix in source: Automation / validator on resolution fields.
Warehouse safety net: Flag invalid dates; null cycle metrics.

Medium Done without resolution

Problem: Status Done without resolution distorts throughput and outcome slices.
Prevent: Workflow: resolution required on Done transition.
Staff must know: Delivery: Done always with resolution (Done/Won't Do/…).
Checks: statusCategory = Done AND resolution IS NULL
Fix in source: Post-function / validator sets resolution.
Warehouse safety net: Map to Unknown resolution; separate KPI.

Medium Sprint issue without story points

Problem: Active sprint items without story points break velocity and commitment.
Prevent: Definition of Ready: story points before sprint start.
Staff must know: Scrum: No commitment items without estimate.
Checks: storyPoints IS NULL AND sprint IS NOT NULL AND statusCategory != Done
Fix in source: Board filter / automation alert; required field on issue type.
Warehouse safety net: Exclude from velocity or flag unestimated.

Medium Duplicate external id custom field

Problem: Multiple issues with same external id distort cross-system joins and DSDR.
Prevent: Unique external id policy; import dedup.
Staff must know: Integrations: Keep external id unique.
Checks: COUNT(DISTINCT issue_key) GROUP BY external_id HAVING COUNT > 1
Fix in source: Automation uniqueness check; merge workflow.
Warehouse safety net: Golden issue key; merge map table.

MDM / duplicates 2

User / accountId User duplicates / site transfers

Match keys: accountId, emailAddress, displayName + site
Prevent in source: Atlassian account as source of truth; no local duplicate accounts.
Resolve in warehouse: Golden accountId; merge map for legacy user keys.
Survivorship: Active accountId with latest activity wins.

Issue Issue vs external ticket id

Match keys: issue key, custom external id, project + summary hash
Prevent in source: Unique custom field; import governance.
Resolve in warehouse: Canonical issue_key via external id map.
Survivorship: Prod-project issue wins over import stub.

Product settings to improve 3

Project settings → Workflows Assignee required before In Progress

Why: Closes owner gaps for workload KPIs.
How: Workflow validator on transition; test in sandbox.

Issues → Custom fields / Field configuration Story points / external id for relevant issue types

Why: Stabilizes velocity and cross-system joins.
How: Field configuration scheme; required where needed.

REST API → /rest/api/3/field Document field allowlist for extract

Why: Prevents PII sprawl and schema drift.
How: Export field catalog; version IDs in Git.

Metadata: what exists & how to get it 4

Source-native catalogs for schema, picklists/properties and access paths — not a full dump.

Issue fields admin

Where in product: Jira Settings → Issues → Custom fields / Field configurations
How to access: UI browse; document field IDs and screens.
Use for: Field inventory, PII tags, load allowlist, DQ checks.
Watchouts: Custom field IDs (customfield_*) differ per site.

Workflows

Where in product: Project settings → Workflows; Jira Settings → Workflows
How to access: Export statuses/transitions and validators.
Use for: Status maps, cycle-time definition, required-field rules.
Watchouts: Multiple schemes per project — do not merge blindly.

JQL

Where in product: Filters / Advanced search; saved filters
How to access: Document JQL for extract scopes; do not misuse as mart SQL.
Use for: Extract filters, scope definition, sanity checks.
Watchouts: Saved filters change without versioning.

REST API /rest/api/3/field

Where in product: GET /rest/api/3/field (+ createmeta where needed)
How to access: Store field catalog JSON; tag custom vs system.
Use for: Extract design, schema allowlist, PII scan.
Watchouts: Permission-scoped fields — extract user may see more/less.

Object overview with short descriptions and load guidance — not a full schema dump.

Table	Description	Grain	Load
`issue` Issue	Jira issue — fact core for created, resolved, cycle time and open bugs.	One issue	Load
`project` Project	Jira project — dimension via project_key / id.	One project	Load
`sprint` Sprint	Agile sprint — timebox and story points completed.	One sprint	Load
`board` Board	Board (Scrum/Kanban) — optional context for sprint and flow KPIs.	One board	Optional
`user` User	Atlassian user — assignee/reporter join; email and displayName are PII.	One user (accountId)	Load
`comment` Comment	Issue comment — free text often PII; check volume and retention.	One comment	Optional
`worklog` Worklog	Time logged on issue — timeSpentSeconds for effort measures.	One worklog entry	Optional
`changelog` Changelog	Field history — status transitions for cycle time; full bulk history often skip.	One changelog item	Optional

Most important fields to load early — not a full schema dump.

Entity	Field	Role	Why
Issue	`Issue.id`	Key	REST join key
Issue	`Issue.key`	Key	Business key (PROJ-123)
Issue	`Issue.project_key`	Dimension	Project join
Issue	`Issue.status`	Dimension	Workflow status
Issue	`Issue.status_category`	Dimension	To do / in progress / done
Issue	`Issue.issuetype`	Dimension	Bug / story / task
Issue	`Issue.priority`	Dimension	Priority
Issue	`Issue.assignee`	Dimension	Assignee accountId
Issue	`Issue.reporter`	Dimension	Reporter accountId
Issue	`Issue.created`	Measure	Created time / period grain
Issue	`Issue.resolutiondate`	Measure	Resolved time / cycle time
Issue	`Issue.story_points`	Measure	Story points (custom field)
Issue	`Issue.sprint_id`	Dimension	Sprint join
Issue	`Issue.updated`	Measure	Last update
Project	`Project.id`	Key	Project join
Project	`Project.key`	Dimension	Project key / label
Project	`Project.name`	Dimension	Project name
Sprint	`Sprint.id`	Key	Sprint join
Sprint	`Sprint.name`	Dimension	Sprint label
Sprint	`Sprint.startDate`	Measure	Sprint start
Sprint	`Sprint.endDate`	Measure	Sprint end
Sprint	`Sprint.state`	Dimension	active / closed / future
Board	`Board.id`	Key	Board join
Board	`Board.type`	Dimension	scrum / kanban
User	`User.accountId`	Key	User join (Cloud)
User	`User.emailAddress`	PII	Email / PII
User	`User.displayName`	PII	Display name / quasi-PII
Comment	`Comment.id`	Key	Comment join
Comment	`Comment.body`	PII	Free text / PII risk
Worklog	`Worklog.id`	Key	Worklog join
Worklog	`Worklog.timeSpentSeconds`	Measure	Time spent
Changelog	`Changelog.created`	Measure	Transition timestamp
Changelog	`Changelog.field`	Dimension	Changed field (status…)

System and noise objects/fields you typically should not sync into the warehouse by default.

Skip tables / objects

Table	Category	Why
`Issue description / ADF body (bulk)`	System	Free-text PII and volume — only with a use case.
`Comment / worklog text bodies`	System	Unstructured PII — metadata (author, created) often enough.
`Attachment binaries`	System	Binaries — expensive, rarely KPI-relevant.
`Audit log / webhook delivery history (bulk)`	System	Ops telemetry — not delivery mart core.

Skip fields / content

Issue description / comment cleartext (bulk) — PII — selectively or not at all.
Unused custom fields (bulk sync all) — Expands DSDR surface and sync cost.
Attachment content — Cost without KPI value.
User email cleartext in marts — accountId is enough for assignee KPIs.
Sandbox / test project issues — Keep prod delivery marts clean.

Warehouse-neutral examples for landing (RAW) and curated models — copy and adapt to your dialect. Not vendor SOQL/API DDL.

Landing (RAW)

RAW issue landing

REST/JQL shape — assignee/reporter as accountId; skip description body by default.

-- Warehouse-neutral RAW from Jira REST extract (not JQL)
CREATE TABLE raw_jira_issue (
  issue_id         VARCHAR,
  issue_key        VARCHAR,
  project_key      VARCHAR,
  issue_type       VARCHAR,
  status           VARCHAR,
  status_category  VARCHAR,
  priority         VARCHAR,
  assignee_id      VARCHAR, -- workforce key
  reporter_id      VARCHAR, -- workforce key
  created_at       TIMESTAMP,
  updated_at       TIMESTAMP,
  resolved_at      TIMESTAMP,
  resolution       VARCHAR,
  story_points     DECIMAL(10,2),
  sprint_id        VARCHAR,
  _loaded_at       TIMESTAMP
);

RAW project + user landing

emailAddress = workforce PII — keep RAW restricted; no cleartext in Curated.

CREATE TABLE raw_jira_project (
  project_id       VARCHAR,
  project_key      VARCHAR,
  project_name     VARCHAR,
  project_type     VARCHAR,
  lead_account_id  VARCHAR,
  _loaded_at       TIMESTAMP
);

CREATE TABLE raw_jira_user (
  account_id       VARCHAR,
  display_name     VARCHAR,
  email_address    VARCHAR, -- workforce PII
  active           BOOLEAN,
  _loaded_at       TIMESTAMP
);

Curated

Curated fact jira issue

Delivery grain — no description/comment bodies; keys + status + dates.

CREATE TABLE curated_fct_jira_issue AS
SELECT
  i.issue_id,
  i.issue_key,
  i.project_key,
  i.issue_type,
  i.status,
  i.status_category,
  i.priority,
  i.assignee_id,
  i.reporter_id,
  i.created_at,
  i.resolved_at,
  i.resolution,
  i.story_points,
  i.sprint_id,
  CASE WHEN i.status_category = 'Done' THEN TRUE ELSE FALSE END AS is_done
FROM raw_jira_issue i
WHERE i.project_key IS NOT NULL;

Curated dim project / user

User without email in default marts; project for portfolio slices.

CREATE TABLE curated_dim_jira_project AS
SELECT
  project_id,
  project_key,
  project_name,
  project_type,
  lead_account_id
FROM raw_jira_project;

CREATE TABLE curated_dim_jira_user AS
SELECT
  account_id,
  display_name,
  active
  -- omit email_address from default analytics dims
FROM raw_jira_user;

Curated measure SELECTs

Created, resolved throughput, open backlog — adapt period filters.

-- Issues created in period
SELECT COUNT(*) AS issues_created
FROM curated_fct_jira_issue
WHERE created_at BETWEEN :period_start AND :period_end;

-- Issues resolved in period
SELECT COUNT(*) AS issues_resolved
FROM curated_fct_jira_issue
WHERE is_done = TRUE
  AND resolved_at BETWEEN :period_start AND :period_end;

-- Open backlog
SELECT COUNT(*) AS open_backlog
FROM curated_fct_jira_issue
WHERE is_done = FALSE;

Tools

Open related Binom-Tools workflows to adapt formulas, PII and measures for this source.

Playbooks

Related governance stories for modelling, PII and measure design around this source.

Vendor help on Resources Official docs, governance and learning paths for this source. Open Resources →