Using a Local 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.

A local cache is a temporary cache which is only used during the execution of the Query Cache stage. It looks up data in the cache based on key fields and lookup conditions and returns data from matching records in the cache, adding the cache record's data to the record in the dataflow. Use a local cache instead of global cache if it is only going to be used in one dataflow or if the lookup table changes frequently.

To use a local cache for queries:

  1. In Enterprise Designer, open the dataflow where you want to perform a query using a cache.
  2. Drag a Query Cache stage onto the canvas and connect it to the dataflow.
  3. Double-click the Query Cache stage.
  4. Select Local Cache.
  5. Select the database connection you want to use. Your choices vary depending on what connections are defined in the Connection Manager of Spectrum Management Console. If you need to make a new database connection, or modify or delete an existing database connection, click Manage Connections.
    If you are adding or modifying a database connection, complete these fields:
    Connection name
    Enter a name for the connection. The name can be anything you choose.
    Database driver
    Select the appropriate database type.
    Connection options
    Specify the host, port, instance, user name, and password to use to connect to the database.
  6. Specify the table or view in the database that you want to query.
  7. Select a key under the option Key field.
  8. Select an input field under the option Input field. The Input field contains the fields coming from the previous stage. If the value in this field matches the key in the Key field in the database, then the query returns the data from that record in the database.
  9. Click OK.
  10. Run the dataflow.