May 21, 2019
emids delivered a GUI-based ETL middleware tool which leveraged Microsoft SQLServer, Snaplogic and a .Net interface.
Overview
One of the main reasons many organizations, especially at the enterprise level, continue to use older or legacy systems is the challenge of updating all of the “jobs” or interfaces which move data into and out of those systems. From years of work and work-arounds, these interfaces become complex and patched with custom coding. Much of the cost, time and risk in any digital transformation is found in this area making data migration one of the greatest challenges. The underlying process is called Extract Transform and Load or ETL.
At the same time, interface work is often thought of as basic blocking and tackling work in many IT departments and is often not considered as prominently as other parts of the system modernization. This is combined with the reluctance of data trading partners to change the incoming feeds or doing so at a cost for many of the same reasons. The basic work of ETL can often become a drag on implementations due to the effort involved in both documenting and accommodating clients who also are slow to change.
Business rules, or the Transformation component of ETL, can end up in multiple IT systems and either lost in layers or trapped in legacy systems. Because they are built over time, there is a tendency to either add on to a very long and complex “rule” or to create a new “purpose-driven” task each time. Either path adds to the complexity and creates a process dilemma. The skills and the knowledge for transformation work are often split between the technical IT and business analysts. This often leads to having purpose-driven interfaces as the analyst attempts to replicate IT functions and use the technical tools directly, or the IT resource creates rules on the analyst’s behalf over and over via individual requests.
Our client, an enterprise healthcare payer/provider, was one of the organizations going through the process of modernization of their IT infrastructure and clinical delivery systems. The UM platform was replaced and interfaces had to be migrated. A new interface management and ETL tool, Snaplogic, was also implemented across the enterprise. As part of those efforts, they recognized that there was an opportunity to not repeat the past, and a need for a tool to migrate the interfaces to the new platform without repeating the process dilemma of purpose-driven interfaces. The purpose of the tool was to create a method for analysts to onboard customers and set up file transfers, or interfaces, without the need for purpose-driven coding.
Solution
We were engaged to build a GUI-based ETL middleware tool which leveraged Microsoft SQLServer, Snaplogic and a .Net interface. Middleware, in this context, is software that operates between the data source and the target system and allows communication that is not dependent on the two systems having a common structure. The middleware solution acts as a translator between the two systems and transformations are typically applied as the data moves from system to system. Middleware solutions can also be made available to the analyst to apply their contextual knowledge without having the overhead of ETL and IT administration.
We started by examining the capabilities of Snaplogic and the existing processes to discover the business context and the actual rules. We then looked into applying a master set of rules globally but instead opted to design a framework so that, by entering configuration details into the application, complex and custom rules were applied to any incoming feed in a generic fashion. We also designed the capability of using MDM-governed or core tables to consolidate multiple, independent incoming feeds into a single stream to the target UM system. The configuration also includes steps to validate the incoming records and diverting potential errors to a path for correction.
Results
By using the application framework we developed, analysts are now able to set up new customer feeds and connect to sources, move and archive files, apply transformation rules, validate data, load to targets and manage job scheduling without manipulating the underlying infrastructure. The resulting tool is a significant reduction in both development resource cost and time to implement new, and eventually change existing, customers.
This innovative no/low code approach keeps the technical delivery in the framework, which reduced the IT support load, and allowed analysts to create and manage the process without assistance in all but the most difficult scenarios. By consolidating the rules within the framework, auditing and reporting of the specifics was simplified and could be more readily understood by both technical and business users. This was accomplished by applying a generic approach to the process while still allowing individual customizations to be applied in a standardized format.
The engagement entailed extensive development, testing and component integration between the application, database and Snaplogic. This tool was not only delivered on time and on budget, but also met all functional requirements and attained all goals and objectives initially established by the client.
Contact us to learn more about partnering opportunities.