When to use caching:

  • Query performance
  • Load optimization – to minimize load on the underlying system.
  • Consistent reporting
  • Source availability
  • Complex transformations
  • Security – to allow users to see aggregate data and still keep them from the ability to access detailed data.

Where to keep the cache:

  • Memory – gives you the fastest access, purged when the server restarts(?), more limited that storage in most envs
  • Database – works well for large data sets, creates and manages tables with indexes
  • File(s) – slower than memory, persistent across restarts, works well for small data sets

Refreshing the cache:

  • Construction refresh – refresh when its built, only once
  • Manual refresh – admin has to initiate a refresh
  • Scheduled refresh
  • Query-driven refresh – refreshed when queried if the cache has expired
  • Event-driven refresh – an event can initiate a refresh. an event could be something over an ESB, a file drop, API call, etc.

How to refresh the cache:

  • Incremental refresh – refresh only adds new data to the cache
  • Live refresh – basically CDC/instant refreshing
  • Full refresh – invalidate the cache and cache the data again

Online vs. Offline refreshing:

  • Offline refreshing – the table cannot be accessed during the refresh period
  • Online refreshing – queries CAN be executed against a table during a refresh. this data is obviously not complete though depending on the virtualization server’s implementation.

Cache replication is useful when:

  • the number of queries/users accessing the table is high
  • geographically dispersed consumers can have data cached physically closer to them for faster access
  • cache replication makes the table more available. even if one cache is down, you still have another cache to query against.