Dashboard Cache Strategy: Improve Query Performance Without Sacrificing Data Freshness

How to separate browser and data-connection caching, set query guardrails and diagnose slow charts without trading away trust 

Fast dashboards are not created by caching everything or by disabling every cache. They are created by deciding how fresh each workload must be, then applying the right control at the right layer. Panintelligence provides data-connection caching, chart-level cache exclusions, connection pooling, query timeouts and table row limits that can be combined into a deliberate operating policy

WORKING PRINCIPLE : Treat freshness as a requirement, not a side effect. Set the data-connection baseline to match the source update cadence, then override only the charts that have a justified need for a fresher path.

Figure 1. Browser caching and data-connection caching are independent; both chart exclusions are needed when every display must reach the database

One dashboard, several performance controls 

The data connection is the operational boundary between a dashboard and its source. Its parameters include Connection Pools, Chart Cache Minutes, Max Rows, Query Timeout Seconds and, for SQL Server, a Pre Query panel. Saving a connection validates it, while the Test Connection action checks the credentials before saving.  These controls solve different problems. Connection Pools caps the number of created connections and allows free connections to be reused. Chart Cache Minutes determines whether the result of a given SQL query can be reused. Query Timeout Seconds can stop a long-running query when the selected database supports timeouts. Max Rows limits Data Tables and their exports rather than other chart types

Product control summary from the official pi Documentation

Cache is often treated as a single switch 

Panintelligence separates the regular browser-oriented chart cache from the configurable cache on a data connection. Excluding a chart from the browser cache does not remove the effect of Chart Cache Minutes. Conversely, excluding a chart from data-connection caching does not configure the short-term browser path.

That distinction matters because a change can look correct while leaving another cache layer active. A team may disable one layer expecting every display to run fresh SQL, or disable both layers for a low-risk daily warehouse dashboard and create unnecessary database work. The control needs to match the freshness requirement rather than a generic preference for cached or uncached data. 

DESIGN CONSEQUENCE : A performance policy should name the accepted data age for each workload. Without that target, neither a fast cached response nor a fresh uncached response can be judged as correct.

Speed without a freshness contract is fragile 

An overly long cache interval can preserve an earlier result beyond the point at which a user expects new source data. An always-uncached path can repeatedly execute SQL even while the underlying data is static. Our documentation specifically identifies higher cache intervals as appropriate candidates for warehouse data or data updated once per day, while chart cache exclusion keeps data current at a performance cost. 

Slow workloads also need guardrails that are not caches. Query Timeout Seconds can cancel a query and show the user a Timeout message when supported by the database. Max Rows governs Data Tables and their exports; the Data Tables guidance documents 5,000 rows as the default and warns that raising the value too far can affect dashboard performance. 

PERFORMANCE CHECK : Do not use Max Rows as evidence that every chart is protected from large source scans. It applies to Data Tables and their exports, not to other charts. 

Build a freshness-led performance policy 

1. Classify workloads by tolerated data age 

Start with the source refresh cadence and the decision being made. A daily warehouse pack, an operational dashboard and a chart that must reach the database on every display do not need the same policy. Record the maximum accepted age of the result, the expected source update time and the owner who can approve a change.

These profiles are implementation recommendations, not fixed Panintelligence presets. 

2. Configure the data-connection baseline 

Chart Cache Minutes defaults to 0, which means chart data is not cached by that setting. With a positive value, the first result for a given SQL query is reused until the interval expires; the SQL is then executed again and the interval restarts. Set a positive value only after connecting it to a known source update pattern and an accepted freshness window. Set Connection Pools with the source connection limit in mind, then validate or test the connection. If variables are used in the username or password fields, connection pooling is disabled, so include that behaviour in capacity planning rather than assuming the pool remains available.

CONNECTION CHECK : A successful connection test confirms credentials; it does not prove that the cache interval, timeout or pool size is appropriate for production traffic. Validate the workload separately. 

3. Apply chart-level overrides deliberately 

Use the browser-cache exclusion when a chart should bypass regular short-term chart caching. Use the data-connection cache exclusion when that chart should not be placed in or retrieved from a configured data-connection cache. When every display must retrieve data from the database, enable both exclusions for that chart.  Keep the exception list small enough to explain. Record why each override exists, what data age it protects and how its source-query cost was tested. This turns a checkbox into an operational decision that can be reviewed when the source cadence or dashboard usage changes. 

4. Set guardrails around the query path 

Configure Query Timeout Seconds only with the selected database’s support in mind. When it applies and the interval expires, the query is cancelled and the user sees a Timeout message. Treat that as a bounded failure mode, not as a substitute for investigating the SQL or source performance. Use Max Rows for Data Tables and their exports. Raising the documented 5,000-row default may affect dashboard performance, so test the display and export use cases together. If a Pre Query is part of the design, remember that the panel is documented only for SQL Server and runs commands before the dashboard executes the chart SQL. 

5. Diagnose the bottleneck before changing the cache 

For a slow chart, run the query directly on the database and compare the timing. If the database timing is similar, our support guidance suggests that the relevant field may need an index. It also recommends checking whether Manual SQL has been used and whether it is optimally coded.  If the source query is not the slow part, inspect the dashboard path: Chart Cache Minutes, Max Rows, free-text filters, memory, timeouts and data-connection settings. Change one control at a time and reproduce the same filter and drill state so that the comparison remains meaningful. 

Figure 3. A direct database comparison helps separate source-query work from dashboard-path configuration and capacity checks

6. Prove the cold path, warm path and freshness path 

Run a controlled test with a representative user, filter state and chart. Measure the first request after cache expiry or bypass, repeat the same request for the warm path, then verify that source changes become visible within the agreed window. For an always-current chart, verify the behaviour with both exclusions enabled.  The acceptance decision needs both outcomes: response time that meets the service expectation and data age that meets the business expectation. A faster chart is not a successful change if it serves data older than the agreed requirement; a fresher chart is not automatically successful if the additional query load is unsustainable. 

Figure 4. Validate performance and freshness together before keeping, shortening or bypassing a cache interval.

A production-ready acceptance checklist 

1. Record the source update cadence and the maximum accepted age of the result. 
2. Confirm the current Chart Cache Minutes value; remember that the documented default is 0. 
3. Identify whether each chart uses the browser-cache exclusion, the data-connection cache exclusion, both, or neither. 
4. For charts that must always query the source, test with both chart exclusions enabled. 
5. Validate Query Timeout Seconds against database support and the expected workload. 
6. Verify Data Table display and export behaviour against Max Rows; do not assume it limits other charts. 
7. Confirm whether variable-based credentials have disabled connection pooling. 
8. Compare slow SQL directly on the database and review indexing or Manual SQL when source timing is similar.
9. Test cold, warm and freshness paths with the same representative user, filters and drill state. 
10. Document the owner, reason and review date for every chart-level cache override. 

Freshness becomes measurable, not accidental 

A deliberate cache strategy separates the connection baseline from chart exceptions. Chart Cache Minutes handles reusable query results at the data connection. Browser and data-connection exclusions let selected charts bypass the relevant layer. Query timeout, row limits, pooling and direct database comparison protect the rest of the path. The result is a dashboard whose speed can be explained and whose data age can be defended.

RECOMMENDED NEXT STEP : Choose one slow, high-use dashboard and classify each chart as batch-aligned, operational or source-current. Record the existing cache and guardrail settings, then run the cold, warm and freshness tests before changing the wider connection policy. 

Documentation basis 

Panintelligence behaviour and configuration details in this guide were checked against the official pi Documentation pages below on 10 August 2026. Freshness profiles, rollout sequencing and acceptance tests are presented as implementation patterns rather than product guarantees.