☰
Navigation :

DirectQuery

DirectQuery allows data to be queried directly from an external database without first loading it into ActivePivot’s in-memory datastore.

In FRTB Accelerator , DirectQuery can be used alongside the in-memory datastore or instead of it. Where historical data might have previously been loaded at the summary level to save memory, it can now be stored at trade level in an external database. A much smaller cache of the summary-level data can be kept in memory to maintain performance and reduce the need to query the external database.

Integration

For details on how to connect your FRTB instance to a remote Database, see the Getting Started with DirectQuery developer documentation.

Preview

This version of FRTB Accelerator includes a preview of DirectQuery support for the Standardised Approach.

This preview includes all SA calculations, but does not have full multi-jurisdictional support and has limited customizability.

Currently, DirectQuery does not support what-ifs, sign-off, or data updates. These features are only available using the in-memory datastore.

DirectQuery Data Access

With DirectQuery we will be able to query data from a database in ActivePivot. At query time, the data is aggregated on the external database and sent to ActivePivot for further calculations.

At start-up, FRTB Accelerator will populate caches of summary data by querying the database for these aggregated values. This means that queries at the book and desk level will be as fast as the in-memory datastore and the only time the external database is used at query time is for trade-level queries.

Read-only access to the external database is sufficient for ActivePivot. It does not write to the database.

Deployment Options

It is possible to load all your data using DirectQuery. However, to take advantage of the features that are only available using in-memory data, we suggest a hybrid approach with the most recent COB dates loaded in memory and older/historical COB dates loaded using DirectQuery.

The hybrid approach uses horizontal distribution:

  • Recent COB dates are loaded into an in-memory data node
  • Historical COB dates are available in a DirectQuery data node
  • A query node seamlessly combines the data

Supported Databases

DirectQuery In Action

Let’s see DirectQuery in action. For this we will load a single date of data, 2018-09-28, into memory with two pre-aggregated dates of historical summary data, 2018-19-27 and 2018-09-26.

Drilling down to the Trade level gives us the following results: Summary Level Data Availability Issue

Now let’s again load the single 2018-09-28 day of data into memory, but this time configure DirectQuery to get the rest of our data directly from our external database.

We retain access to the Trade Level data without having the data locally loaded: DirectQuery Trade Level Data