Jira backend database ‐ experiences?

James Gilmour
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
May 1, 2024

Hey all first post and might be a doozy, sorry in advance!

I'm posting here about our experience of with Jira or rather multiple instances. First things first I don't directly support / manage the atlassian suite of products in our business we have a dedicated team for that which I work closely with, secondly I lead a team of data engineers to support data integration / reporting needs for business demands.

So our experience started with a demand to perform reporting against jira, great. This should be a doddle, Jira has several inbuilt or marketplace reporting capability we'll use that. But no customer wanted to Integrate with data from other sources also, hmm OK Jira's Restful API - Nope, the Atlassian team did not want 3rd party clients impacting their Jira service.

I'd done the homework, research, community forums etc all recommendations were to use the API & stay away from the database. I'd pleaded, provided evidence but the architects stood their ground. The only other option was the backend database (note this was before data pipeline was available as an alternative option). I thought it's SQL it's my "bread and butter" how hard can it be?

Well ha, what a journey I've been on. I'm pleased to say we made it work, through jira upgrades / migrations / continuous level customisation it all works.

Is it perfect hell no, does it account for everything that application does nope (we're continuous expanding the scope to add new features all the time).

What we ended up with a two stage process. 1. A replication / ETL staging routine, 2. A dynamically self maintaining star schema.

Most data folk will be firmiliar with replication / ETL so I won't explain that part, the second element though is vast, firstly it requires manipulating Jira's core data structure to identify how all the attributes are stored and referenced (the core out the box attributes are simple enough, but the custom defined fields CDFs are sourced from many varied places), pivoting the data into a format users can understand how to interact with is also challenging (did you know you can two attributes with same name with different context and use cases.

The result is having to build out a meta data set to then dynamically generate (dynamic SQL is frowned upon) table and procedures to represent the associated cdf attributes all connected back to a central Fact table, we've over 1200 custom cdfs in one of our Jira instances.

So in summary, can it be done - hell yes, would I recommend it - only if you are very confident with SQL and comfortable with complex data structures and functionality. Can it be stable - yes we've had the generation process running daily for 8 months lived through 3 upgrades, lots of customisation. The two largest pain points are regarding the level of flexibility / customisation that Jira offers thus managing though changes through to the star schema model, and secondly trying to decode how all the 3rd party bolt-ons function and store its specific data.

The key to all this is would I recommend it, the journey and experience. Whilst it does offer a fully functional self service reporting model that can be used by most bi clients, and is more feature rich than the data pipeline offering. I would recommend sticking with the API route first and foremost. 

Like many have already mentioned before me the SQL backend is vastly complex (kudos to any that have tried to understand it) especially for functionality extended through 3rd party / marketplace addons. For the majority of use cases the API can be worked to provide the information required. For those attempting to navigate through the relationships of the backend good luck.

My only actual question in relation to this post, is what's everyone else's experience been like?

1 answer

0 votes
James Gilmour
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
May 1, 2024

I will add the best part about this experience was once we built a model to handle the Jira source, we pick it up deploy against another Jira Instance and generate a model for that one with completely different configuration within 2 hours.

Suggest an answer

Log in or Sign up to answer