prompts take long to load

Question

Choice list prompts can take minutes to load if the table is large enough (even dimension tables), and can cause a script error and not run at all.  In this case report is unusable.  Note: When in analysis, the filter returns the list immediately.  This issue is seen when using the prompt from the prompt tab in the report and from the dashboard.

Thomas Dodds · Answer

And if he can - get himself physical mini-dimensions which only have the distinct list of values ... if need be!

FTisiot · Answer

How is the  Prompt list of values calculated?

is it a pure list of all possible values or is a

Select Dim.Value where Fact.Value>0

If it's the latter case you may need to investigate how to speed up the query creation.

In my experience also the following help:

1) Default values -> assigning static values to default prevent queries to run

2) List Box instead of checkboxes -> the checkboxes are calculated on page load while the list box is calculated only when "opening" the filter

3) Optimise your model! if you have performance query DON'T rely on caching but spend time analysing your model's problem or slowness

[Deleted User] · Answer

Using caching to tackle performance issues is never a solution but just something to hide these issues.

Also you'll simply create the new problem of stale data. If you don't have a proper cache management strategy with ETL/data refresh-driven cache purging and re-populating then you're just creating situations where old data is shown even though newer exists.

Long story short: cache doesn't solve performance issues. It hides them and does never do anything to address the actual root cause.