Exporting issue details to an SQL data warehouse

I am exploring JIRA Core as a solution for workflow management.  Our primary driver is to capture workflow metrics e.g. how many instances of a particular business process are executed, how long does the process take, how long do steps within the process take etc.  We have other solutions which provide these measures and which are stored in a SQL data warehouse for the purpose of blending with other data and producing analytics using Tableau.

We would want to extract process measures from JIRA into this data warehouse and blend it with other data in the data warehouse in order to provide consistent, complete business analytics to our customers.


Is it possible to extract, ideally in an automated way, process information from JIRA?

Thanks in advance for any insight you can provide.

3 answers

1 accepted

0 votes
Accepted answer

If your data warehouse supports importing Excel, then the Excel Automation Plugin can export issue data to XLSX spreadsheets periodically. This add-on delegates the spreadsheet rendering to the Better Excel Plugin, which is super flexible in terms of customization and scripting

Even if XLSX is not natively supported by the other end, it can be easily converted to CSV or an SQL INSERT statement.

Make sure you check this out

Yes, you can script extracting data from the JIRA database using SQL and then populate your datamart accordingly. We use to do this to support various integrations with other applications and reporting. You need to find the appropriate tables in JIRA and plan to handle upgrades. Most of the tables are pretty stable. 

For Cloud it is a different solution (as Nic said, no database access for Cloud) which I am currently using for our JIRA site. For this, I just collect issue and version information so I can do reporting with SQL. A Bamboo plan runs on an interval to synchronize JIRA data with the datamart. The data is extracted from JIRA using getIssueList and getVersionList from JIRA Command Line Interface (CLI). A time interval based JQL (like updated > -1d) drives the getIssueList to minimize updates. I happen to use a groovy script to process the csv output so I have a bit more control, but, you could also use database csv import tools.

Thanks for that Bob.  That is indeed good news.  Do you know if this applies both to JIRA Server (I presume that that was the context within which you were answering) and JIRA Cloud?

We are Confluence users and also have an on premise instance of JIRA.

You have no access to the database on Cloud.  You would have to write something that extracts the data over the REST interface.

Thanks Nick.  I suspected that that might be the case.

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted Oct 09, 2018 in Jira Core

How to manage many similar workflows?

I have multiple projects that use variations of the same base workflow. The variations depend on the requirements of the project or issue type. The variations mostly come in the form of new statuses ...

222 views 6 0
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you