Department of Human Services Data Exchange


DHS Data Exchange Application

The Allegheny County Department of Human Services uses a data warehouse to collect information from over 185 sources and providers with many being external. It’s used to improve the services offered to clients as well as support management decision-making. It’s vital that this data is correct, clean and organized.

Data Warehouse Consultants (DWC) crafted an online application that allows external sources to check their files before they get loaded into the Allegheny County Data Warehouse.

Are you curious if you could benefit from this application? Check out our FAQ and contact us today to see what we can do for you.

Situation

Since the county receives large amounts of data from numerous external sources, it’s common for the data to be presented in spreadsheet form. This means that it’s often created by humans. Therefore, extensive validation must be completed to ensure data enters the warehouse correctly.

The warehouse will not accept or load bad data to prevent corruption of the systems already in place.

The county was in search of a process and system that would verify the data as it’s being loaded to allow direct flow into the warehouse.

Goal

DWC created a web-based tool called Data Exchange. The purpose was to give users the ability to do the validation. They are required to do the verification as they are giving data to the county.

They are prompted to fix errors before the data is loaded. Errors must be fixed as they actually prevent data from being loaded into the data warehouse. Users will upload a file to the application, see the errors that need to be resolved, make changes as needed and reupload so they can submit.

The main goal is to seamlessly load data into the data warehouse and reduce the amount of manual data manipulation that the county must perform.

Results

The county now has over 75 providers that are set up in Data Exchange. They use the tool to send their data ad hoc and it can automatically flow in when correctly formatted. The tool has been transformative for the data warehouse team since it requires very little maintenance on their end.

There are two additional results to note:

  1. The application saves time and money for users loading the files.
  2. The county has the capacity and ability to expand to larger and larger numbers of data providers thanks to the app being configurable.

Strategy

Developing an online application that enabled users to validate a file before transmitting it to a database became apparent. It was of utmost importance the application was intuitive and easy to navigate. This was accomplished by keeping the application to two pages, having clear sections and utilizing colors.

The preview page offers the most information on the status of the file. This is where validation and transmit details will be displayed after being compared to a predetermined format. Valid, warning and error records will be listed alongside accepted values.

Users can then return to the original file to make appropriate changes and updates. After, they must reload the file to check the new status. The transmit file button will only appear when at least half of the records are valid.

It’s important to mention that this system is configurable. It doesn’t need to be recreated for every new source. Configurations are quickly updated on the back end to set up new sources. They will then have the ability to submit their data to the warehouse.

Watch two of the DHS Data Exchange how-to videos below:

Although we are only showing two, these videos are used for many providers.

CAC Referral

 


 

Out of School Time