Some Inconvenient Truths About SDWIS

Recently, the Association for State Drinking Water Administrators (ASDWA) published a report titled Beyond Tight Budgets 2018 Resource Demands Analysis for State Drinking Water Programs. The report discusses the tight funding that drinking water programs are experiencing around the country despite the demands placed on them. Page 4 of the report, under The “Safe Drinking Water Information System,” section briefly describes SDWIS Prime and the Compliance Monitoring Data Portal (CMDP):

SDWIS Prime is the replacement for both the state and federal sides of the Safe Drinking Water Information System (SDWIS) which handles data for all drinking water activities. As such, it is critical for state implementation and EPA oversight. This effort, which began in the late 1990s to develop an updated electronic system, should offer significant advantages over the current SDWIS. If it is not done right, state SDWA implementation could crash. Therefore, states have been putting significant effort into monitoring the development effort and providing input on Prime and the Compliance Monitoring Data Portal (CMDP). Communication challenges on the EPA side have created a need for extra vigilance by states. Additionally, uncertainty about the timing and resources needed for the transition process has further complicated states’ planning processes.

In this article, I will attempt to clarify some statements made in this section concerning SDWIS, the SDWIS Prime project, and the CMDP system.


Before I start, a little legalese is in order. The opinions expressed in this article are mine and mine alone. They do not represent the official policy of the United States Environmental Protection Agency (EPA) or any of its employees.

What is the Safe Drinking Water Information System (SDWIS)?

SDWIS is not a single system, but an information technology program supporting EPA’s pubic water system supervision program. SDWIS consists of a collection of systems, projects and services managed by EPA’s Office of Ground Water and Drinking Water (OGWDW – located in the Office of Water, or “OW”). The diagram below shows the various systems in the SDWIS program:

Figure 1
Current SDWIS Program System Portfolio

As you can see in Figure 1 above, the SDWIS systems portfolio is divided into a State side and a Fed (also known as “SDWIS Fed”) side. State-side components, though developed and maintained by EPA, are installed and operated by the reporting agencies. The SDWIS Fed components are installed at EPA’s National Computer Center (NCC).

A “reporting agency” is a state, US territory, the Navajo Nation having primary enforcement responsibility under the Safe Drinking Water Act (SDWA). EPA regions are also reporting agencies, but as they are part of EPA, delegated primacy responsibility for enforcing SDWA is not required of them.

There is more to SDWIS than Systems

From a program management perspective, the SDWIS program is divided into three portfolios, or logical groupings of systems (in Figure 1), services and projects. The services and projects portfolio and their components are:

Services portfolio components:

  • Compliance Monitoring Data Portal (CMDP) operations and maintenance
  • CMDP user support
  • Legacy SDWIS State/Fed user support
  • Production control processing – activities for processing federally required data reported to EPA and loading the data into the data warehouse
  • Operating and maintaining the Fed side components
  • SDWIS EPA-state governance

Project portfolio components:

  • SDWIS modernization project – this is the project for acquiring SDWIS Prime (more commonly known as the “SDWIS Prime project”) and, at the time this article was written, the largest consumer of the SDWIS Budget.
  • SDWIS State Java Upgrade – the project to update SDWIS State to make it compatible with the latest version of Java.
  • CMDP update – the project to fix software bugs and to enhance some CMD features.

The rest of this section focuses on the systems portfolio and its components since they tend to cause the most confusion when discussing SDWIS. I’ll start by discussing the state-side systems following-up with the fed-side systems.

State-Side System Components

Currently, there are two SDWIS state-side components: the legacy SDWIS State system and FedRep.

SDWIS State was developed by EPA and its contractors and made available to reporting agencies to help them mange their drinking water programs. When I was working at EPA (before I retired), there were about 55 instances (shown in parenthesis in Figure 1 above) of SDWIS State running across the country. The diagram doesn’t do justice to the complexity of SDWIS State because there are about 10 separate components making-up the system.

FedRep is a separate application developed and supplied by EPA to the reporting agencies. The applications helps with quarterly data submissions to EPA through the fed-side discussed a bit later. FedRep:

  • Extracts data from the local SDWIS State database and writes the data out into a standard file format suitable for transmission to EPA
  • Checks the XML (eXtensible Markup Language) structure of the files
  • Performs a series of data validation checks on data in the transmission files and generates warnings and errors for fixing data quality issues
  • If so configured by the reporting agency, stages the files for transmission to EPA through the reporting agency’s Exchange Network node.

Off the Grid – Going SDWIS “Free”

Approximately 10 reporting agencies use proprietary systems other than SDWIS State. These systems are not supported by EPA and do not appear on the diagram. There are also reporting agencies (the exact number escapes me) using SDWIS State as a “pass through” between their proprietary system (or systems) and SDWIS Fed.

Of the reporting agencies that are EPA regions, three (maybe more) use a proprietary system developed by EPA region 9. While this system runs on EPA infrastructure, it is treated like a proprietary system because it is different from SDWIS State.

The EPA regional proprietary system and the reporting agency proprietary systems each use unique database structures unknown to EPA. Because of this, FedRep cannot extract data like it does for SDWIS State. Instead, each reporting agency extracts the data required for EPA reporting from their proprietary database and writes the data out to files using the same file format FedRep uses. FedRep “reads” these files and process them as if they were generated from SDWIS State.

Fed-Side System Components

All of the components on the fed-side, know as “SDWIS Fed”, run at EPA’s national computer center (NCC) located at Research Triangle Park in North Carolina. The SDWIS Fed components are:

Central Data Exchange – (CDX) a web portal used by many EPA media programs for accepting data submissions in bulk from regulated entities. EPA’s Office of Environmental Information (OEI) maintains CDX. Reporting agencies submit their transmission files generated (or checked by) FedRep to CDX by manual upload through the Exchange Network Services Center or through their exchange network node.

Production Control – A web application used by EPA staff to monitor processing of the files submitted by the reporting agencies through CDX. EPA regional coordinators use Production Control to review and approve submissions made by primacy holders located within the coordinator’s region. EPA regional personnel also manage federal actions (violations and enforcements) using a Production Control feature. This feature will likely move to SDWIS Prime when it goes live. Production control was recently upgraded by EPA.

Operational Data Store – Production Control parses the submission files, checks the data in the files again, and then loads the resulting data into the Operational Data Store database. This database is a “holding tank” for consolidating the data from the 68 SDWIS State and proprietary systems used by the reporting agencies. SDWIS Prime, with its single, centralized database, renders the Operational Data Store redundant and EPA may decide to retire it at some point after a critical mass of reporting agencies have move to SDWIS Prime.

Data Warehouse – After all data from the reporting agencies are loaded into the Operational Data Store, a Production Control process copies data to the Data Warehouse. Along the way, the process also summarizes data and calculates derived values. EPA uses the Data Warehouse as the single source of drinking water data for Fed Reporting Services, the GPRA reporting tool (both described below) and EPA’s Envirofacts and ECHO web tools. The Data Warehouse is the source for drinking water data for other federal agencies and is the official record from the federal side used for Freedom of Information Act (FOIA) data requests and storage at the National Archives.

GPRA reporting tool – available the public, users can generate reports showing drinking water program measures broken-down to the state, territorial, and tribal levels. The tool uses data housed in the Data Warehouse.

SDWIS Fed Reporting Services –also available to the public, is a robust system for generating all kinds of reports based on drinking water data stored in the Data Warehouse.

Compliance Monitoring Data Portal – (CMDP) while not technically part of SDWIS Fed, this system is operated and maintained by EPA and shares its database with SDWIS Prime. I’ll dive into CMDP a bit more later in this article.

What is SDWIS Prime?

SDWIS Prime (short for “Primacy Agency”) is a new system slated to replace SDWIS State. The SDWIS modernization project, commonly called the “SDWIS Prime project”, is the project to acquire the SDWIS Prime system. The ASDWA report conflates SDWIS with SDWIS Prime. As I described above, SDWIS is the program and the SDWIS modernization project is the effort to acquire the SDWIS Prime system through custom software development.

Figure 2 shows what the SDWIS system portfolio might look like sometime after SDWIS Prime goes live and reporting agencies have decommissioned their SDWIS State instances:

Figure 2
Future SDWIS System Portfolio

My “crystal ball” version of SDWIS has a couple of changes from what we saw earlier:

Local Data Mart instead of SDWIS State – EPA has since renamed this to something like “Local Data Repository”, but it is the legacy SDWIS State database (minus the application layer) meant to assist reporting agencies with moving to SDWIS Prime. Several years back, the EPA project team proposed setting up a synchronization process that would keep the local data mart up to date with SDWIS Prime. Reporting agencies would use the local data mart and the synchronization process to maintain their investments in “interfacing applications” (described later). These applications, which use SDWIS State data, would “point” to the local data mart instead of SDWIS Prime. It’s a rather ingenious way to keep these interfacing applications going without having to spend a lot of resources to replace them.

No separate CMDP component – Later in this article, I’ll describe how EPA can merge CMDP into SDWIS Prime, CDX and Production Control. Doing so simplifies the system portfolio and removes redundant functionalities and improves efficiency.

The upshot of this long winded explanation is that SDWIS Prime only replaces SDWIS State, the Operational Data store, and, potentially, the samples and operational data entry and file submission features of CMDP. It does not replace “SDWIS”. It’s kind of an apples and oranges type thing – systems do not replace programs and projects deliver systems (and updates to them) and the services supporting those systems.

Other Inconsistencies

Other inconsistencies I noticed in the report are that SDWIS Prime:

  • Is not critical for state implementation and EPA oversight (the report says it is).
  • Does not handle data for all drinking water activites (the report says it does).
  • Development did not in the 1990s as the report states, but in 2010.

I’ll clarify these inconsistencies in the next sections.

SDWIS Prime is not Critical for State Implementation and EPA Oversight

As I discussed at the beginning of this article, SDWIS State is not used by all of the reporting agencies and EPA does not require them to use it. Similarly, EPA (unless it has changed this position since I retired) will not require reporting agencies use SDWIS Prime. Furthermore, several reporting agencies currently using proprietary systems have no plans to move to SDWIS Prime.

If SDWIS Prime is critical for state implementation, then EPA would have required that all reporting agencies use it.

SDWIS Fed components are critical for successful state implementation, but, as I have discussed, all of these components are operated and maintained by EPA.

Having a well-designed and efficient SDWIS Prime will make the reporting agencies more efficient, resulting in cost savings and better public health outcomes over SDWIS State. This may not be the case when comparing a proprietary system to SDWIS Prime.

For example, the proprietary system may be highly customized to work seamlessly with the reporting agency’s business processes. Some reporting agencies may be in this position. But for others, their proprietary system, which may be just as effective, were built using obsolescent technology that is no longer being updated or supported by its vendor.

These reporting agencies are in a bind because most of them don’t have the resources to replace their obsolete systems. They’re depending upon EPA and SDWIS Prime. ASDWA is right to point out that every delay in SDWIS Prime affects these reporting agencies because they need to plan and budget for transitioning from their old systems to SDWIS Prime. And it’s tough to plan around a moving target.

SDWIS Prime does not Handle Data for all Drinking Water Activities

SDWIS provides a subset of features and data supporting the drinking water program, but it does not handle, as stated in the report, “…for all drinking water activities”. Figure 3 below shows the SDWIS Prime core features.

Figure 3
SDWIS Prime Core Features

The core features diagram looks fairly extensive, doesn’t it? And it’s based on the last version of the legacy SDWIS State system. But that system doesn’t handle all drinking water data, either.

Here’s some drinking water activities not handled in SDWIS State and not planned for inclusion in SDWIS Prime:

  • Tracking engineering plan review dates and events
  • Issuing operating permits to water systems
  • Tracking water system operator certifications
  • Collecting and managing fees from water systems (none of which goes back directly to EPA)
  • Collecting sanitary survey findings
  • Handling sample bottle labels
  • Robocalling water systems to remind them that samples are coming due
  • Tracking travel and incidental costs associated with sanitary surveys
  • Integrations with Geographic Information Systems to map aquifers, sources of water, and such

Reporting agencies acquired a host of systems and applications supporting the activities listed above not covered by SDWIS State. Several years ago, the SDWIS project team, coordinating with ASDWA, completed a census of these applications and identified several hundred.

Many of these proprietary interfacing applications use a combination of data stored in the local SDWIS Sate database and in the interfacing application proprietary databases. (Some reporting agencies may have modified their SDWIS State database schemas and added new database objects, but the SDWIS Prime team was never clear on this.)

A critical user acceptance risk faced by the SDWIS Prime team is that most interfacing applications use an old (but timeless) technology called “Open Database Connectivity” (ODBC) to connect their interfacing applications to their legacy SDWIS State local database instance. As the new SDWIS Prime system is centralized and the states access it over the world wide web, the safest way to use ODBC connections over the web is through an expensive virtual private network (VPN) solution.

The prohibition against using ODBC connections across the web without a VPN was a critical factor preventing EPA from moving SDWIS State databases into NCC (an earlier SDWIS Prime alternative called “SDWIS Central”). To avoid the expense of a VPN, SDWIS Prime uses a ReST application programming interface for exchanging data between the central SDWS Prime database and reporting agency interfacing application databases. However, reporting agencies have to retrofit ReST into their interfacing applications and, consistent with ASDWA’s report, don’t have the resources to do this.

The local data mart, shown earlier in Figure 2, is a strategy developed by the SDWIS Prime team to help reporting agencies maintain their interfacing applications without having to rewrite them. CMDP uses a similar approach to synchronize data between a local SDWIS State database (which becomes the local data mart) and the CMDP centralized database (which is the nascent SDWIS Prime database). The corresponding synchronization process for SDWIS Prime and local data mart would have to be more robust and easier to setup and maintain than the process currently used with CMDP (called the Data Synchronization Engine, or “DSE”).

But the main point is this: if SDWIS did handle all data for all drinking water activities, then interfacing applications would be unnecessary.

Building SDWIS Prime so that it does handle all drinking water data would be breathtakingly expensive and time consuming. Like SDWIS State, SDWIS Prime was never intended to handle all drinking water data

SDWIS Prime Started in 2009

This section gives a brief history of state side of SDWIS, culminating with SDWIS Prime. I can’t give too many details before 2009 because that’s when I started with EPA and, being a pensioner, I don’t have access to earlier documentation.

EPA started developing SDWIS/LAN, the SDWIS State predecessor, in the 1990s. However, SDWIS Prime didn’t start until 2010 when EPA completed the cost/benefit/alternatives analysis for a SDWIS State replacement. The initial version of the analysis was shared at the 2009 ASDWA Data Management Users Conference. Shortly thereafter, EPA worked with ASDWA to establish the SDWIS Steering Committee and User Advisory Group. Up until that point, there was no effective joint EPA-state governance of the SDWIS program.

From 2010 through early 2013, EPA conducted a number of requirement sessions with drinking water program subject matter experts, created screen mock-ups, and setup the technical framework for SDWIS Prime. Sprint 1 of SDWIS Prime development started at the beginning of March 2013.

The team had to stop the project after six months because the proprietary technology framework used at the time was too labor intensive and ill-suited to the SDWIS Prime user access and data security requirements. The team restarted development six months later, using a new Java-based framework (Spring and Google Web Toolkit, or GWT) the development contractor chose to address issues with the earlier framework.

EPA suspended SDWIS Prime development again a year later to focus on CMDP development. During this time, the SDWIS Prime team was busy working with a user experience (UX) designer and developed a new user interface that took a task scheduling approach commonly used with project management. The team identified an off the shelf (COTS) task scheduling component that would integrate into GWT, make the developers more efficient, and trim significant time off of the project schedule.

However, the contractor (now a different company, but with mostly the same developers hired from the old contractor) preferred a different COTS component that was incompatible with GWT and lobbied EPA to change the framework again to Angular JS.

With much justified trepidation, OGWDW management approved the switch to Angular. Development started again in the fall of 2016 with the contractor not using the COTS scheduling component they recommended, but shifting to Angular JS. While the developers could reuse many components from the earlier GWT version of the system, they had to start from scratch and build a totally new user interface.

Let’s pause for a second. The contractor decided not to use the COTS scheduling component they selected and claimed that EPA approved this decision. EPA wasted a lot of time and effort and consultation with General Service Administration’s (GSA) 18F web development experts to justify the contractor’s desire to switch to from GWT to Angular. And to top it off, the contractor chose the obsolescent Angular JS version. I’m highlighting this because it’s a symptom of a broken project change control and project management process.

The prototypes demonstrated to the SDWIS user community at the 2016 Data Management Users Conference, particularly the scheduling user experience which relied on the off the shelf task scheduler, would not accurately portray the system the contractors would deliver. It also ensured that EPA would not make the March 2018 completion date as promised to the EPA executives and the reporting agencies at that time.

Concurrent with application programming (started in 2010) was the effort to define the business logic for the national drinking water rules and write the logic for the Drools business rules engine. This effort continued on despite the stops and restarts of SDWIS Prime development. A challenge the team experienced (and was still dealing with when I worked at EPA) was integrating the business rules engine development into the application development stream. Hopefully they’ve worked-out the issues since then.

In the late summer/fall 2017 the SDWIS Prime project team conducted the first round of user testing. About 40 reporting agencies sent in data for testing purposes, including data for testing three drinking water rules. In addition to identifying issues with the SDWIS Prime database design, the test protocol proved unrealistic and cumbersome and only a handful of reporting agencies were able to complete the test. Development continued into the new year with plans for a “version 1 release” by October 2018.

I retired from EPA at the end of March 2018, so I don’t have much in the way of details beyond that time.

That’s SDWIS Prime development in a nutshell during my time on the project. I’ve left out a lot of details because they would have made this posting painfully long.

Communications Issues

ASDWA has gone to great lengths in making sure its stakeholders received as much information as possible about SDWIS and the SDWIS Prime project. I agree with the ASDWA report that much of the miscommunications and misunderstandings originate from the EPA side.

Any large, complex software acquisition project like SDWIS Prime having many independent stakeholders, maintaining effective stakeholder communications is a challenge. Having poor project fundamentals, which, in my opinion, also plagues the SDWIS Prime project, makes effective communications harder.

Projects are like baseball teams in that you can’t expect a winning record by playing with poor fundamentals. EPA should review how it manages the SDWIS IT program, paying particular attention to:

  • Sponsorship at the EPA executive level (since EPA is paying for SDWIS Prime)
  • An effective project scope/change control process to prevent unmanaged change
  • Stakeholder governance to facilitate communications between EPA and ASDWA stakeholders
  • Well defined project team member roles and responsibilities
  • Leveraging modern project and program management practices
  • Using good conflict resolution techniques, especially to guard against “group think”

As far as improving communications between ASDWA members and EPA regarding the SDWIS program and the SDWIS Prime project in particular, the first step is to look at the Advisory Board composition. ASDWA and EPA should consider placing a member of the ASDWA Board on the SDWIS Advisory Board as a liaison.

ASDWA and EPA should also consider re-staffing the SDWIS Advisory Board with individuals at the executive level in the reporting agencies at a level comparable to the ASDWA Board. See my LinkedIn article Stakeholder Governance for more information.

Beyond the ASDWA Report

I agree with ASDWA that the drinking water program is woefully (my emphasis) underfunded. I saw this every day during the commute to my job at EPA. Every morning I would hitch a ride to the Pentagon and then catch a train to Federal Triangle – the Metro stop underneath EPA HQ.

As I walked through the Pentagon parking lot from the carpool drop-off to the Metro station entrance, I would marvel at the constant construction going on. I often thought about what EPA could do with SDWIS if it had the equivalent of the Pentagon’s parking lot construction budget. It also made me think about priorities – is Pentagon parking lot traffic realignment more important than water safe to drink?

But getting back to reality, here’s some things that ASDWA and EPA should be on the lookout for with SDWIS:

Data Model Dated?

SDWIS Prime uses essentially the same data model dating from the 1980s. Is this still adequate? From what we saw of the interfacing application census, data reporting errors, long standing data quality issues, and new drinking water rule requirements, it probably isn’t. It also wouldn’t hurt to review the data definitions and make any adjustments (which would have to be retrofitted into SDWIS Prime).

Angular JS Obsolescence

As I mentioned above, the contractor chose the Angular JS framework for SDWIS Prime, but Angular JS development has stopped and long term support ends June 30, 2021. What is the contractor’s plan for moving SDWIS Prime to supported versions of Angular? What is the estimated cost of the next “replatforming,” if there is one, given that converting an application from Angular JS to Angular is not trivial?

What About CMDP?

After EPA completes SDWIS Prime development, it should seriously consider reengineering CMDP and merging it into SDWIS Prime and production control. (A potential Angular JS – Angular conversion adds another layer of complexity to this.)

When EPA “froze” SDWIS Prime development to focus on CMDP, it pared-down the SDWIS Prime application to the samples and operational data management screens, creating a new CMDP web application. Unfortunately, the CMDP team (which did not include drinking water IT specialists) duplicated the data submission features of EPA Central Data Exchange (CDX) into the CMDP web application.

EPA should consider leveraging CDX and the SDWIS Fed production control process for samples and operational data submissions by the laboratories and water systems. In addition, EPA should also think about integrating Shared CROMERR Services (SCS) with SDWIS Prime to implement the on-line data entry features for samples and operational data entry for users representing regulated entities (consistent with the original SDWIS Prime plans).

In the long run, EPA would save operating cost and leverage CDX, which is better suited for bulk data submissions than the CMDP web application. There are other disconnects between CMDP and SDWIS Prime that EPA should correct that I won’t get into here because of the details.

Re-Think the Business Rules Engine

Machine learning technology has changed dramatically since SDWIS Prime development started in 2014. Originally, it was thought that SDWIS Prime could leverage the Business Rules Engine (BRE) to help create monitoring and sampling schedules consistent with the drinking water regulations. With advancements in machine learning and artificial intelligence, it’s worth investigating to see if it could be more effective and efficient than using the BRE for creating schedules (and possibly compliance determinations).

The development team should look at leveraging the simulation tools available in with the business rules engine. Using simulations should alleviate the burden like that experienced in round 1 user testing and give better, less ambiguous testing results.

The development team should also be on the lookout for overusing the business rules engine. There are some decisions (like checking for monitoring and sampling violations described in this article) where using the business rules engine might be overkill.

Ensure Auditing Features are Core to the System

SDWIS Prime was originally designed for audibility. The system should keep track of who did what when and why they did it. For example, let’s say SDWIS Prime generates a candidate violation and a compliance officer rejected the candidate. The compliance officer would have to select a reason for the rejection and SDWIS Prime would record the reason code, date/time of the decision, and the user’s system ID. As the system (through the business rules engine) is considered the umpire for making compliance decisions, EPA and the primacy holders and EPA regions could use the auditing information to assist with oversight.

My concern with building this portion of SDWIS Prime is that the drinking water administrators – the executives running the primacy holder programs – never seemed to show much interest in this. I proposed giving briefings at ASDWA conferences about SDWIS Prime proposed auditing and business activity monitoring (discussed next) several times, but it went nowhere. If there isn’t much interest shown by the executives, then it won’t be a priority to the state representatives working with the developers. It might not get done.

By the way, EPA needs the auditing features for its program oversight role. Without it, oversight will be labor intensive, more expensive, and less effective than if the audit features were “baked” into the system.

Don’t Forget Business Activity Monitoring

One of the more intriguing possibilities with SDWIS Prime is the possibility of using auditing data for business activity monitoring. For example, SDWIS Prime could generate reports showing the average time between when the system generated a candidate violation and when a compliance officer accepted or rejected the candidate. This would help management identify processing bottlenecks and make adjustments to improve overall efficiencies.

Business activity monitoring reports could be used to justify more resources for a drinking water program. For example, tier III public notice (PN) violations are ignored by some regulators because they 1) usually aren’t indicative of an immediately public health issue and 2) don’t have the personnel resources to handle them. SDWIS Prime could automatically accept a tier III PN violation and then rescind it due to insufficient resources to process. A drinking water administrator could then use the business activity reports from this PN process to show work not getting done, but automatically handled by the system because of a lack of resources.

Keep it Simple

One of the more frustrating things I saw during my time on the SDWIS project team was the tendency to make things more complicated than needed. From the business rules engine programming to the burdensome round one user testing and adding in excessive task completion features, I saw unnecessary complexity going into and around the system.

The more complexity added, the more code the developers have to write, the greater the number of moving parts in the system, the more testing that has to be done, the greater the opportunity for missing something so the users find the bugs, and the greater the cost. I must have sounded like a broken record repeating this mantra during our sprint standup meetings.

Furthermore, the time and effort spent in adding overelaborate features is wasted effort. Simpler solutions are better and the simplest solutions are the best. You can always make it more complicated later if you really have to.

It is also important to differentiate between a want and a need. I ran into many users who prioritized user stories and other requirements based on how badly they wanted it, regardless if the feature was a critical need or not. I want to drive to my gym in a Ferrari, but I can just as easily get there just as fast driving a Volkswagen Golf.

Be Open to Open Source

Earlier in this article I mentioned the SDWIS Prime ReST API (application programming interface). The idea behind this interface is that reporting agencies can build alternative user experiences that use SDWIS Prime data and functions.

Think about that a second. A developer could completely replace the “out of the box” SDWIS Prime user experience with, say, an iPad app.

I strongly suggest that EPA and ASDWA consider opening the ReST API to the public so that any developer can create open source products reporting agencies can leverage to their advantage.

To do this, EPA will have to come up with a “sandbox” SDWIS Prime containing nonsensitive test data that developers can program against. Wouldn’t it be cool if there was a SDWIS app store?

5 responses to “Some Inconvenient Truths About SDWIS”

  1. Thanks, Stephen. I hope this article has some influence on the project.


  2. Well done! All I have to add is Hire the Right Vendor for Job. Keep it simple.Just cannot keep re-platforming. Make a Most Viable Product from with few states release and go from there. BRE is a very bad idea. Not everything can be automated.


    1. Thanks for the comments, Mittch. A big challenge with the SDWIS Prime project is that the stakeholders early on, and consistently, insisted that the new system have equivalent features and functions as the legacy SDWIS State system. This despite the fact that very few states used the full capabilities of SDWIS State to begin with. In a sense, the stakeholders are demanding a Maximum Viable Product instead of a Minimal Viable Product. I think the BRE had some merit some 9-10 years ago when it came up, but I think that time has probably passed now. I didn’t have a whole lot of confidence in the BRE integration with the system when I retired. Hopefully things have improved since then.


  3. Hi Greg,
    Have read EPA approach to fix the data model. I found it so vague and high level for regular ITS like me to comprehend.


    1. Hello, Doug! Good to hear from you! Since I’ve retired, I have limited access to information about SDWIS Prime. I’ve gathered from the ASDWA News Room that EPA was looking at doing something with the SDWIS Prime database, but I don’t have any of the details. I do remember that EPA encountered problems when trying to upload data submitted by the states into the SDWIS Prime database for testing the Business Rules Engine. I believe this was back in the fall of 2017. EPA should have been able to use CMDP to do the data transfer, but some poor technical and design decisions made by the Protection Branch (which had the lead) and the PMO during its development made that impossible without having to go back and make significant and costly changes. If you recall, EPA used to maintain a spreadsheet that mapped SDWIS Prime to SDWIS State data elements (you can probably find it in the SDWIS Prime SharePoint site), but that stopped when the project shifted from Google Web Toolkit to Angular JS. So, it was up to EPA (the Infrastructure Branch) to do the data mapping in order to load the data submitted by the states into SDWIS Prime for the testing. This proved to be very difficult because of the way the SDWIS Prime database was structured. At first, I was really worried and expressed my opinion that if EPA was concerned about the database structure, it should pause the project and refactor the database. However, before I retired, I came to the conclusion that the database design is a red herring. It really doesn’t matter what database structure is used so long as the services are there for providing the data in the form needed when it’s needed with the appropriate security applied. IMHO, I’d focus on making sure the services are up to snuff. But my opinion may be obsolete since so much time has passed since my tenure on the project effectively ended in late 2017. In any case, I hope they straighten out the issues because the program and its stakeholders deserve a decent data system.


Leave a Reply to Greg Fabian Cancel reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: