Written by Ted Swinyar
May 19, 2017
Category: Analytics, Business Intelligence, IT, Power BI, Technology
Using Microsoft’s Power BI to Implement a Management Dashboard Directly Connected to Atlassian’s JIRA APIs
The Beyondsoft Data & Analytics Team lives and breathes JIRA—Atlassian’s phenomenal issue and task tracking solution. We particularly love the myriad data points it captures during a ticket’s life cycle. Unfortunately, however, we’ve found its reports to be sadly lacking; this deficiency evolved from a nuisance into a major bottleneck during the team’s adoption of Scrum, when we needed to analyze how we were moving our work forward during each sprint.
During a few spare cycles I reviewed JIRA’s API documentation and found that most of the data was accessible; we just needed to put it all together. Our team brings data together for clients all the time using a variety of techniques and tools — from full scale cloud data warehouses to lightweight tabular models to everything in between — but this project seemed to call less for something bullet-proof and more for a quick, iterative approach. We needed data fast to help us improve our team’s effectiveness and answer some key questions:
• What work are we doing?
• Are we focusing on the right priorities?
• Are we neglecting any of our projects?
Enter Microsoft’s Power BI. Over the past few years this application has become our go-to tool for data visualization. Its underlying engine supports rapid prototyping that allows for mashing up data without the need for extensive supporting infrastructure. Could we quickly build a Power BI dashboard for our management team to use in reviewing and prioritizing our dev team’s progress? Spoiler alert: we could, and did. And it was awesome. Power BI surpassed all my expectations, and we used it to build a quick and dirty tool in our spare time. Here’s the story of how this came together (leaving out some details of a few dead ends).
If you’re interested in integrating an alien data source into Power BI, one of the first things you should do is review whether your data source supports a Power BI service content pack. Conveniently, Atlassian has one at https://app.powerbi.com/groups/me/getdata/services/jira. Its goal is to provide an instant dashboard for visualizing workloads and velocity.Connecting our JIRA instance’s data to Power BI was quick and easy, but the canned dashboard wouldn’t work for the particular workflow our team was focused on. It provided a simple way of viewing all tasks but didn’t break apart by project or provide deeper insights into where our team’s workflow was stalling at the sprint level.
After determining the Power BI service content pack wasn’t going to provide an adequate solution, we dove into an exploration of the JIRA REST API and what it would take to pull together the view we were interested in. Our first step was to determine what data was available, so we began reviewing the Atlassian JIRA API documentation. For those following along at home we found the following resources particularly useful:
• Tutorial for beginning rel="noopener noreferrer" to explore the API: https://developer.atlassian.com/jiradev/jira-apis/jira-rest-apis/jira-rest-api-tutorials/jira-rest-api-version-2-tutorial
• rel="noopener noreferrer" Documentation for the Board-getAllBoards rel="noopener noreferrer" endpoint: https://docs.atlassian.com/jira-software/REST/cloud/#agile/1.0/board-getAllBoards
We determined that the key data streams for our team would be sprints and issues for several of our JIRA boards, and began creating test API calls to start exploring the data:
• Sprint data: https://[instanceName].atlassian.net/rest/agile/latest/board/[boardID]/sprint
• Issue data (at board level): https://[instanceName].atlassian.net/rest/agile/latest/board/[boardID]/issue
• Issue data (at sprint level): https://[instanceName].atlassian.net/rest/agile/latest/sprint/[sprintID]/issue
The JSON returned was pretty much as we expected and contained all of the attributes necessary to build the dashboard we wanted to see. The next step was to consume the API rel="noopener noreferrer" data from within Power rel="noopener noreferrer" BI.
A good reference rel="noopener noreferrer" for the next steps is https://blogs.msdn.microsoft.com/charles_sterling/2016/05/25/how-to-call-rest-apis-and-parse-json-with-power-bi/. The consumption of JSON within Power BI is incredibly easy but it took a few transformations to get the data into the exact form we were looking for. We had to do the following transformation steps (raw “M” code in parentheses if you’re working with the advanced editor).
• Converted to Table: #”Converted to Table” = Record.ToTable(Source)
• Transposed Table: #”Transposed Table” = Table.Transpose(#”Converted to Table”)
• Promoted Headers: #”Promoted Headers” = Table.PromoteHeaders(#”Transposed Table”)
• Expanded values: #”Expanded values” = Table.ExpandListColumn(#”Promoted Headers”, “values”)
We repeated this for each of our three main data sources, resulting in three queries containing our basic set of data:
• Tickets in sprints
At this point we were able to do cleanup of data within Power BI: set necessary data types, remove unnecessary columns, rename columns, etc.
The data we had was real but broken across several disparate sources, making it unsuitable for the analysis we hoped to perform.As a helpful intermediary for our slicing and dicing we implemented a basic date dimension using the following DAX and the “New Table” feature in the Data tab:
With a fully realized data model now available, we had the foundation for building a dashboard suitable for our team to view sprint progress and have data to support our post-sprint retrospective conversations.
Our team found Microsoft’s Power BI to be a phenomenal tool for rapidly implementing a management dashboard directly connected to Atlassian’s JIRA APIs. Our team didn’t have to build any databases, ETLs, or code. We found that the main limitation of this approach is that no data is stored outside of the upstream API sources, so complex warehousing (e.g. of historical data) might require kludgy workarounds.