Killik & Co Turns to Progress to Connect SQL Server with MongoDB for Seamless Data Exchange
Operating in more than 30 markets globally, Killik & Co provides wealth planning, investment management and advisory stockbroking services. As a brokerage firm, the company must track every interaction it has with a customer, from phone calls and emails to sending and receiving physical mail and packages. The company stores all event information within a MongoDB database. Additionally, its company information and contacts are kept in a CRM system based on SQL Server.
Killik & Co’s IT department adopted MongoDB a few years back to accommodate its move to node.js for Web services. The team realized MongoDB could be used for other projects. For example, Microsoft dynamics CRM was being used for managing client information, and to summarize a client for a broker, the system had to perform 20 different SQL calls to compile the information. This led to a performance issue—it took 15-20 seconds to summarize the client, and brokers needed instant access. “We created a single view and began storing the information in MongoDB,” said Simon Cater, Lead Systems Developer at Killik & Co. “As a result, contact summaries appear in an HTML web page template within 50 milliseconds.”
MongoDB became a production database in Killik & Co’s infrastructure, and the team began to move many processes from SQL to MongoDB, including a queuing system for handling smaller jobs that come up on a daily basis. However, various departments began asking for data for reporting purposes, which created a new challenge. “Our Reporting Services likes to get data out of SQL but it doesn’t play well with MongoDB,” said Simon. “Yet our systems of truth were gradually moving to MongoDB, and we had no way to connect it to our SQL Server instance. Loading the data into SQL Server a little at a time would be insufficient; we needed real-time access.”
Simon attended a Progress session at a MongoDB conference in London, where he was introduced to Progress DataDirect Connect for ODBC. “Progress DataDirect Connect for ODBC was exactly the piece of technology we were missing, to connect SQL Server with MongoDB,” he said.
The solution went live in April, 2015. Using DataDirect Connect for ODBC for MongoDB, Killik & Co will be able to expose the data in the MongoDB database as normalized relational tables, similar to the schema in its SQL CRM system, enabling the team to query, sort and aggregate data from both systems to gain a far more comprehensive view of its customers.
The team plans to leverage the solution for other projects, as well. For example, Killik & Co has used the connection between MongoDB and SQL to build a trade confirmation process that uses both our service queue system in MongoDB and our trade and transaction data in SQL. This has improved both the internal mechanism for trade confirmation approval and the delivery to end clients.
“Progress DataDirect has enabled our Development and MI reporting teams to consolidate data held in MongoDB with data held in SQL Server in a seamless manner, enabling data analysis and management reporting from new and existing legacy systems" says Craig Gould, Head of Systems Development, Killik & Co.
Killik & Co has realized significant cost and time savings from using DataDirect Connect to bridge its MongoDB and SQL instances. “We’re a relatively small development team with lots of projects and time pressures,” he said. “We could have assigned a resource to putting together a connectivity solution in-house, but from a cost perspective, it was more efficient to license a copy of DataDirect Connect. Now, we can focus on other development projects rather than worrying about connecting the data sources.”
The real benefit, said Simon, is how fast it is to configure new connections to other data sources. “Once we had instant live access to data in MongoDB, new projects were easy to implement,” he said. “It only took an afternoon to configure a new connection and new schema— a fraction of the time it would have taken us to build one from scratch,” he said. “We might have had an expensive resource working on it for quite some time and still not be where we are now.”
Simon said he’s had no support issues since implementation. “Progress DataDirect Connect just works,” he said. “Any time we need to create a new connection, it takes a day or two, and provides massive time and cost savings.”
Simon said the support he’s received from Progress has been exceptional. “Progress Support is like a security blanket,” he said. “We can try new things with the product and we know we’ll get the help we need when we need it.”
About Killik & Co
Established in 1989, Killik & Co provides wealth planning, investment management and advisory stockbroking services, with access to all major asset classes - equities, fixed income bonds, offshore bonds, commodities and currencies – in over 30 markets worldwide. http://www.killik.com