SQL and using jira database for reporting

[Mary Mark]

Would like to know if there is a better / efficient way to query all jira issues and display the custom fields and values used. We have tried using sql however involves many joins, which makes query inefficient and slow. Looking for suggestions and alternatives.

Thanks

[Nic Brough -Adaptavist-]

SQL is the single worst possible way you can try to report on Jira.

In descending order of good ways to do it:

1. Use the built in reports, gadgets, dashboards, etc
2. Find or write specific reports to fill in gaps when option 1 fails you (it will, especially if you want historic reports)
3. If 1 + some 2s still are not enough, use the more generalised 3rd party reporting apps built by Atlassian experts.  EazyBI and Dataplane are the starting points for comparisons
4. Build something that interrogates Jira over its REST interface

[Mohamed Adel]

I add my voice to Nic, plus SQL option is the worst way, JIRA DB schema also is very complicated.

I would recommend to use one of the following :

- EasyBI 

- PowerBi, you can integrate with Rest API and get all the issue information for reporting.

[Mary Mark]

Hi Nic,

First, thank you for your response.  I have been actually following your blog/responses in the community for a while and I've seen answers regarding the topic about SQL not being efficient tool for reporting from the JIRA database.

I wanted to ask for your insight/opinion as well as other experts in this community regarding the following:

1. At our company we have many intake tools and we are trying to simplify by choosing JIRA as the primary intake tool. However currently intake from JIRA does not align well to our cost tracking system called Changepoint and does not provide key metrics reporting on support cost per application hence reporting for Total cost of ownership cannot be produced. Some new key (custom fields) to be created in JIRA have been identified, which supports this cost reporting to be added onto JIRA with a governance process to produce key metrics. We are looking to combine 3 systems: JIRA, Changepoint (resource cost tracking system) and Architecture library (an application repository) to allow cross referencing to occured to allow for reporting and analysis.

2. All 3 sources of data are hosted on 3 SQL databases with fields required from tables in each of the 3 databases. Initially was thinking of grabbing the tables from the 3 databases that contained the required fields and creating a new database.

What are thoughts around methods to produce/generate reporting? I hear that SQL would not be a very good option

Thanks in advance.

[Mohamed Adel]

Hi Mary, 

I would like to share with you my experience with a similar situation, I had also to generate report by collection the data from 5 different sources (SQL,EXCEL,API ).

After a lot of search & trial to have one integrated dashboard,I would definitely recommend Microsoft §PowerBI for you. 

You can add more data sources and play with the table relations, formatting the data and present it in a modern and simple way. 

Thanks

[Lauma Cīrule]

Hi all!

Thank you for suggesting eazyBI as a possible solution!

I just want to jump in saying that you can also import from other data sources in eazyBI additionally to Jira data or in separate cube / account. See more details in the documentation page or let me know if you have any further questions.

Kind regards,
Lauma / support@eazybi.com

[Markus Pöhler]

Hello,

I am looking for a solution to gather JIRA (on premise) live Data into Power BI without steps in between like Export a list and updload this list. I have seen your recommendations NOT to use the most intuitive and Logical solution to query the Jira Database using SQL. The data is there, it is recent, so why not use it?  Fair enough that it does not respect the permissions the the users within JIRA, that maybe a problem.

The fact that it may be hard to understand the data models and data relations is right, but so far I have not seen any other solution or Suggestion that is not even harder to understand.

I have tried RSS Feeds. JIRA Supports RSS - and I failed. I have tried OData and failed. I have tried CDATA Power BI Connector and failed.

 You suggest using REST. How?

I have paid massive amounts of money for JIRA. The builtin reporting Options are Close to ridiculous and I am NOT ready to spend additional Money to get enabled to do some Basic reporting, BI and create some historical statistics.

Thanks for your thoughts.

[Mohamed Adel]

Hi @Markus Pöhler 

did you try Jira conent pack 

[Piotr Bojko]

@Markus Pöhler yo can try https://marketplace.atlassian.com/apps/1218767/smart-ql-sql-reports-charts?hosting=server&tab=overview

I am the product owner of Smart QL app. It respects Jira permissions to its data and it gives you a jdbc driver with which you can try to connect Power BI with Jira. 

Please note that if you connect with an user which does not see some tickets in Jira then he wont see any data through our JDBC smart ql app related to those issues (which is a good thing and desired case). 

[Piotr Bojko]

With Smart QL you are able query with SQL and do reports based on those queries: https://marketplace.atlassian.com/apps/1218767/smart-ql?hosting=server&tab=overview

1. It honors JIRA permission system - you want see data from JIRA your are not meant to see.
2. It gives you an option to connect to JIRA via JDBC with dedicated JDBC driver.
3. It gives you simplified views for JIRA data.
4. It allowes you to use JQL inside SQL - so you can search data with regular JQL and then make summaries on this with power of SQL (aggregates functions, windows and partitions available).