Caching Remote Tables

This explains what a cached remote table is and how to configure a remote database table to be cached locally.

Overview

Caching a table locally takes a copy of a remote database table and stores it in an in-memory datastore store.

This is useful for tables where you want to perform GetByKey queries or datastore lookups. Essentially, if you need to retrieve specific facts (that is, rows) from the table, then it will be more performant to cache the table in-memory.

How it works

We will create an in-memory store for the remote table and populate it with the contents of the remote table. We will then use this in-memory store for all queries. The remote table will only be used when the application is refreshed.

This process of caching tables can be applied to any store that is not part of the star-schema.

Cached Table Example

Let’s say we want to cache the remote FXRATES table in-memory.

Under the hood, we will create an in-memory store named FXRates and populate it with the contents of the remote FXRATES table. We will give the remote name an alias of FXRATES_CACHE_SOURCE to allow us to use the FXRates store for all queries. The remote FXRATES_CACHE_SOURCE table will only be used when the application is refreshed.

We can use the AdminUI to see the two tables side by side. The FXRates store is the in-memory store and the FXRATES_CACHE_SOURCE is the remote table.

By caching the FXRates table in-memory, we can perform GetByKey queries on the FXRates store without any performance penalty of having to query the remote database.

Application Refresh

When the Application is Refreshed through the DirectQuery REST Service, the in-memory Stores will be completely re-populated with the content of the remote Tables.

Defining Cached Table

To define a cached table we simply register it as a table to cache in-memory and expose the table as a bean with a qualifier of SideStoresToCache.

Registering the Table to Cache

First we must define our table in our datastore description. See Create Datastore Definition on how to add new Tables/Stores to the application.

Once our store is defined in our datastore configuration, we can register it as a table we want to cache locally. This is done by creating a bean that is annotated with DirectQueryCachedTablesConfig.TABLES_TO_CACHE_LOCALLY and returns a list of the table names we want to cache.

We can override the default tables to cache by creating a new bean with the same method name and returning a list of the tables we want to cache.

We will end up creating a configuration class along the lines of the following:

@Configuration
public class CustomCachedTablesConfig {

	/**
     * Add additional tables to cache
	 */
	@Bean
	@Qualifier(DirectQueryCachedTablesConfig.TABLES_TO_CACHE_LOCALLY)
	public List<String> addAdditionalTableToCache() {
		return List.of(
                "MyTableToCache",
                "MyOtherTableToCache"
        );
	}

	/**
     * Overrides default cached tables defined in the
     * DirectQueryCachedTablesConfig.commonTablesToCacheLocally() bean.
	 */
	@Bean
	@Qualifier(DirectQueryCachedTablesConfig.TABLES_TO_CACHE_LOCALLY)
	public List<String> commonTablesToCacheLocally() {
		return List.of(
				"FXRates",
				"MyTableToCache",
                "MyOtherTableToCache"
		);
	}
}