Using a Global Cache for Queries
If you have a large dimension table you can load the dimension table's data into a cache, and use the cache to look up surrogate keys. Using a cache improves performance compared to performing lookups directly to the dimension table with Query DB.
To use a cache you must create two dataflows: one to populate the cache with data from the dimension table, and another that uses the cache when updating the fact table. The following diagram illustrates how the two dataflows work together:
If you want to make sure that the cache is populated with the latest data from the dimension table each time you update your fact table, you can create a process flow that first runs the job to populate the dimension table, then runs the job to update the fact table. This allows you to execute the process flow in order to run both dataflows in succession. For more information about process flows, see the Dataflow Designer's Guide.