Oracle Business Intelligence

Products Banner

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

Received Response
1410
Views
117
Comments

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.

«134

Answers

  • Hi,

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

    Thanks & Regards,

    RCB

  • Hi,

    Please also provide the screenshots of the RPD too.

    Thanks & Regards,

    RCB

  • 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

  • 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.

  • 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.

  • 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 ✭✭✭✭

    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

  • 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 ✭✭✭✭✭

    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.

  • Thanks Robert ..

    but first not getting confidence that session variable will certainly work as it has to also pass through similar init blocks.

    second thing that using session variable instead of repository variables will increase unnecessarily extra queries on the server and hence load.Obiee itself is slow so don't want to put extra stuffs on it.

  • Any other pointer  plz for resolving it ....

    For now I have raised SR with oracle but they just referred me to some documents of 11g problems which are certainly not applicable in my case.

    OBIEE 11g Dynamic Repository Variable may not Be Updated Dynamically ( Doc ID 2176875.1 ).

    Other options am trying is

    open the RPD online and create a dummy variable with high frequency and see if it triggers to run others as well.

    Thanks

  • The problem at the moment seems to be that they just don't acknowledge the existence of this bug and don't properly invest time in its analysis.

    Unfortunately from our side there's not much we can do. There isn't an internal call which forces bulk refresh of respositoryvariables for example that we could use.

    Also, the option of going for session variables as an alternative is...well...not really the same. Yes the variable will be refreshed upon each login but it will not be refresh while the session persists.

    So for you, @3319735 and @Carsten Weber I can only say: escalate your Service Requests to the highest possible level - they just won't do anything if you don't. Escalation makes the SRs visible in their internal management food chain and is quite literally the only thing which will force them to have a second look...or better: to actually read the SR once.

  • Did you try creating a new fake one to see if it does refresh?

    Because if it does ... you can invest 5 minutes to drop the existing init blocks and create new ones using the same code and pointing to the same variables. It's maybe enough. (It isn't a solution to the potential bug, just a workaround if you need it)

  • Thanks Christian!!

    Very true .. seems Oracle is missing some concrete mechanism to acknowledge bugs and work on their resolutions. Also from the customers side they try to create SR for any discrepancies but if it takes longer then they try to create their own work around and leave the SR. Timely response from them is also a concern. Who from end users side  will follow so long till the resolution and their inclusion in the later version unless some management team from Oracle takes this responsibility. I am also looking for quick work around. How can I hold the prod env for such a long duration and wait for Oracle's soln.

    Thanks

  • Thanks Gianni for asking it ...

    Having limited access in prod env so not able to open RPD in online mode . Have requests in process to open rpd in online mode..

    Will update if it works ...

    Thanks

  • 3319735 wrote:I am also looking for quick work around. How can I hold the prod env for such a long duration and wait for Oracle's soln.

    Bounce OBIS all the time?

  • Do you have multiple Ini Blocks? I had an Issue with Rep variables that did not update and the cause was actually another  Ini Block that failed thus preventing all other blocks behind it from completing.

    Make sure all of your  Ini Blocks are valid. You might have already ruled that out but I wanted to share anyway.

  • Invalid init block? Or was it a blacklisted connection pool which was serving those init blocks?

  • The Query had become Invalid. So the IB could not complete (Because of an ORA message).

  • And there you get the message in logs your init block has been blacklisted. Didn't you?

  • Thanks for the clarification Gianni, that may well have been in the logs but I never saw it because I realized what was going pretty much directly.

    After correcting the query everything refreshed properly again.

  • So as I mentioned above probably blacklisting caused all others to fail as well

  • Robert Angel
    Robert Angel ✭✭✭✭✭

    Hi Christian / Gianni,

    I have suffered this one a number of times so much so that it became personal policy to run the same code as the same user on the same database to avoid the blacklisting issue - and I come from DB developer background so am most at home there.

    I found that the dynamic variables failed so consistently that I started to only use session variables, and never found a case where session variables expired or failed, as frequently you use these for row based security so it would be diabolic if they did.

    I know I was using a screwdriver to hammer nails in, but sincerely I do not see why it is not advisable provided the sql in question is not a huge overhead?

    Thanks for your feedback or flaming as the mood hits you!

  • Robert Angel wrote:I know I was using a screwdriver to hammer nails in, but sincerely I do not see why it is not advisable provided the sql in question is not a huge overhead?

    No no what I meant is that your session variable is instantiated once upon login. Depending on your session persistence settings (ClientSessionExpiresMinutes) this means that you may run into the case that a new value has arrived in the underlying data source but you session variable is still holding the old value because you never logged out or your session just hasn't expired - both of which would force a re-logon and a reload of the variable.

  • Hi Robert,

    Honestly it's a bit like constants vs variables in programming etc. They 2 kind of variables have different targets.

    From a conceptual point of view it's obvious that a value which isn't personal (so different value based on the user) is better as a repository variable than a session variable.

    The downside with session variables is that often all the init blocks are fired at login, making this step super slow. Also depends on the sizing of your system: if you have 10K users and you make 10K queries returning always the same value it's obvious (from a conceptual point of view) that a repository variable is better.

    I must also admit I never had issues with my repository variables, they always run without problem and refreshed as expected by the scheduling.

    So nothing bad in using session variable instead if you do not suffer of any extra load on the DB or the BI server, the login isn't slow etc. It's just a factor you need to keep in mind for the future, when problems will start, when people will start complaining it's slow, when your DBA will start complaining you are doing too many things on the DB etc. You will need to keep in mind that you are doing more things for each user than what you would really need and maybe at some point you will need to revert back to repository variables.

    In a ideal world repository variables would work as expected and people experiencing problems with them will all go through the painful process of opening a SR so that Oracle can maybe find a issue there and fix it.

    In a more practical world everyone will take the best solution based on the context: if no resource or performance issue transforming their repository variables in session variables, or trying recreating variables from scratch in the RPD etc.

    Your approach isn't wrong, it just isn't ideal, but idealism hardly pay a salary nowadays

  • Thanks to all for valuable suggestions...

    No specific response or solution from Oracle on the SR raised.

    They suggested to open the RPD in online mode and verify variable's values from session manager.

    In our case due to firewall issues not able to open RPD in online mode . Not able to query underlaying database from RPD in offline mode due to again firewall issue.(in process of getting secured access)

    In the mid tried the option of

    1. downloading RPD from prod env

    2. Open the RPD in offline mode and changed the default initialization values of dynamic repository variables.

    3. Upload the RPD back in prod env.

    4. Now all dynamic repository variables are getting refreshed automatically at their defined frequency.

    Other observations

    1. update of initialization value of one  variable is sufficient for triggering auto refresh for all variables defined on the same datasource but it doesn't trigger auto refresh of dynamic repository variable defined on other data source.

    that mean we need to update the initialization value of at least one variable defined from each distinct data source.

    Not sure how long these dynamic variables values will keep refreshing automatically in view of recurrence of this problem again as mentioned by Robert and Carsten.

    But things look perfect and happy for now till this problem reoccurs.

    Thanks

    Onkar

  • Good workaround for a bad situation!

  • Automatic refresh of dynamic repository variables has stopped again. Not sure what has triggered them not to refresh.

    Although when I opened the rpd in online mode and tried to test them , they are getting latest values correctly. So that mean queries are fine and if have triggered then certainly would have refreshed variable's values. To me looks like some how queries would not have triggered at the refresh time schedules.

    Just looking into it ....Anyone has any other thought or have experienced similar?

  • chillychin
    chillychin ✭✭✭✭✭

    I dont mean to revive a thread that is past, but I came across this thread while experiencing the same issue.

    I opened my RPD in online mode

    Tested my variables

    My variables came back correct, my database values were correct but for some reason the initialization block was "stuck" on a certain date. I tried to clear my cache etc, but the variables stayed "stuck"

    I finally restarted my services and now the variables are returning correctly.

    I am guessing that a colleague may have made a change online to the initialization block, and somehow got the values "stuck"

    This is my first time seeing this in 12c, but will see how it goes