This property specifies BI Server's strategy for caching metadata and user query results.
Values and behavior:
- NONE - No caching is used at the BI Server level. This may result in slower queries.
- AUTO - The system will determine which query results should be cached when the execution time exceeds the QUERY_CACHE_TIME_THRESHOLD.
- For materialized cubes, all query results are cached for SQL, MDX, and Kyvos visualization queries.
- For a non-materialized cube, only the SQL metadata queries are cached. No user queries will be cached.
- For HOLAP cubes, results of:
- metadata queries will be cached.
- user queries having only materialized dimensions and measures will be cached.
- user queries having non-materialized dimensions or measures will only be cached when partitioning strategy (Sliding Window) is defined on the cube.
- ALL - Enables query caching for all SQL, MDX, and Kyvos visualization queries when the execution time exceeds the QUERY_CACHE_TIME_THRESHOLD.
Cube: If the property is set on a cube, then the value will override the connection level value for that cube’s build job.
Comes into effect:
The value of the property can be changed at any time (not recommended) and comes into effect the next browsing session.
Dependencies and related properties:
Using AUTO is highly recommended. Choosing ALL may return old values on non-materialized cubes. For additional performance improvement with data validated for accuracy, set the property value as ALL. Set the value to NONE, only if performance is not an issue, and the BI Server has minimal resources.
Metadata queries are filter list queries,
- for regular and location type hierarchies when only a single level is queried
- for time type hierarchy multiple levels are queried
- when a single attribute is queried
Kyvos supports only SQL Connectivity for non-materialized cubes.
For HOLAP cubes, Kyvos supports only non-materialized querying dimensions, measures, and drill-through using SQL.
When performing cold query performance testing, such as with JMeter, this property can be set to NONE.