Skip to content

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

null

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.

Powered By GrowthZone
The West Coast Chamber often takes photographs & video during our events for use in brochures and on the Web. 
By registering you agree that the Chamber may use any image of you that might appear in photographs or video taken at a Chamber event. 
Scroll To Top