Data Modeller

Position: Data Modeller
Duration: 15 Months + extension
Client: North Carolina Department of Environmental Quality
Location: Raleigh, NC, 27603


Introduction and Project History:

The Underground Storage Tanks (UST) Section (SECTION) manages the Underground Storage Tank program, the Non-UST petroleum releases program (including petroleum above ground storage tank (AST) releases and other petroleum releases), and the Ex Situ Petroleum Contaminated Soil Remediation Permit program. The SECTION issues permit, collects annual fees, and handles requests for information for regulated and/or commercial underground storage tanks. The SECTION ensures compliance with all relevant state and federal laws, policies, rules, and regulations by assisting owners and operators in complying with operational standards (leak detection, spill and overfill prevention, etc.). The SECTION also oversees the permanent closure activities of UST systems and corrective actions required to address spills and releases from USTs. The SECTION also oversees the administration of a trust fund for the reimbursement of cleanup costs associated with commercial UST petroleum releases.

The SECTION currently uses a combination of six (6) MS Access front end databases linked to six (6) separate back ends developed and operated by UST. Four (4) of the back ends are MS Access, two (2) are SQL Server. Some of the databases contain documents files, primarily Word documents stored as Binary Long Objects (BLOB). The SECTION's requirements have expanded to a point where it is necessary to develop a new system for stability, integration, and modernization. Two (2) of the three (3) UST Section branches (the Corrective Action Branch and the Trust Fund Branch) utilize the MS Access databases as does the Home-based inspections staff.

The SECTION also utilizes a legacy ORACLE database Tank Information Management System (TIMS) with custom Java-based web application, developed in the mid-2000's, with Facility, Billing, Inspections & Permitting. It is desirable, but not required, to have this information interface with the new system.

Business Need and Scope:

Goals of the Statement of Work The long-term monitoring and stewardship of an increasing number of incidents and tank data requires effective organization of both spatial and non-spatial data. This project will deliver a consolidated data model, workflow, and document solution that facilitates the SECTION's ability to make programmatic improvements to transparency, incident management, logistics, document management, and enforcement.

The SECTION requires a modern solution with the ability to track and edit incident data and associated compliance, enforcement, and claims data.

The SECTION requires a modern database solution to manage incidents assignments, workflows, claims against the funds, and possible enforcements. The system will need to generate and store documents related to the incidents and claims. Currently the files are stored as doc and docx in MS Access databases.

The SECTION requires a user-based security model for varying roles assigned to effectively manage the data with a high number of users including AGENCY employees and employees of other organizations. Previous inspection reports (PDF files) will need to be accessible for a user to search and view.

The SECTION requires a knowledge transfer and training series for all products developed under this contract.

Project Complexity

General Complexity

The CONSULTANT will evaluate the SECTION's existing systems and data to develop a costeffective replacement solution(s) to the SECTION's data management, financial, document, workflow, storage, and reporting needs.

All solutions shall be compatible with the NC Department of Environmental Quality (NCDEQ) and NC Department of Information Technology (NCDIT) systems' requirements.

The CONSULTANT will develop a solution using some combination of NCDIT approved technologies.

The CONSULTANT will develop a strategy for migrating legacy data into replacement solutions; storing, searching, and archiving documents; and, where appropriate, leveraging integration with Microsoft Power Platform technologies. Data migration will include data normalization.

The CONSULTANT will develop a strategy for extract and import of documents, ability to create, store, retrieve, and alter documents after migration as necessary. Documents must be available to the public. The current Division of Waste Management (DWM) document storage system is Laserfiche. It is desired that the solution should include use of Laserfiche for document storage.

The CONSULTANT will provide management and applications expertise that will be utilized to develop and implement the interface between the SECTION's solution and the ORACLE database.

The CONSULTANT will provide database design expertise to operate existing schema, provide recommendations on how to address weaknesses and areas of concern within existing schema, and develop new schema as required by the SECTION.

The CONSULTANT will help manage the collection, storage, and use of data that is migrated to the SECTION solution and will provide critical expertise and workflow to maintain and update the SECTION's data in real time.

The CONSULTANT shall utilize a cloud-based workflow and queue system to ensure work documents are moved efficiently to and from the SECTION and the CONSULTANT. File sharing platforms utilized must meet all requirements of NCDEQ and NCDIT.

The CONSULTANT shall provide a training workshop at implementation of the replacement solution. The training series shall be specific to the SECTION's needs and may be a mix of online and instructor-led courses.

The CONSULTANT will maintain regular and open lines of communication with the SECTION project manager throughout the duration of this contract.

Timeline

Discovery Phase = 2 months

Data conversion, normalization, and digitization = 3 months

Solution Development and Testing = 3 months

Implementation Phase = 9 months

Maintenance and updates to existing database features = throughout

Training and Knowledge Transfer = throughout

Resources

The CONSULTANT will be provided access to the existing custom MS Access based systems and schema.

Existing user manuals and workflow documentation will be provided to the CONSULTANT.

Digital records and data pertaining to conservation properties and easements will also be provided. It is anticipated this file sharing will be conducted through the CONSULTANT's cloud-based workflow and queue system.

Common file formats to be shared with the CONSULTANT include Adobe Acrobat (.pdf), AutoCAD (.dwg) and Environmental Systems Research Institute (ESRI) ArcGIS files as well as screen shots and snippet captures.

Property files will include Adobe Acrobat PDF copies of the recorded deeds and plats. Text metes and bounds descriptions for tracts and easement areas will also be provided.