Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Dynamic repositoy variables are not being refreshed in obiee12c(12.2.1.2.0)

Received Response
1561
Views
117
Comments
Onkar Nath Tiwari
Onkar Nath Tiwari Rank 3 - Community Apprentice
edited August 2024 in Oracle Analytics Cloud and Server

I created one  dynamic repository variable through admin tool in obiee12c.The underline init block query is returning correct records but variable are not being refreshed.I tried to verify variable's values from the manage - > session in admin tool but there as well I noticed that variables are not being refreshed as per their frequency.

Any idea what could be wrong here. All services are up and running.

«134567…12

Answers

  • Ramakrishnan Bharadwaj
    Ramakrishnan Bharadwaj Rank 1 - Community Starter

    Hi,

    Could you share the screenshots for the init block you have created?

    Thanks & Regards,

    RCB

  • Onkar Nath Tiwari
    Onkar Nath Tiwari Rank 3 - Community Apprentice

    pastedImage_0.png

  • Ramakrishnan Bharadwaj
    Ramakrishnan Bharadwaj Rank 1 - Community Starter

    Hi,

    Please also provide the screenshots of the RPD too.

    Thanks & Regards,

    RCB

  • Onkar Nath Tiwari
    Onkar Nath Tiwari Rank 3 - Community Apprentice

    The screen shot if from prod env  and the query in the init block is very simple

    select

    trunc(sysdate)-1,         

    trunc((sysdate)-1,'MM'),          

    trunc((sysdate-1),'YY'),

    decode(to_char(sysdate,'dy'),'sun',trunc(sysdate-2),'mon',trunc(sysdate-3),trunc(sysdate-1))

    from dual

    When I run direct database request query from answers I am able to see that query mentioned in the init block are running fine.

    pastedImage_0.png

  • Gianni Ceresa
    Gianni Ceresa Mod

    The repository variables works fine in 12.2.1.2.0, they refresh as expected ...

    So it's up to you to double check the query you use, the connection pool and make sure everything is fine because you must have a little issue there somewhere.

  • Onkar Nath Tiwari
    Onkar Nath Tiwari Rank 3 - Community Apprentice

    Last week only we migrated our 11g env to 12c. We migrated repository and catalog through  migration script. In 11g these variables were working fine but when moved in 12c they  created this problem and not being refreshed automatically.

    Am able to run the queries behind these variables in the init block through data direct request from answers so it implies that queries are connections are fine .

    Also I am able to run reports through the same connection detail so that also implies that connection detail are fine.

    In log also  obis1-diagnostic-1.log not finding anything like initialization  block failed or any similar msg.

  • Gianni Ceresa
    Gianni Ceresa Mod

    Ok, but are init blocks still setup correctly? If you open the RPD and check them are they fine? The connection pool selected is still valid? Are they based on the second connection pool of a given database? There are by default strong checks on the init blocks, like not being allowed to use a connection pool also used to access physical tables defined in the RPD etc.

    So check these things, maybe it's just a checkbox to enable again or something like that.

    Try to add a new dumb repository variable and a dumb init block with a select sysdate from dual; refreshed every few minutes and look if that one is refreshing as expected or not.

  • Carsten Weber
    Carsten Weber Rank 5 - Community Champion

    Maybe you are facing the same issue as me in the past and now again which I described in another topic: Oracle BI Admininstration Tool: Variables do not update

  • Onkar Nath Tiwari
    Onkar Nath Tiwari Rank 3 - Community Apprentice

    Thanks Gianni, Carsten ......

    I got the prod RPD offline and able to verify options there .

    1. It's enabled

    2. Queries looks fine as same queries I  tried running them from direct data base request and am able to see data from answers so no problem with queries.

    3. It's prod env rpd so am not able to query tables from the same connection pool to which init block is linked. It's Firewall which restricts.

    4. It's offline RPD and again due to firewall not able to test init block queries from admin tool.

    5. No online rpd so not able to manage -->sessions  to verify variables values.

    Thanks Carsten for pointing to the link. I went through the link and seems similar problem you faced as well.

    But of course as you pointed out that disabling the cache will clear the old cache of reports only. Hardly it's going to effect variable's value. Variable's value should be initialized to new one as per it's frequency.

    In my case, based on values of these variable's reports have to be triggered so no option of using SET VARIABLE DISABLE_CACHE_HIT=1

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    I have not experienced this on your version, but I previously used session variables to refresh instead, one it makes sure your variables really are 'current' and two it consistently works and is usually not too much of an overhead.