What is the database relationship between users and user fields on an issue?

Eric Sara
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
June 17, 2022

I am using Power BI to do reports based on JIRA data. 

I have been able to work out how most of the tables can be linked together, however, linking a user to fields such as creator and assignee, I have not been able to work out. 

Add to this we use the support part of JIRA so we have many users who are linked to Organisations.  I have worked out how to link the Organisation to the user but not the user to the issue. 

I am in no way an SQL expert so please be simple with me.

What I would love to know is the data tables to bring into Power BI and the fields to use to link each. 

Thanks, 

2 answers

Comments for this post are closed

Community moderators have prevented the ability to post new answers.

Post a new question

0 votes
Eric Sara
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
June 19, 2022

While not perfect I found that if I take the 'PUBLIC.APP_USER' table I can link the 'user_key' to the 'reporter' in the 'jiraissue' table and this gives me the link between the user and issue. 

And then from the 'PUBLIC.APP_USER' table  I can link the 'user_key' to the 'user_key' in the 'AO_54307E_ORGANIZATION_MEMBER' table and this gives me the link between the user and the org.

I then link link the 'ORGANIZATION_ID from the table 'AO_54307E_ORGANIZATION_MEMBER' to the field 'ID' in the 'AO_54307E_ORGANIZATION' field and this gives me the org name. 

What this does not give me is when the user is not linked to the an org but the org is on the issue. This one still stumps me.  

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
June 19, 2022

Yep, and there's another 2 things (maybe more, the 2 is from memory) you need to look at.

Please, please, please, stop reading the database.  

It is always the wrong way to ask Jira for information.

0 votes
Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
June 18, 2022

Ugh, that's a horrid thing to do. 

Your best option is to stop using a direct connection to the database.  It's slow, complex, messy and won't tell you as much as the UI will because some stuff you see is done by the code, rather than the data.

If you did this properly, with the REST API, you'd find all the stuff you are looking for is automatically available when you make the simple "show me an issue" call.  If you're not going to do it properly, then take a look at the apps in the marketplace, there are loads of them that give you all sorts of reports, without you having to look at (and hence understadn) the database.

Eric Sara
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
June 18, 2022

Hi Nic, 

Thanks for taking the time to respond. 

The UI has its place, and we use it well. However, there are times when it is not the right solution. 

I am merging data from JIRA with data from other systems to give my team a one-stop dashboard overview. It is not taking the place of the UI used to track and update live issues, but more management reports showing a snapshot. 

I have used APIs before, but more for product-to-product process integrations, not for getting data out to store and report. Is there any documentation you can point me to help with this?

Concerning the apps, again, if you can point me to one that is recommended, I will take a look.  However. I was looking to avoid them as we are about to merge with another company with around 2,000 users, so any app for my team (about 50 users) could be a significant expense. 

I get that my request may be horrid, and I also understand that some things are done by code, but I also understand that in each database, there is a logic that can be followed, no matter how horrid.  But, sometimes, that horrid is just not worth the effort, depending on how necessary the outcome is. 
Are you saying that finding, understanding and then creating the link between issue and organisation cannot be done?  Or that it can, it is just difficult?

So far, I have not had any issues with speed.  Getting data from JIRA has often been faster than from the other data sources I have needed to use. 

Any help you or anyone else in the community can give here would be appreciated. 

Thanks, 

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
June 18, 2022

>The UI has its place, and we use it well. However, there are times when it is not the right solution. 

I'm with you on that, there's a lot that Atlassian doesn't directly provide.

And "look at the database" is the wrong answer when you run into "it doesn't quite do what we think we want"

The "right solution" is to look at the REST API, not the databaase, especially as the database is going away for most of us.

>I am merging data from JIRA with data from other systems to give my team a one-stop dashboard overview. It is not taking the place of the UI used to track and update live issues, but more management reports showing a snapshot. 

 

DEPLOYMENT TYPE
SERVER
VERSION
8.12.2
TAGS
AUG Leaders

Atlassian Community Events