Google Apps & Microsoft Azure in field services

 

Google App Script Solution Architecture

Customer Background

Country of deployment: USA

Industry: Field Services Management

Client operations: Site & plant maintenance, access chamber placement, trenching, fiber back-hauling, fiber splicing, structured wiring, electrical infrastructure maintenance.

Situation

In phase I of this project, Allied Consultants had delivered the client with a bare-bones field service application. The PoC was delivered within 6 weeks of starting the project (the PoC for field service app can be accessed here). As per client’s requirements, the initial tablet optimized UI (User-Interface)  was developed in Google App Script. It had a dashboard that contained drill-down reports, workflows and alerts. The data was stored into Google sheets in which 20-30 thousand field inspections could be saved. However, the reporting requirements of the client increased as the business expanded  into different states of the East Coast. In the existing solution, the scalability and integration of database (Google sheets) with back-end software (Office 365) became a major challenge. Thus, the existing solution had to evolve.

The challenges boiled down to:

  • Lack of real-time reporting by field staff to the back-office, at scale
  • Lack of real-time communication between field-inspectors and back-office staff for just-in-time assignments
  • Integration of  a scalable cloud development environment with Office 365 for seamless real-time data retrieval, collaboration & reporting

Solution

Allied Consultants proposed the evolved solution in the form of three-tiered architecture containing:

    • User Front End on Google App Script (keeping the existing tablet-optimized app)
    • A database hosted on SQL Azure (shifting the data from Google sheets)
    • Reporting on Office 365 using Business Connectivity Services

Front end: The field service application was developed on the Google Apps platform using the native Google App Script technology. Google App Script was chosen because it provides a Rapid Application Development platform using HTML5, JavaScript & CSS 3.

Back-end: The back-end was an SQL Server instance hosted on Azure cloud.  The interface between App Script & the SQL Azure DB was App Script’s native JDBC API.

Reporting end: While the field workers were using their gmail accounts for authentication, office workers were using Microsoft Outlook as the email within the Office 365 environment. The administration & reporting interface for the field service app was exposed on Office 365 using the OOTB (Out of the Box) SharePoint business connectivity services architecture.

Since, the client had employed hundreds of subcontractors, each subcontractor could  sign-in to the company’s tablet based cloud-application using the native Gmail account. The field service application allowed the back office staff to allocate jobs to subcontractors and field inspectors based on availability and skill set, which can reduce the chances of jobs being missed.

Benefits

The use of Google Apps, Azure cloud and Office 365 allowed the company to maintain a real-time reporting & analysis environment. This helped the back-office staff to plan the optimum route for their employees, again helping to save time and money. This architecture is easily scalable for similar workloads in field services environments. Anyone with a valid Gmail account can become your field inspector and utilize the field service application to send data to the back-office workers. Some of the primary benefits reaped by the customer were:

  • Rapid allocation and re-allocation of resources through real-time communications
  • Reduced the operational cost of adding each new subcontractor/field worker
  • Threefold improvement in workforce efficiency as they readily access job reports, repair reviews and compliance indicators
  • Easy scalability of solution architecture

Technologies used in the solution

  • Google Apps
  • Office 365
  • SQL Azure
  • Microsoft Azure
  • JDBC API
  • SharePoint Online
  • SharePoint Business Connectivity Services