Instructions for configuration/usage of the JIRA content pack for Microsoft Power BI?

Jon Abad October 20, 2016

A new content pack for Power BI was announced on a blog post today but there doesn't seem to be any guidance as to what it tries to do or how to configure it.

http://blogs.atlassian.com/2016/10/jira-content-pack-for-microsoft-power-bi/

For example, it appears to try to query for every issue in the JIRA instance via the REST API. I have a limited test case i'd like to start with, so how do i change that behavior?

10 answers

4 votes
Anton Storozhuk September 26, 2019

Hi @Jon Abad 

 

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

 

More details on YouTube:

https://www.youtube.com/watch?v=6tlx5KzXLeg

2 votes
Aron Gombas _Midori_
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
October 22, 2016

Are you using JIRA Cloud? According to the labels on your question, you don't.

There is a comment from the MS guys telling:

Hi Stephen, are you using on Premises JIRA or the cloud service? Currently the content pack will work only with the cloud version.

Interestingly, the Atlassian blog post does not make any mention about cloud vs. server, and they allow commenting, either.

Jon Abad October 24, 2016

I'm not using JIRA Cloud but I do have external access setup via HTTPS, have the REST APIs enabled and used Basic authentication with an account with view rights to the issues in the system.

Not the best circumstances but ok to take a look to start.

The comments also have links to a Power BI desktop template that matches what is on the site so I'm going to take a look at that as well.

Jack Brickey
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
October 24, 2016

yes. cloud only. I have it up and running now and seems pretty powerful albeit not overly intuitive at the moment.

Manikandan Muruganandam March 21, 2019

how to get power bi reports for a project from power BI content pack

Like TKurtz likes this
Leonid Sokolovskiy May 11, 2020

I would appreciate if somebody will provide details regarding Power BI content pack.

How to install and use it.

Like Mike Bevilacqua likes this
Linkedsoft July 31, 2020

@Charis Church : Appreciate you you can share how to use this.

https://drive.google.com/file/d/1rc7ngRHPH38AWwHEMKJ6mcPjhSVT-rJe/view

My email: lyhuynh.ueh@gmail.com

Nice to connect with you on this matter.

0 votes
Divya Sudhakar (US - ADVS) April 7, 2020

is the power bi content pack available for JIRA server?

Charis Church April 7, 2020

No, unfortunately.  I'm finding some success with Power BI Connector for Jira app. 

Divya Sudhakar (US - ADVS) April 7, 2020

R you referring to the AIO Power BI connector?

Charis Church April 7, 2020

No.  It's called "Power BI Connector for Jira".  I couldn't get the AIO PowerBI connector to work with my data because we have issues, epics and initiatives.  I think it might work if you only have issues and epics.

Like Anton Storozhuk likes this
Anton Storozhuk April 7, 2020

@Divya Sudhakar (US - ADVS)  we've built apps for BigQuery, Tableau and Power BI Connector for Jira.

Please let us know if you need any assistance in exporting your Jira data to Power BI, we'll be happy to help.

Divya Sudhakar (US - ADVS) April 7, 2020

@Anton Storozhuk  what kind of apps are those.can you elaborate?

Navarambh Software - Gmail
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.
April 7, 2020

Hi,

AIO Power BI Connector supports Portfolio and works with issues, epics and initiative. 

 

Our connector is #1 selling Power BI Connector on marketplace and is used by hundreds of customers including several fortune 500 companies in cloud/server/data center environments.

Here is the documentation link: https://aioreports.atlassian.net/wiki/spaces/APBCFJ/overview

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

 

If you need any help, please email us at support@aioreports.com. 

 

Regards

AIO Support

Anton Storozhuk April 8, 2020

Hi @Divya Sudhakar (US - ADVS) 

We're specializing in creating apps for exporting Jira data to business intelligence platforms like:

Also we have a set of apps for exporting ecommerce data (Shopify, Magento) to BI platforms.

Fast & responsive support is here to help you to install and configure the app.

Please let me know if you have any questions, we'll schedule a demo call to show you  how easy to set up the app.

Best regards,
Anton.

Like # people like this
Jason Lesperance May 1, 2020

Any idea when you will have the Google Data Studio connector?

Anton Storozhuk May 3, 2020

Hello @Jason_Lesperance ,

We're planning to rollout Google Data Studio for Jira Server/Data Center by the end of June.

Are you looking for Server or Cloud connector?

Thanks,
Anton

0 votes
MAG-II February 14, 2019

Hello - 

Has anyone been able to figure out how to get Jira Server data onto PowerBi desktop? Everything I am using is self-hosted. Any suggestions / help would be very much appreciated.

Cesar Vinas February 15, 2019

Michael, you can use the Web connector and Web.Contents to extract the data via Jira's API. I also have Jira self-hosted and this method has worked fine for me. You will have to deal with the fact that the API is paginated. Also, if you plan to publish your reports to Power BI Services, you'll have to implement the M query in a way that can deal with the dynamic Jira URL's that the query will use to pull the data out or otherwise, the scheduled auto-refresh won't work. This article should help you get started: http://blog.datainspirations.com/2018/02/17/dynamic-web-contents-and-power-bi-refresh-errors/. The examples are based on the JazzHR API (https://www.jazzhr.com), but the process for Jira is very similar. I hope it helps.

MAG-II February 15, 2019

Hi Cesar - 

Thanks for the response. I am attempting to walk through the article you posted. Unfortunately I have very limited experience with data tables, and this is my first attempt to visualize data in PowerBi. After reading through the posted article I am not exactly sure on where to even start when it comes to importing live data from Jira into PowerBi.

Again I appreciate the info you passed along. I will continue to attempt to make sense of the article :)

Cesar Vinas February 15, 2019

Michael, look at the query below. It should get you started. Here I'm using a Service Desk type project so depending on the type you have, you will need to make adjustments:

 

let
Source = Json.Document(Web.Contents("https://yourjira/rest/api/2/search?maxResults=1&jql=project=XXX&fields=key&startAt=0")),
#"Obtained Total Issues" = Source[total],
#"Created List Of Start Values" = List.Generate(() => 0, each _ < #"Obtained Total Issues", each _ + 100),
#"Created List Of URLs" = List.Transform(#"Created List Of Start Values", each "https://yourjira/rest/api/2/search?maxResults=100&jql=project=XXX ORDER BY key&fields=*all,-comment&startAt=" & Text.From(_) ),
#"Obtained Issues" = List.Transform(#"Created List Of URLs", each Json.Document(Web.Contents(_))),
#"Converted to Table" = Table.FromList(#"Obtained Issues", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"issues"}, {"issues"}),
#"Expanded issues" = Table.ExpandListColumn(#"Expanded Column1", "issues"),
#"Expanded issues1" = Table.ExpandRecordColumn(#"Expanded issues", "issues", {"key", "fields"}, {"key", "fields"}),
#"Expanded fields" = Table.ExpandRecordColumn(#"Expanded issues1", "fields", {"resolution", "priority", "assignee", "status", "reporter", "issuetype", "project", "created", "updated", "summary"}, {"fields.resolution", "fields.priority", "fields.assignee", "fields.status", "fields.reporter", "fields.issuetype", "fields.project", "fields.created", "fields.updated", "fields.summary"}),
#"Expanded fields.resolution" = Table.ExpandRecordColumn(#"Expanded fields", "fields.resolution", {"name"}, {"fields.resolution.name"}),
#"Expanded fields.priority" = Table.ExpandRecordColumn(#"Expanded fields.resolution", "fields.priority", {"name"}, {"fields.priority.name"}),
#"Expanded fields.assignee" = Table.ExpandRecordColumn(#"Expanded fields.priority", "fields.assignee", {"displayName"}, {"fields.assignee.displayName"}),
#"Expanded fields.status" = Table.ExpandRecordColumn(#"Expanded fields.assignee", "fields.status", {"name"}, {"fields.status.name"}),
#"Expanded fields.reporter" = Table.ExpandRecordColumn(#"Expanded fields.status", "fields.reporter", {"displayName"}, {"fields.reporter.displayName"}),
#"Expanded fields.issuetype" = Table.ExpandRecordColumn(#"Expanded fields.reporter", "fields.issuetype", {"name"}, {"fields.issuetype.name"}),
#"Expanded fields.project" = Table.ExpandRecordColumn(#"Expanded fields.issuetype", "fields.project", {"name"}, {"fields.project.name"})

in
#"Expanded fields.project"

MAG-II February 19, 2019

Good Morning Cesar - 

I attempted to insert the above query into PowerBi. I copied it into the "Edit Queries" section, and replaced all of the URLs and project names with my instance of JIRA. Nothing seems to happen when I get this query onto PowerBi. There must be a critical step that I am missing.

Subhramani Sathyanarayan August 1, 2019

I really hoped there was a reply to this. This has been eating my brains out for over a month and a half and I have still not managed to get what I want.

The content pack starts downloading every issue that exists in my organization. I want to be able to specify the project I need the issues for and I have just NOT managed to do that. Changing the fields look easy though.

0 votes
Clayton Nesslein February 19, 2017

What are the steps for downloading the JIRA content pack to Power BI desktop? I'm already set up with the content pack in the Power BI service, but can't seem to get the .PBIX file. 

Cesar Vinas October 13, 2018

Clayton, were you able to figure how to get the PBIX file? I have the same need.

Like Kristen Butler likes this
Kristen Butler November 2, 2018

Same here

Ezhil Arasu November 25, 2018

I do need this file and not able to find it here

Stuart_Baynes December 3, 2018

I don't think you can download PBIX from the online workspace yet.

Martin Dean January 22, 2019

Has anyone managed to find steps to get a content pack as a start point for further edits? 

I can get Jira working as a source in desktop - but am looking for a start point of a report with content!

Like Christel Gray likes this
0 votes
Espen Olsen December 5, 2016

Got the content pack all up and running. I'm using it to create reports on JIRA Service Desk. But I cannot find the Organizations field anywhere. So hard to create reports based on customers, which is what I need. Also not getting my custom fields.

Any updates on this Content Pack planned?

Brent August 16, 2018

Hi Espen,

Did you get custom fields working in the end? I've managed to get connected to Jira Cloud from PowerBI Desktop, but finding the same as you did with custom fields. Trying to decide if I should persevere or if it's going to take too much time and fiddling to get it to work!

Espen Olsen August 18, 2018

Hi Brent,

We managed to figure out how to add our own custom fields and the Organizations field. The easiest way is to open the "Query Editor", and then go to "Advanced Editor"

There you can specify what you need using "M Formula". I am attaching an example of how we did it:

let
Source = FetchPages("", 500),
#"Expanded Column1" = Table.ExpandRecordColumn(Source, "Column1", {"expand", "id", "self", "key", "fields"}, {"expand", "id", "self", "key", "fields"}),
#"Expanded fields" = Table.ExpandRecordColumn(#"Expanded Column1", "fields", {"issuetype", "project", "aggregatetimespent", "resolutiondate", "created", "aggregatetimeoriginalestimate", "assignee", "updated", "status", "components", "description", "summary", "reporter", "customfield_12100", "customfield_11614", "customfield_11616"},{"issuetype", "project", "aggregatetimespent", "resolutiondate", "created", "aggregatetimeoriginalestimate", "assignee", "updated", "status", "components", "description", "summary", "reporter", "customfield_12100", "customfield_11614", "customfield_11616"}),
#"Changed type Organizations" = Table.TransformColumnTypes(#"Expanded fields",{"customfield_12100", type any}),
#"Expanded Organizations" = Table.ExpandListColumn(#"Changed type Organizations", "customfield_12100"),
#"Expanded project" = Table.ExpandRecordColumn(#"Expanded Organizations", "project", {"id", "key", "name"}, {"project.id", "project.key", "project.name"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded project", each [id] <> null and [project.key] = "ASSIST"),
#"Expanded issuetype" = Table.ExpandRecordColumn(#"Filtered Rows", "issuetype", {"name"}, {"issuetype.name"}),
#"Expanded status" = Table.ExpandRecordColumn(#"Expanded issuetype", "status", {"name"}, {"status.name"}),
#"Expanded components" = Table.ExpandListColumn(#"Expanded status", "components"),
#"Expanded components list" = Table.ExpandRecordColumn(#"Expanded components", "components", {"name"}, {"components.name"}),
#"Expanded reporter" = Table.ExpandRecordColumn(#"Expanded components list", "reporter", {"displayName"}, {"reporter.displayName"}),
#"Expanded assignee" = Table.ExpandRecordColumn(#"Expanded reporter", "assignee", {"displayName"}, {"assignee.displayName"}),
#"Expanded SeverityLevel" = Table.ExpandRecordColumn(#"Expanded assignee", "customfield_11614", {"value"}, {"customfield_11614.name"}),
#"Expanded TTFR" = Table.ExpandRecordColumn(#"Expanded SeverityLevel", "customfield_11616", {"completedCycles"}, {"customfield_11616.completedCycles"}),
#"Expanded TTFR List" = Table.ExpandListColumn(#"Expanded TTFR", "customfield_11616.completedCycles"),
#"Expanded TTFR Record" = Table.ExpandRecordColumn(#"Expanded TTFR List", "customfield_11616.completedCycles", {"breached", "elapsedTime"}, {"customfield_11616.completedCycles.breached", "customfield_11616.completedCycles.elapsedTime"}),
#"Expanded TTFR elapsedTime" = Table.ExpandRecordColumn(#"Expanded TTFR Record", "customfield_11616.completedCycles.elapsedTime", {"millis"}, {"customfield_11616.completedCycles.elapsedTime.millis"}),
#"Calculated time spend" = Table.TransformColumns(#"Expanded TTFR elapsedTime", {{"aggregatetimespent", each _ / 86400, type number}}),
#"Changed time spend" = Table.TransformColumnTypes(#"Calculated time spend",{{"aggregatetimespent", type duration}}),
#"Calculated time estimate" = Table.TransformColumns(#"Changed time spend", {{"aggregatetimeoriginalestimate", each _ / 86400, type number}}),
#"Changed time estimate" = Table.TransformColumnTypes(#"Calculated time estimate",{{"aggregatetimeoriginalestimate", type duration}}),
#"Calculated time SLA" = Table.TransformColumns(#"Changed time estimate", {{"customfield_11616.completedCycles.elapsedTime.millis", each _ / 86400000, type number}}),
#"Changed time SLA" = Table.TransformColumnTypes(#"Calculated time SLA",{{"customfield_11616.completedCycles.elapsedTime.millis", type duration}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed time SLA", "resolutiondate", "resolutiondate - Copy"),
#"Inserted Parsed Date" = Table.AddColumn(#"Duplicated Column", "ParseDate", each Date.From(DateTimeZone.From([#"resolutiondate - Copy"])), type date),
#"Inserted Month" = Table.AddColumn(#"Inserted Parsed Date", "resolution month", each Date.Month([ParseDate]), type number),
#"Inserted Year" = Table.AddColumn(#"Inserted Month", "resolution year", each Date.Year([ParseDate]), type number),
#"Renamed columns" = Table.RenameColumns(#"Inserted Year",{{"customfield_12100", "organizations"}, {"issuetype.name", "issuetype"}, {"status.name", "status"}, {"components.name", "components"}, {"reporter.displayName", "reporter"}, {"assignee.displayName", "assignee"}, {"customfield_11614.name", "severity level"}, {"customfield_11616.completedCycles.breached", "time to first response breached"}, {"customfield_11616.completedCycles.elapsedTime.millis", "time to first response elapsed time"}}),
#"Removed columns" = Table.RemoveColumns(#"Renamed columns", {"expand", "id", "self", "project.id", "project.key", "project.name", "ParseDate"})
in
#"Removed columns" 
0 votes
Jon Abad October 21, 2016

Yes, the "importing data" message does not indicate that it's working or not. I noticed that the "dataset" in the left side pane is grayed out when it is trying to load.

As I'm admin on the JIRA side, i saw the activity in the log and figured out that it was querying for issues 500 at a time and trying to get through the entire set.

0 votes
Jack Brickey
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
October 21, 2016

thanks. maybe that is the issue for me. the "importing data" pop up disappears so I assumed it completed but maybe not. Will look at it later and see. Good luck!

0 votes
Jon Abad October 21, 2016

I let the package run late at night and it seems to have "worked" as it loaded enough data and build the report and dashboard.

The blog post on the Power BI side is much more active as they actually have comments enabled there...

https://powerbi.microsoft.com/en-us/blog/explore-your-jira-data-with-power-bi/

 

One of the interesting things is that there is a template pack for the Power BI Desktop edition and that allows for the queries to be edited.

I'm going to try that out later on.

0 votes
Jack Brickey
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
October 21, 2016

Jon,

I saw the same and am giving it a try now but getting stuck early. Likely partly because it is new and partly because it is MS. In any event I set up the connection and it started importing (a few minutes) but then nothing. According to documentation it was going "to automatically create an out-of-the-box dashboard, report, and dataset with data from JIRA.". No sign of that yet. I will update here as I learn more.


Here is the doc I'm reading.

Suggest an answer

Log in or Sign up to answer