It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

how to use Microsoft power bi with jira

 

10 answers

Hey @Jen-Qology 

 

Our team released Jira plugin, which allows to create Data Sources for Power BI (with a feature to filter projects and/or fields). Makes life easier for a non-techie https://marketplace.atlassian.com/apps/1221150/power-bi-connector-for-jira?hosting=server&tab=overview

 

Selection_999(057).png

Not sure if I can jump on this thread or not (but will try anyway) I can't get the template to work, I'm at the Connect to JIRA step. 

I've entered the URL and used Authentication method Windows but get the following error: Failed to update data source credentials

Any ideas what I need to do/check?

Thanks 

Stuart, was this issue ever resolved? wondering what solutions you found if so? Thanks

Hi,

You can also try our recently launched add-on: All-In-One Power BI Connector for Jira to import Jira data inside Power BI.

 

Regards

AIO Support

Hi Support team,

I tried but this is not working. Is there other ways to pull the DATA from JIRA to Power BI? I tried to get JIRA content pack in Power BI but the screen freezing after i enter the JIRA link and Basic Credential. I have attached a screenshot below...not sure what I am doing wrong. Appreciate your help.

BI Service to JIRA.JPG

 

Thanks in advance

HI,

You can use AIO Power BI Connector for Jira App available on the marketplace here:

https://marketplace.atlassian.com/apps/1219072/aio-power-bi-connector-for-jira?hosting=cloud&tab=overview

 

It is available for Jira cloud, server and data center versions. In case of any issues please feel free to reach out at support@aioreports.com

0 votes

Depends - can it talk to JIRA over REST?  What does it do, and what do you want from it?

Its a data analytics software similar to tableau.

Well, I knew that really, but my point is more about what you want from it, and how you propose to connect it to JIRA.

I wan to be able to pull the data from jira into building more robust dashboard with Jira and also to create reports through Power Bi

How do you plan to connect it it? What's the method for "pull"?

Thats where I'm stuck, the goal is to pull, but now sure how I can accomplish that

Or even if it its possible

The best option is to use REST - it doesn't change much, when it does change, the changes are usually "make more stuff available" and it's available off-the-shelf. But does Power BI support that? At a guess, it'll probably demand access to the database, which is a nightmare because you'll need to build and maintain data dictionaries.

Yes, that's pretty much what I say about a lot of Microsoft Software.

Can I grab the raw data?

What do you mean by "raw"? The data in the database?

Can I export the data in other words

I just found out I can export from Jira to Excel which is compatible with Power BI such a process! Thanks as always for your help Nic

In the following link, altassian describe how to create SQL database that you can use with PowerBI or JSON link.

https://tech-connect.atlassian.net/wiki/display/UPH/Microsoft+Power+BI+Connector

Just found this thread - I've been able to crack this using a content pack with some minor tweaks on my end. Happy to share some insights if anyone still has the need.

Here's the content pack link that includes the functions needed: https://onedrive.live.com/?authkey=%21AH4_6OHXy6siE74&cid=E7E2C1CD5556023F&id=E7E2C1CD5556023F%21116639&parId=E7E2C1CD5556023F%21116638&action=locate

Using query functions that produce a REST call using search - then using PBI's query editor to break out the result (json) into table fields. No DB required. 

Then you can use DAX to do some pretty neat stuff. 

Scott,

I've loaded up the content pack and it tends to get stuck on 'getissues' after I provide basic authentication. I was only able to get it to load all issues (all 100,000+!!) once yesterday.

Do you have any insight as how to modify to only get issues for certain projects? I'm new to PowerBI:)! 

Nevermind - was able to figure it out! 

Jonathan Chatwin - how did you modify to only get issues for certain projects?

Hi

Scott/ Jonathan - did you manage to edit the query to pull back any other fields other than the ones in the content pack? If so, could you share please, I'm stuck and need some other custom fields that are not showing in the list.

 

Thanks - 

Dom 

@Jonathan Chatwin how did you figure it out? That's where i'm getting stuck too...

 

Try to give it ```https://jira.csc.com/rest/api/2/search```

and it spits back:

```Refresh

GetIssues

OLE DB or ODBC error: [DataSource.Error] WebContents failed to get contents from 'https://jirea.csc.com/rest/api/2/search/rest/api/2/search?maxResults=500&startAt=0' (404):.```

whenever I try to load -  https://jira.ext-inc.com/secure/Dashboard.jspa, this thing comes up. Please help me to resolve the getissue problem.get issue.PNG

Hi Arslaan,

You just need to provide https://jira.ext-inc.com that's it!!

Cheers

Like Shalini P likes this

Hello Maitrey,

The link helped but the JIRA page is blank how do I load my reports?

Thanks

I also cannot get the template to work. I am getting the following message.

GetIssues
OLE DB- of ODBC-error: [DataSource.Error] Web.Contents kan de inhoud niet ophalen van 'https://nameprojectof.myorganisation.local/rest/api/2/search?maxResults=500&startAt=0' (404): Not Found.

Is it possible that this template does not work for on-premises instalations?

It is working for On premises installations as well. Can you check that REST is enabled for your JIRA?

Hi Tjitse,

I think the JQL parameter is missing...

The REST API will return status 400 when there is a problem with the JQL query and 403 when the user doesn't have permission to search.

It wouldn't surprise me if Power BI/ODBC/OLE would say that any 4xx status is 404.

Has anyone had success with getting data related to a specific project in Jira?

Hi Monica,

Yes - I was able to make Power BI to run a custom search/query on JIRA and parse its results. In our case, we were required to create a Power BI page with indicators about worklog data.

Is it similar to your needs ?

How familiar are you with Power Bi (create/edit a query on advanced query editor) ?

Regards,

Tiago.

Hi Tiago, 

Thank you for your reply. I was able to edit the query using advanced editor to increase the number of rows returned. I am looking to query for only our projects data. The organization I work for has many departments that use Jira and they each have their own Project. 

Do you know how one would add a project key parameter?

 

Thank you!

Hi @Monica Labrador,

 

On this post I showed how to make Power BI query JIRA using a JQL search.

 

What you could do is to change the JQL from "assignee = currentUser()" to something like "projects in (projectA, projectB, ...)"

 

Hope it helps

Hi @Tiago Machado

 

Your post is helpful.

I was able to use the code provided and modify it to bring in only my project's data. However, the data is aggregated. How would I build a table with the issues and necessary fields I am interested in?

 

Thank you for your help.Screen Shot 2018-02-13 at 3.06.18 PM.png

Like Rick Post likes this

Hi,

The "data" you retrieve at the end of my script is a list. So, the first thing to do is convert it to a table (List Tools/Transform -> To Table).

Then you will have a "Column1" with some records. Click on the icon with two tiny arrows on the right of the Column1 to expand its values:

powerBi_expand_icon.png

You may select all and then delete the columns you don't want later or select just the values you are interested in.

Then you will have something more likely to a useful table. But the important values aren't available yet. Their are still "grouped" under the "issues" column. Repeat the expand process (click on the two arrows icon), expanding the values to new rows.

And you should repeat the expand procedure until all the important data is available in columns for your reports use.

Regards

Did you end up getting this to work? I have API connectivity to Excel if that helps. See my comment at https://community.atlassian.com/t5/Jira-questions/Fetch-data-from-JIRA-to-excel/qaq-p/198786

Hello, I try to find out how to connect Jira cloud within Power BI desktop. So far, i didn't find any .pbix templates. Any ideas?

Hi @Cyrille Arnold

 

Try checking this thread

 

Regards

Any thoughts on connecting Jira Server with Power BI, this blog indicates its only available for cloud.

Hi,

You can try our add-on:

https://marketplace.atlassian.com/apps/1219072/aio-power-bi-connector-for-jira?hosting=cloud&tab=overview

 

In case of any issues please feel free to email us at support@aioreports.com

 

Regards

AIO Support

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted in Jira

Demo Den Ep. 7: New Jira Cloud Reports

Learn how to use two new reports for next-gen projects in Jira Cloud:  Cumulative flow diagram and Sprint burndown chart. Ivan Teong, Product Manager, Jira Software, demos the Cumulative ...

273 views 1 2
Join discussion

Community Events

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

Find an event

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

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you