CloverDX is a new name for CloverETL Learn more
Back in 2012, Health Research Incorporated were in the midst of a data migration and looking for a tool to migrate data from an Informix system to a Microsoft SQL Server environment. Director of Information Systems Paul Bartosik remembers “We looked at SSIS that came with Microsoft, and at the time it was terrible - SSIS essentially didn’t like working with any data or products that weren’t Microsoft”. After evaluating several data migration solutions, Bartosik concluded that “CloverDX was head and shoulders above the others”.
The Information Systems team thought they were buying a tool to handle their data migration project. What they have discovered in the 7 years since that project is that CloverDX can be a workhorse right across the business to save time and reduce effort in almost every aspect of their day-to-day operations.
Health Research Incorporated (HRI) handles the administration of research grants for health-related entities. In Bartosik’s words, they manage “the business end of the grants management” - payroll, purchase orders and financial transactions - which means data needs to be pulled from, and integrated to, many different sources.
Data coming from partners and needing to be pushed to financial systems and back-end databases forms a large part of the Information Systems team’s work. Posting payroll transactions, projecting effort allocation for future periods, loading data from the company’s cloud-based onboarding systems into internal HR systems - all these tasks require different data processes.
Bartosik explains “I would have to queue things up and interrupt a Java programmer who was engaged on other projects. So they’re working on deadlines, they’re got other projects that are up and running, and now I discover a problem in an existing data feed. We’re queuing that up as an interrupt to whatever they’re working on, like, ‘Stop, we need to fix it before tomorrow when the data comes back again’. So all of a sudden we’re pulling them off a key project to work on this little tweak.”
But Bartosik realized that he could use CloverDX to replace these custom written Java programs with reusable Clover data transformations and eliminate the bottleneck (as well as freeing up the development team to work on other priorities).
The flexibility of Clover meant that instead of having to go to a specialist developer to provide emergency fixes, the team could now handle much of the work of managing the data processes on their own. And because CloverDX is built to handle ever-changing data easily, tweaking processes became much easier and faster than having to completely rewrite code.
CloverDX also helped reduce the amount of problems that occurred in the first place by verifying and validating the data upfront, so errors were no longer being caught just with Java exceptions but were flagged before the process even started, reducing the manual effort needed to repair and re-run the jobs.
The team has created a set of data integrity alerts using CloverDX, which run daily or hourly to check the validity of data in HRI’s systems. Transactions against closed accounts; back-dated transactions; invalid data items; or mismatches between database elements are all detected, and any inconsistencies are identified and either fixed there and then or sent to users to act on.
And HRI have seen how well CloverDX can work with cloud-based software to make web service calls and reduce even further the amount of manual work they need to do.
Bartosik explains “We’ve actually used CloverDX to front-end our financial application and issue web service calls to effect transactions. Load the data up with Clover, then hit the system with a web service call to tell the third-party system to process the data we just loaded up.”
Bartosik says that relatively, the volumes of data they’re dealing with are small, and can often involve something like loading up just one purchase order. But as well as having to repeat processes again and again and spend time fixing code, problems also stemmed from the number of different file formats HRI have to work with.
He explains “Even in the current world of JSON, XML and CSV, there are still plenty of places, systems and processes - especially when it comes to government entities - that require fixed length data files with multiple record types, including the IRS 1099, SSA MMREF and NACHA clearing house files and more”
Another area the Information Systems team brought CloverDX into was analytics.
Previously, the team had been using Excel spreadsheets for analysis. But the big problem with using Excel was that nothing was repeatable. If a complex spreadsheet was handed off to someone else and returned with a comment such as ‘Some of the data’s changed, can you do it again?’, the analyst had to go right back to the beginning of their manual steps in order to recreate the process.
But Bartosik discovered that switching to CloverDX for these tasks could transform the way he and his team were working. “With CloverDX, I was able to replace these spreadsheets with Clover processes - processes which were then testable, repeatable and robust. I can now run a single CloverDX graph to perform processing that used to take hours.”
While HRI may have initially used CloverDX to help with their data migration, some of the main benefits they have seen have come from the team’s initiative in understanding where else Clover can make a difference. The problems solved could be seen as rather mundane, but for the Information Systems team, there’s a lot of value in solving them.
The flexibility and repeatability of CloverDX has meant that Bartosik’s team has been able to save time and decrease effort on day-to-day tasks, reduce reliance on expensive development teams, and make their data processes more efficient - adding value every day.Solving More Data Problems Than Expected: Download the case study (PDF) Explore Topic: Data Integration