Solving More Data Problems than Expected with CloverDX

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”.

But it was after the migration project was done that the team really began to see the power of CloverDX.

The Information Systems team thought they were buying a tool to handle their data migration project. What they discovered after initial migration 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. 

Handling data from multiple sources

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. 

The pain of manual processes and custom code

Before CloverDX, these processes were managed with custom written Java programs. But this was proving a major source of pain, for several reasons:

• Whenever they needed to fine tune something, or whenever the data changed, someone needed to go in and manually modify the code.
• Those constant code tweaks and fixes to accommodate ever-changing data had to be escalated to a dedicated Java developer.
• And for that developer, these fixes were a distraction from their main job. So not only was there a rush to fix something, but the fix was delaying other priorities for the business.

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 CloverDX 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. 

Reducing manual effort by validating data before processing

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.”

Dealing with painful data formats

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”

And converting from and to these file formats repeatedly was time-consuming. But now with CloverDX, the team can customize their processes to define and create any file format they need. 

Bartosik comments “I have found no better tool than CloverDX for creating these fixed format files. It’s just amazing, it’s perfect for it.”

Eliminating manual Excel for analytics

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.

And running the same analysis the following day or week meant the whole process of importing, formatting, sorting, parsing and analyzing the data had to be done all over again. 

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.”

Running that analysis every day, week, or even every hour, became possible at the click of a button, with none of the additional effort that had been needed before. 

Buying it for one thing, using it for everything

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 CloverDX 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.