New Jira-related data points available for archived issue, Compass components, and story points

[#1]

Hi, community!

We're releasing these new data points and enhancements starting today:

• "Is archived" column in the "Jira issue" table - for archived issue

• New "Jira component" table - for Compass components

• Enhancement to the existing "Story points" column in the "Jira issue" table

🎥 Video example

Here’s a demo with an example of how to use these new data points to see your organization’s components coverage in terms of issue count and story points:

For archived Jira issues

When you archive a Jira issue, you can now report on this data in Atlassian Analytics.

The new "Is archived" column in the "Jira Issue" table is a boolean column indicating whether or not an issue is currently archived.

If you want to report on who archived an issue and when they did it, the "Issue field" table now has a new field name (jira_issue_field.field_name) called Soft archived. The value of this field includes the account ID of the person who archived the issue, as well as the timestamp the issue was last archived. Both the boolean column and new "field value" represent the current archiving status of an issue.

Here is an example how to parse out account ID and timestamp from “Soft archived” field.

WITH base AS( SELECT GET_JSON_OBJECT(GET_JSON_OBJECT(GET_JSON_OBJECT(GET_JSON_OBJECT(value, '$.soft_archived_jira_issue_field_soft_archived_value_raw'), '$.soft_archived_value_raw'), '$.archived_by'), '$.value') AS archived_by, 
GET_JSON_OBJECT(GET_JSON_OBJECT(GET_JSON_OBJECT(GET_JSON_OBJECT(value, '$.soft_archived_jira_issue_field_soft_archived_value_raw'), '$.soft_archived_value_raw'), '$.archived_date'), '$.seconds') AS archived_seconds, 
GET_JSON_OBJECT(GET_JSON_OBJECT(GET_JSON_OBJECT(GET_JSON_OBJECT(value, '$.soft_archived_jira_issue_field_soft_archived_value_raw'), '$.soft_archived_value_raw'), '$.archived_date'), '$.nanos') AS archived_nanos 
FROM jira_issue_field WHERE field_type = 'softArchived' AND value IS NOT NULL ) 
SELECT archived_by,CAST(archived_seconds + (archived_nanos /1e9) AS TIMESTAMP) AS archived_at FROM base

To see issue archived history, the “Issue history” table will have a boolean value anytime the archive status changes. Please note that there will not be account ID.

For Compass components

If you use Compass components and need to report on it, the new "Component" table under the "Jira" category stores the Compass components' attributes, such as ID, name, type and description.

You can use the existing "Issue component mapping" table to associate these Compass components with Jira issues. We’ve added a new "Global component ID" column in this mapping table that can be used to join with "Component ID" in the new "Component" table. If you want to determine if a component is scoped globally or to a specific project, you’ll need to use a join to see if the "Component" or "Project component" table has a matching ID.

Enhancement to the existing "Story points" column

A while back, we introduced the "Story points" column into the "Issue" table under the "Jira family of products" category for the "Story point estimate" field. Now this column also takes into account the "Story Points" field. This column is derived by coalescing these two fields, where "Story point estimate" takes priority if both fields are used on the same issue.

While the "Story point estimate" field is a locked field for Jira team-managed project, the "Story Points" number field is a custom field that can be configured. The solution provided in this column is a standard solution that covers only the "Story point estimate" locked field and the un-configured "Story Points" number field. If you've changed the name of the "Story Points" field or are using other story points-related fields, you can continue to use the "Issue field" table.

We’ll roll out this change over a week starting today. Let us know if you notice any problems.

How to get the new tables and columns

For new Data Lake connections that include Jira data, these new tables and columns will automatically be available.

For existing Data Lake connections,

• The enhancement to the "Story points" column, and the "Soft archived" field in the "Issue field" table for archived issues, will show up automatically if you use the "Story Points" field and the issue archiving feature.

• The new "Component" table, the "Is archived" new column in the "Issue" table, and the "Global component ID" column in the "Issue component mapping" table will require editing Data Lake connections. Organization admins can edit the connection and save it without making any changes. More about editing Data Lake connections.

For existing data shares, no action is required from you.

Your existing data shares were automatically updated with:

• The new is_archived column in jira_issue_enhanced_table

  • You can also find the "Is archived" field under jira_issue_field.field_type_key = 'softArchived'

• The story point enhancement for the story_points column in jira_issue_enhanced_table

The new "Jira component" table for Compass components is currently not available on data share.

Tell us what you think

Comment below or contact support if you have any questions or concerns. Thanks!