Procore + Sage + Outbuild in Fabric & Power BI

[00:00] All right. Today we are going to be reviewing the Power BI reports, specifically integrating Procore and Sage, mapping the cost codes, and putting together all of the data associated with it. We are also pulling in data from Outbuild for our scheduling. I'll walk through the reports, and then from there we'll talk about the ingestion, how we're bringing the data in, and how we can validate it. Specifically, the goal is this: previously, we were having to create these monthly progress reports and updates manually, by exporting data from Procore and Sage, putting it into an Excel spreadsheet, and mapping everything. The goal is to pull all of this data live. So instead of

[00:45] building it once per month, it's always going to be accessible and available. And it's always integrated with the actuals, so you never need to run your own manual calculations, risk errors, or spend the time. We initially have the Monthly Progress Report, which shows the portfolio and all the projects we're pulling from Procore: contract amounts, anything outstanding, and what's been billed to date. We can of course drill into any specific project by clicking it, and from there we can see the metrics for it. Right here is the scorecard. I'll dive into the scorecard and how it's calculated in a moment. In a sense, we're pulling out all the metrics. Then the overview is the general

[01:30] overview and health of the organization and all of the projects being brought together. It covers billed to date, what invoices have been sent, anything outstanding, and what the growth is. It goes through each of the months and breaks it out by each of those measures. As you'll see down here, we also have the last refresh. This is constantly refreshing, and it goes through the checks in the gold-standard pipeline. We'll talk about that a little later. In a sense, we can validate that it's pulling in all the data properly, and if it fails at any point, it always reverts to the previous

[02:15] data. So it's not calculating anything incorrectly. When we dive into the financials, this brings up the entire report for all of our cost codes and the budgets for each one. We mapped all of these based on the actuals that were provided. We did a transformation from the past cost codes that were being used to the new cost codes that are being used now. Those should map 100% to the new standard we are implementing between Procore and Sage. There is a mapping exercise done in the back-end notebooks, so it will always map and associate them to the correct cost codes. There are some cost codes that we may want to expand into that

[03:00] are in the list of requirements, or requests for information, that I sent over as well. We're bringing data in from Outbuild as well. Some of this data needs to be associated with specific projects, and the projects we need to assign to a specific Procore project will then be pulled into the unresolved records. But it is pulling some of the data here, and we can see how the project milestones are moving along and whether there's anything outstanding we need to pay attention to. Quality and safety: this one's big because we're pulling from Procore. There is also some data in here that is

[03:45] not pulled from any source, some of it being safety and quality and how users are inputting data. So we have SharePoint lists that we can import and load into the Power BI report. Some of that will be updated here once we have the project managers and others filling out those lists and collecting that data. Billing and retainage: this pulls what's been billed to date, what's being retained, and any additional forecasts for the projects being held. This is great for being able to dive

[04:30] into the contracts and identify this. One thing to touch on too is that with each of these, we can use a drop-down. If you want to select a specific project, you can get the specifics there. Or if you want a specific month, we can pull in that month and it will pull the data for it. Direct costs and vendors: we are mapping all of our vendors in Sage and associating all the invoices to them. All of the committed costs and unapproved costs are mapped through here, tracking all of our actuals and what's been spent and costed so far.

[05:15] Vendor insurance is another important one. We still need to identify where the certificates are being stored, to make sure they're not expired. Right now I'm pulling them all from Procore, and they're expired in Procore. So we need to update these to make sure they're up to date and aligned. I think it's being tracked in another location, and I'm getting verification on that soon. The scorecard here is the team's measurements for how they want to calculate the data. They gave specific metrics, scored zero through three, for how each scorecard item should be scored,

[06:00] and what the weight is for each of those sections. We run these calculations across all of the data, and that gives us the scorecard report. You'll see that scorecard coverage is 71% right now. The reason is that the other 29% is all manual input through the SharePoint lists. Once we input that data, we'll be able to increase this. Right now it's looking at the safety incidents, observations, and so on. Those are some of the things we'll be able to move up, to get full coverage of all of the data once we train our users on how to use the SharePoint lists.

[06:45] Source coverage shows where all of the data is coming from, and where the cost codes and vendor mapping line up between the two systems. This is great for validating the data: making sure we map all of the projects to Sage projects, that all the cost codes are matched and appropriate to the division, and that we have all the data we need. This would be very good to dig into further to make sure all of the projects are mapped. We haven't mapped some of them from Outbuild, maybe because some are in pursuit and haven't turned into actual projects created in Procore yet. So that's kind of expected, but it's also good to review and

[07:30] dive into. Here we have a data quality report, but this specifically refers to the quality of the data we're pulling into this report. It tells you how frequently it's been run, any errors, and how it completed. Then it shows all the projects it pulls in, the invoices, and any gaps in the data it's found. This is great for identifying, for example, that we need to match some AR invoices to assign this dollar amount. There are of course some invoices here that we can review and assign. And then we have some of our details

[08:15] around prime contracts and crosswalks. When we need to dive into any data that's unresolved, this will show, for example, whether there's a Procore project that's not assigned to a Sage project. We recently cleaned all that up for one of the projects. And here are any record gaps we need to assign and associate. Right now, we need to assign some AP invoices to a Sage job. These are floating right now, so we'll need to assign them to a specific project. This is great to review to identify where we need to assign and associate data, or whether any information isn't aligned or calculated properly within the report. All of the

[09:00] data is calculated properly and shows the actuals, disregarding the unresolved records. So it's intelligent: it doesn't show incorrect numbers in the report because of unresolved records that aren't associated. That's very important. Next, we have a quality report. This Project Quality Plan pulls all of the data from Procore and Sage as well as the SharePoint lists, and it pulls in all of the data associated with the quality of each project. Much of this is pulled from daily logs, incidents,

[09:45] commitments, and observations within Procore. But much of it is being brought in from the SharePoint lists. Right now we have our monthly progress reports, and we have project managers and executives uploading and associating data that's not necessarily tracked in Procore or Sage. So we need some way to track it. Previously, we were manually creating an Excel spreadsheet, sharing it among the team, and having people update it. There was no validation of the data or any verification that

[10:30] the data was properly input or verified. So we have a list of lists here that tracks each specific input, and this is sent back to the Power BI report through our pipeline to display the data. The same goes for the Project Quality Plan. We have a set of SharePoint lists that users fill out each day, week, or month, whatever it may be, with their specific information. As you'll see, each report does different things. The Monthly Progress Report is really for

[11:15] identifying how the projects are progressing. This one is about the quality of how the project is progressing. You'll see we have all of our projects here. Then we dive into observations. These observations are pulled from Procore, and we can identify what needs to be opened, how frequently things are happening, and what we need to assign. Punch completion has all of the punch lists pulled from Procore. We have our submittals and mock-ups, so these are all the submittals we're pulling in, and the inspections being pulled. This is a great overview of how the project is progressing and any inspection

[12:00] items that need to be associated and assigned. Statutory gates: this defines the gates that have been completed for any requirements on the project, against the specific template we have. There are the trade checklists, to make sure all of the trades are assigned to the CSI code and that we're mapping everything appropriately to the specific tiers and any inspection logs and checklists we're reporting. Then we have a data quality page on this one as well: are we making sure all of this is properly mapped and associated? Are we missing any data? This will

[12:45] give a general overview of what information we need to assign, or anything we need to identify and collaborate with the team on, to make sure the data quality for these reports is best in class. Those are the two reports. I can show you one of the SharePoint ingestion lists here. They're pretty straightforward, but this one would be the wins. When it loads, we don't have anything entered yet, so it doesn't show anything. But what a user would do is come in here, click New, and enter all of this information: the project key,

[13:30] the win number, description, win type, key project, month start. We defined all of these fields and inputs, and we can change them as necessary. Each user would come in here each time, and any data that's not in Procore or Sage that we need to capture from users is entered here, and the user is tracked and associated with it. All of this then flows into the reports. So how do we get to this point? How are we ingesting all of this data? This gets a bit more technical and in the weeds, but in a sense, each report has a semantic model. The semantic model

[14:15] pulls in the data from these notebooks and brings it through different stages to clean the data, validate it, test it, and verify everything. When we look at the semantic model, it basically shows how the data flows through the different lakehouses and how we're transforming the data once we've ingested it through the notebooks. It pulls all of the tables and shows how we're assigning all of the data to each object and to the respective fields. This can get pretty expansive and robust. Of course, a lot of

[15:00] data is associated with many different things, so it's very important to assign and associate data to the respective fields. Then we have these notebooks that do the extractions from Procore, Outbuild, and Sage. We land it in our bronze lakehouse. We're also landing manual data, which would be the SharePoint lists the team is filling in. Then we bring this from the bronze to a silver lakehouse. Essentially, that's cleaning the data: removing extra spaces, or fixing any data that isn't formatted properly. And then from there,

[15:45] we feed this into the gold lakehouse. The gold lakehouse is really our source of truth. When we build it, we run checks against it, then we publish, and do the whole validation of everything. If you want to look at the raw data from the SQL database, this would be the best way to dive into it. Under dbo, you'll see the tables we have available. There are also views and functions. The views are for how we're viewing this in the Power BI report. But the tables we're pulling in here are where you can see the row and column values for

[16:30] the specific data we're pulling in. This is our project vendors, so we can see all of the information there. Same thing if we look at our projects: here are all of our projects. This is the exact data being pulled into the Power BI report, so this would be the best way to validate any specific data. If you need to export it, you can as well. This is a huge value-add if you need to look at any of the specific tables behind the report. From there, we have our gold lakehouse, then our ingestion pipelines,

[17:15] and then the reports we just walked through. Overall, that's the key functionality of everything we've reviewed. One thing I think is important to touch on is some of the key tools I've used to get started, and credentials and access to the solution: getting access to email, Procore, Sage, and the gateway; getting access to Fabric as well as the Key Vault. All of the keys for the credentials, like Procore and Sage, are stored in the Key Vault. So access to

[18:00] all of that was very important to get everything spun up. What I did was use Claude here, and in Claude we have MCPs. Within the MCPs, you'll see we have a Fabric MCP that we can reconnect to. This does all of the interactions: it's able to create the notebooks, upload things, and reassign and re-associate various aspects of the data. It's able to create, validate, test, and look up everything. There are other MCPs, but this one closed. What I can do here is, when we open Claude,

[18:45] we also leverage... let me pull it up here. My computer is being a little slow right now. So we have our MCPs here. We use our Fabric one, which interacts with Fabric. And then we also have our

[19:30] Azure plugin as well. This is to interact with our Azure instance for things like hosting or the Key Vault we're setting up. Additionally, we're leveraging the Power Automate MCP as well. There are some additional automations and flows we're building for specific tasks, like creating a project once a new project is won and setting it up properly. There are different automations, and we're using the MCP to interact with Power Automate. And one more thing I used: Claude has something called Claude in Chrome. It uses the Chrome browser to load

[20:15] the page. It can view it, click through, and test. This is super helpful because it can view the reports, validate things, test things, and click through. So those are a few of the tips and tricks, along with the data behind the Power BI reports and all of the capabilities we've implemented. The next step is to review the data quality and the unresolved records, answer a few of the open questions, and then provision the team to start entering data into these lists. Anyway, that's everything. Feel free to reach out if you have any questions or need anything. Happy to help

[21:00] and support as needed. Thank you very much for the time. Appreciate you. Bye.