Data Integration Using Enterprise-wide Libraries

Traditionally, ETL tools are kind of a black box. You have ton of business logic that you program in them that is essentially specific to the tool and cannot be transported to another tool. This lock-in causes problems when you are trying to switch to a new ETL vendor.

Lately I've been working with a unique data integration requirement. We want to build ETL that takes data from several sources, then cleanses and de-duplicates it, eventually make it to a master database. Our requirement was that we wanted to abstract out the business rules that would be in an ETL tool and make it reusable and available to other applications. We also wanted to make sure that we don't lock ourselves in to using a particular ETL vendor.

Now, unlike traditional data/data warehouse teams, we have a strong set of developers with core programming skills who have a strong say in the data integration process. I personally, am approach agnostic and believe that the right tool must be used for the right kind of problem and that the requirements drive what tool(s) you end up using. I've worked as a developer coding C#, Java backend/web applications and also on the other end of the spectrum using data integration tools such as Talend, SSIS, etc. Both these approaches have their own advantages and disadvantages.

We ended up creating a library in Java using Eclipse. We used the Test Driven Development methodology and wrote ample tests in JUnit. We used Git for version management and Github for hosting our code. The build server was Jenkins and was instructed to pull from Github at every commit. The artifacts such as jar files were published using Artifactory. The idea was to have a central repository of business rule/logic which would undergo constant testing and quality assurance.

This allowed us to package the business rules as a jar and use it in our ETL tool (Talend). Our requirements were that we wanted a solution which was highly modular and re-usable by other applications. If we were to put our business logic and transformations in to the ETL tool, we would be highly dependent on the ETL tool if we ever had to switch tools. We ended up using the ETL tool as a workflow engine that triggered parts of the process, handle failures, generate emails, etc.

You might want to try similar approach in your next data integration project. Let me know how it goes!

Integrating Unstructured Data in a Data Warehouse

I've been meaning to write about this topic for a while. Here's a succinct excerpt of my thoughts.


Traditional data warehouses are generally relational and fed by back-end systems which contain structured data. Most often than not this data is generated by internal source systems or arrives as external data from partners. But what about the web? There's tons of data out there. Possibly about your company, competitor or a business trend about your industry..and the list goes on. As we share more data on the web, this list is expanding every day. The traditional warehouse is not designed to handle such unstructured data thereby limiting the locus of control of your decision support system.

Search engines are good at handling both structured and unstructured information in various formats (e.g. database tables, XML, PDF, DOC, etc.). Case in point - We helped a client index almost 75GB of unstructured data stored in .pdf, .doc, text files going as far back as 70 years. The ancient files were scanned pdfs which were later OCRed.  This is massively helpful not just from a pure enterprise search standpoint but in the terms of opening up the data to other parts of the organization in an easily accessible fashion. So how does this tie to a warehouse? Well, the search index in itself is a warehouse. 


So how to access it? - With your existing BI systems. If your tweak your BI tool, you can make REST based HTTP calls to a web server. In goes your query and within a second out comes your data. Search engines are inherently fast! You can use this data for discovering relationships you never thought existed. Obviously  this works better with certain types of data than other. I foresee a large interest in this area in the future as  enterprises explore more potentially crawl-able publicly available data sources. 

Using Audit Logs for Data Integration

Recently we integrated two applicatons with Solr in near real-time: Siebel and Documentum. We achieved this by monitoring the Audit Logs of the applications. Audit logs work great for data integration where you have to push a change in a business object to another system. Audit logs for mature applications like Siebel and Documentum can be configured to write update/delete events. Usually, this is done by reading from the audit log table (without locking to prevent performance issues) to sniff out interesting events/changes to business objects.

Risks

• The auditing mechanism must be bug-free and consistently record events - which is mostly the case with mature COTS software systems.
• The audit log is generated by the application. So every-time you need to test your integration solution, you have to make changes in the application to see if they come through. Sometimes, developers don't have access to front-end apps which may cause problems. 
• Also, thorough testing is required from the application front-end perspective. Care must be taken to capture all event log signatures a particular business action in the application  can generate.