From manual reports to Microsoft Fabric

[00:00] All right, so today we are going to be reviewing the integration between Procore and Power BI: essentially how we are loading our data in from Procore and storing it in a data lake, i.e. Microsoft's lakehouse, then doing our transformations, and ultimately displaying it in Power BI. To get started, we have our semantic model, which is mapping all of our data together. Then we wrote some notebooks. Essentially, these notebooks do all the API requests to extract from Procore. That then goes to our bronze lakehouse, where it's just raw data. We have a land manual step. We're essentially taking in a CSV

[00:45] file for project updates that users need to input manually. This also has an integration with a SharePoint page that users can input into as well. I'll probably show that later in another video. Then we take the bronze and convert it to the silver lakehouse, which is basically cleaning the data a bit more. And then we seed this into our gold, and we build our gold database. This is our golden set. It's verified, it's validated, and we've gone through and done all of our checks on the data as well. Then we run our query. This data is stored in our lakehouses, and then we have our pipeline. We're integrating Sage as well for some

[01:30] financial data. This is direct via Sage 100 Contractor. It's read-only right now because I'm getting access, and once I'm provisioned access I should be able to pull in that data. And then we created our monthly progress report from there. So, walking through it, we have our Extract Procore notebook. This references all of our credentials to make API requests to the endpoints. What I did is I provisioned an entire file that does the authentication up front, and then this file goes through and provisions every single endpoint that we have access to. So we're not writing one request per notebook or anything. It's essentially going through

[02:15] with one file that runs the request for every single data point that we need. Then we take that and land it into our bronze. We convert that into our silver. Then we take any dimensions that we have, build our gold, and run the data quality gate, which validates all of our data before it's displayed. We do this to ensure that the data we pull in and run through this process is what's displayed in the Power BI dashboard. We don't want to pull in some data and then have it affect other data. So we validate the data on every single push, and then we know the dashboard is live and functional and all the data is proper and validated.

[03:00] We then write some SQL commands to send this into our lakehouse. Essentially, this is taking the raw data from the endpoints we extracted and dumping it into our lakehouse and storing it for reference. Once it lands in our lakehouse, we do have our manual input. This is specifically a CSV that we're allowing, but we also have a SharePoint page that is mapped to the exact same data fields and validates inputs as well. The one thing with a CSV is that there's no data validation, so a user could input a field wrong or incorrectly. Whereas with SharePoint, it'll allow

[03:45] us to validate some of these input fields that are provisioned here. A user would come in here and fill in all of these fields, and then it's loaded into our lakehouse, and all of that data is pulled and associated on a per-project basis. It also allows multiple users to enter data, log it against a project, and extract it for that period. We then bring this into our bronze database. This is just raw data. As you can see here, there's an entire payload, and it's pulling in all of the data before it's cleaned. Then we convert this from bronze to silver, which is basically trimming and looking through any additional data, formatting it

[04:30] properly to extract all the fields that we need. And this is pulling in all of our data through SQL, through Sage, as well as our Procore instance. We will add additional solutions soon, and that will just be an additional endpoint in the notebook for that solution, then pulling that into the lakehouse, cleaning the data, and setting it up again. So then we feed this into... we pull in all the fields or tables that we need from our previous silver, and then we build out our gold, seeding it with all of our data. And once it goes through, we build it. This build runs a validation. So when we go

[05:15] through and build this, we're validating and looking through all of these endpoints. This one's quite large because it's pulling in every single table that we need. Then we run our checks on this. The goal is to validate all the data before it goes into the dashboard to ensure that it's all appropriate. So we have our script here that runs. Once that happens, we run our query. This query goes through and maps all the different fields, and does any calculations or builds any specific fields that we need. That data is then loaded into our gold lakehouse. So this is all cleansed, and

[06:00] it's clean now for all the different fields that are specific to the tables necessary for our view. Then what happens is we go through and map all of these and associate all the fields. As you'll see here, one of the main ones is the due date. The dates that we're pulling in, we have to normalize all of those. And then of course, when we go into dim project, that is associating every single project. So whether it's Sage or Outbuild or Procore, all of them need to reference the same specific project. And then we pull in all those tables and map all the data to each section. Of course, there are

[06:45] quite a few fields here. If we zoom out, all of these need to be mapped and associated to the correct location and data. Once we have all that data, we bring it in, and now we have our portfolio. This pulls in our projects: what we have currently in contract and new builds. We can then select a specific project if we want, and this will pull in the data specifically for that project. Then we created a few additional pages. This one is an overview, showing a broad overview of the project: budget, spend, how much they've paid, any general numbers. Then we start getting further into the financials. This pulls in all of your

[07:30] budgets, what you've spent to date, what's completed, and any variances. Then we get into the schedule. We're not pulling the data from Outbuild yet, so that will be coming up here, but this data is from Procore, where it's pulling in different submittals and information for our milestones. Then we have our safety and quality. These pull in endpoints from Procore again: punch items, any incidents, and daily logs. Finally, we get into billing and retainage. This goes through our contract values and shows any outstanding invoices and contracts

[08:15] that we have left. We then look at our direct costs, which show our cost breakdowns by cost code and the vendors associated with them. And then we go into our vendor insurance, making sure that all of our vendors have insurance, what certificates we have on file, and whether we need to gather any more. Then we go into a scorecard. This is scoring the data that we have in here and what coverage of data we have. It's 59% because this is currently the data that we've pulled in from Procore and Sage. Once we start filling in additional data, like the manual log with the CSV where users input their data,

[09:00] that's data we can't get from any solution; it's more user-based. As well as that, we'll start pulling in data from Outbuild and a few of our other solutions, and this will start showing higher coverage. And then we have our source page, where we're seeing where we're pulling our data in from and making sure that it's all validated. From there, that really showcases the general overview of what's been developed: pulling the data in from Procore and Sage, extracting that data and formatting it in the data lake, running it through validation, storing it, associating it to its correct areas and

[09:45] project IDs, and then ultimately displaying it in the Power BI dashboard. Of course, if there are any other questions or information you need, feel free to let me know. Happy to help. All the best. Thanks.