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.

      Locus EIM SaaS environmental software selected by Hudbay Minerals

      The Locus EIM SaaS with integrated GIS mapping will streamline environmental field and analytical data management and reporting for Hudbay Minerals

      MOUNTAIN VIEW, Calif., 19 June 2018 — Locus Technologies (Locus), the industry leader in multi-tenant SaaS EHS and environmental management software, is pleased to announce that Hudbay Minerals, a premier mining company in Canada, will use Locus EIM to improve their environmental data management for field and analytical data reporting. In addition to the standard features of Locus EIM, Hudbay Minerals is opting to use Locus’ GIS+ mapping solution, Locus Mobile for iOS, and the robust LocusDocs document management solution to enhance and streamline their processes.

      Locus EIM and the integrated GIS+ solution will help Hudbay Minerals to improve efficiency of sampling and monitoring activities for both field and analytical data. The SaaS solution is enhanced by Locus Mobile for field data collection, which works offline without any internet connection.

      “The Hudbay team in Arizona looks forward to working with the Locus team and using the system,” said Andre Lauzon, Vice President, Arizona Business Unit at Hudbay.

      “By using the powerful smart mapping technology of Locus GIS+, powered by Esri and integrated with all the functionalities of Locus EIM, Hudbay Minerals can save data queries as map layers to create more impactful visual reports,” said Wes Hawthorne, president of Locus Technologies.

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

      Do you currently use a system of Excel spreadsheets to store your environmental data? If so, ask yourself the following questions:

      • Do you find yourself having to make the same changes in multiple spreadsheets?
      • Is your spreadsheet growing unwieldy and difficult to manage?
      • Are you finding that you’re spending more and more time scrolling through your spreadsheet, looking for specific information?
      • Do you have to jump through hoops to view specific subsets of data?
      • Do multiple people sometimes need access to the data at the same time? Or, are your colleagues continually asking you to provide them with copies or subsets of the data in your spreadsheet?
      • Are there redundancies in your data? Is the same information repeated on multiple rows of your spreadsheet?
      • Do you ever encounter erroneous entries that have been typed in by hand?
      • Are you concerned about the long-term security of your data?
      • Do you often wonder exactly where your data are?
      • Does someone else really own your data (perhaps your IT department)?

      If you answer “yes” to any of these questions, you might be outgrowing your homegrown system of Excel spreadsheets.  It may be time to consider a more mature tool to manage and store your environmental data.

      The advantages of databases over spreadsheets for managing complex data

      Before we look at other options, let’s examine the differences in how data are stored and managed in spreadsheets and databases.

      A spreadsheet consists of rows and columns. At the intersection of each are cells that store data values. Some cells can refer to other cells, and some cells can perform processing on other individual (or groups of) cell values.

      In contrast, a database is made up of named tables that contain records. Each record has columns in which values are stored.  Each table stores information on a particular type of entity. For environmental data, this could be field samples, sampling locations, analytical results, regulatory limits, or laboratory methods. Typically, one or more columns in each record store values that uniquely identify an instance of the entity. In the case of a field sample, this could be the “field sample ID”; for a location, the “location ID”.

      Complex data - Excel spreadsheets

      Locus user tips
      In Locus EIM, Site ID is also part of the primary key for locations and field samples to accommodate customers with multiple waste sites, facilities, or water systems.

      As we move to analytical or field measurements, we have to use more columns to uniquely identify a record (e.g., date, time, field sample or location ID, parameter). The remaining columns in a table that are not part of the “primary key” identify other attributes of the entity.  For samples, these attributes include sample date and time, sample matrix, sample purpose, sampling event, sampling program, etc.

      If you think of a data table as a grid with rows and columns, it seems very similar to a spreadsheet—but there’s a fudamental difference. With a spreadsheet, how you view or report the data is dictated by how it appears in the spreadsheet—WYSIWYG. If you need to view the data differently, you must reformat the spreadsheet.  In contrast, you can view information stored in a database (or serve it up in a report) in multiple ways that doesn’t necessarily depend on how the data is stored in the underlying tables.

      Databases, which are often referred to by the acronym DBMS (Database Management Systems), offer many other advantages over spreadsheets when dealing with complex data.

      Here are 12 key areas where databases—especially cloud databases built for industry-specific needs—surpass their spreadsheet counterparts.

      Locus user tips
      Pay close attention to section 2 on “Data quality”. Over the years, Locus has helped many of our customers move their data from spreadsheets into Locus EIM. Invariably, these migrations have unearthed many data issues that went undetected until we had to map and move the data into Locus EIM.

      12 reasons why commercial SaaS databases are ideal

      If, at the end of this guide, you’re still not convinced of the advantages of databases over spreadsheets for data storage, consider Microsoft’s recommendations as to when to use its low-end DBMS (Access) and when to use Excel.

      Microsoft emphasizes that Excel can store large amounts of data in worksheets. However, it notes that Excel is not intended to serve as a database, but is optimized for data analysis and calculation.

      According to Microsoft:

      Use Access when you:

      • Anticipate many people working in the database and you want robust options that safely handle updates to your data, such as record locking and conflict resolution.
      • Anticipate the need to add more tables to a dataset that originated as a flat or nonrelational table.
      • Want to run complex queries.
      • Want to produce a variety of reports or mailing labels.

      Use Excel when you:

      • Require a flat or nonrelational view of your data instead of a relational database that uses multiple tables, and when your data is mostly numeric.
      • Frequently run calculations and statistical comparisons on your data.
      • Want to perform sophisticated “what-if” analysis operations on your data, such as statistical, engineering, and regression analysis.
      • Want to keep track of items in a simple list, either for personal use or for limited collaboration purposes.

      In this 4-part blog series, we’ll explore in detail each of the 12 key areas where cloud-based environmental databases excel over home-grown spreadsheets.

      Let’s get started!

      1) Data entry is better with databases

      Complex data - Data entryIf you use spreadsheets to manage your environmental information, how do you get data into it?

      If you’re collecting the same information every week, month, quarter, or year, perhaps you have a template that you use. You might fill in only the data fields that change from one event to another, then append the rows in this template to an existing worksheet, or insert them into a new one. Alternatively, you might copy a set of rows in your spreadsheet, and then edit any fields with values that have changed.

      In the case of analytical data, if you don’t have to type in the data manually, perhaps your lab provides data in a spreadsheet that mirrors the structure of your spreadsheet, allowing you to cut and paste it without edits.

      Each of these methods of entering data has limitations and risks:

      • Manual entry inevitably introduces errors, unless someone is independently checking every entry for accuracy.
      • Copying and editing are notoriously prone to mistakes. It is too easy to overlook fields that should be updated in the copied records.
      • Getting a lab to send you data in a spreadsheet whose structure mirrors yours can be problematic, even more so if you deal with different labs for different types of analyses. Even then, there is no check on the validity of the laboratories’ entries.
        • Are all date and number fields actually the correct data types?
        • Do all required fields have values in them?

      Databases provide various means of data input.  Two of the most commonly used methods are form entry (for when you need to enter a few records at a time) and EDDs (Electronic Data Deliverables), used for uploading text files containing tens, hundreds, or even thousands of data records in text or zipped files.

      Flexible form configuration as a standard database feature

      Databases provide unlimited flexibility in designing forms—with searchable lookup fields, advanced form controls, sophisticated styling, context-sensitive help, data validation, event handlers, and the ability to conditionally display individual or blocks of fields, based on the user’s selections.

      Locus user tips
      Locus EIM offers over 30 forms for entering and editing water systems, locations, sample collection, chains of custody, analytical data, field measurements, water levels, boreholes, well construction and other information. All of these input forms have multiple dropdown list boxes for the display of lookup values and online help.  You can easily hide unused forms for your organization to simplify the system interface and menu structure.

      Better, faster batch data loading with EDDs

      The real strength of databases comes about from their ability to load and process EDDs. Each record in an EDD typically consists of 10-50 fields (e.g., in the case of laboratory analyses: Field Sample ID, Analytical Method, Analysis Date, Lab Result, Units, etc.).  The data in these EDDs can be checked for incorrect data types, missing required values, entries that are restricted by lookup tables or LOVs (Lists of Values), and duplicates.

      Locus user tips
      Locus EIM’s powerful EDD loader can upload and error check several thousand records in under a minute. Labs need not all use the same format – the data will still end up in the same place in the database.  In fact, Locus EIM even has a special lab interface so (with your permission) your labs can upload their own EDDs.  This lab interface shows only a very small part of Locus EIM, namely the EDD Loader and selected LOVs that lab users would need to know.

       

      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.

        Shape of Water: Cape Town running out of drinking water

        The city cut daily water use limits first to 87 liters and then 50 in a bid to avert shutting off supplies.

        The city had set a 50-liter daily limit and had told citizens “Day Zero” was approaching when people would have to queue at standpipes.
        But water-saving efforts in the South African city have seen the day pushed back from April to 27 August. Seasonal rains should mean that date is now averted, the city said. The shortages follow three years of low rainfall. The city had resorted to increasingly drastic measures to clamp down on water usage, including “naming and shaming” the 100 addresses using the most water and fining residents who failed to comply with the 50 liters (13 gallons) limit per person.

        By comparison, the average California consumer uses some 322 liters (85 gallons) of water per day. Water use in California was highest in the summer months of June through September, where it averaged 412 liters per person per day. By comparison, during the cooler and wetter months of January through March of 2016, average per capita water use was only 242 liters per person per day.

        Although the risk that piped water supplies will be shut off this year has receded, politicians and environmentalists warn that the water crisis is there to stay in Cape Town, as year-on-year rainfall levels dwindle.

        WM Symposia 2018 provided an excellent showcase for Locus GIS+ in LANL’s Intellus website

        At the annual WM Symposia, representatives from many different DOE sites and contractors gather once a year and discuss cross-cutting technologies and approaches for managing the legacy waste from the DOE complex.  This year, Locus’ customer Los Alamos National Laboratory (LANL) was the featured laboratory.  During their presentation, they discussed Locus GIS+, which powers Intellus, their public-facing environmental monitoring database website.

        If you haven’t been to LANL’s Intellus website recently, you are in for a surprise!  It was recently updated to better support casual users, and it features some of the best new tools Locus has to offer.  Locus reimagined the basic query engine and created a new “Quick search” to streamline data retrieval for casual users.  The guided “Quick search” simplifies data queries by stepping you through the filter selections for data sources, locations, dates, and parameters, providing context support at each step along the way.

        Intellus - quick searchWhile a knowledgeable environmental scientist may be able to easily navigate a highly technical system, that same operation is bound to be far more difficult for a layperson interested in what chemicals are in their water.  Constructing the right query is not as simple as looking for a chemical in water—it really matters what type of water you want to look within.  On the Intellus website (showing the environmental data from the LANL site), there are 16 different types of water (not including “water levels”).  Using the latest web technologies and our domain expertise, Locus created a much easier way to get to the data of interest.

        Just querying data is not necessarily the most intuitive activity to gain insights.  Locus integrated our new GIS+ visualization engine to allow users to instantly see all the data they just queried in detailed, context-rich maps.

        Intellus GIS+ Map

        Intellus GIS+ map showing “Quick search” query results for chromium levels in the LANL area

        Instead of a dense data grid, GIS+ gives users an instant visual representation of the issue, enabling them to quickly spot the source of the chemicals and review the data in the context of the environmental locations and site activities.  Most importantly for Intellus users, this type of detailed map requires no GIS expertise and is automatically created based on your query.  This directly supports Intellus’ mission to provide transparency into LANL’s environmental monitoring and sampling activities.

        GIS+ also allows users (albeit with a bit more experience in GIS mapping) to integrate maps from a wide range of online sources to provide even more insight to the available data.  In the example below, we overlaid the publicly-available US Fish and Wildlife critical habitat maps with data from the LANL site to show the relationship of the site to critical habitats.  This type of sophisticated analysis is the future of online GIS.  Locus takes full advantage of these opportunities to visualize and integrate data from varying sources with our GIS+ tools, made simple for users and integrated with ArcGIS Online by Esri.

        Intellus GIS+ Map

        Intellus GIS+ map showing imported layers of US Fish and Wildlife critical habitats in relation to LANL environmental sampling data

        WM 2018 - Sean and Nita

        Overall, Locus is very proud of our cross-cutting environmental information management tools.  We were one of many WM18 attendees enjoying LANL’s presentation and getting even more ideas from the audience on the next steps for better environmental visualization.

        [sc_button link=”https://www.locustec.com/wp-content/uploads/2018/03/WM-18-PPT.pdf” text=”View a copy of the presentation” link_target=”_blank” color=”ffffff” background_color=”52a6ea”]

        EHS&S in the age of blockchain technology

        Blockchain is a highly disruptive technology that promises to change the world as we know it, much like the World Wide Web’s impact after its introduction in 1991. As companies look to the blockchain model to perform financial transactions, trade stocks, and create open market spaces, many other industries are looking at utilizing blockchain technology to eliminate the middleman. One sector well-positioned to benefit from blockchain technology is the data-intensive Environment, Health, Safety and Sustainability (EHS&S) space.

        In particular, I see three major ways that the EHS industry can utilize blockchain technology to change how they manage information: 1) Blockchain-based IoT monitoring, 2) emissions management, and 3) emissions trading.

        My belief is that blockchain technology will help to quantify the impact of man-made emissions on global warming trends and provide tools to manage it. One cannot manage what one cannot measure!

        Imagine this: every emissions source in your company, whether to water, air, or soil, is connected wirelessly via a sensor or another device (thing) to a blockchain ledger that stores a description of the source, its location, emission factors, etc. Every time that the source generates emissions (that is, it is on), all necessary parameters are recorded in real time. If air emissions are involved, equivalent tons of carbon are calculated and recorded in a blockchain ledger and made available to reporting and trading entities in real time.

        Blockchain ledgers may exist at many levels. Some may record emissions at a given site. Others at higher levels (company, state or province, country, continent, etc.) may roll up information from lower level ledgers.

        Suppose that emissions are traded so that they are not yours anymore. In that case, someone else owns them, and you do not need to report them again, but everyone knows that you were the generating source. The same logic can be applied to tier 1, 2, and 3 level emissions. Attached to the emissions ledger are all other necessary information about the asset generating those emissions, financial information, depreciation schedule, time in service, operating time, fuel consumption, operators’ names, an estimate of future emissions—the list goes on.

        To learn more how blockchain technology will impact emissions monitoring, management, reporting, and trading click here.

        Locus Technologies receives the prestigious EBJ Award for innovation and growth for 12 consecutive years

        Environmental Business Journal (EBJ) recognized the firm for record sales and innovation in automating compliance and sustainability at enterprise level

        MOUNTAIN VIEW, Calif., 8 February 2018 — Locus Technologies, a leading provider of multi-tenant, SaaS-based EHS software, was awarded its 12th consecutive award by Environmental Business Journal (EBJ) for growth and innovation in the field of Information Technology. EBJ is a business research publication providing high-value strategic business intelligence to the environmental industry. Locus received the award for reporting record sales of its multi-tenant, SaaS-based EHS software as an independent company, while most of its competitors were bought out, merged, or disappeared.

        In 2017, Locus launched a SaaS financial management application on Locus Platform for Honeywell International, a long-term customer of their Locus EIM and ePortal products. The app will help Honeywell to better manage their environmental liability risk management and purchase orders associated with environmental projects. Locus also released an upgraded Sustainability app for Locus Platform, which allows XML submission to multiple climate programs and tracking/reporting of sustainability indicators.

        Locus’ products are evolving to support the future of EHS software, which will inevitably comprise AI, IoT, and virtual reality applications. The company intends to revolutionize the way its EHS software is automating compliance and sustainability at the enterprise level.

        “We are honored to receive the EBJ Information Technology award once again, and we shall continue to build robust solutions in the emerging space of cloud and mobile-based environmental information management and EHS compliance,” said Wes Hawthorne, President of Locus Technologies.

        Along with Locus Platform, Locus’ flagship Environmental Information Management (EIM) software service continues to grow with new customers in the mining, water utilities, water engineering, and US DOE environmental surveillance sectors.

        The world’s most sustainable companies

        One notable difference among attendees of the World Economic Forum’s annual event in Davos, Switzerland last month was the presence of Chief Sustainability Officers in much larger number than ever before.

        This makes sense given the themes of many of the discussions. None more than a panel that focused on integrating sustainability risk into enterprise risk management.

        This is an important evolution for the sustainability community—one that we welcome at Locus and are working with our clients to achieve using our fully-integrated, multi-tenant software platform.

        Just in time for the World Economic Forum, an annual assessment of the world’s most sustainable companies emerges, highlighting large firms from around the world whose sustainability in various categories puts them in a league of their own.

        The report, now in its fourteenth year of publication, is compiled by Corporate Knights, a Canada-based financial information company and magazine with a focus on how business and societal and ecological benefits can go hand in hand.

        In compiling its report, Corporate Knights looked to publicly-disclosed data—financial filings, sustainability reports, etc.—from some 6,000 financially healthy companies across the globe, in all industries, with minimum annual revenue of $1 billion. Key factors Corporate Knight included in its analysis included energy use, carbon, waste, and clean air production.  Top 900 companies were contacted for data verification before the results were boiled down to a final 100.

        Topping the list this year was Dassault Systemes, a French firm that designs engineering software to assist organizations in waste reduction.

        Behind Dassault is Neste, a Finnish company that deals in renewable diesel and other petroleum products. Within the next five years, according to Corporate Knights, more than half of its revenue will come from renewable fuel and bio-material.

        Overall, the U.S.-based companies held 18 spots in the ranking.

        Locus is here to help simplify your fenceline regulatory reporting!

        US EPA fenceline monitoring data collection officially begins on January 30, 2018. We have put together an infographic to show you some of the ways Locus EIM can help you streamline, consolidate, and take control of all your important environmental information using maps, data reports, formatted outputs, charts, and more!

        Locus Fenceline Monitoring - Infographic

        Click image for larger version  

        [sc_button link=”https://www.locustec.com/applications/environmental-information-management/#quote-form” text=”Set up a demo” link_target=”_self” centered=”1″]