This discussion is archived
10 Replies Latest reply: Sep 7, 2012 5:36 AM by user652652 RSS

Report not hitting pre seed cahe created using iBot by Admin

JV123 Journeyer
Currently Being Moderated
Hi,

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.

any help appreciated.

Thanks
Jay.

Edited by: Jay on May 16, 2012 7:23 PM

Edited by: Jay on May 17, 2012 5:42 AM
  • 1. Re: Report not hitting pre seed cahe created using iBot by Admin
    Satya Ranki Reddy Expert
    Currently Being Moderated
    Hi Jay,

    I have given some inputs.

    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.

    Hope it helps.

    Thanks,
    Satya
  • 2. Re: Report not hitting pre seed cahe created using iBot by Admin
    MuRam Expert
    Currently Being Moderated
    Hi,

    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.

    Hope this helps you.

    Regards
    MuRam
  • 3. Re: Report not hitting pre seed cahe created using iBot by Admin
    MuRam Expert
    Currently Being Moderated
    Hi Jay,

    Hope below links will sort out your problem:
    ibot - seeding cache for dashboard pages
    John comments:
    iBot runs but the cache doesn't gets seeded
    http://www.artofbi.com/index.php/2010/03/obiee-ibots-obi-caching-strategy-with-seeding-cache/

    Hope these helps

    Regards
    MuRam
  • 4. Re: Report not hitting pre seed cahe created using iBot by Admin
    SYedurur Newbie
    Currently Being Moderated
    Jay,

    Did you get any solution for this.I am facing the same issue .We are using obiee 11g.THe same scenario holds for me.
  • 5. Re: Report not hitting pre seed cahe created using iBot by Admin
    JV123 Journeyer
    Currently Being Moderated
    HI,

    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.

    does anyone has an answer for my question??

    Thanks
    Jay.

    Edited by: Jay on May 17, 2012 2:01 PM

    Edited by: Jay on May 18, 2012 5:30 AM
  • 6. Re: Report not hitting pre seed cahe created using iBot by Admin
    RAM CH Explorer
    Currently Being Moderated
    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.
  • 7. Re: Report not hitting pre seed cahe created using iBot by Admin
    JV123 Journeyer
    Currently Being Moderated
    Ram,

    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??

    do u have any clue?

    Thanks
    Jay.

    Edited by: Jay on May 20, 2012 8:45 AM
  • 8. Re: Report not hitting pre seed cahe created using iBot by Admin
    RAM CH Explorer
    Currently Being Moderated
    select the option run as individual users and execute ibot by adding a specific user (say user abc) apart from admin. later login as abc and check the report cache.

    let me know the result.
  • 9. Re: Report not hitting pre seed cahe created using iBot by Admin
    SYedurur Newbie
    Currently Being Moderated
    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:

    http://docs.oracle.com/cd/E23943_01/bi.1111/e10541/querycaching.htm#BCGCEBHD

    3.Include the elements and their ancestor elements as appropriate, as shown in the following example:

    <ServerInstance>
    <Cache>
    <Query>
    <MaxEntries>100</MaxEntries>
    <MaxExpireMinutes>60</MaxExpireMinutes>
    <MinExpireMinutes>10</MinExpireMinutes>
    <MinUserExpireMinutes>10</MinUserExpireMinutes>
    </Query>
    </Cache>
    <ServerInstance>
    4.Save your changes and close the file.

    5.Restart Oracle Business Intelligence.
  • 10. Re: Report not hitting pre seed cahe created using iBot by Admin
    user652652 Explorer
    Currently Being Moderated
    Any suggestion? we are also facing the same problem

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points