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

Gianni Ceresa

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

Martin van Donselaar

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.

[Deleted User]

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

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!

[Deleted User]

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.

Gianni Ceresa

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

Onkar Nath Tiwari

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

[Deleted User]

Good workaround for a bad situation!

Onkar Nath Tiwari

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

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