Showing 0 projects in 00 States



Focus Areas




  By State By Project
  • Environmental Data Warehouse Clackamas County Water Environment ...
    • Zoom Out
      There are no projects in this viewport.
      Zoom out to see more.

Environmental Data Warehouse


Windsor partnered with Clackamas County Water Environment Services (WES) to build a user-friendly, integrated data warehouse application. The data warehouse integrates data from seven different systems including ambient and in-plant water quality, treatment plan operations, financial data from the County’s PeopleSoft Financials system, employee timecard data, and customer data into an integrated desktop viewer. WES staff use the data warehouse query wizard to easily construct an integrated view of disparate data over time and to spot trends. The data warehouse includes charting functions and the ability for users to create, save and share custom queries and data extracts in Excel format.

The project was performed in several incremental projects, each with its own requirements gathering, design, build, test, and implementation phases. The first task was for Windsor to help WES develop a vision for the data warehouse. To accomplish this, a business needs assessment was performed with WES management. The product of the assessment was a long-term vision for the data warehouse composed of discrete, incremental expansion over time.

The most immediate need was to provide management and staff with access to laboratory and plant operations data. To meet this need, the initial project established the desktop data warehouse application and integrated laboratory and wastewater treatment operations data, making this data available to non-laboratory staff for the first time. Extract, Transform, and Load (ETL) routines were developed to integrate data into the warehouse database from four different sources of operations and laboratory data.

The following year, the data warehouse was expanded to include financial data from the County’s PeopleSoft Financials system. First, a requirements analysis was performed. WES staff indicated that it was very difficult to extract summary revenue and expenditure data from PeopleSoft. To address this, Windsor built ETL routines to extract totals from the PeopleSoft Financial general ledger into the data warehouse. The Query Wizard was expanded, allowing staff to plot financial revenue and expenditures against laboratory and operations data over time. This enabled WES staff, to determine whether a capital investment in treatment technology correlated to an improvement in water treatment efficacy or affected electricity consumption at the plant, for example.

The success of the first two projects prompted WES to again partner with Windsor for yet another expansion of the data warehouse. The third project integrated data from the Utility Billing System (UBS) into the warehouse. The UBS is used to produce invoices to residential, commercial and industrial customers of the County’s sanitary sewer system. After requirements were gathered and design was conducted, Windsor integrated customer data into the warehouse, again allowing WES staff to trend customer data over time. With customer data in place, WES could now view trends in customer growth by area and customer type and overlay the data with financial, laboratory and operations data.


CLIENT: Clackamas County Water Environment Services
DISCIPLINES: Water, Cross-discipline

Interested in learning more?

Steve Rosenberger

Contact Steve Rosenberger
Windsor Solutions
(503) 675-7833 ext. 204