Case Study:  Job Control from 20,000 feet above Connecticut

Do you ever get tired looking at boring business reports? Lists and lists of data that are difficult to read and even more difficult to make sense of. What if you could back away from your data and visualize it from 20,000 feet?

One way to achieve this perspective is to place data on a map. In fact, this is a common request we receive from our clients. One of our recent clients operates a business where work crews travel to various cities and perform many jobs while they are in the area. It can be difficult to know which crews are working where and what their status is at any given time.

Here is how we helped our client manage this situation:

Excel-Based Work Order Application

To deliver this custom solution, we worked with our client to create an Excel database application to manage their work order data. All work orders are received via Excel from various customers and are imported into this application using VBA macros. This application allows our client to manage these work orders from crew dispatch to invoicing.

SQL Database in the Cloud

The Excel application described above works in conjunction with a SQL database on Microsoft Azure. All application data is organized and stored in this database. Azure provides a very easy and inexpensive way to host a powerful database in the cloud.

Google Maps API and Google Directions API

One of the more complicated aspects of this application is that it uses Google to create an optimized driving route for a particular geographical region. This optimized route is used by site managers to efficiently travel to dozens of work sites to gather measurements and other job information. The route is also used by work crews to complete their work in an efficient manner. Google provides a “Directions API” which is utilized by this application to optimize the routes.

Google also provides a “Maps API” which is used by the application to geo-code each work location. The geo-code process provides the latitude and longitude of each work site so that it can be plotted on a map.

Power BI Maps

The final step in providing the 20,000 view of this data is to plot it on a map in Power BI. Specific data about each job is included on the job “pin”. When you hover over the pin on the map, you can see the job address, crew, job type, and job status. When new jobs are added to the database, new pins automatically show on the map. When jobs are closed, they are automatically removed from the map. Our client has this map showing real-time job status on a large monitor in their business office.

Summary

Displaying important data on a map can provide business leaders with new insights into their operations that are difficult to achieve with a standard report. One of our recent clients took this approach to help them manage jobs all over their state.

About the author: Brian Busscher is the Founder and CEO of Thesis Technologies which pursues a mission to “help you get your systems right.” If you would like to learn more about how the right business systems can help drive your business forward, please contact the Thesis Team at (888) 705-7253.

Find other posts here.

What Can We do For You?