Moving towards real-time risk reports using in-memory column-based structures

Risk management reporting is moving from overnight processing to delivery of reports and results in near real-time. The results need to be available within a few minutes rather than a few hours of the data becoming available to the risk data warehouse. For example, a market risk middle office (MRMO) team may be under “T+1” deadline pressure to produce daily results and reports within a few minutes once the final adjustments for the day have been loaded into the database.

One step on the way is achieving this is the use of in-memory column-based tables and indexes. Most BI vendors are now incorporating this technology. My tests used Microsoft’s implementation – the columnstore index in the latest version of its SQL Server database. This is designed to make typical SQL queries, used in analytics, run much, much faster.

The tests were designed to see if these techniques can work on typical risk data sets and if so what performance improvements are possible. I set up a database with a typical structure of a market risk data warehouse and a reasonable amount of data, 70 million rows. This represents typically 2-3 days of data for an asset class so you can run reports comparing results from the latest business day with the day before on a data set of this size.

I then created a set of SQL queries typically used to in risk reports. I ran these against the data with and without the columnstore Index applied. The results were impressive – over a suite of tests, the performance improvements were between 11 and 77 times. In the better cases, this translates into a 15 minute query becoming a 15 second one.

These techniques also avoid the step of “reprocessing the cube” so save valuable minutes here also. Cubes are useful for rich exploration analysis of the data but we don’t need them any longer for generating the basic results.