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

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

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?

9 answers

2 votes

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.

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 Community Leader Oct 24, 2016

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

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

Like TKurtz likes this

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

0 votes
Jack Community Leader Oct 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.

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 Community Leader Oct 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!

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.

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?

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!

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" 

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. 

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

Like Kristen Butler likes this

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

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

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!

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.

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.

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 :)

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"

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.

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.

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 ...

303 views 1 3
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