Roster Management
Business Problem
One of the largest healthcare networks in the country approached us about streamlining a process that was manual and incredibly inadequate. Membership demographic data was being provided on a monthly basis from 9 different Health Plan providers. This data was being sent in various formats: Excel, text files, tab-delimited, etc. and the data being provided was obviously distinct and in no particular standard layout.
Individuals at each of the hospitals would consolidate the data manually and organize the data to the best of their ability and then print this large list of members and deliver the lists to each of that particular hospitals point of intake for member verification, plan information, etc. The process took up to 2 weeks of 1 person’s time each month. There was no reporting, and the time spent manually looking up a member’s information at point of intake was causing a great deal of dissatisfaction with the member population.
Business Solution
The solution that we put together was built using the power of Microsoft SQL Server. Because they were accustomed to Java-based applications we built a web front-end client that consisted of:
- An Administration Module – User accounts, security access right, and screen rights.
- Search Interface Screens – End-user search interface on the various data elements available. This included over 21 distinct attributes that could be searched to return an individual or set of individuals.
- Reporting – This included various canned reports and graphing with KPI indicators for management users. Financial reports were included as well for reconciliation of monthly capitation payments. Export to Excel capabilities were also a part of every list that was generated by the end users.
- Data Upload – Administration module to upload each individual plan
The real guts of the application however was done on the SQL Server side of the application. As a file was uploaded to the application server, we needed to create individual DTS packages for each type of file and that would be pushed into a staging table where individual stored procedures would process the data and normalized the data into a consistent format for our front-end application.
Business Result
This solution has proven to be quite valuable for the 11 hospitals and over 27 outreach facilities that are currently using the application. The obvious ROI wins came immediately when the solution allowed the managed care office to save over 80 hours each month in processing costs.
Customer service at point of intake was cut from 5-10 minutes down to 2 with the instant access of data via the solution’s web front-end.
Management and financial reports allowed the facilities to process payment and reconciliation data. Also, demographic analysis, new member, re-certification, and trending information over time provided the marketing and sales departments at each hospital network vital information on outreach efforts and marketing plans.
The solution is still in place today.
Technologies Used
- Microsoft SQL Server 2005*
- Data Transformation Services**
- Reporting Services
- i-Net Crystal Clear
- Eclipse
- Apache Tomcat
* Database has been upgraded to SQL 2008 R2
**All DTS Packages have been upgraded to Microsoft Analysis Server 2008 R2