Offloading Operational Databases
When a query cannot be entirely satisfied by accelerations in the cache, in-situ processing becomes necessary. The query is pushed down into the underlying source (or multiple sources), to the extent possible, and the results are retrieved and processed in memory and returned to the client. When the data source is a production operational database, it may not be desirable for analytical queries to hit the database, as that may impact the operational workload. This can be avoided by ensuring that all tables and views that are accessible to users can be satisfied by accelerations in the cache.
For example, consider the following data graph consisting of physical and views:
In this environment, there are four tables that users can access directly (purple), and three tables that users cannot access directly (grey) due to restricted permissions. The tables and views outlined in red are anchors with raw reflections. Because all access to these three tables must happen through two views, enabling raw reflections on these two views ensures that all queries will be satisfied by Data Reflections rather than impacting the operational database.
With that said, it’s important to consider the impact of cache update jobs on the operational databases. To minimize the impact, consider utilizing incremental updates and reducing the data freshness for that data source.