Slow Dashboard Caused by a High-Cardinality Column
An operations dashboard has three analyses. Two are fast, but the third, which groups order count by customer name and shows the top 20, takes 70 seconds. The fact table has 40 million rows; CUSTOMER has 2 million rows. The report applies a filter for the last 30 days and sorts descending. The BI Server generated SQL that selects customer name from the dimension, joins to the fact, groups, and sorts, but the database plan shows a full scan of the fact and a hash join to CUSTOMER. The database has an index on the fact date key and on the customer key. The dashboard is viewed by 200 users each morning.
1. Identify the likely cause of the slow query and propose tuning steps.
Read the worked answer
The query groups by a high-cardinality attribute, customer name, after filtering to 30 days. Even with a date index, the optimizer may choose a full scan if the 30-day slice is a large fraction of the table, and the hash join to a 2-million-row dimension is expensive. Sorting a large intermediate result adds cost.
The first step is to confirm the filter is pushed to the database and uses the date key index; check the physical SQL and the plan. If the slice is large, an index may not help. Consider a materialized aggregate at the day-by-customer grain, or a summary table, so the report reads far fewer rows. Ensure the aggregate includes the date key and customer key and is mapped as a logical table source.
Also review whether the top-20 sort can be pushed down. If the database supports it, the BI Server may still fetch all rows before sorting; a database-side limit or a pre-aggregated source reduces transfer. Measure before and after.
2. How does caching change the picture for 200 users each morning?
Read the worked answer
The query is identical for all users if no user-specific predicate applies, so one execution can serve all 200. Enable caching and seed the dashboard after the nightly load so the first user does not pay the 70 seconds. Set persistence long enough to cover the morning but shorter than the load interval, and ensure the seed uses the same logical SQL as the dashboard.
If the query does include a user-specific filter, the shared cache will not help across users; in that case, reduce cost per execution through aggregation instead. Verify cache hits in the logs and confirm the entry is not invalidated by an unrelated event. Caching complements, but does not replace, fixing the underlying high-cardinality aggregation.