Tag Archive for: Compliance

Unified EHS Platform: Enter Once, Report to Many

Is your organization still using multiple software systems for EHS&S when you can (and should) be using one robust and unified platform? Unify your compliance, sustainability, water, air, and environmental data with Locus’ cloud platform for EHS&S. It’s easier, cheaper, and more efficient.

Unified EHS Platform - Infographic

With Locus Platform, you can easily and securely feed all of your different EHS data sources to the cloud via a wide array of import options. You will then be able to analyze and report to virtually any regulatory agency, meeting any specific requirements they may have. We believe in making EHS compliance simpler.

Contact us to see Locus’ Unified EHS Platform in action

    Name

    Company Email

    Phone

    Tell us about your company's needs

    Locus is committed to preserving your privacy.

    Indirect Impacts of COVID-19 on EHS Industry

    For the better part of 2020, it’s safe to say that predominant changes to our daily lives have been brought on by COVID-19 and the associated response measures. This is certainly true for those of us working in the EHS field. EHS workers have an active role on the front lines, preparing our workplaces with new safety measures, including social distancing signage, training, and personal protective equipment (PPE).

    Impacts of COVID-19 on EHS | Locus

    Beyond those direct response actions, the realities of the ‘new normal’ have already impacted how other compliance and sustainability programs are implemented.  And for good reason… many of the routine activities like inspections and onsite data collections now have a new safety issue to consider. Even with all the new protective measures we’ve implemented to address this pandemic, there remains some unavoidable added health risk caused simply by staff presence and interaction. For activities that are not mandated by a permit or regulatory requirement, the benefit of continuing those activities must now be weighed against the added health risk.  For example, a daily waste inventory walkthrough may have been a standard routine for many EHS managers to collect data on waste generation as a key performance indicator. The value of that data collection effort may now be called into question, since it may increase travel and contact between staff. Depending on the value of those optional activities, some may be temporarily suspended simply because they are not worth the additional risk to health and safety 

    For compliance requirements, this situation is more complex, due to the involvement and oversight of the applicable regulatory agency. The ultimate decision about whether a compliance activity must proceed generally lies with the regulator. The majority of EHS managers initially proceeded with meeting all their obligations without any changes.  Although there are many applicable state and local ordinances and shelter-in-place orders related to COVID-19 that prohibited normal business operations, most of them include exceptions for activities that are ‘essential’ or required to maintain compliance with other regulatory programs.  Public agencies have made very few blanket decisions to waive requirements for regulatory programs, even when they conflict with those local ordinances.

    Manager in hardhat looking over his factory-Locus automation and sensors solutions prepare your organization for the Internet of Things

    Despite the lack of a uniform response from agencies about whether or how compliance programs should be modified to accommodate COVID-19 precautions, I’ve observed several cases where regulatory staff have been given some level of authority and discretion to suspend or modify requirements. This is happening at federal, state, and local levels for various regulatory programs ranging from Superfund to GHG programs to land use covenants.  I’ve experienced required deadlines delayed on remediation projects, modified approaches accepted for health risk mitigation, and on-site inspections postponed or drastically modified to accommodate social distancing. Any of these changes would have been unthinkable just a few months earlier.  But now the regulators are seriously considering whether the continued enforcement of these requirements would create a potential health risk, and how their agency would defend their decision if the implementation of their requirements impacted someone’s health.

    This ad hoc approach to compliance modifications brings its own new challenges for EHS managers, most of whom have detailed programs to track their efforts and ensure they stay on top of all the applicable compliance programs. Most of the regulatory programs that we work in have been in place for many years or decades, so the systems we’ve built up for those programs have been operating with minimal deviation for a long time.  But now, in addition to the original set of requirements we’ve been implementing, we have new modified versions to track. In all the cases I’ve observed, the original requirements aren’t officially edited by the agency. Rather, the agency staff have issued temporary amendments in the form of a letter, memo, or email.   So EHS managers will need to maintain the original requirements as well as the approved modifications in these various formats. Regulators are still planning that eventually these COVID-19 precautions will be lifted, so they can get back to the ‘old normal’ with the previous requirements we’ve implemented for years. This means that we can’t just overwrite the requirements in our compliance program, so we stay prepared to revert to the original official requirements if/when that happens.

    Engineer with tablet and oil rig tower- Locus software solutions for the Energy, Oil & Gas industries

    The long-term impact of these compliance modifications is yet to be seen.  The COVID-19 pandemic has forced more thought to be put into the cost/benefit of routine EHS activities. This is true not just for EHS managers but for regulators as well.   

    Since many EHS compliance programs have been largely unchanged for years, this is a rare opportunity to rethink or update those requirements. Technology has advanced significantly since many EHS requirements were written. This technology offers better and safer methods to achieve the same objectives. For example, I’ve attended several remote EHS inspections over the past few months, which were previously conducted in person. And after those inspections were completed, I can’t think of anything that was reduced or lost in terms of oversight.  For some facilities, I’ve also seen remote automated monitoring used in place of manual field measurements, where it was previously only considered supplemental to the required manual data collection. Although the regulations technically required this work to be done in person, the remote versions were just as effective, and completely avoided the added health risks associated with physical gathering and travel.

    So instead of wondering ‘When can we go back to the old normal?’ we might ask ‘Should we go back to the old normal?’  The regulatory programs we work with were designed to be protective of human health and the environment, but they were also mostly developed when things like handheld phones with live video were present only in science fiction.  Obviously, these technologies are not new anymore, but this situation has provided an unprecedented opportunity to implement these alternatives, and ultimately confirm that they can be just as protective as the former methods they replaced.  In addition to the cost savings that these options provide, there is a very real safety concern that they circumvent. And while cost-effectiveness is usually a difficult point on which to drive regulatory change, a safety issue is harder to dismiss.

    EHS Hardhats and Jackets

    While it still may be a while before we reach the end of this pandemic, there’s a lot we have already learned about how resilient EHS programs can accommodate this kind of major event. If we use this opportunity to engage with regulators, and closely review and update our programs, there’s no doubt they will only become stronger and better suited to the modern workplace and way of life.

    [sc_button link=”https://www.locustec.com/applications/ehs-compliance/” text=”See How Locus EHS Software Helps” link_target=”_self” centered=”1″]

     

    5 Common Compliance Issues for EHS Managers

    At Locus, we understand the unique requirements of EHS managers. More than many, EHS managers are dealing with a wide range of duties instead of a few pointed ones. With so many responsibilities, it can be hard at times to stay on top of your organization’s  EHS needs. In this blog we highlight a few common compliance-related issues that should resonate with most EHS managers and the steps we’ve taken to help you with them.

    Regulatory Change Alerts

    The worry of missing a regulatory change

    They say it takes a village to raise a child, but it also takes a village to keep up with your organization’s regulations. If you are dealing with compliance, then chances are you’ve not been the first to know about a regulatory change, or you’ve found out about one later than you would have liked.

    When you’re getting notifications from OSHA and the DOT and you’re checking specific permits and getting letters and emails about changes, sometimes it can all be too much. With Locus, you have the added benefit of an extra set of eyes, well… multiple sets of eyes. Our team keeps up with every rule and regulation used in our applications to further assist you with the breadth of information you have to manage. Locus EHS software is also integrated with RegScan, giving users seamless real-time access to current EHS regulations. This will allow Locus users to customize a watchlist in RegScan to quickly and readily view EHS regulations relevant to them.

     

    Low maintenance costs

    Managing maintenance costs

    When you have to worry about ever-changing costs that touch several parts of your business, the last thing you need is a gated product update from your EHS software vendor. With Locus’ SaaS model, you see reduced implementation costs and no costly upgrades – everyone is on the same version. And since everything is in one place, you have a reduced amount of wasted time finding information and making it actionable.

     

    Data security - AWS - cloud

    Being cognizant of your data security

    EHS managers deal with sensitive data, ranging from social security numbers to workman’s comp issues. Not taking proper care of this information can be anything from a PR debacle to a legal battle. With Locus, you have the peace of mind in knowing that your data is stored in entirety on the most secure cloud, Amazon Web Services (AWS). Not only that, but you have extensive security and admin access options, so you can have the relief in knowing only those with privileges can see certain information.

     

    Quick access to information

    Quick access to stored information

    Whether you’re looking for purchase documentation of PPEs or you need to reference yesterday’s GHG numbers, you need access to that data without having to wade through multiple applications. And with all of your data stored in one secure repository, not only can it be accessed quickly, but it can be incorporated with other tools like automated reporting.

     

    Compliance data consolidation

    Consolidation of compliance data

    Are you still dealing with a different filing cabinet or file folder for each type of compliance? Not having your compliance data consolidated into one application means wasted time and time spent re-entering information (possibly incorrectly). Locus combines water, air, hazardous waste, DOT, PPE, workman’s comp, incidents, and more into one streamlined application to help with your organization and efficiency.


    We are determined to support the needs of the user, you, first. By focusing on product development and customer service first, we feel that we have created a software as a service model that is both flexible and time-saving. If you are experiencing any of these issues with your current provider, we ask that you speak with a Locus representative today for a consultation or in-depth demo of what we can offer.

    Contact us to see our Compliance app in action

      Name

      Company Email

      Phone

      Tell us about your company's needs

      Locus is committed to preserving your privacy.

      Top 10 OSHA Cited Violations of 2019

      OSHA has released their most cited violations of the 2019 fiscal year, and perhaps unsurprisingly, the same mistakes are being made year after year. They are:

      1. Fall Protection – General Requirements (1926.501)
      2. Hazard Communication (1910.1200)
      3. Scaffolding – General Requirements (1926.451)
      4. Control of Hazardous Energy – Lockout/Tagout (1910.147)
      5. Respiratory Protection (1910.134)
      6. Ladders (1926.1053)
      7. Powered Industrial Trucks (1910.178)
      8. Fall Protection – Training Requirements (1926.503)
      9. Machine Guarding– General Requirement (1910.212)
      10. Personal Protective and Lifesaving Equipment – Eye and Face Protection (1926.102)

      With over 30,000 cumulative violations for the top ten alone, and the same mistakes being cited repeatedly, there is an obvious need for an EHS software solution that provides a number of tools to prevent these missteps from being made. From configurable smart notifications to follow-up assignments when accidents, near misses, or when other incidents are logged, Locus EHS&S compliance software offers assurance that your safety procedures can be followed promptly and correctly.

      [sc_button link=”/applications/ehs-compliance/health-safety-incidents” text=”See our Health & Safety App” link_target=”_self” color=”#FFFFFF” background_color=”#52a6ea” centered=”1″]

       

      Top Enhancements to Locus EHS Compliance Software in 2019

      Let’s take a look back on the most exciting new features and changes made in Locus Platform during 2019!

      [sc_icon_with_text icon=”tasks” icon_shape=”circle” icon_color=”#ffffff” icon_background_color=”#52a6ea” icon_size=”big” level=”h3″]

      New Task Types

      Two additional types of task periodicity have been added: Triggered tasks, which allow the automatic creation of a Task based on the creation of a triggering event (e.g., a spill or storm event), and Sequenced tasks, which allow the creation of a series of tasks in a designated order. Learn more about our compliance and task management here.[/sc_icon_with_text]

      [sc_icon_with_text icon=”mobile” icon_shape=”circle” icon_color=”#ffffff” icon_background_color=”#9ac63f” icon_size=”big” level=”h3″]

      Mobile Form Builder

      Users can now create a mobile version of any data input form. Every form in the desktop platform can be mobile-enabled, so you can introduce new ways of streamlining data collection to your team.[/sc_icon_with_text]

      [sc_icon_with_text icon=”workflow” icon_shape=”circle” icon_color=”#ffffff” icon_background_color=”#3766b5″ icon_size=”big” level=”h3″]

      Process Flow

      ‘Process Flows’ have been added, which guide users in completing processes following a simple step-by-step interface.[/sc_icon_with_text]

      [sc_icon_with_text icon=”facility” icon_shape=”circle” icon_color=”#ffffff” icon_background_color=”#52a6ea” icon_size=”big” level=”h3″]

      Expanded Facilities Management App

      Our expanded Facilities Management App is designed to map at the enterprise level showing all locations, navigate your facilities hierarchy to review information and quickly take action at every level. Locus Facilities is a comprehensive facility management application that aims to increase the efficiency of customer operations and centralize important company information.[/sc_icon_with_text]

      [sc_icon_with_text icon=”settings–configuration” icon_shape=”circle” icon_color=”#ffffff” icon_background_color=”#9ac63f” icon_size=”big” level=”h3″]

      User Configurable Dashboards

      Users can choose from existing portlets (found on the dashboard pages) to customize their landing page to their unique needs. Create custom dashboards to highlight exactly the information you want in any format (charts, maps, tables, tree maps, diagrams, and more).[/sc_icon_with_text]

      [sc_icon_with_text icon=”email–contact” icon_shape=”circle” icon_color=”#ffffff” icon_background_color=”#3766b5″ icon_size=”big” level=”h3″]

      Edit via Email

      Add notes to any record by sending an email directly into the system. Allows anyone to add or append to a record in the system simply through email.[/sc_icon_with_text]

       

      Contact us to see a demo of Locus Platform

        Name

        Company Email

        Phone

        Tell us about your company's needs

        Locus is committed to preserving your privacy.

        Top 8 Things to Look For in Sustainability Software

        Sustainability is a corporate necessity, and finding the right software to support company-wide sustainability goals and initiatives is imperative to streamlining this time-consuming activity.  This is especially true if you are managing inputs from many facilities/locations or have required or optional reporting requirements.  Not to mention, most corporate annual reports demand a summary of key sustainability initiatives as part of the corporate annual reporting process.

        Here are some features to look for when selecting a sustainability software—to make sure your new software will actually help your company track and report its sustainability initiatives more accurately and efficiently.


        1. Make sure software is accessible to everyone who needs to input data

         It is very important that data owners/data collectors throughout your facilities can directly enter their own relevant Key Performance Indicator (KPI) and greenhouse gas data—no more searching for data from disparate company groups, or searching through email for spreadsheets or invoices, and no more tracking down the field technician for the field log, or hunting for other assorted documentation.

        This is especially important when dealing with company locations in various geographic regions. A well-designed software system can solve this most vexing problem: finding the relevant data.

         Check for the following features in any sustainability software you’re considering:
        • Data stored in one managed location
          All sustainability data should be stored in one place—whether text or numeric, and whether from an automatic data acquisition system, external database, hand-written field logs, or third-party documentation (e.g., air permits).
        • Streamlined reporting from centralized data
          Reporting is streamlined because all input is consolidated in one managed location.
        • Standardized terminology and units
          A centralized system enforces common terminology, units, and values (numbers vs. text) that are so important for final reporting. No one wants to get energy data from 10 different sources, all in different units, formats, and terminologies.
        • Built-in notifications and workflows
          Also, look for built-in reminders, notifications, and escalations to ensure the inputs are completed in a timely manner, and if deadlines are missed, you know exactly what is missing and who to contact.
        Multiple data sources

        Data can come from multiple sources, and your sustainability software should be able to handle them all—then consolidate this data into a single source of truth.


        2. Make sure the software application includes quality assurance and third-party review tools

        Any decent software can make data collection easy, but to truly improve your company’s sustainability initiatives, it must also have tools for quality assurance reviewers and third-party verifiers to easily review the information, track the reported values to source data, and understand how the data were processed.  Ultimately, the software also needs to allow the reporter to easily make updates or corrections as needed.  Because these data are reported to regulators or shareholders, accuracy is paramount.

        Look for the following features to support transparency and auditing:

        • Visible and accessible calculations
          All embedded rules, queries, and calculations should be visible and traceable to anyone reviewing so they can check the calculations and raise a flag if issues are found.

          EPA equations

          Your sustainability software should make it easy to see and understand the formulas that produced any calculated data values.

        • Accessible and auditable source data and final values
          All source data and final reported values should be visible, traceable, and tracked. Watch out for “black box” calculations that will confound auditors and cost you in labor hours while you are determining how the reported value was obtained, what the data inputs were, and where the source data originated.
        • Complete audit trails
          Ensure audit trails are present for any changes in key data. You should be able to find out exactly who entered a value or who changed it. Be sure the software is keeping track and that everything is recorded and traceable to ensure the integrity of the process and reports. Good software will have an audit tool that tracks who did what, who is responsible for which datasets, and who changed which values and how.

        3. Make sure the software includes tools for reporting to multiple regulatory or voluntary bodies

        Many companies report to various regulatory or voluntary bodies, and the software you select should support all the major reporting requirements to avoid the need for separate calculations for some jurisdictions.

        • Enter once, report 10x
          Look for the concept of “enter once, report many times” when reviewing software applications. The gold standard is the capability for reporting methodologies and calculations configured for reporting to multiple agencies from a single dataset, all in a single tool.
        • Check support for your actual, specific needs
          Review your reporting requirements to see if the software handles them. Key reporting requirements include state or federal regulations, internal corporate social responsibility (CSR) and other sustainability reporting, the Carbon Disclosure Project (CDP), Global Reporting Initiative (GRI), and The Climate Registry (TCR).
        • Consider export formats
          Ensure the software includes exports to XML, which is a common format for EPA and ARB reporting, and an option for reporting to other agencies. Having such outputs easily generated from the software will save time and money during the reporting season.
        Regulatory formats

        Find out what formats you need for regulatory reporting, and make sure your software supports exporting in these formats.


        4. Look for data verification flags so you don’t spend time fixing obviously bad data

        If you normally report 500 metric tons of GHG per year and you are finding entries of 500,000 metric tons per year in your data, chances are, it’s just simple data entry errors.  However, no one wants to track these down months after the data entry event.  Look for software that will flag these anomalies on entry and force the user to fix them before you ever get to the data review step.

        • Ability to set validation rules
          Look for software that allows you to set rules to flag data entries that fall outside of expected thresholds, catching errors before they make it to QA personnel or auditors.
        • Options to specify acceptable ranges and add comments for unusual values
          Look for features that will help you avoid last-minute questions about the validity of your data. Look for the ability to specify an outlier range to flag values so that you can address them immediately before the report is due. Allow for the opportunity to enter a comment right alongside the flagged value, providing a record that the value was double-checked and is correct for a specified reason.

          Fuel warnings

          Immediate, inline alerts about outlier data values help prevent last-minute surprises.


        5. Look for user-defined workflows to help you and your users step through sustainability reporting and tracking process

        The sustainability software you select should help simplify data entry and reporting by supporting your preferred workflows.  Software with configurable workflows can be a huge help for both data entry personnel and managers reviewing data, by making the status of all data entry and reporting business processes abundantly clear.

        • Options for lockdown after manager review
          Look for the ability to include manager overrides to data entry and workflows that will lock the data entries to editing once reviewed. This will help ensure others are not modifying data while you are in the report preparation process.

          Edit workflows

          Options for managers to lock down data are important for preventing edits to data that is being prepared for reporting.

        • Quickly identify current workflow status
          Check for easy visual indicators of workflow status to ensure the process is on track to be completed by the reporting deadline.

          Workflow status

          There should be an easy way to see the current workflow status of any data in your system.

        • Easily modify workflow along the way
          Also look for the ability to easily modify the workflow if your original configuration was not optimal. Not everyone knows the best workflow for new software when they initially start using it.  The ability to modify the workflows—without needing a software developer—is an important feature to consider when choosing a sustainability software solution.

        6. Look for robust audit trails to help solve “whodunit” issues

        All software that handles critical or regulatory data should provide auditing on key data fields.  Find out the details of what is audited and how you will be able to access the audit information.

        • Full history of all changes
          Software should retain a history of values with every report change.
        • Who, when, what
          Look for a complete audit trail of who did what, and what was changed, and when. Tracking any modifications to values supports a rigorous audit and is sure to make your QC staff really happy.

          Workflow history

          Your software should be automatically recording a history of all changes at each step of your workflow.


        7. Look at out-of-the-box data outputs—but also consider how easy (or hard) it will be to create specific reports for your corporate needs

        Every software has built-in report and dashboards, but they may not meet all your needs out-of-the-box.  Assume some reports will need to be configured, and review the software accordingly.

        • Tracking specific KPIs
          Does the software provide an easy way to track year-to-year KPIs for internal evaluation or for preparation of public-facing sustainability reports?
        • Consider future reporting and visualization needs
          If you need a new report, chart, or other visualization of your data, will this request incur a custom software development charge, or is it an easy configuration?
        • Adapt dashboards to your needs
          Can you easily customize the software’s default dashboards?

          GHG emissions dashboard

          Look for options to easily configure reports, charts, and other visualizations that help you easily review summaries of your data.


        8. Make sure the software has a robust notification engine

        Software can shoulder the burden of getting people to do what they are supposed to do (reminders), alerting people to when an action is needed (notifications), sharing information (messaging) and sending them information (report notifications).  Be sure to review the strength of all notification features of the software, as this can be a huge help during reporting season—and it can lighten the burden on your inbox as well.

        • Multi-purpose notifications
          Look for routine workflow notifications to ensure you are notified when a workflow step is completed AND if a workflow step is ignored beyond the due date.
        • Actionable notifications
          Look for reporting notifications that will send the link (URL) to applicable users so they can quickly jump to the information in the software. No one likes knowing a report is ready, but then having to log in and search for it.
        • Group and individual notifications
          Ensure you can send notifications by individual user OR to user groups. It can be very tedious to select large numbers of individuals for routine notifications—it is much easier to select “all Facility XYZ EHS staff”.
        • Decide where to receive notifications
          Consider in-app messaging to keep important information in front of the users and spare their inbox.

        Robust notification engine


        Final thoughts: Imagine what implementation success looks like

        While you are evaluating software options, use these points as a guide to make sure you choose a solution that will truly make a difference for your organization’s sustainability initiatives and reporting goals.

        As more sustainability software solutions appear in the marketplace, it can be difficult for a company to discern which features really matter for its workflow.  Try a simple exercise—imagine what a perfect sustainability management business process would look like if you found the perfect software solution.  Consider the challenges you face now, and what it would look like if those problems were handled by your software.

        Then, ask how well the sustainability software you’re considering will make this dream a reality.  The right software selection can help reduce operational risk, fulfill regulatory reporting requirements in less time and with less effort, and provide safeguards against bad data and missed deadlines.  All you have to do is ask the right questions.

        The complete guide to evaluating EHS software

        Get more tips for what to look for when evaluating EHS&S software!

        [sc_button link=”https://www.locustec.com/white-paper/complete-guide-evaluating-ehs-software/” text=”Download eBook” link_target=”_self” background_color=”52a6ea”]

         

         

        Blockchain: aggregate emissions reporting

        In the next few years, an opportunity exists to make significant advances in how we monitor and manage environmental emissions to the air, soil, and water, potentially resulting in significant disruptions in current approaches. Currently, industries and commercial establishments monitor their emissions and submit reports on a regular basis, often as frequently as quarterly, to federal and state agencies to demonstrate they are meeting regulatory requirements. However, no one on the generating or receiving end of these data dumps and reports is aggregating these emissions to create a more composite, inclusive picture of emissions across sources or media. The reason is that emissions of different types and to different media are reported to separate regulatory entities that, in general, do not interact or talk to one another. And although there are significant potential benefits to both generators and regulators in reviewing integrated environmental data sets, our traditional methods of storing and sharing this information make such integrations a hugely difficult effort.

        Only by integrating all available data can we begin to (1) assess local, regional, and ultimately the global impacts of these emissions, and (2) identify net improvements to our environmental practices that are only apparent when looking at the combined, interconnected body of collected data. Blockchain enables the integration of these data sets for quick, yet comprehensive “big picture” assessments.

        Blockchain technology is a highly disruptive technology that offers an efficient way of storing records (called blocks) which are linked using cryptography. While still in its infancy, blockchain promises to change the world as we know it, much like the internet did after its introduction in 1991. Today, the technology is most widely associated with digital currencies and money transfers. In time, however, blockchain technology will not only shift the way we use the internet, but it will also revolutionize the global economy and almost all transactional business that relies on an intermediary.

        One Environment, Health, and Safety and Sustainability  (EHS+S) sector well positioned to benefit from blockchain technology is emissions monitoring and reporting. I reported more on the technology and its impact on EHS space here.

        Environmental monitoring current practice

        Presently, companies with emissions monitor these following regulatory requirements, input the resulting data into a database or spreadsheet, perform emissions calculations on the entered readings, and then report the results of these calculations to regulators. The entire focus of this process is to (1) determine whether emissions of a single chemical or chemicals exceed prescribed levels and (2) evaluate the effect of these discharges on the media to which the compounds have been introduced by the polluting industry or other sources. There is no suitable software technology or mechanism to look at aggregate emissions across geographical areas or sectors or how emissions of one type interact with emissions of an entirely different kind. Examining such interactions could be far more critical than monitoring and assessing the impacts on human health and the environment of single parameter emissions to only one media, and may reveal new opportunities for optimizing our EHS+S practices for reduced cost with similar or improved performance.

        Aggregate emissions

        To take a hypothetical scenario, consider the possible consequential damages when two incompatible streams of chemicals or waste mix to create even worse chemicals as a result of their chemical reaction.  EPA has only recently started looking into these type of scenarios. Its Envirofacts databases allow the public to retrieve information from multiple sources of Envirofacts’ System Data relevant to your area of interest. However, each database is a separate silo of information (Figure 1). The next step that ought to be taken is to assess and as needed, report on the possible interaction of incompatible emission sources that are nearby, but are independently monitored and stored in disconnected databases (see Figure 2 below).

        EPA Envirofacts 1

        Figure 1: EPA Envirofacts databases allow the public to retrieve information from multiple sources, but only one source at time and disconnected from each other.

        Most everyone taking prescription medicines comes to understand that interactions between drugs are quite common. Imagine something similar to the interaction of drugs in your body happening on a much larger scale in the environment. One does not have to imagine. EPA recently imposed the highest environmental fine ever at the 2,530-acre Eastern Michaud Flats Contamination Superfund site near Pocatello, Idaho. Two adjacent on-site phosphate ore processing facilities, the FMC Corporation and the J.R. Simplot Company, began operations at the site in the 1940s. The J.R. Simplot facility produces solid and liquid fertilizers using phosphate ore, sulfur, air and natural gas. The FMC plant is North America’s largest producer of elemental phosphorus which is used in a variety of products from cleaning compounds to foods.

        Operations at these plants have independently contaminated both the groundwater and soil with hazardous chemicals. Both plants have received numerous environmental violations, many of which were settled with the EPA. Each of the sites has its environmental ills (and fines), but the more significant environmental problem is a combined regional plume. Everyone knows that acids and metals do not play well together. Sulphuric acid from the J.R. Simplot operation has leaked from surface impoundments into the groundwater and, on its way downstream, has leached all kinds of toxic metals from the FMC site, creating a highly poisonous plume of contaminants. An accurate assessment of the environmental disaster that exists in this area requires that the environmental impact of the two plants be examined in toto. Blockchain-based monitoring technology would allow both the public and regulators to see the resultant subsurface commingled plume and possibly pave the way to a more comprehensive remedy.

        Issues involving contamination of multiple media have also arisen at sites where discharges of volatile organic compounds or VOCs have occurred. In Silicon Valley, where I live, many engineering consultants have made their living chasing plumes of VOC chemicals (e.g., TCE) and then, when deemed appropriate, have installed various groundwater treatment plants tucked in the back of parking lots of companies like Google or HP to ameliorate this contamination. Santa Clara, the central county in Silicon Valley, is home to more Superfund sites than any other county in the United States.

        The process is analogous to rinsing detergent from a sponge. After many rinses, it still seems to have more in it. It is an endless process with little environmental benefit. Has anyone looked at the additional impact of the high energy demand for treatment systems that have minimal effect on improving groundwater, but can contribute significant CO2 equivalents to the atmosphere?

        With blockchain technology, we could simultaneously measure the positive effect of the treatment plant removing contaminants from water and the negative impact that this same plant produces by contributing to the CO2 emissions. Quantities of removed chemicals over time could be plotted in real time vs. CO2 emissions produced resulting from high energy usage of the treatment plant. This would allow companies operating treatment plants and regulators overseeing them to determine at what point in time continued treatment could be harming, not helping the environment. It is these type of analyses that would benefit society and help with the decision to shut down a remediation process when diminishing returns of the treatment system are reached.

        EPA Envirofacts 2

        Figure 2: Interaction of incompatible emission sources is better managed if emissions are aggregated than if independently monitored and stored in disconnected databases.

        How would blockchain technology help in a scenario like this? Chemical removal rate would be tracked in one block (of the chain) and CO2 emissions in another. Owner and regulator would agree on the formula to determine when the treatment process ceases to produce a significant environmental benefit. At this point, the system would be shut down. All of this would be monitored and measured in real time, and more importantly, it would be transparent to the owner, regulator, and the public.

        Emissions measures should be preemptive, not reactive

        When you think about emissions, they are generally (except incidents and accidents)  operating problems that can be managed and optimized before discharges even happen. It is to the benefit of companies to do it this way. Every process that has an exhaust or smokestack for dispersing air emissions or pipelines for discharging liquids to surface receptors or water bodies could be managed to reduce harmful emissions coming out the system regardless of regulatory prescribed permissible levels. As an organization with a legacy environmental site knows, it is far more cost-effective to eliminate the original cause of emission than to spend decades of effort to remediate after the fact.

        Unfortunately, many businesses are currently not genuinely looking at the aggregated data they collect about their emissions, wastewater, and energy use alongside their operational metrics. Current practices for EHS+S data management only allow for very simplistic comparison of normalized indicators between these disparate data sets.  But it would benefit these operators to gather, aggregate and analyze data, and then make better, more cost-effective decisions as part of their risk-management protocols, while still maintaining their environmental compliance requirements. Blockchain technology allows for review of more detailed data when making decisions with aggregated data sources so that managers can look beyond the simple normalized performance indicators. For example, many organizations only review their environmental and sustainability performance on an annual basis, mainly because the current tools to aggregate this data require them to be evaluated on a consistent time frame, and there is a significant investment in bringing all of the relevant data together. But through blockchain technology, the data maintain their connection at every level.  This allows for trend evaluation at other time frames not currently being examined. So if some short-term operational practice causes a spike in emissions, that issue can be identified and resolved immediately, rather than waiting for the end of the year, when the emissions have already happened, and the effect may not even be apparent when averaged out on an annual time frame. Then, even looking beyond the facility or organization, blockchain also allows for data aggregation across industry, region, and country, so that we will be in a better position to forecast the future and assess the viability of different measures to ameliorate the problems confronting us.

        A bigger picture

        There is a growing need for companies to bring together information from their vast disconnected databases, single tenant clouds, and spreadsheets, and then mine the data they collect from these sources. In a decade or so, planet Earth may be a meshed grid of static sensors coupled with movable ones installed on people, animals (yes animals roaming in the wild), transportation devices, and other moving objects to collect data in real time. The conversation about the environmental landscape has evolved drastically over the last 50 years as we continue to understand the extent to which human activity has affected the planet. Companies and society need a collective and holistic understanding of the problems we face.

        The only way to understand the full picture, and in turn to act meaningfully on a global level, is for all individuals and companies to understand the impact of their activities. It’s impossible to mitigate the net risks and effects of these activities on the planet when we have not fully assembled the data to characterize the problem and understand the full picture. Blockchain technology offers the best path forward, making it possible for environmental data be integrated at multiple levels. Any coordinated effort of this magnitude will be years in the making, but every journey starts with a first step. There are two impediments to institute a change like this: technology (until recently, blockchain did not exist) and a government with the initiative to require such technology. Just as was the case with the internet revolution of the nineties, the rate of progress in technology is surpassing politicians’ ability to come to grips with its impact on society.

        So far, there have been no imposed data exchange standards; a prerequisite for a broad data exchange, land for implementation of blockchain technology.  But in the meantime, progressive organizations will want to start taking advantage of this technology to look at their operations and make more informed EHS+S decisions.

        Looking forward with blockchain technology

        Perhaps blockchain technology is not ready for prime time. Some may argue that it creates a secondary problem of additional energy consumption much like water treatment systems described earlier. This is a theme that is advocated by some media outlets and blockchain skeptics who argue that the computer algorithms require significant amounts of electricity to power the servers on which they run. Estimates of blockchain’s soaring energy use are likely overstating the electric power used as the current debate on power consumption is not backed by hard data. When it comes to technology, history has consistently shown that the cost will always decrease, and the impact will still increase over time. It is inevitable that blockchain technology will become more accessible with reduced infrastructure over the next few years.

        Blockchain IoT Decentralization

        Blockchain could completely change how companies run their businesses and present new opportunities far beyond sustainability and environmental emissions management.

        We are living in a world where companies and governmental agencies are not able to comprehensively analyze  EHS+S information efficiently. Using blockchain technology will allow organizations to track, store, rollup, gain insights into, and also share their data with other interested parties as needed. It has the potential to put accurate and verifiable information into the hands of companies and regulating agencies more quickly. To make better progress on how we use EHS+S information, regulators will need to find ways that reward positive and proactive behaviors. We are not going to solve these issues by fining emitters until they behave. Blockchain technology can help us move us away from the punitive approach and toward a more collaborative one by assisting companies to reduce their emissions while lowering their operating costs at the same time. Social sharing elements may also play a role here, giving companies that benefit from the fruits of blockchain technology a valuable marketing and PR advantage over those who do not adopt this technology, and as such, lag behind in their progress on environmental issues.

        12 ways commercial SaaS can save your complex environmental data (part 4/4)

        Continued from Part 3

        Complex data - Data stewardship11) Databases are simply more capable when it comes to data stewardship

        Data management is a broad term that includes the range of activities that we have discussed elsewhere in this blog series, including sample planning and collecting, inputting data, uploading EDDs (Electronic Data Deliverables), and analyzing and reporting environmental data and information.

        However, the full scope of data stewardship is even broader than this, including necessary things like knowing where your data is located and knowing the quality of the data used in your regulatory reports.

        Here at Locus, we have had new customers come to us with some incredible horror stories:

        • data “held hostage” by third parties
        • data lost over time with multiple contractor changes
        • data stored in email or file cabinets
        • data in scattered piles of PDF documents or hard copies (very typical for boring logs)
        • labs unable to generate fresh EDDs due to laboratory LIMS system changes or industry consolidation

        These are just some of the latest examples we have encountered.  We are constantly surprised and concerned at the variety of ways that organizations can unwittingly put their critical data at risk.

        The key to effective data stewardship is to know where it is, know its quality, and have uninterrupted access to it.  This is something that Excel can’t offer, and it’s also something a hodgepodge of spreadsheets, emailed PDF files, stacks of hard copy boring logs in multiple offices, and custom-built databases simply can’t do.


        Complex data - Software quality assurance12) Databases are more supportive of software quality assurance practices

        Quality assurance is a popular topic of discussion, but few people consider it in terms of the configurations (behind the scenes code) that people add to popular off-the-shelf programs such as Excel or Access.  Of course, these programs go through rigorous quality assurance testing before being released to the public to ensure they perform as expected.  After all, no one questions that Excel can perform math correctly.

        However, what is often not considered, are the macros, custom functions, and calculations that are often added to spreadsheets when deployed for managing environmental data and other tasks.

        Here at Locus, we have yet to encounter one Excel spreadsheet or Access database from a customer that has been documented, testedand comes with clear user instructions.   We also have never encountered anyone that has never made errors in Excel by picking the wrong cells for a formula.

        You would avoid these types of oversights and lax QA protocols with commercial software that relies on expert functionality for its business.  For example, if Locus EIM did not perform proper calculations (repeatedly) or load data properly (repeatedly), the product would not be successful in the marketplace, and we wouldn’t have thousands of users who trust and use our software every day.  This level of quality assurance is simply not found in user-configured, ad hoc “databases” built-inExcel or Access.

        As more environmental sites become embroiled in litigation, or are in the process of making health and risk clean up decisions, the importance of data quality assurance cannot be ignored.  Water utilities that are charged with providing clean safe drinking water to the public can’t rely on ad hoc Excel or Access systems to analyze such critical data.


        Organize complex data in SaaS databaseCustom databases built in-house vs. commercial software

        If you’re serious about rethinking your environmental data management system and finally ditching your spreadsheets for a more mature and secure solution, you might be considering the advantages of having an in-house team or a contractor build a custom database system for you with Access or another widely available tool.

        After all, only you know the idiosyncrasies that your organization deals with, right?  You can have your developers tailor your system to fit your needs exactly.

        Moreover, with a custom solution, you can make sure that it’s integrated with your organization’s other systems and processes, like document management and invoicing.

        Some of this might be true, but let’s take a closer look—and consider the tradeoffs.

        Is your organization as unique as you think?

        Finding a differentiator for R&D success in cloud SaaS applicationsEvery organization is different, especially if we’re comparing organizations and businesses across different industries. Water utilities face an entirely different set of challenges than a multinational oil and gas corporation. Despite these differences,  diverse organizations share some remarkable similarities when it comes to managing environmental data, and in most cases, you’re not the only environmental professional who has experienced most of the challenges your organization has faced.

        A commercial software vendor with customers in a wide variety of industries naturally collects an aggregated body of knowledge about the environmental data management needs (and quirks) of their customers. By adopting existing commercial software, your organization can benefit from the wisdom of this crowd, getting access to functionality and modules that can streamline your processes in ways you couldn’t imagine (or afford to develop).

        On top of this, commercial software solutions that have been around for a while usually have pretty good support for various API integrations of commonly-used systems, or they can easily build the integrations into their solution (usually for a small fee). Attempting to build these integrations into a custom, in-house solution can lead to astronomical costs and unforeseen complications that often can’t be accurately estimated until the work is well underway.

        Can your in-house resources fully examine your business processes and accurately identify your needs?

        Locus Platform ConfigurabilityCommercial software vendors are in the business of translating real customer needs into successful software products.  As an environmental professional, you probably have a good understanding of your business processes, but do you trust yourself and your development team to find and implement the most efficient, effective, and scalable solution for managing your ever-increasing amounts of data?

        “Off-the-shelf” can sometimes be a misnomer.  Many commercial vendors nowadays have learned to build their platforms to be configurable and customizable, to better accommodate the wide variety of customer industries and organization-specific needs.  Don’t be afraid to reach out to a few vendors to see what they can offer. Consider a vendor that has experienced domain experts, that have been in your shoes and are motivated to help you solve your problems, and deliver the solution you need.

        A good vendor will ask many questions about your business processes, your current system, and your pain points.  You might be surprised at how easy-to-configure and flexible “off-the-shelf” systems can be.

        Think forward—could today’s “bells and whistles” become tomorrow’s critical features?

        Locus GIS+Are GIS and mobile part of your current environmental data management process?  If so, you will absolutely want to have them integrated with any database solution. Otherwise you’ll be dealing with a mess of duplicate and out-of-date data all over again.  Building integrations with these complex systems can be just as challenging as building the database management system itself.

        A robust commercial software solution comes with these features built-in.

        Let’s go even further—have you ever thought about how automation, the Internet of Things, or artificial intelligence could impact your business processes in 5 or 10 years (or sooner)?  Commercial software vendors often have the resources and the incentives to explore new frontiers of technology and stay on the cutting-edge of their market.  When your peers or competitors start integrating these new technologies into their workflows, will your custom system be able to adapt and keep up?


        Hopefully, by this point, we have convinced you of the superiority of database management systems over spreadsheets when it comes to managing environmental data.  Now, it’s time to make some efforts to examine the specific shortcomings of your current system and consider your options.

        Now that you have had the opportunity to consider why SaaS databases allow you to manage your complex data efficiently, make data integration and reporting faster and easier and scale to your requirements, contact Locus Sales at (650) 960-1640 or fill out the contact form below to find out what Locus can do for you.

        12 reasons why commercial SaaS databases are ideal

        Make sure to read the entire series to find out about 12 reasons commercial SaaS databases excel at managing complex environmental data!

        About the author—Gregory Buckle, PhD, Locus Technologies

        Gregory Buckle, PH.D.Dr. Buckle has more than 30 years of experience in the environmental field, most of which have been devoted to the design, development, and implementation of environmental database management systems. When he joined Locus in 1999, he was responsible for building and deploying Locus’ cloud-based EIM software. He was also instrumental in customizing EIM for the water utility industry and developing EIM’s powerful Sample Planning and Data Validation modules. The latest iteration of the Sample Planning module that Dr. Buckle built is currently being used by Los Alamos National Laboratory and San Jose Water Company to plan and schedule thousands of samples per year.


        About the author—Marian Carr, Locus Technologies

        Marian CarrMs. Carr is responsible for managing overall customer solution deployments and customer relationships with Locus’ government accounts. Her career at Locus includes heading the product development team of the award-winning cloud-based environmental ePortal solution as well as maintaining and growing key customer accounts with Locus’ Fortune 100 enterprise deployments. In addition, Ms. Carr was instrumental in driving the growth and adoption of the Locus EIM platform with key federal and water organizations.


         

        Have a question about Locus’ cloud-based environmental software?

          First name

          Last name

          Email address

          Phone number

          Company

          Job title

          Tell us about your company's needs

          Locus is committed to preserving your privacy.

          12 ways commercial SaaS can save your complex environmental data (part 3/4)

          Complex data - Simultaneous usageContinued from Part 2

          6) Simultaneous usage is better supported by databases

          Microsoft Support confirms that it is possible to share an Excel workbook.  Two or more individuals can indeed access the same spreadsheet simultaneously. Edits are even possible:

          You can create a shared workbook and place it on a network location where several people can edit the contents simultaneously… As the owner of the shared workbook, you can manage it by controlling user access to the shared workbook and resolving conflicting changes. When all changes have been incorporated, you can stop sharing the workbook.

          Sharing a spreadsheet may work in a small office or facility with a couple of users, but it certainly is not a viable option when more users need to access, view, and generate reports. This is a task for which databases are far better suited.

          On any given day, for example, Locus EIM supports hundreds of simultaneous users. Some may be inputting form data, while others are loading and checking laboratory EDDs, and still others are creating reports and graphs and viewing data on maps and in tables.  Many of these are very data-intensive processes—yet Locus EIM handles them seamlessly.

          Being able to handle such simultaneous activity is inherent in the designs of relational databases. In contrast, the ability to share an Excel workbook is not a native feature of such software and, as such, is unlikely to meet the needs of most organizations (especially as they evolve and grow).


          Complex data - Processing speed & scalability7) Processing speed, capacity, and scalability is better with databases

          Compared to spreadsheets, databases are the hands-down winners with respect to processing speed and the numbers of records they can store. Higher-end databases can store hundreds of millions of records.  In contrast, spreadsheets with hundreds of thousands of records can bog down and become difficult to manage.

          An underappreciated, yet  the critical difference is that while you’re using a spreadsheet, the entire file is stored in a computer’s random access memory (RAM). In contrast, when using a database, only the dataset that you are currently working with is loaded into RAM.

          To illustrate just how fast a powerful database can be, I sent a query to EIM at our secure facility on the opposite coast, asking how many “benzene” records were in one of our larger laboratory results table (N > 4,500,000).  Sitting at a desk here in the hinterlands of Vermont, the result (“number of records = 64773”) came back in less than a second.  I did not even have time to call in the cows for their afternoon milking.

          Because they are both faster and can store more, databases scale far better than spreadsheets.  As such, they can meet both your current and future requirements, no matter how fast the information you are required to store grows over time.


          Complex data - Workflows8) Databases support creating and following complex workflows

          In contrast to spreadsheets, databases support the creation of formal workflows. Let’s consider one example from EIM—its cradle-to-grave sample planning, collection, and tracking process.

          Using EIM’s Sample Planning module, you can:

          • Identify one-time or recurring samples and analyses that need to be collected
          • Transfer information on these planned samples and analyses to Locus Mobile
          • Collect field data
          • Upload field data to EIM (where it is stored in various tables)
          • Generate chains of custody and sample bottle labels (after which the samples are sent to the lab for analysis)
          • (Days or weeks later, labs upload their findings to EIM’s holding table, where they are automatically matched with the previously uploaded field information)
          • Receive notifications that the lab results are now available (additional notifications can be sent if any results are found to exceed a regulatory limit)
          • Track the status of the samples throughout this process with forms that can tell you the status of each planned sample, including whether any results are late or missing
          • Generate relevant reports, maps and charts for internal use or for submittal to the appropriate agency

          Complex data - Workflow

          You simply could not build such a comprehensive and sophisticated workflow in Excel.  Notice we mentioned maps.  Building complex workflows is yet another area where advanced, integrated database management systems shine, especially as they can automatically create GIS-based maps of the results from data housed in the database—without the need (or expense) for ancillary software.


          Complex data - Security9) Databases provide more security than spreadsheets

          Microsoft identifies the following security features available in Excel:

          User-level data protection

          You can remove critical or private data from view by hiding columns and rows of data, and then protect the whole worksheet to control user access to the hidden data. In addition to protecting a worksheet and its elements, you can also lock and unlock cells in a worksheet to prevent other users from unintentionally modifying essential  data.

          File-level security

          At the file level, you can use encryption to prevent unauthorized users from seeing the data. You can also require password entry to open a workbook, or you can secure a workbook by employing a digital signature.

          Restricted access to data

          You can specify user-based permissions to access the data, or set read-only rights that prevent other users who may be able to view the data from making changes to it.

          Perusing the web for postings comparing the features of databases to spreadsheets, you’ll find plenty of accusations that spreadsheets lack security and control features. Clearly, Microsoft’s description of the security features available in Excel shows that this isn’t the case.  However, these security features may not be as robust as Microsoft claims, and they may prove difficult for the average user to implement.

          As Martin Cacace of BoundState Software explains, “Although Excel allows you to protect data with a password and Windows-based permissions, it is extremely delicate and requires a deep understanding of Excel.” Some of these features won’t work if you have people using different operating systems or if you need access from other computers. Even a password protected Excel file is not really secure; there are tools on the Internet that anyone can use to unlock a protected Excel file without knowing the password.”

          Databases offer far more control than spreadsheets over who can access and make changes to data.  As an example, Locus EIM users must have a unique username and password. Users can be assigned to multiple privilege levels, ranging from “administrator” to “guest”.  Customers that require a more fine-grained approach can use “roles” to assign permissions to specific modules, activities, or functionality to users.  Password security is typically robustly designed in commercial databases, and can be configured to require complex passwords, session expiry, and password expirations to match customer IT requirements, something Excel would find challenging. Locus EIM also tracks all users and makes that information available to database admins to provide yet another layer of security for the system.


          Complex data - Data loss & corruption10) Databases are better at preventing data loss and data corruption

          Because of the general lack of controls that exist in most spreadsheets, it is far easier for a user to wreak havoc on  them. One of the most dreaded developments that can occur is associated with the “Sort” function. A user may choose to sort on one or more columns, but not all—resulting in the values in the missed columns not matching up with those in the sorted ones.  Nightmares like this are easily preventable (or are simply not possible) in databases.

          Another advantage of database management systems is their ability to create audit trails, which preserve the original values in separate tables when changes are made to records.  In the event that a user wants to undo some changes (including deletions) that he or she has made to a table, a data administrator can retrieve and restore the original state of the modified or deleted records.  Also importantly, the circumstances of these changes are fully tracked (who, what, when, where), which is a minimum requirement for any quality assurance process.

          Lastly, Excel stores the entire spreadsheet in memory, so if there is a system crash, you will lose everything you have entered or edited since your last save. In contrast, each operation you perform in a database is saved as you complete it. Moreover, most databases have daily backups, and in some cases, maintain an up-to-date copy of the data on a secondary device. Additionally, data is typically backed up in multiple geographic locations to provide even more recovery options in a disaster situation.  Any good commercial database vendor will be happy to share their disaster recovery process because securing and maintaining your data is their most important job. In short, you can rest assured that your valuable data—often gathered over many years at a high cost—will not be lost if it is stored in a DBMS like Locus EIM.


          12 reasons why commercial SaaS databases are ideal

          Make sure to read the entire series to find out about 12 reasons commercial SaaS databases excel at managing complex environmental data!

          About the author—Gregory Buckle, PhD, Locus Technologies

          Gregory Buckle, PH.D.Dr. Buckle has more than 30 years of experience in the environmental field, most of which have been devoted to the design, development, and implementation of environmental database management systems. When he joined Locus in 1999, he was responsible for building and deploying Locus’ cloud-based EIM software. He was also instrumental in customizing EIM for the water utility industry and developing EIM’s powerful Sample Planning and Data Validation modules. The latest iteration of the Sample Planning module that Dr. Buckle built is currently being used by Los Alamos National Laboratory and San Jose Water Company to plan and schedule thousands of samples per year.


          About the author—Marian Carr, Locus Technologies

          Marian CarrMs. Carr is responsible for managing overall customer solution deployments and customer relationships with Locus’ government accounts. Her career at Locus includes heading the product development team of the award-winning cloud-based environmental ePortal solution as well as maintaining and growing key customer accounts with Locus’ Fortune 100 enterprise deployments. In addition, Ms. Carr was instrumental in driving the growth and adoption of the Locus EIM platform with key federal and water organizations.


           

          Have a question about Locus’ cloud-based environmental software?

            First name

            Last name

            Email address

            Phone number

            Company

            Job title

            Tell us about your company's needs

            Locus is committed to preserving your privacy.

            12 ways commercial SaaS can save your complex environmental data (part 2/4)

            Continued from Part 1

            Complex data - Data quality2) Data quality is better with databases

            Since 2002, a dedicated group of Locus employees has been involved with migrating data into EIM from spreadsheets provided to us by customers and their consultants. As such, we have firsthand experience with the types of data quality issues that arise when using spreadsheets for entering and storing environmental data.

            Here is just a small selection of these issues:

            • Locations with multiple variations of the same ID/name (e.g., MW-1, MW-01, MW 1, MW1, etc.)
            • Use of multiple codes for the same entity (e.g., SW and SURFW for surface water samples)
            • Loss of significant figures for numeric data
            • Special characters (such as commas) that may cause cells to break unintentionally over rows when moving data into another application
            • Excel’s frustrating insistence (unless a cell format has been explicitly specified) to convert CAS numbers like “7440-09-7 (Potassium)” into dates (“9/7/7440”)
            • Bogus dates like “November 31” in columns that have do not have date formats applied to them
            • Loss of leading zeros associated with cost codes and projects numbers (e.g., “005241”) that have only numbers in them but must be stored as text fields
            • The inability to enforce uniqueness, leading to duplicate entries
            • Null values in key fields (because entries cannot be marked as required)
            • Hidden rows and/or columns that can cause data to be shifted unintentionally or modified erroneously
            • Bogus numerical values (e.g., “1..3”, “.1.2”) stored in text fields
            • Inconsistent use of lab qualifiers— in some cases, these appear concatenated in the same Excel column (e.g., “10U, <5”) while in other cases they appear in separate columns

            With some planning and discipline, you can avoid some of these problems in Excel. For example, you can create dropdown list boxes to limit the entries in a cell to certain values. However, this is not standard practice as most spreadsheets we receive come with few constraints built into them.

            While databases are indeed not immune to data quality issues, it is much easier for database designers to impose effective constraints on users’ entries. Tasks such as limiting the values in a column to selected entries, ensuring that values are valid dates or numbers, forcing values to be entered in selected fields, and preventing duplicate records from being entered are all easy to implement and standard practice in databases.

            However, properly designed databases can do even more. They can check that various combinations of values make sense—for example:

            • They can prevent users from entering analysis dates that are earlier than the associated sample dates.
            • They can verify that numerical entries are within a permitted range of values and make sense based on past entries. This is so popular its even part of our Locus Mobile app for collecting field data.

            Databases also provide the ability to verify the completeness of your data:

            • Have all samples been collected?
            • Have all analyses been performed on a sample?
            • Are there any analytes missing from the laboratory’s findings?

            You can specify such queries to run at any time. Replicating these checks within Excel, while not impossible, is simply not something most Excel users have the time, skill, or desire to build.


            Complex data - Data redundancy3) It’s easier to prevent data duplication and redundancy when your data resides in your database

            One of the most striking differences between spreadsheets and databases is the prevalence of redundant information in spreadsheets. Consider, for example, these three tables in EIM:

            1. LOCATION
            2. FIELD_SAMPLE
            3. FIELD_SAMPLE_RESULT

            In this subset of their columns, “PK” signifies that the column is a member of the “primary key” of the table. The combination of values in these columns must be unique for any given record.

            Complex data - Table - Primary key

            The two columns LOCATION_ID and SITE_ID can be used to link (join) the information in the FIELD_SAMPLE table. Furthermore, FIELD_SAMPLE_ID and SITE_ID can be used to link the information in FIELD_SAMPLE_RESULT to FIELD_SAMPLE. Because these links exist, we only need to store the above attributes of a given location or field sample once— in one table. This is very different from how data is handled in a single spreadsheet.

            Let’s compare how the data in a few of these columns might appear in a single spreadsheet compared to a database. We’ll look at the spreadsheet first:

            Complex data - Location Table

            Next, let’s see how this information would be stored in a database. Here we can see more fields since we’re not as constrained by width.

            First, the LOCATION table:

            Complex data - Location ID Table

            Then, FIELD_SAMPLE:

            Complex data - Field Sample Table

            Lastly, FIELD_SAMPLE_RESULT:

            Complex data - Field Sample Result Table

            Note one of the most striking differences between the spreadsheet and the database tables above is that much redundant information is included in the spreadsheet. The Location Type of “WELL” is repeated in every record where location MW-01 appears, and the sample date of “04/17/2017” is repeated wherever sample MW-01-12 is present. Redundant information represents one of the most significant drawbacks of using spreadsheets for storing large amounts of data when many of the data values themselves (e.g., LOCATION_ID and FIELD_SAMPLE_ID above) have multiple attributes that need to be stored as well.

            Most spreadsheet data that we have received for import into EIM have consisted of either:

            1. Multiple worksheets of the same or similar formats, all containing a combination of sampling and analytical data
            2. A single worksheet containing tens of thousands of rows of such data

            Occasionally, customers have sent us multiple spreadsheets containing very different types of data, with one or more hosting sample and analytical results, and others containing location, well construction, or other supporting data. However, this is atypical; in most of the migrations that we have performed, redundant data is pervasive in the spreadsheet’s contents and inconsistencies in entries are common.

            Entering new records in a spreadsheet structured like the example above requires that the attributes entered for LOCATION_ID and FIELD_SAMPLE_ID be consistent across all records whose values are the same in these columns.

            The real problems surface when you have to edit records. You must correctly identify all affected records and change them all identically and immediately.

            Sounds relatively straightforward, doesn’t it?

            In fact, judging by what we have seem in our data migrations, discrepancies invariably creep into spreadsheets when edits are attempted. These discrepancies must be resolved when moving the data into a database where constraints prohibit, for example, a single sample from having multiple sample dates, times, purposes, etc.

            In addition, audit trails are all but nonexistent in Excel. Many users tend to save the edited version with a new filename as a crude form of audit tracking. This can quickly lead to a data management nightmare with no documented audit tracking. Just as important, almost all our customers, especially customers involved with regulatory reporting, require audit tracking. This is typically required on sites that may be involved in litigation and decisions are made on the health and safety risks of the site necessitating defensible and unimpeachable data.


            Complex data - Entity relationships4) Entity relationships are more manageable in databases

            The discussion of data duplication and redundancy touches on another significant difference between databases and spreadsheets—how entity relationships are handled.

            Excel stores data in a two-dimensional grid. While it is possible to create relationships between data in different worksheets, this is not the norm and there are many limitations. More often, as we have stated elsewhere, Excel users tend to store their data in a single spreadsheet that grows increasingly unwieldy and hard to read as records are added to it.

            Let’s consider some of the relationships that characterize environmental sampling and analytical data:

            • Sampling locations are associated with sites or facilities—or, for our water utility customers, individual water systems. They may also belong to one or more planned sampling routes.
            • Different sampling locations have their own analytical and field measurement requirements.
            • Individual samples may be associated with one or more specific permits or regulatory requirements.
            • Trip, field, and equipment rinsate samples are linked to one or more regular field samples.
            • Analytical results are assigned to analysis lots and sample delivery groups (SDGs) by the laboratory.
            • Analysis lots and SDGs are the vehicle for linking laboratory QC samples to regular samples.
            • Analytical parameters are associated with one or more regulatory limits.
            • Individual wells are linked to specific boreholes and one or more aquifers.

            Modeling and building these relationships in Excel would be quite difficult. Moreover, they would likely lack most of the checks that a DBMS offers, like preventing orphans (e.g., a location referenced in the FIELD_SAMPLE table that has no entry in the LOCATION table).


            Complex data - Reporting & Integration5) Data reporting and integration is faster and easier with databases

            How do you create a report in Excel? If you’re working with a single spreadsheet, you use the “Data Filter” and “Sort” options to identify the records of interest, then move the columns around to get them in the desired sequence. This might involve hiding some columns temporarily.

            If you make a copy of your data, you can delete records and columns that you don’t want to show. If your data is stored in multiple spreadsheets, you can pull information from one sheet to another to create a report that integrates the different types of data housed in these spreadsheets. But this is a somewhat tedious process for all but the simplest of reports.

            Let’s contrast this drudgery with the simplicity and power offered by relational databases.

            In Locus EIM, for example, you pick the primary and secondary filter categories that you want to use to restrict your output to the records of interest. Then, you select the specific values for these data filter categories (usually from dropdowns or list-builder widgets). There is no limit on how many categories you can filter on.

            Typically, you then choose a date range. Lastly, you pick which data columns you want to view, and in what order. These columns can come from many different tables in the database. For ease of selection, these also appear in dropdowns or list-builder widgets.

            When you have made your filter selections, Locus EIM pulls up the records matching your selection criteria in a data grid. You can further filter the records by values in specific columns in this grid, or hide or rearrange columns. If you want to share or keep a record of these data, you can export the contents of the displayed grid to a text file, Excel, XML, PDF, or copy to your clipboard.

            The list of reports spans all the major types of data stored in Locus EIM, including location and sample collection information, chain of custody and requested analyses data, analytical results, field measurements, and well and borehole data. Additional reports provide options to perform statistical calculations, trend analyses, and comparisons with regulatory and other limits.

            In short, when it comes to generating reports, databases are superior to spreadsheets in almost every aspect. However, that doesn’t mean spreadsheets have no role to play. Many Locus EIM users charged with creating an ad hoc report prefer to download their selected output to Excel, where they apply final formatting and add a title and footer.  Although, with some of the newer reporting tools, such as Locus EIM’s new enhanced formatted reports, that functionality is also built into the DBMS. The more sophisticated the database, the more advanced and robust reporting options will be available.

            12 reasons why commercial SaaS databases are ideal

            Make sure to read the entire series to find out about 12 reasons commercial SaaS databases excel at managing complex environmental data!

            About the author—Gregory Buckle, PhD, Locus Technologies

            Gregory Buckle, PH.D.Dr. Buckle has more than 30 years of experience in the environmental field, most of which have been devoted to the design, development, and implementation of environmental database management systems. When he joined Locus in 1999, he was responsible for building and deploying Locus’ cloud-based EIM software. He was also instrumental in customizing EIM for the water utility industry and developing EIM’s powerful Sample Planning and Data Validation modules. The latest iteration of the Sample Planning module that Dr. Buckle built is currently being used by Los Alamos National Laboratory and San Jose Water Company to plan and schedule thousands of samples per year.


            About the author—Marian Carr, Locus Technologies

            Marian CarrMs. Carr is responsible for managing overall customer solution deployments and customer relationships with Locus’ government accounts. Her career at Locus includes heading the product development team of the award-winning cloud-based environmental ePortal solution as well as maintaining and growing key customer accounts with Locus’ Fortune 100 enterprise deployments. In addition, Ms. Carr was instrumental in driving the growth and adoption of the Locus EIM platform with key federal and water organizations.


             

            Have a question about Locus’ cloud-based environmental software?

              First name

              Last name

              Email address

              Phone number

              Company

              Job title

              Tell us about your company's needs

              Locus is committed to preserving your privacy.