We created an iBot to pre seed the cache using Administrator and it is creating the cache but when normal user run this particular report for the 1st time it's not hitting the cache(created using iBot). all the subsequest users request hitting the cache created by normal user. I still dont understand why it is not hitting the cache.
Did anyone comeacross this issue before?
what are the reasons not hitting cache with above scenario??
does cache created by administrator using ibot work for other users? we actually running whole query with no filters on it using admin and normal users run the same report with some filters on it. as per oracle cache strategy it should work but it's not.
The way the OBIEE server creates the SQL (both logical and physical) for a request can be a bit funky sometimes, the cache seeding only really works in fairly simple cases. Does your request have a pivot table in it by any chance? These are notorious for not caching properly, if you look at the log for the request you can see why as the server adds this strange "aggregate by" to the request (why this can't be done at the presentation level since the only change you are asling for is in the presentation of the content is beyond me). Those "aggregate by"s tend to stop a request being a cache hit unless it is identical to the one that seeded that cache, any change in parameters, columns etc (even if a logical subset) will not get a cache hit.
2. "Oracle BI Server Cache" Cache Seeding option present in Destination tab of Delivers -- Please check this option.
3. Caching is one of the many approaches to improve performance but it's not a magic solution. You need to understand that you can't cache everything. In particular you won't be able to cache reports that are driven by parameters and have facts that are too granular and exceed the number of rows each cache entry can have. If your fact is of a small size then you can get around the parameters issue by caching a report without any filters. The BI Server should able to derive subsequent queries as long as they meet the cache hit criteria. Have a look at the administration manual for all the rules a cache hit must meet.
The cache seeding only really works in simple cases. Generally observed that pivot table with complexity more/less doesnt gets seeded properly. Please clear the log and check if its getting cache hit in the log for the request. And if any weird "aggregate by" addes to your request, then those stop a request being a cache hit, as per my knowledge. So, it takes a cache hit by the user once. And check the query log with and without the seed cache. Compare their differences.
Let me explain little more about how I configured.
Login as Administrator created an ibot with personalized individual visibility.
Recipients: just selected ME. do I need to select security group for whom I want to seed cache here???
destinations: Oracle BI Server cache, Interactive dashboard
save iBOt. logged into job manager and ran ibot checked cache file in rpd and is created.
now login to answers using nornal user and ran report. we expected to hit the cache but it send request to database and created another cache file.
we are using OBIEE 10.1.3.4.2 and it is integrated with oracle apps 11i. individual users login to OBIEE through Oracle Apps.
Did I miss anything?? Is it a know deect? the report is very simple and very limited data simple table view and no filters. same report ran by admin and normal user.
LOOKS LIKE WE HAVE ROW LEVEL SECURITY IN PLACE MAY BE THAT IS THE REASON IT'S NOT WORKING. I came to know cache doesn't work if row level security is implemented. Can someone conform it. Please post if you have any SR or defect available with oracle support.
when you have row level security your sql is equipped with user name in where clause. so when user runs the report, that seeded but this cache will only be used for him only not for other users as where clause in sql differs. you run the ibot with admin and cache created. when normal user runs (directly or ibot) the sql is different from admin sql so obiee thinks it is a new query and runs against db.
You know one of the ibot properties says run ibot with individual user visibility. I tried this with recipient just with admin, cache is creating but other users not able to use that cache. and again I added other security groups in recipient list but cache is not at all creating. I don't understand why ibot not creating cache in this scenario??
Try adding this.You are running the cache night time.But the cache expire is default set to 60 .so when you are opening in the morning it dosent hit the cache and created new entrie.Old entries will still be there but they are not used.Try increasing the MAxExpireMinutes to 60*12 min.
Follow the documents below:
3.Include the elements and their ancestor elements as appropriate, as shown in the following example:
4.Save your changes and close the file.