In order to do this effectively, APH exchanges data with its clients' past and current third-party administrators, pharmacy-benefit managers, laboratories, workers' compensation and biometric companies. All of that data comes to APH in different formats that must be translated into a single standard format for processing. To accomplish this, the organization used an integration solution based on an outsourced ETL provider and custom-coded SQL scripts, but this solution frequently broke down. Often, to get a new customer up and running took 20 weeks — and even longer if the outsourcer encountered issues. In addition, data quality wasn't under APH's control. Frequently, problems were only discovered after the fact, causing even more delays.

Chris Andrews, managing director of information, says, "The outsource provider was taking about five months or so to get a client onboard. The model for APH is a PEPM basis, so we charge per employee, per month. And, we can't start charging until companies and their employees are live in the system."

As APH's business and product offerings grew (270,000 patients currently), the company also needed more effective reporting and analysis. To that end, APH management planned a data warehouse for business intelligence and monthly file validation, as well as to house customer data.

Selecting the software

APH experts evaluated four different integration companies on 25 criteria, including ease of mapping, metadata management, data validation, scalability and financial stability of the vendor. In the end, only two of the four — Informatica and Pervasive — offered the tools with the necessary capabilities.

"We chose the one that demonstrated whose representatives knew what we were going through, and how we needed to get rid of the problems in our area," Andrews says. "It was a pretty easy choice going with Pervasive, especially when we considered just the overall costs compared to other integration suites. Some of the quotes that we saw were almost laughable. How could anyone justify ROI for those?"

Building the solution

Pervasive consultants architected the high-level processes for the initial APH migration, including profiling and analytics to make sure that only clean, valid data went into APH's data warehouse. APH staff attended data training, working alongside the Pervasive services staff at its headquarters, completing the work in tandem.

Initially, the plan was to use Pervasive Data Profiler to check only the data against business rules and provide detailed audit reports. However, Pervasive's workflow designer took the plan past basic file validation (such as "no blank required fields" or "dates in the proper format"). Andrews explains, "We're even doing eligibility checks within our project. The process design completed by Pervasive's consultants goes out into our data warehouse and checks to see if our clients' employees are eligible for the benefits we're about to process."

Processes were set up to handle the several hundred different types of files from third-party administrators such as Blue Cross, Blue Shield, Aetna and the many small regional groups. Pervasive Data Integrator pulls data from all of those formats, transforms the data to a standard format and validates the metadata against key data in the data warehouse.

Several project requirements changed midstream, causing some concern that the six-week implementation time line would be missed. Andrews says of the professional services consultants, "We had an aggressive time line, yet everybody was super-professional and flexible about all of our demands. Everything was turned around fast." The result was an easy-to-use system for non-technical staff, without the high levels of expense, time and upheaval normally associated with major integration projects of this kind.

Results

From the initial 20 to 28 weeks, new customer on-ramping was reduced to nine to 12 weeks with fully automated validation, thanks to reusable Pervasive integration components and the ability to handle virtually any format. "We only get paid when we're providing analytics," Andrews says. "Getting customers' data in the system faster allows APH to provide the analytics faster, which leads to faster return of revenues."

"Replacing the outsourced ETL process with the solution from Pervasive cut APH's new-customer load time in half," says Andrews. In addition, automated quality assurance and error checking decreased loading existing customer updates from eight hours on average to only four hours.

Even as APH's business has doubled in size, the organization hasn't had to add any new staff. The new data warehouse has become the hub of data integration in the enterprise providing better master data management, richer data segmentation and improved data and risk selection.

By discovering actionable data through data profiling, predictive modeling and advanced analytics, APH can more accurately evaluate historical trends as well as project future health costs and risks, thus better meeting customer needs. This enables APH to provide proactive suggestions to its clients to mitigate some of the claims payments and improve the health situation of patients.

Planning for the future

Nothing stays static in the current business climate, so the Pervasive integration workflow was designed for change. Since the business-oriented APH staff has full in-house understanding and control of the integration processes, the group has been able to organically add on functionality since the initial implementation to expand the company's business offerings. For instance, workmen's compensation claims are now checked for "double dipping" and fraud through the ability to check new claims against previous claims in the data warehouse.

Andrews says, "Our client satisfaction improved tremendously … partly because they're seeing that the data set we're giving them is really clean. Now the APH system from Pervasive has become the system of source for our clients — they really trust our data."

Matt Cheatham is managing director of information systems and data management at APH.