Customer Innovation: Lights-out Healthcare Integration
LifeMasters Dynamically Matches Map Designs to File Types for Fully Automated Healthcare File Processing
Contributed by Steve Hromyko, ETL manager for LifeMasters Supported SelfCare, Inc.
Additional resource: Interview with Steve Hromyko from the 2007 INext user conference, 8 minutes.
LifeMasters' Business
Founded in 1994, LifeMasters Supported SelfCare, Inc. is a leading
provider of health promotion and disease management programs and
services that create health partnerships among individuals, their
physicians and payors. Insurance companies provide LifeMasters with a
list of patients with chronic conditions who can benefit from scheduled
reminders and other help with healthy diet, exercise, and medication.
By helping people with long-term illnesses like diabetes or asthma
maintain a healthier lifestyle, LifeMasters saves insurance companies
money while helping people live more fulfilling lives. Healthier lives
mean fewer insurance claims which benefits the insurance companies, and
in the long run, lowers insurance rates, which benefits everyone.
Challenge
I’ve always thought that ETL should be ETL-A. Pervasive software is
the "A" - Automation. ETL in itself is great. A lot of companies do
that. But, with Pervasive® Data Integrator™, it’s the automation that
is provided around the ETL process that really completes the bang for
your buck.
LifeMasters receives and processes large amounts of data from a
broad spectrum of sources. With growth in business came the realization
that data volumes would ultimately outpace their manageability.
Previous methods required a significant amount of expensive time from
data analysts, and meant that the only way to acquire more customers
and grow the business was to hire more people.
The data files that the insurance companies sent were dropped into
an FTP server in various X12 healthcare formats like HIPAA, HL7, and
NCPDP as well as non-standard flat and delimited ASCII and COBOL
formats. Data analysts would manually query that folder periodically to
check for files. The analysts would download, decompress, decrypt, and
further manually process the file, then run it through 10 to 20
different stored procedures to load this data through our system into a
number of tables that are then accessed by our home-grown application,
a data-driven web-based application. The challenge we faced was to turn
this manual, multi-step process into a smooth, automated procedure with
no error-prone human intervention unless a problem existed which
required human insight to solve.
We needed a set of tools that could not only digest and transform a
variety of file types (ASCII, COBOL, EDI), but that also provided added
functionality to enable full lights-out automation of our loading
process. Pervasive Data Integrator gave us this capability.
Solution
The old process typically took about five days for one of our larger
customers. With Pervasive's tool, we've gotten the process of loading
the data down to two hours. We now pull files from a secure FTP server,
decrypt, stage, transform, and load them to our SQL databases without
the need for human manipulation. This significantly reduces the time
required to process data, and the data itself is inherently more
secure.
From a process that was taking 7 to 10 days to load new custom data
files, we're now moving toward our goal which is to get that down to 1
to 2 days. And that’s for the tool, 1 to 2 days to work. The analyst’s
time will approach zero. That’s unseen, but very important. We can
bring the time down to load this data to a certain small number that’s
much less than what we do today, but the analyst’s time to load the
data is going to go away. They can use their time to do what they were
hired to do --analyze data and turn it into useful information.
Designing the Process
One of the aspects that we looked for when choosing a solution was
the ability to use our existing processes. The Process Designer
provides components to access existing technologies (SQL procedures,
DTS routines, and custom applications) so we can leverage the skill set
of our employees and reuse what is already built and works well. This
enables us to automate now and optimize over time. Out of the box,
Pervasive Map Designer provides considerable built-in functionality to
implement transformations quickly, without the need for development
resources, which was also a big plus.
We handle more than 120 different file formats, many of which
require unique transformation logic. Using Pervasive Process Designer,
we were able to build a single master process into which any file from
any source can be fed. The process queries the FTP server to see if a
file has arrived. It brings the file down, and then it goes through a
process of parsing the file, decrypting it, and unzipping it. That’s
all completely automated at this point. And then we upload that
information into tables that store information such as where the file
is located. We associate the file with a transformation. This process
is very dynamic in that there are no hard-coded parts. It’s all done
dynamically through variables and meta information loaded into tables.
The idea is straightforward, but very flexible. It is designed to
process files, not customers. As a file comes in, it's married
programmatically to its associated transformation and loaded. This
relationship, along with additional information needed to manipulate
the file, is stored in meta tables and gathered during runtime using
RIFL, Pervasive’s scripting language.
The transformation is done by calling another instance of
djengine.exe within the master process and feeding in macros. This was
done prior to the release of the Dynamic Map Invoker. I'm working to
put that in place now. As the process cycles through files, summary
e-mails are sent with statistical information regarding the data loads.
All logging information, reject file information, etc. is done with
macros. Nothing is hard-coded.
To maintain simplicity, we made a critical assumption that a data
file could be associated with one and only one transformation. When
processing a file, no matter how complex the layout, we use a single
mapping step to transform and move the data into a standard set of
tables. This can create challenges when working with files containing
complex looping structures, but Pervasive’s powerful Map Designer makes
this possible. As of yet we haven't come across a data file that cannot
be transformed using a single map. I don't expect to because of the
power of the tool.
We have sub-processes that run within this master process to send
out notifications when there are problems, to run quality checks, to
bring the data as a result of those quality checks into tables, and to
use that to further analyze the data. The idea behind this is to be
more proactive in pulling the data down and notifying our analysts when
there is a problem. We put the data through, but we also are aware of
what’s happening while we’re doing so.
Using Pervasive Data Integrator, we will be able to reduce the time
it takes to process files from days to hours. Further, as the
automation becomes more sophisticated, the time required by operations
personnel to monitor this process will approach zero.
Benefits
-
There is one single process to manage that handles every customer.
-
Any changes occur in the process file only. There are no supporting
files that need to be packaged as djars and uploaded to the server
because everything the process needs is pulled from the network from
locations stored in a meta table.
-
A number of sub-processes exist that can be called from the master
process for various purposes. Currently, only an email notification
sub-process is in place.
-
Any modifications that involve the use of more sophisticated process
components, such as an HL7 iterator, will be addressed using decision
steps in sub-processes.
In addition to these and the benefits I've already mentioned, one
clear benefit of the new automated process is getting new customers
implemented and up to speed faster. We receive monthly refreshes of
customer data and need to process that data as quickly as possible
because that’s how we get paid. By using the Pervasive toolset and
relying on standards, we can go through the implementation process much
quicker. We’ve got that down from around 90 days to around 3 to 4
weeks, even for the more complicated customers.
Looking Forward
In the processes we're working on now, we're making more use of
Pervasive® Data Profiler™. We want the ability to assess the quality of
data received from our clients prior to loading. Pervasive Data
Profiler lets us generate metrics quickly and easily so that files can
be analyzed and, if necessary, recycled back to the customer before the
loading process begins. If the data is bad, we package it up, send it
back to the customer with the information they need to fix it. Then,
they can resubmit it. It saves us a lot of time. In some cases we can
even use the Pervasive tools to cleanse the file ourselves. Naturally,
we are automating all of that, as well.
I think the main strength of the Pervasive toolset is flexibility.
Pervasive has a lot of built-in functionality which is great, but it’s
nice that there are interfaces and API’s so that if we need to get in
there and write a function or develop some specific path through either
API’s or through coding, we can do that. Pervasive Data Integrator has
all the pieces of the puzzle. It’s not just about transforming data.
The Map Designer is a big part of what we do, but we wouldn’t really
realize the benefits of the Map Designer if we didn’t have access to
the Process Designer to connect all these pieces together.
Honestly, even if we took all the stored procedures and just hooked
them together with statements and put them in a chain of processes,
that would justify the cost of this software alone. Keeping the
analysts from sitting there and monitoring these stored procedures is
worth a lot of money. So, I’d really like to emphasize the automation
and the full cycle of support that Pervasive provides.
We enjoy a great relationship with the Pervasive support and
services teams. Not only are they knowledgeable and proactive when
working with us to resolve issues, but they listen to us as a company
and are genuinely interested in how we use their software for our
business. A number of enhancements have made it into their tools based
on our feedback and ideas.