Cache seeding drill down reports?

Question

I am familiar with the concept of seeding the cache for reports by scheduling them via an agent job and sending them to the BI cache.  However, how would one go about caching a report that is meant for drill-down purposes?  so basically, the columns in the report are dynamic based on the user's drill down path.  It starts up top at a higher aggregation and goes down dimension levels from there.  Since there's a couple different drill down columns, the combinations could be a large # so there isn't a way to take just the 1 report and seed it ahead of time.  Is there any way to cache something like this?

jepva · Answer

I have tried to create a large report with almost all of the columns, but OBIEE doesn't seem to think there is a cache hit.  It seems the difference in aggregations depending on user's path does not cause a cache hit even though that data is theoretically in the cache.

rmoff · Answer

+1 to @Gianni Ceresa

Use the nqquery.log (11g) / obis1-query (12c) to see detail of cache hit/miss, as well as Usage Tracking. As well as agents to seed the cache, you can directly populate it with SASeedQuery passing in a specific logical query (for example, at a detail level like Gianni suggests)

Gianni Ceresa · Answer

In theory cache doesn't match only 1-to-1 things, if you have in cache something bigger than what the user ask but containing all the required details it can still come from the cache.

So if you can put in cache the detail with all the details (even in one shot taking maybe really long to produce) I guess it could match many of your drill-down as they will be subsets of what is in cache.

Of course it require some tests to make sure it really acts in that way ;-)