Windsor Solutions Designs and Builds a Data Warehouse for the Michigan Department of Environmental Quality
Windsor Solutions partnered with the Michigan Department of Environmental Quality (DEQ) to design a Facility Data Warehouse that would provide a consistent view of facility data throughout the agency and across the state.
Background
The Michigan DEQ administers a diverse number of programs that are designed to protect the environment and the public’s health through proper management of information. The agency is organized into several program areas: pollution prevention, air, land, waste, water and assistance and support services.
Like many environmental agencies, the Michigan DEQ has many different information systems that reflect regulations and organizational structure. However, internal and external stakeholders needed a more holistic view of the data regarding regulated activities occurring across the state.
The Challenge Presented
Technology drivers were pushing the DEQ away from a segregated regulatory approach to an agency-wide planning and reporting approach. The DEQ explained that department management, regulated entities, non-governmental organizations and the general public were frustrated that despite large investments, the existing systems failed to assess environmental programs at the facility or community level.
The DEQ wanted to bring this information, including GIS layers, together and make it available to stakeholders through a sophisticated data warehouse.
Windsor at Work: Our Solution
Windsor Solutions first conducted business needs analysis and created design tasks for the development of a browser-based application and enterprise-wide data warehouse that would support the integration of data from agencies in Michigan that are subject to environmental regulation.
A current systems assessment was conducted on seven agency program systems in order to develop an enterprise data model that would be a logical and physical database. Analysis tasks focused on how the different programs use facility information and how any differences in usage should be accounted for when designing an integrated data source for the entire agency.
Windsor Solutions then developed an enterprise-wide data model of the information that would be shared across the agency and conducted consensus sessions to ensure the model would allow duplicated data to be integrated without conflicting with any program’s specific data needs.
The design of the system was based on the issues of integrating and sharing common data across multiple agencies. This included geographic location data and affiliation data (facility owners, contacts and addresses). The team also developed a web-enabled prototype application to illustrate the capabilities that would be available to users once the data warehouse was completed. This early prototype enabled the team to receive valuable feedback from the stakeholder communities.
After the design phase, Windsor Solutions developed an Internet-ready, web-based data inquiry system that supports the following functions:
- Ad hoc reporting
- GIS interface for locating facilities and overlaying other demographic data layers
- Identification and reconciliation of duplicated data across the agency
Screen shots of Facility Data Warehouse application, click for details.
This gives Michigan DEQ the ability to access information about facilities in one easy-to-use system. The implemented enterprise-wide data warehouse features the following:
- Integrated facility identification data warehouse
- Internet-ready Web-based data inquiry system
- GIS Interface for mapping facilities
- ETL sub-system
- Client/server application for identifying and reconciling duplicated data
The next phase of the project will expand the system to include the remaining program’s facility data and enhance the GIS capabilities already provided. The project team also assisted the state of Michigan with a variety of strategic decisions to support cross-program data integration.
Results That Reward
The successful completion of the Facility Data Warehouse provided DEQ with several benefits:
- A consistent view of facility data across the agency and state
- Intelligent data cleansing
- The ability to view data via a GIS interface
- Synchronized data with source systems that ensure data integrity
- Integrated access to facility data, making information easily accessible to all stakeholders