Procore + P6 + Power BI

[00:00] All right. Today I am going to walk through what I have been putting some hours into: getting a proof of concept going with Microsoft Azure Cosmos DB, creating a function that ultimately leads into our Power BI reports. Initially, what I did is I created a Cosmos DB account. You'll see here that I have my Cosmos DB with our URI. Then, within Data Explorer, we created a container. This container is labeled "procore," and I'm loading in the RFIs here. I do this by creating a function. So I went

[00:45] through and created a function app. Within this function app, I created a function which has the various types of resources that it will go through and map out. Essentially, I created the function app here, and I created a few actual functions within this function app. The first one is "sync RFIs to Cosmos." What this will do is run the authentication for our Procore instance to get the access token and be able to authenticate against Procore.

[01:30] Once it's authenticated, it will connect to our Cosmos database and make sure that we're receiving all of the data that's currently there. Once it receives all of that data, it's going to fetch all of the RFIs for that Procore project. Then it will identify if there are any that are already there. If one was deleted, it will remove that from Cosmos. And if there are any updates or any new ones, it will add those into our Cosmos database. So this will go through and insert all of that into our Cosmos database. Then it runs a bunch of code. This is all the logic that will go through and

[02:15] basically extract all of the RFIs. This is where it deletes an RFI if it no longer exists. Then it will confirm that all of those were written to the container within Cosmos. Once that runs, I do have a "sync RFI." The other one was initially to initialize proper authentication and get all of that into the container. I created a sync RFI, so this will run and sync any new or updated RFIs to ensure that all of that's functioning. And then I created a timer, essentially. Because there are webhooks, and I can

[03:00] actually pull this up here in our Procore instance. Within Procore there are webhooks. Webhooks essentially allow us to do an automated check to see if there are any updates, and they automatically send that response to a POST request endpoint. When I went through and looked at what webhooks are available for Procore, it did not have any of the key ones that we need. So we will need to do some form of polling request. But essentially, I went through and created a webhook here, and here are the resources that are available. There are 66 events, things like change

[03:45] orders and change events within construction financials, commitments, and then there are the core modules, which are mainly projects and companies, and the associated portfolios, like projects that are updated or created. Within project management, this is typically where we would see the RFIs, submittals, everything like that. Those capabilities aren't in there. So ultimately this requires us to do a polling GET request to receive all of the RFIs, submittals, and various other things. The idea here is that the webhooks can be used, but they're a bit limited in this use case given the data that we're looking to receive.

[04:30] So I created a trigger that will trigger the sync RFIs endpoint, or function, within the function app. And then we have a "get RFIs." That would be if I wanted to view all the RFIs. It's not necessary in this use case, but I added it so that in the browser I can visually see that the RFIs are there. We can also validate that the RFIs are there by going into Data Explorer within our Cosmos DB, and we'll see here the container that we created. And here are all the RFIs that were created. These will load and consistently update based upon the

[05:15] trigger that is set within this instance. I did have it flatten some of the RFIs. It's not being run right now. The reason for flattening was my thought process that the conditional objects and the arrays for the subdirectories, like assignees or locations, may not be loaded within Power BI, which is not the case, so this is actually omitted. Ultimately we can fetch all the RFIs, and that would be using the trigger to gather all the ones that are currently available in the Cosmos database. What I did then is I went into my Power BI Desktop, and you will see here that we have all of the data that is associated with it. So I created an

[06:00] RFIs object. How I did that was a Get Data. I can click this button here, and this allows me to bring in data. I chose Cosmos, and within Cosmos I selected Cosmos DB version 2. I connected to it, and this is where I would submit the Cosmos database endpoint, which is equal to this endpoint right here, the URI for the Cosmos database. Once I submit that, and I can submit it here just to show, this will then require an access key. So

[06:45] the access key is found within the instance of our Cosmos database. It didn't require it this time because I already provisioned it. But this would then provide any type of data that is associated with it. As you see here, the RFI assignees, ball-in-courts, and questions: those are all the sub-arrays for the RFIs. Essentially, we can load in all of this data depending on what we're looking to load in. When we load all the questions, we have all of the responses here that are associated with the questions, and we can load that in as well. I already did all this, so we should be good from there.

[07:30] So that brings in the RFIs, assignees, ball-in-courts, and questions, and this data can then be referenced and leveraged within this display table, or this workbook that we created. What I also did is learn a little bit about how we can get the P6 .xer file into Power BI. This is a bit different because it's a delimited file, meaning that there's specific data that's associated with it. So when we get the data here, we have to load it as a Text/CSV file. When we connect, we then reference the

[08:15] data that's associated with it. I have to choose "All files" here. Essentially, this would leverage the P6 schedule. Once that's loaded, I ran some queries against it, and this will partition the data and format it correctly. If we go into here, this would be the project, or I'll do the task one here. I did a query. Power Query essentially takes that 2D flat file and delineates it further based upon the transformations that we set up. So this is the initial

[09:00] source file that it loads in, and as you know, this is not really usable. So I had to run some logic. I went through and put together a transformation, in the Advanced Editor here. What I did is a task query that runs through all of this code to partition the data and make it visible and usable. It runs each of these steps. It converts it to a table and then it adds tags to it, so it takes out all the tags that are associated with it. It adds the values and separates the tags from the values. It creates the table name. We

[09:45] then fill the table name. We filter based upon tasks, so you'll see here that we have our tasks. We provision the headers for it. Once we have the headers, we count how many headers there are and how many rows. We then format the rows and split the rows within the data. What this does is let us normalize it to make sure that it runs well, and then we put them into columns. This makes it into a readable format that is usable and that we can input into Power BI. Once this data is in Power BI, we can run that query. That

[10:30] query gives us all of the associated headers that we formatted, which I then load in here. You'll see here this is the RFIs. I created a graph that shows the schedule impact based upon status. And then we can load in all of the RFIs that are associated with that, which I did by selecting these specific fields. And then I did the same thing for the schedule here. Within the schedule I had the schedule tasks, and we are specifically pulling key attributes here. Then I created a Gantt chart

[11:15] visualization so this can be viewed and referenced. This is in Power BI Desktop, so I then went to File and published this to Power BI. It's already published, so I can show you what it looks like. One thing I forgot to mention: once this function app is created, I had to actually publish it and deploy it to Azure. Once this is published to Azure, it loads all of the functions that we created and provisions those. Then they are run, and it's

[12:00] automatically running this trigger. So it will automatically trigger the sync RFIs endpoint. Once it syncs all the RFIs, that updates all of the data in our Cosmos database here. When we go into Data Explorer, it will automatically update based upon it gathering all of that data for the RFIs, which then updates our data source. So once this is provisioned, and this Power BI report is provisioned and synced with that Cosmos database, it will always update on that periodic cadence. Specifically, once we push this into Power BI, I have

[12:45] a workspace that's created. Within this workspace, this is pulling in all of that data, and it's linked to the specific resources. What I had to do is go into the settings section here and manage connections and gateways. I created gateways that are associated with the schedule. Here's the schedule, which is pulling my schedule from the on-prem version, which is this one. It's not the personal one. I provisioned a file, and this is referenced to the specific file location where it lives. And then I provisioned the Cosmos

[13:30] database. I plugged in the credentials for the Cosmos database and the key associated with it, similar to what we did before. So what this does is create the workspace, and within this workspace it pulls in the Power BI dashboard project that we created, which has all of the data associated with it. This pulls in all of the data, which is then interactive. You can reference all of the data, create any charts, and filter all the data to make sure it is visible for what your needs are. From there it should be good to go and running. What I did do is go

[14:15] into the datasets, where we can set a scheduled refresh. The scheduled refresh will run and gather the new data that is pulled from Cosmos DB, as well as any file. It will still reference that main file. So if we give that file a specific name, maybe "current schedule," and it's in the same location, then it will always be refreshed based upon the scheduled refresh that we provision here. And so this is ultimately the dashboard that is

[15:00] created for a sample, pulling in all of the RFIs. Of course, we can pull in any dataset from Procore. All we'd have to do is create a new function and have it run, and it will also pull in the P6 schedule, which is based upon a file on a server that we reference by a specific name. Anyway, I hope that helps explain everything that's been done to this point and gives a good overview of what's capable and what's possible. Of course, this is just the tip of the iceberg. I'm eager for your feedback, and I appreciate the time and opportunity.