Exporting issue details to an SQL data warehouse

[Michael Lock]

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.

[Midori · Accepted solution]

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

[Michael Lock]

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.

[Nic Brough -Adaptavist-]

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

[Michael Lock]

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

[Bob Swift OSS (Bob Swift Atlassian Apps)]

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.