Seed cache for all users of dashboard

Adam Wickes

I understand what you're saying but it seems like something isn't working as expected based on the troubleshooting I've done. Next step is to try the same experiment using a different subject area. If that works fine, then I know it's definitely related to the design and not a big with the product. Thanks Joel.

Adam Wickes

I have tested other subject areas using combinations of calculated columns and non-calculated columns. Every test results in the following:

If the column used in the filter does NOT exist in the criteria, cache hit will not occur.
The column can be excluded from the analysis and it will result in a cache hit.

Would there be any chance of someone testing this on their environment?
Setting up is fairly simple..

1. Create analysis with column A in the criteria and column B as a filter using "is prompted"
2. Create a prompt with column B as the filter. Leave all prompt options default so "all column value" are returned in the drop down but nothing is selected.
3. Put analysis and prompt on dashboard page. Run the dashboard and check your cache entries in RPD. You should see 1 or 2 values appear (one for analysis and one for prompt).
4. Limit your analysis using your prompt, hit apply and check cache entries. My testing suggests you should get another cache entry and no hit on the first one.
5. Add your filter column to the criteria and repeat steps 3 and 4. You should now see a cache hit with step 4.
6. Exclude column B from analysis but leave in criteria and repeat steps 3 and 4. You again, should still see a cache hit.

If it doesn't occur on another environment, I will need to contact Oracle support.

Adam Wickes

I have also realised that as soon as my query introduces any sort of complexity, using AGO for aggregation etc, cache is created but not utilised. Can anyone confirm whether certain functionality is deliberately not put into cache?

Christian Berg-0racle

Time series functions work outside of the WHERE clause since otherwise they simply wouldn't work at all. Hence it's logical that they don't use the cache.

Adam Wickes

Ok that makes sense.
I ended up turning cache off on all the tables this report uses and now, without cache, it runs in 7 seconds vs 20.
Go figure!

Christian Berg-0racle

Caching and performance are empirical topics and dependent on the underpinnings of the solution.

If those are questionable (let's be nice this morning) then everything on top becomes questionable.

I can only try to explain, not solve your problem. It's like with the current virus outbreak: if people listen to neither evidence nor science, then at some point they just can't be helped.

Adam Wickes

You don't need to be nice to me Christian haha. I am well aware that the problems we are facing are with our design and not the product itself.
As for the virus, I hope you and your family are staying safe. I have family friends who live in Cologne, Germany and believe me, I am taking it very seriously.l the

All the best