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

[Jon Abad]

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?

[Anton Storozhuk]

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

More details on YouTube:

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

[Aron Gombas _Midori_]

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]

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]

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

[Manikandan Muruganandam]

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

[Charis Church]

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

[Leonid Sokolovskiy]

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

How to install and use it.

[Linkedsoft]

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

[Divya Sudhakar (US - ADVS)]

is the power bi content pack available for JIRA server?

[Charis Church]

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

[Divya Sudhakar (US - ADVS)]

R you referring to the AIO Power BI connector?

[Charis Church]

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.

[Anton Storozhuk]

@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)]

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

[Navarambh Software - Gmail]

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]

Hi @Divya Sudhakar (US - ADVS) 

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

• Power BI Connector for Jira: https://marketplace.atlassian.com/apps/1221150/power-bi-connector-for-jira?hosting=server&tab=overview
• Tableau Connector for Jira: https://marketplace.atlassian.com/apps/1221376/tableau-connector-for-jira?hosting=datacenter&tab=overview
• BigQuery Connector for Jira: https://marketplace.atlassian.com/apps/1221749/bigquery-connector-for-jira?hosting=datacenter&tab=overview
• Google Data Studio, Looker, Klipfolio, and others are coming soon.

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.

[Jason Lesperance]

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

[Anton Storozhuk]

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

[MAG-II]

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]

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]

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]

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]

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]

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.

[Clayton Nesslein]

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]

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

[Kristen Butler]

Same here

[Ezhil Arasu]

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

[Stuart_Baynes]

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

[Martin Dean]

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!

[Espen Olsen]

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]

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]

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" 

[Jon Abad]

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.

[Jack Brickey]

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!

[Jon Abad]

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.

[Jack Brickey]

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.