Based on your business objective or needs, you may have decided to monitor certain construction KPIs. As a theoretical exercise, that may sound relatively straightforward. But, as a practical matter, how do you get data for your KPIs? Where do they reside and what does it take to bring them all together so that you can compute and monitor your KPIs effortlessly at all times?
It is most likely that the data you need to build your KPIs reside in different locations. For example, your accounting software has all the financial data while your project management software holds all project related data. Also, a lot of valuable information is buried in documents and emails and never get to see the light of the day. How do you bring together data from different silos so that you can monitor your KPIs using a single dashboard?
The process of bringing together data from various sources, or data aggregation, is perhaps the most technically challenging aspect of putting together a dashboard for monitoring KPIs. It is more so today than in the past because of the growing fragmentation of software solutions.
The challenge of fragmentation gets even more complicated in the construction industry where each project involves a new set of stakeholders that come with their collection of construction software, datasets, IT infrastructure and IT policies. Aggregation of data from all these stakeholders can be very challenging.
In this post, we will go over some of the methods that can be used to overcome this challenge.
Application Program Interface (API)
Application program interface (API) is a very common method used by software systems to communicate with each other. APIs are software interfaces that allow one application to communicate with another, even over the Internet, to exchange information. The use of APIs, when available, provide an ideal method for retrieving data from a software application for a dashboard.
Most of the modern day software make use of APIs. It is particularly the case with those that are web or mobile based applications.
Although APIs methods are industry standards, the details of APIs are mostly proprietary to each vendor. Some vendors make those APIs available for use by 3rd party software. Others keep the aspect of their APIs private and may even restrict their usage.
API offers the “cleanest” method of data aggregation because they are designed to facilitate data integration. However, using APIs does not mean that the method is going to work indefinitely since software vendors can sometimes change their APIs. For all the methods outlined here, it is important to make sure that they get validated against a known set of data.
Scripting is a computer language that supports programs written to automate the execution of tasks on computers that can usually be performed by people. Such tasks include accessing a web page and scraping it to retrieve data.
Automatically retrieving web pages and scraping it for information is a practice as old as the web itself. It is a practice routinely performed by search engines such as Google and Bing and can be used to retrieve data automatically from a web application.
With this method, one can write a “script” in one of many popular scripting languages to retrieve relevant web pages and scrape it to get the data of interest. Once extracted, the data can be exported to a file or a database. A script can be run as many times as desired and triggered by another software application such as a dashboard application.
One benefit of this approach is that it does not rely on a vendor’s API for retrieving data. Much like the API method, this method allows integration with your dashboard application so that the entire system can be fully autonomous.
On the other hand, this approach has some limitations. It may not work if the web application makes use of certain plugins such as Adobe Flash. It also runs the risk of not working correctly if the software vendor makes changes to the web pages being scraped. Therefore, each time you use this use method, it is important to validate its correctness by checking the outcome against some known data.
Direct Database Access
Virtually every modern software uses a database to store its data. This method is valid whether you have a Web-based software, client-server software or a “native” application running solely on a single computer. If you have direct access to the database used by your software, your dashboard application most likely can retrieve data directly from it.
Whether or not you have direct access to the database is based on the type of software you use. If you are using a cloud-based software, it is unlikely that you have direct access to the database. However, if it is a product that you purchased and host on a server you have access, you most likely have direct access to its database.
One benefit of this approach is that it does not rely on a software’s API or the UI (user interface) for retrieving data. It relies on the structure of the database and vendors rarely change it. Therefore, among all the methods described here, this method is by far the most stable one. Much like the previous two methods, this method also has the benefit of integrating seamlessly with your dashboard so that the entire system can be fully autonomous.
A key challenge in this approach is that it requires knowledge about the structure of the database, commonly known as “schema.” All databases store data in a very structured format of tables, rows, and columns, and software vendors do not usually make this information available. However, with some investigation of your database, the structure can be understood fairly well.
Browser automation is a technology that can – automatically and repeatedly – launch a website, sign in, navigate to the desired page, and retrieve data from the page, much like one would do manually. The technology is widely used to test Web-based software and can be used to automate data retrieval from web applications.
Browser automation is relatively easy to use. It is as simple as setting up a workflow to access the desired data using a web browser and running the workflow automatically to retrieve and record the data. To set up the workflow, you need a tool to record your interaction with the web application as you use it. Once the workflow is recorded, you can run it automatically as often as you want.
As straightforward and easy to use as it is, browser automation has several limitations. Unlike the API and scripting methods described before, it is more challenging to integrate this approach with your dashboard application. Furthermore, this method relies on the web application’s user interface. When vendors update their software, the workflow may need to be re-recorded to use the new user interface. However, this can be an effective method when the others described above are not available.
As we all know, the vast majority of data that can be turned into useful information for you construction dashboard is not located in one simple database or a spreadsheet, but distributed across a large number of files. For example, a large number of RFIs and change orders that each project generates have a vast amount of data. Retrieving that data to create KPIs can provide insights into how the project and stakeholders are performing. One of the ways to get such raw data to create KPIs is by scanning all the relevant documents and extracting pertinent data.
A key benefit of this approach is it relies on information that is already available and does not require a change in business processes. An additional benefit is that it does not rely on interfacing with 3rd party software applications, which can be a bit tricky as described above.
This approach is not without limitations. It often depends on the format of the document where the data resides. It assumes that the documents, such as an RFI, always use the same format so the software can extract the information. Unlike the other methods where you can validate them against some known data, this approach requires a more complex mechanism.
It is quite often the case that construction companies, like many other companies, keep track of their KPIs in spreadsheets. Although spreadsheet may not be an ideal platform for storing and presenting your KPI dashboard, it is a practical solution for collecting data from various sources in your organization.
One of the steps towards building a dashboard is to retrieve data from these spreadsheets into a unified dashboard. Dashboard applications can be designed to import data directly from spreadsheets, either automatically or through portals for stakeholders to upload the spreadsheet.
One of the benefits of this approach is that it allows one to continue to use the existing method of data collection through spreadsheets. Stakeholders who are supposed to provide information do not necessarily have to make any changes in their methodologies.
The growing trend towards siloing of software solutions means deeper fragmentation of data for everyone. In the construction industry, this problem is even wider because each new project brings a set of stakeholders with their own baggage of data fragmentation. This fragmentation makes it challenging to turn the vast amount of data into useful information. However, several techniques can be used to aggregate data and build a dashboard that provides valuable insights.