State/Federal Data Sharing Initiative Randy Hippen EPA OERR April 25, 2002
Overview • State/Federal Data Sharing Initiative • Two prototypes • Data Harvesting • Institutional Controls Data Sharing • Lessons Learned • New Directions
State/Federal Data Sharing Initiative Why is data sharing needed?
Data Harvesting Enables the integration of state and federal data, with minimal burden on the data providers.
Data Harvesting STEP 1: Submitting Queries Through the Data Harvesting Web site, users can submit queries on site remediation data. Topics for queries include: • Remediation Activities • Locational Information • Media and Contaminants • Institutional Controls
Data Harvesting STEP 2: Querying Data Using Information Builders, Inc. (IBI) EDA middleware, queries are submitted directly to state and federal data sources across the Internet. The data remain in their native format, with no need for manual data conversion. EDA has the ability to query virtually any type of data source, including databases, text files, and XML documents.
Data Harvesting STEP 3: Virtual Warehousing Query results are temporarily held in a virtual warehouse, where data from all sources are brought together and transformed into a common format. It is from this virtual warehouse query results are presented to the user through their Web browser.
Institutional Controls Data Sharing Facilitates the sharing and tracking of institutional controls data through a simple Web interface.
Institutional Controls Data Sharing Prototype Concept Importing data from Stakeholders who are already tracking Institutional Controls data Collecting data from Stakeholders who are not currently tracking Institutional controls data IN IN Central Repository OUT OUT Reporting tools for Stakeholders Public Interface
Institutional Controls Data Sharing STEP 1: Data Gathering Institutional Controls data are procured from state and federal data sources such as: • MO - DNR’s Site Management and Reporting System (SMARS) • SC - DHEC’s Site Assessment and Remediation Program (SARP) • NJ - DEP • EPA - CERCLIS The data come in multiple formats, including MS Access, Visual FoxPro, and Oracle
Institutional Controls Data Sharing STEP 2: Data Conversion The data are converted into XML. Commercial off-the-shelf tools such as XMLSpy, make database-to-XML conversion simple. An XSL stylesheet is applied to each XML document to standardize the data format.
Institutional Controls Data Sharing STEP 3: Data Loading The new XML data are loaded into the Oracle database by customized Java routines that utilize the Oracle XML parser and the Oracle XML SQL utility STEP 4: Sharing Data The Institutional Controls (IC) data sharing Web site gives visitors the opportunity to view IC data from all states and agencies. Agencies that do not have a means of tracking IC’s on their own can also use site to maintain their IC data
Institutional Controls Data Sharing Web Site Query Using a Web browser, visitors can submit predefined queries to search the database… …or they can create customized searches to hone in on specific sets of data.
Institutional Controls Data Sharing Query Results Page Visitors can also get more detail about the site, such as address, latitude/ longitude, media, and contaminants From the Query Results Page, visitors can view maps of each site’s location.
Underlying Technology Data Query and Compilation Mechanism Preparatory work necessary Flexibility/Scalability Ease of Implementation/Use Impact on Data Owners’ Resources/ Infrastructure Compatibility with EPA and Industry Data Exchange and Security Standards Demonstrated Use in Large Scale Data Exchange Efforts Comparisons
Lessons Learned • Agree on a common set of realistic goals. • Assess underlying source databases and technologies prior to selecting a tool. • Select the right technology for the task at hand. • Establish firm standards, rules, and operating procedures. • Establish a mechanism for evaluating success and fostering change where needed.
Additional Information Randy Hippen EPA Office of Emergency and Remedial Response Phone: 703-603-8829 email@example.com • EPA Electronic Data Registry: www.epa.gov/edr • EDA (Information Builders): www.iwaysoftware.com