Data Integration: Automated Budget and Trend Analysis

There is an idea out there that all of us have heard or even been a part of on certain projects or with our respective employers. The idea is this: If you are a General Contractor and receive a building information model from the consultants working on your projects, you can get the budget automatically with not much leg work.

At this point, we all understand how important this is: the ability to quote projects quickly, accurately, and with less margin of error.

There are a few ways to get this done. We have seen some well-sophisticated software (i.e.: RIB MTwo) that brings all the parts of the puzzle together: Cost, quantities, risks/opportunities.

Another alternative is to achieve the same result through integrations. Below is a recap of our experience deploying certain integrations between existing systems our customer was using to achieve the desired results of understanding budgets, trends, risks, and opportunities.

Our Challenge

A few months ago, a major General Contractor (GC) came to us with a problem we see with most GC's: data distributed in different software platforms.

That data needed to be gathered and massaged to produce a system that allowed our customer to visualize and analyze their projects through dashboards (graphics, tables, and KPIs). For this project, the customer was interested in Voyansi building dashboards within Power BI but it could have been Looker, Data Studio, or any other data visualization platform.

Oftentimes, we are brought into conversations with customers that are using different internal systems with no way of connecting data. We are not interested in proposing additional costs for our customers so when our customers already have licenses for certain pieces of software, we will work with what we got.

As you all can imagine, the data lived in silos on a few different platforms. In this particular case, data was distributed across the following software:

• Autodesk Revit + Autodesk Assemble: Quantities

• Trimble Winest: Unit Costs

• Smartsheet: Risk and Opportunities and Owner's Budget

An important consideration: for all those who are not familiar with the terminology; there's a difference between data and information in the database world. While data refers to raw facts, information refers to the result of data processing to reveal its meaning.

The customer was trying to move away from a very manual process that involved exports from each of the platforms mentioned above. Each project had the data exported and then it was manually entered into Excel for manipulation. This manual process lacked one important component: history. There was no way to look at similar past projects and use past data to make projections.

The challenge was clear: Automate this process but also make it consistent, reliable, and include historical data as well.

The Solution

The first challenge was to identify the 'key'. In relational databases, the key is the one thing that allows you to link one dataset with another dataset. For example, a CMU wall in Revit needs a way to connect with the CMU unit cost in Trimble Winest.

We identified the Classification System Uniformat II to be the key we were looking for. Even though the customer was not using it on this particular project, activating it in Revit, Assemble and Winest software was pretty straightforward.

This was an important step in this project's development. Without this key, we would have had to create a common parameter/link between the different programs so that we could easily connect and visualize different tables within Power BI.

The next step was to connect directly to the databases of each software to eliminate the manual export. For this particular project, we chose Microsoft Power BI as the business intelligence tool (all reports and dashboards would be created on this platform).

For the initial proof of concept, we connected directly from Power BI to the databases of each software. After a few iterations, we were able to connect and pull the data "live". The key (Uniformat II classification) was used to connect the different data from the different sources and then the data was ready to be processed.

At this point is important to mention that the final solution included a Data Warehouse. The reason is explained below:

Application Software's databases are considered "operational databases". These are databases designed primarily to support a company's day-to-day operations. In contrast, "analytical databases" focus primarily on storing historical data and business metrics used exclusively for tactical or strategic decision-making.

Below is a visual that explains how we were able to "move" data through the system in a much more automated fashion that allowed our customers to interact with their project data not just at one point in time, but for the entire lifecycle of the project.

Power BI is a great tool for visualizing and interacting with numerous datasets. Below are a couple of sample images of the dashboards we created for our customer that achieved the same exact result as the manual Excel workflow but in an entirely automated way.

Summary

As we all know, BIM equals data, and with some processing, that data becomes very valuable information.

The promise of "automatically" processed budgets can become true. You just need to find the right approach based on your current technology and staff expertise. You can opt for an "all-in-one" platform or the integration of your current data as we showed above.

At Voyansi, we are not only experts when it comes to working with databases, we are also experts in the construction industry. We understand our customer's needs, the software used, and have solved most of these problems before.

Reach out to us so we can help you build the integrations and overall solution needed so that you can create more accurate bids faster. We are here to help you and we want you to win more projects!