Leveraging Standard Microsoft® Software to Manage EHS Compliance



Environmental compliance requirements at complex industrial facilities involve extensive data collection, review, validation, documentation, communication, and reporting.  Environmental staff members are often overwhelmed with daily scheduled and unplanned events. It is not uncommon for a plant to have numerous spreadsheets, each with multiple checklists for various types of tasks in different schedules, all of which must be updated for compliance certification on a regular basis.

Since the advent of Title V operating permit compliance certification requirements, many companies have also implemented expensive EMIS software solutions to manage and streamline the daunting compliance management process. Recent technology advances have resulted in the availability of more and more commercial-off-the-shelf (COTS) software applications developed specifically for EHS compliance, giving EHS compliance managers more options for information technologies (IT) solutions for their specific needs. One readily available but lesser known approach available to EHS is the leveraging of Microsoft’s standard software suite on a SharePoint platform.

Most companies already own and provide user licenses of these software programs to their employees under companywide Microsoft enterprise license agreements. By leveraging these standard programs, a solution can be developed to address many of the needs of both small and large compliance programs in a way that is customizable, scalable, and cost-effective.

Environmental Compliance Needs

As organizations begin to evaluate IT solutions for EH&S compliance management, the following are commonly requested characteristics for a system to streamline the compliance demonstration process:

  • Centralized system (typically via an internal web-based portal)
  • User friendly interface for collecting compliance data (i.e., data entered by operations personnel)
  • Clearly defined compliance tasks for the responsible person(s)
  • Easy reference to permit conditions and regulatory requirements
  • Centralized compliance documents and records library
  • Comprehensive compliance tracking calendar for assigned tasks
  • Compliance reporting functions
  • Interface with the plant process data historian to retrieve hourly process data required for emission calculations
  • Data quality validation to avoid bad results
  • Centralized emission calculation tool(s)
  • Emission calculations for specified averaging periods and at predefined schedule
  • Dashboard to display critical compliance limits
  • E-mail notification/warning to operations managers for open action items
  • E-mail notifications to the environmental department for deviations
  • Reporting results for required emission reports

The list may extend further for complicated compliance demonstration requirements. To meet all the requirements with COTS software applications can be costly; however, many of the task tracking processes can be achieved using the workflow system provided in the Microsoft SharePoint® system.

Software Technology Overview

SharePoint is a set of web-based development and management technologies that drives collaboration between workgroups. SharePoint can be tightly integrated with Microsoft Office® (i.e., Word®, Excel®, Visio®, Project®, and Access®) so that multiple users can simultaneously work in SharePoint-hosted documents, forms, and databases and handle tasks such as the collection, review, and approval of data, documents, and forms. The features used most often for managing compliance requirements are discussed below.

Control and Sharing of Regulatory Documents
Document management is the foremost feature provided by SharePoint. SharePoint document management provides “life-cycle” control of documents including how documents are created, reviewed, and published, and how they are ultimately disposed of or retained. Regulatory documents often require such capability. An effective document management system allows users across the plant site to control documents as follows:

  • Who can create and upload documents
  • How to store documents at each stage of its life cycle
  • How to control access to a document
  • How to move documents within a team of reviewers through the document creation, review, approval, publication, and disposition process
  • How to handle documents as corporate records, which must be retained according to legal requirements and corporate guidelines

Figure 1 shows a typical document sharing page in SharePoint with folders and controls very similar to Window Explorer. However, user can also easily search and control the document in SharePoint.

Sharepoint_Figure 1

Managing Compliance Tasks
Managing the numerous EHS compliance tasks is a daunting responsibility at most industrial facilities. There is a continuum of tools that have been used to manage these tasks from paper to tables, and lists in Word or Excel to centralized COTS systems. Within this universe of options, SharePoint task lists offer those unfamiliar with formal task management system with a relatively simple and cost-effective solution. The power of SharePoint task lists lie in their simplicity and flexibility to share and edit them via SharePoint or Outlook®. This means that all team members involved in compliance tasks, from Environmental and Operations, can monitor task status. SharePoint task lists feature the following capabilities:

  • Add, edit, or delete tasks through either a web page (SharePoint) or desktop application (Outlook)
  • Take tasks offline (via Outlook)
  • Automate notifications of task assignments and updates
  • Attach documents to any given task
  • Add specifics and instructions to any task
  • Create simple, web-based reports
  • Publish basic Gantt charts
  • Schedule and monitor tasks in calendar view

Figure 2 shows an example compliance task list page using the SharePoint “List” feature. Under the List tool, users can easily filter tasks such as by task owner, due date, or other criteria.

Sharepoint_Figure 2

Collecting Operational Activity Data
Microsoft publishes InfoPath®, a powerful web-based form builder, as a tool for designing and creating forms that can be integrated with all web-based software. InfoPath is included in the professional version of Microsoft Office. This application allows nontechnical users to build and deliver methods to collect and manage data. InfoPath provides greater functionality and better control of the data collection process over other applications such as Word and Excel. In fact, Word and Excel files can be easily converted to InfoPath to create more robust data-collection forms. InfoPath uses an XML-based data format that is extremely useful for additional applications to read and process the form data. The form can easily be published on SharePoint to collect and send data in readable formats via e-mail to SharePoint. Most of these tasks can be accomplished with no compiled coding.

Two significant features of InfoPath are the rich rules and validation components. The application allows the form designer to view and manage common interface controls. For example, one can design data fields with rules to check the contents, process simple calculations, and compare with limits, to immediately let users know if the data passed or failed predetermined specifications. A designer can also build forms to collapse sections of the forms for repetitive data or conditional review for different users.

InfoPath connects natively to SharePoint in multiple ways. It can read data from SharePoint lists quickly and easily, query live SharePoint data, and return results to the form to process a variety of options. InfoPath forms can be stored locally in SharePoint document libraries in the same way as any other type of document. They can also be made the default template for a given content type. Users can start a new form from a list to automatically create a new record using these custom forms to open, complete, and save locally in the library for record processing. Via SharePoint, the EHS department can centralize data collection forms. All the data collected can be stored with databases, typically an SQL database, that can be used for compliance demonstration.

Figure 3 presents an example data collection form built with InfoPath and published in SharePoint. The example shows that a company has converted a previous paper form to an InfoPath form that can be accessed via SharePoint to collect incident-related data for recordkeeping and reporting purposes.

Sharepoint_Figure 3

Calculating Emissions and Managing Emissions Sources
SharePoint can accommodate all standard calculation tools built in Excel and Access. The web platform is also ideal to host and/or connect with emission calculation systems that are developed to integrate with the process data historian. A common approach is to publish Excel workbooks to SharePoint so that other users can view its data in a web browser without opening Excel. In addition, by setting some Publish options, users can emphasize specific parts of the workbook in the browser. All Excel calculation functions can be performed as normal when users update input data via the web. This is also a great way to display Excel charts and tables on a SharePoint page.

Building the Site Portal and Dashboards
SharePoint provides many design templates that can be used by organizations to build and customize their sites without any programming. Within the site portal, companies can add their logo and select among SharePoint options to accommodate their needs and processes.

Like COTS solutions, SharePoint enables users to create and use dashboards that provide up-to-date information in a centrally managed, easily accessed location. Dashboards can be customized for an individual (e.g., environmental manager), a user category (e.g., the environmental department), or the entire site. SharePoint offers several tools for users to configure dashboards. Figure 4 shows an example of dashboard elements. Typical functions such as charts, calendars, open action items can all be built on the portal from data collected from lists, library, and other integrated tools. One can also embed Google® map functions for an area view of the sources and locations of interest.

Sharepoint_Figure 4
Conclusion

Many companies have already standardized their IT solutions using Microsoft products. Integrating these Microsoft programs offers a customizable, scalable, and cost-effective alternative to large, expensive commercial products. Using SharePoint, EHS managers can build powerful compliance solutions to streamline data collection and reporting. T3 is working with several organizations to leverage Microsoft products for environmental compliance. To learn how this approach may provide value to your organization, contact Tom Grosch at tgrosch@trinityconsultants.com.

Based on an article presented at the October 2012 AFPM Environmental Conference in Denver, CO.

Microsoft, SharePoint, Microsoft Office, Word, Excel, Visio, Project, Access, Outlook, and InfoPath are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.  Google is a registered trademark of Google Inc.