Stale Dashboard After ETL Refresh
A nightly ETL job loads the SALES fact table at 02:00 and finishes at 02:40. Users report that an executive dashboard opened at 08:00 still shows yesterday's totals until they press Refresh. The dashboard contains four analyses that all query the same subject area. The BI Server has caching enabled with a persistence time of 12 hours, and the cache seeding job was scheduled at 01:00, before ETL completes. Two of the analyses use a logical column whose expression includes CURRENT_DATE. The cache is shared across all users, and the data is not user-specific. The administrator wants correct data at 08:00 without forcing every user to wait for full re-execution of the largest analysis, which takes 90 seconds cold.
1. Diagnose why the dashboard shows stale data and propose a caching schedule change that fixes it.
Read the worked answer
The cache seed runs at 01:00, before the ETL finishes at 02:40, so it caches pre-load results that remain valid for 12 hours. The dashboard therefore serves cached rows until roughly 13:00, and pressing Refresh only helps if the client forces a new logical request rather than reusing the entry.
The fix is to move cache seeding after ETL completion, for example at 03:00, and to shorten the persistence time so the entry cannot survive into the next load window. The cache entry must be invalidated when the underlying data changes; scheduling a seed after the load effectively repopulates it. Do not simply disable caching, because the 90-second cold analysis would then hit every user.
Also check the two analyses that use CURRENT_DATE. Their logical SQL includes a time-varying value, so the cache key differs by day and they may not benefit from the same seeded entry. Confirm the seed uses the same logical SQL, including filters, as the dashboard.
2. How should the administrator handle the two analyses whose logical column depends on CURRENT_DATE?
Read the worked answer
These analyses are not stable across time. If the cache key incorporates the evaluated date, the seeded entry for one day will not match the next day's request, so the seed appears to do nothing. If the key does not incorporate it, the cached result can be wrong after midnight.
The safe approach is to make the time dependency explicit and controlled. Either remove CURRENT_DATE from the logical column and pass the date as a presentation variable or filter so the request is deterministic, or accept that these analyses bypass the shared seed and are executed on demand. In the latter case, keep them small or pre-aggregate the data they touch.
Verify the physical SQL generated for the seeded request and for the user request; they must match exactly for the cache to be reused. Document that time-dependent columns limit cache reuse, and test by seeding, then querying the next day to confirm the expected behavior.