Open source is future, they say. In more ways than one it stands absolutely true. It has been well accepted across industries since ages but a lot of enterprises took time to open up to open source. Today however, there is an overwhelming response from banking, insurance & retail enterprises to embrace open source tools and technologies to solve their business problems. Very recently, we delivered 2 projects for one of Singapore’s top Insurance companies. We were able to provide the best solutions to their business use cases using the power of open source technologies.

1. The first one was basically a bancassurance data exchange program, where the insurance company wanted to incorporate ACORD standard to exchange policy, commission & premium related information with its partner broker bank, in near real-time via XML SOAP messages.

With regards to the technology stack, we proposed Pentaho Data Integration as the ETL tool and EDB Postgres (an enterprise-class data management platform based on the open source database PostgreSQL) as the backend relational database. Pentaho was used for data provisioning from the source systems, data enrichment & transformation and loading into RDBMS. On the other hand we leveraged the features available in EDB Postgres to generate XML content from the tables. Pentaho & EDB Postgres were used judiciously to provide a perfect solution for the business use case.
On a high level solution, this project uses Pentaho Data Integration to extract the data from the source systems and loads into a staging area. Further the data is enriched & transformed to align to the desired XML data structure. Next, we used the XML features & functions available in EDB Postgres to generate the ACORD standard XML content. Finally, Pentaho was used to post the XML data to the REST API end point to be consumed by the applications, on the partner bank side. In order to generate highly complex XML structure along with the high volume of data to be processed, exploiting the XML functionalities of EDB Postgres was the most performance optimized solution.

2. The second project was focussed on mobile application development – to provide insurance related information of the clients to the agents on the field. There was a need to provide client centric data to the insurance agents on the go, into their mobile devices with offline & online capabilities. The proposed technology stack to host and prepare the data for mobile platform comprised of Pentaho, EDB Postgres & Couchbase Server, Sync Gateway & Couchbase Mobile Lite.

Pentaho was used for data provisioning from the legacy source systems, data enrichment & transformation and loading into EDB Postgres. On the other hand, we leveraged the features of EDB Postgres to generate JSON content from the database tables. Further, Pentaho was used to push the JSON data from EDB Postgres to Couchbase document store using HTTP post to the Couchbase Sync Gateway. Couchbase database is the primary source of the data for the mobile devices. Couchbase Lite is used to cater the offline data at the client/mobile end. Couchbase gateway takes the sole responsibility of data sync between the Couchbase database at the server side & the Couchbase Lite database on the client/mobile end to provide the online/offline capability. This powerful blend of EDB, Pentaho & Couchbase helped to achieve the most seamless integration between a legacy source data & the mobile application data. In this case again, EDB Postgres was a clear winner when it came to generating complex JSON documents along with the high data volume for processing. When it comes to mobile based application with two way synchronization along with online & offline capabilities the Couchbase Suite did an exemplary job.
In my next article, I will discuss in detail on the XML & JSON features of EDB Postgres along with sample codes. As an end note, if there is a need in your project to convert high volume of relational data in database tables, to custom XML or JSON formats, we would suggest you to use the in-built functions available in EDB Postgres database for a more performing and optimized solution. Or just reach out to us at success@ashnik.com and we can step in to help with your project needs.
Saurav Mitra, BI and Data Integration Consultant and Architect, Ashnik

Saurav’s vast and global experience spans across Business Intelligence & Visual Analytics, Big Data Management, Data-warehouse, Data Integration, Data Migration, Data Profiling and Data Quality Assurance. Among the technology tools he has expertise in Informatica Power Centre, SAP (BI, BO, and HANA), SQL, Server, Oracle, MySQL and Teradata tools.