# Presentation: 874927

## A Data Management System (DMS) for Air Quality Management Agencies

- Prepared by:
- Mark Stoelting
- Bay Area Air Quality Management District
- San Francisco, CA
- and
- Eric Gray
- Sonoma Technology, Inc.
- Petaluma, CA
- Presented at:
- U.S. EPA 2008 National Air Quality Conference
- Portland, OR
- April 6-9, 2008

## DMS:  Why, What, How, Who

- Why build a DMS?
- What is it?
- How does DMS work?
- What are the DMS capabilities?
- Who is involved?
- Who paid for DMS?
- Where is it going?

## Why the DMS?

- New technology: instruments, communications, data access
- Real-time data access
- Improve data quality (QC checks)
- Graphical data reviewing tools
- Minimize/eliminate need for paper
- Track instruments and performance
- Shared need by all monitoring agencies

## What is DMS?

- Data Management System
- Database to ingest, process, export data
- Archive
- Programmable database with _optional_ features
- Process sub-hourly data (1 minute)
- Collect instrument metadata
- Track instruments and performance (OpCodes)
- Automatic Quality Checks
- Export (AirNow, AQS)
- Notification (email, process controls)

## Simple Data Structure

- DATA

- SITE

- PARAMETER

- INSTRUMENT

- {OpCode}

- Site,Parameter,Date_Time,OpCode,Data,[alpha_data]
- 7011,CO_chamt,10/05/2007 08:16,0,48.1580,

- (optional, assigned in database)

- DATE_TIME

## Sites, Parameters, Instruments

- Establish Monitoring
- Sites

- Create Parameters

- Create
- Instruments

## Assign Parameters to Instruments and Instruments to Sites

- Assign Parameters
- to Instruments

- Assign Instruments
- to Sites

## Automated Quality Checks/Export

- Raw
- Data

- Ambient
- Air Quality
- Data

- Range
- checks

- Instrument
- checks

- Site checks

- Network (off-site)
- checks

- AIRNow

- District
- Web Page

- Exports filtered by QC code

## Automatic Quality Checks

- Assign Automatic
- Quality Control
- Checks to
- Parameters

## Data Input Process

- Ingests data files generated by datalogger or data formatting software
- Duplicate input data is automatically rejected
- Automated Quality Checks establish initial QC code
- Min/max, zero drift, sticking, rate-of-change, compare
- QC conditions can activate process/alarm
- Electronic chain-of-custody created
- Sub-hourly data aggregated to 1-hr averages
- Automated data export to AIRNow and Air District web site

## DMS Status

- Station Reporting Status

- Event Log

- QC Log

## Manual Data Review

- Time-series graphical editing and QC tools for staff data review
- DMS records all reviews and manual adjustments to QC status and data values (electronic chain-of-custody)
- Tracks manual data reviewer status
- Lock final data prior to export
- Exports final data for regulatory submission to AQS or other agencies

## Time Series Edit/QC Screen

## Scatter Plot Analysis

## Other DMS Features

- Data access security (user rights management)
- Maintains instrument equipment database
- Supports electronic instrument and site logs
- Daily instrument performance reports (using OpCodes)

## DMS Software Requirements

- DMS host computer:
- Windows Server 2000/2003 or XP/Vista
- Microsoft SQL Server 2005 (all editions)
- DMS Client Software:
- .NET 2.0 Framework
- Windows 2000, XP, Vista
- Future tools may require Java and ERSI arcGIS

## DMS Hardware

- DMS Server:
- Minimum server requirements – Pentium 4 laptop
- Must support Microsoft Windows
- Hardware determined by processing needs
- Laptop processes hundreds of thousands of records per hour and store millions
- Server can process millions of records per hour and store billions
- DMS Client:
- Pentium III class PC or better
- Broadband connection to DMS Server

## DMS Performance Benchmarks

- AIRNow DMC DMS will be running in August on a Quad Core Intel processor with 8 GB of RAM. The operating system will be Windows Server 2003 64-bit Edition
- •	Currently, AIRNow DMC processes 12 million records a day for over 5000 sites
- •	The DMS system can process 22 million records per hour or 1⁄2 billion a day (historical data import)
- •	The Bay Area DMS system will process 1 million data records per day

## DMS Storage Benchmarks

- The database will grow by 100 MB for every one million records
- One billion records can be stored in 100 GB data
- DMS query performance has been tested with 1⁄2 billion data records
- AIRNow DMC will test performance for over a billion records by fall

## DMS Can Be ...

- An instrument monitoring system
- A data analysis system
- A data Quality Check and Review system
- A data export tool (to AirNow, AQS)
- A data warehouse for historical data

## Agency Deployment

- DMS developed using AIRNow technology in 2004
- DMS to replace AIRNow technology by fall 2008
- DMS in use by BAAQMD, SCAQMD, and CARB by end of 2008
- AIRNow International will be based on DMS for 2009
- Stable DMS distribution package available for agency use by early 2009
- Looking for 3 or 4 additional agency development partners in 2008

## Development and Funding

- DMS was development and funded by agencies with an interest in the result
- Contributors include:
- Bay Area AQMD (BAAQMD)
- EPA Region IX, 103 Grant
- EPA National Environmental Information Exchange Network (NEIEN) Challenge Grant
- South Coast AQMD (SCAQMD)
- EPA (AirNow and AirNow International)
- California Air Resources Board (CARB) soon
- New DMS features must be available to all agencies
- New features must not reduce DMS functionality to other agencies

## Future DMS Development

- Enhanced view/edit features
- Viewing tools - fingerprint plots
- Statistical tools - box plots, reports
- Support for toxics, PM filter data, Special Purpose Monitoring
- Expanded reporting capabilities
- Instrument Control Charts
- GIS mapping capability, web services, and NEIEN XML data exchange node

## DMS Summary

- Simple, scalable, cost-effective
- Fast, secure, and efficient operation
- Comprehensive quality control tools
- Applicable to a wide variety of data
- Free to public agencies under a share-ware use agreement

## Contact Information

- Mark Stoelting
- Bay Area Air Quality Management District
- mstoelting@baaqmd.gov
- (415) 749-4619
- Eric Gray
- Sonoma Technology, Inc.
- eric@sonomatech.com
- (707) 665-9900