Twelve Users, One Warehouse Query, and Stale EMEA Numbers

The low query count is a cache symptom, not evidence that users are sharing one query in real time. MicroStrategy can serve the dashboard from the result cache and the dataset cache. The result cache stores a completed report result for a report definition, prompt answers, database instance, VLDB settings, and the user/security context. The dataset cache stores a broader dataset that can be reused by multiple report views. If the 12 users fall into a small number of prompt and security-filter combinations, most openings hit an existing entry rather than the warehouse. A scheduled or first-miss refresh can produce roughly one query per hour.

EMEA is stale because its cache entry was created before the 02:10 ETL commit and had not expired at 09:00. A 30-minute result-cache expiration should have expired it by 09:00, but the entry may have been refreshed from an older dataset cache, or the dataset cache may have a longer lifetime. APAC is fresh because its entry was missing or evicted and was rebuilt after the load. The fact that both regions are served by one database instance does not mean they share one cache entry; prompt answers and security filters can partition the cache.

The investigation should start in the cache monitor: identify whether each response came from result cache, dataset cache, or the warehouse, and compare cache creation times with the 02:10 commit. Without that, the team may tune the wrong cache.

Separate the regulated path from the cost-optimized path. For EMEA, set result-cache expiration to 15 minutes or less, and invalidate the dataset cache after the 02:10 ETL commit rather than waiting for a time-based expiry. Better, trigger cache invalidation from the ETL job through a cache-invalidation event or an automatic cache-refresh table so the new data is visible as soon as the load commits. If the dataset cache cannot be invalidated reliably, run EMEA against a live or short-lived query path and cache only the aggregate result.

For APAC and other non-regulated regions, keep a shared dataset cache with a longer lifetime and use pre-aggregation at Region and Date to reduce scanned rows. Push the Region prompt into the aggregate table so each region reads a smaller partition. Use a query governor and warehouse resource limits to control runaway queries. Warm the EMEA cache immediately after the ETL commit so the first 09:00 user does not pay the full query cost.

Measure the 40% target with warehouse credits by query type, not just cache hit ratio. A high hit ratio on stale data is not a saving. Track cache age, invalidation lag, and rows scanned per region, and confirm that EMEA never exceeds the 15-minute freshness bound.

'Use cache for all users' is safe only if the cache key still respects the security context. If two users have different security filters, their results must not be served from the same entry. Otherwise an EMEA user could receive an APAC result, or a broader result, simply because the cache was warm. Confirm whether the cache key includes the security filter and whether the users actually share the same filter object. Do not assume that a Region prompt provides row-level security; the prompt is user-controlled, while the security filter is enforced by the platform.

For the regulated path, prefer separate cache partitions or no shared result cache for users with different security profiles. Log the security filter identifier with each cache entry and each delivery. Audit should capture user, report, prompt answers, security filter, cache source, cache creation time, row count, and SQL fingerprint. Query for cases where a user's region does not match the rows returned, and for EMEA cache entries older than 15 minutes. Retain invalidation events and cache-purge records so the team can prove that stale entries were removed, not merely overwritten.