A few months ago, our team was tasked with integrating the application we were using to manage our projects (Teamwork PM) with Power BI, specifically to look at the different workloads for our implementation team across projects. Our end goal was to have several reports that our project manager could use to adjust our team members’ workloads to ensure that everyone had a doable load, as well as make sure that each team member was allotted enough hours a week to meet our goals. While searching for an integration tool, we came across Microsoft Flow and were enamored by the workflow tool having the ability to connect to Teamwork out of the box, as we needed to get all of the Task data from Teamwork.
Once we started working with Flow to try to pull Teamwork PM data into Microsoft’s Power BI, we ran into a range of issues – for one, we were having difficulty isolating some of the data points in Teamwork (such as the estimated time, or progress bar) that we were interested in bringing into Power BI. Additionally, we ran into limitations with the type of dataset Power BI requires for integrations with Flow – some of the functionality we wanted to build visualizations and custom measures or calculated columns weren’t available for live streaming datasets. Because of issues like this, we went back to the drawing board and tried to brainstorm a way that we could make this work without having to devote some of our development resources to writing and integration application. One idea we had was to use the HTTP Request action to utilize the JSON Output that Teamwork exposes. The HTTP Request action is an easy to use tool that allows the user to build an HTTP Get, Post, Put, Patch, or Delete request within the tool. Using this, we were able to get all Project Data from Teamwork, and then store it in an object variable using the “Initialize Variable” and “Set Variable” actions in Flow as can be seen below.
Once we got our project information, we used the Parse JSON action and the “Set Variable” actions (shown below) to isolate our project ID’s in an array in an “Apply to each” loop. The “Apply to each” tool is used to iterate through our entire project ID’s and do a second HTTP request to get all of the tasks from each project. The expression builder tool was used to get the “Id” property from each project JSON object in the overall project array.
Once we had an array of all Project IDs, we used a second apply to each to do another HTTP request. We used variables with custom expressions to create a URL for the HTTP requests in the second loop – the concatenated URL returns an array of Task objects for each Project. We then saved each set of tasks to a folder located on one of our servers using the File System Connector.
Once we set up a gateway connection in Power BI to the server, we then used Power Query to parse the JSON files, and loaded all of our task data into Power BI to then be used to build reports and dashboards which lead to actionable insights. Using the scheduled refresh feature in Power BI, and the recurrence trigger in Flow allowed us to refresh the data once or twice a day to ensure that tasks are being completed on time, and our consultants’ have balanced schedules.
Ignoring the huge benefits of having this data in a business intelligence suite as powerful and agile as Power BI, building a knowledge base of how to leverage a tool like Flow opens up a ton of doors for integrating different tools. Another example is being able to use a Suitelet in NetSuite to expose data which the HTTP Request action can consume. While there are certainly more elegant solutions to integrate applications, Flow offers an easy workflow tool than can be leveraged in a variety of ways to accomplish your company’s goals. As always, don’t hesitate to reach out if you have any questions!
