# Presentation: 874926

## Bay Area AQMDData Management System (DMS)

- Prepared by:
- Mark Stoelting
- Bay Area Air Quality Management District
- San Francisco, CA
- Presented at:
- 2008 National Air Quality System Conference
- Milwaukee, WI
- August 21, 2008

**Bay Area AQMD****Data Management System (DMS**)

## 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
- Minimize/eliminate need for paper
- Track instruments and performance
- Improve data quality (QC checks)
- Reduce data review time by using graphical tools
- Reduce time to submit data to AQS

## 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
- Staff notification (email, process controls)
- Export (AIRNow, web page, AQS)

## Simple Data Structure

- DATA

- SITE

- PARAMETER

- Instrument

- 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

- Op Code

- (optional)

## 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
- Automated Quality Checks establish QC codes, send alerts, flag suspect data
- Min/max, zero drift, sticking, rate-of-change, compare
- Electronic chain-of-custody created
- Sub-hourly data aggregated to 1-hr averages
- Automated data export to AIRNow and Air District web site
- Imports AQS Raw Data (RD) records

## 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)
- Flexible data export and reporting tools

## DMS Software Requirements

- DMS host computer:
- Windows Server 2000/2003 or XP/Vista
- Microsoft SQL Server 2005 (all editions including Express, which is free)
- 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 2008

## 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

## Funding and Deployment

- DMS was originally developed by Bay Area AQMD with EPA grant funds
- DMS will largely replace current AIRNow data management by fall 2008
- AIRNow International will be based on DMS in 2008-9
- DMS used by Bay Area AQMD, South Coast AQMD, and California Air Resources Board by end of 2008

## DMS Available to Other Agencies

- DMS is free to any public agency and may not be sold or used for commercial purposes
- DMS must be collectively supported by the agencies using it
- A User License will grant rights, limit liability and distribution, specify user obligations
- New DMS features must be available to all agencies and not impact other features
- A draft DMS User Manual is available now at ftp://ftp.sonomatech.com/public/DMS/DMS%20v2.0
- A DMS beta test version will be available for distribution after a User License is finalized
- A stable DMS distribution package will be available for agency use by early 2009

## 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

## Contact Information

- Mark Stoelting
- Bay Area Air Quality Management District
- mstoelting@baaqmd.gov
- (415) 749-4619