Microsoft SQL Server served as an OLTP database for FIS, which runs transaction processing and financial analytics on the same database tables at the same time.
Name of Organization: FIS
Industry: Financial services
Location: Jacksonville, FL USA
Opportunity or Challenge Encountered:
Think about real-time data for financial transactions, and stock or bond trades come to mind. Beyond that, real-time data also plays a role in the securities lending market, in which large institutional investors lend a portion of their shares through broker-dealers who borrow those shares on behalf of clients such as hedge funds.
FIS, a provider of financial technology solutions with more than 55,000 employees, has been providing both historical and real-time trading data, and sought to make it easier to enhance and expand its real-time analytics in its securities lending market data applications.
FIS provides software, services, consulting and outsourcing solutions focused on retail and institutional banking, payments, asset and wealth management, risk and compliance, trade enablement, transaction processing and record-keeping. As explored in a recent case study, the vendor’s clients are both lenders and borrowers. The lenders are typically large institutional investors such as pension funds, insurance companies, and investment banks. The borrowers are typically broker-dealers representing clients such as hedge funds, who borrow to facilitate short sales so they can sell high and buy back at a lower price, or hedge against price fluctuations in their portfolios. Successfully navigating these transactions requires access to real-time data.
To make this capability more widely available to clients, FIS introduced near-real-time data analytics with additional intelligence on trends in loan volume, distribution of borrowers, new and returned loan levels, and average age of loans. The goal was to enable lenders and borrowers to more easily able to see how their securities lending or securities borrowing activity compares with anonymous market-wide trading activity to inform their negotiations with their counterparts in the trade.
The challenge was to provide a back-end infrastructure that could scale to meet FIS’s ever-expanding services in the real-time data space.
“Our challenge is to add those capabilities without a high amount of operational burden or data-processing requirements behind the scenes,” Aaron Gerdeman, senior vice president of product development at FIS, said in the case study.
How This Opportunity or Challenge Was Met:
The FIS securities lending market data application has been using Microsoft SQL Server for many years and was running on SQL Server 2012, following the standard practice of having separate database systems for operational and analytics workloads. For such systems, extract, transform, and load jobs regularly move data from the operational store to a data mart dedicated to running analytics queries. However, those ETL routines are less efficient in the realm of real-time analytics.
To address the need for greater real-time data delivery, FIS implemented SQL Server 2016, an in-memory relational database management system with enhanced real-time operational analytics (RTOA) capabilities. RTOA allows an organization to run both analytics and on-line transaction processing (OLTP) workloads on the same database tables at the same time, which can eliminate the need for new ETL operations and additional data marts.
An OLTP database for real-time analytics
The FIS market data application includes a client dashboard with a broad group of real-time analytics that enable users to more quickly screen for stocks of interest and to identify stocks that are attracting the most demand. It also presents data graphically in charts and heat maps as well as numerically, providing lending and borrowing agents a better view of where they stand with their trading partners and their portfolio of security loans.
Before introducing the dashboard, which would significantly increase the number of analytical queries on the databases, FIS needed to ensure that it could maintain sub-second response times. This was particularly challenging with real-time streaming data because there isn’t much time for data transformation. There was concern that the analytical queries would compete with the frequent inserts in the real-time dataset and slow response times.
Benefits From This Initiative:
With the real-time dashboard from the OLTP database, FIS is able to provide transparency into the intraday securities lending market as a whole, “including showing traders who are lending securities in real time how their trading desk is responding to market events,” says Gerdeman.
The new implementation has quadrupled multiple query speed, the case study reports.
The development team started off by simulating 1,000 new transactions processed into a table with 900,000 rows (the database size at midday) and with 1.2 million rows (the database size at the end of the day), the case study reports. The results showed that the new approach delivered significantly better performance for most queries.
The performance gain was “surprising,” the case study relates, “because column store technology can process large amounts of data very quickly, so it typically works best with tables that have millions of rows. The real-time analytics database starts at zero each day and loads about 100,000 rows per hour reaching the maximum of about 1.2 million rows by the end of the day.”
Several individual queries marked improvements from eight to 18 times faster with the new configuration, and repeated inserts of new records did not significantly slow down query response times. “We just applied an index to the database and all of a sudden the queries ran four times, 10 times, even 20 times faster,” Gerdeman is quoted.
(Source: Microsoft)
