BI Query not giving same results as Database Query

Question

I have a query in BI that populates the default value on a prompt.

Recently it stopped populating the correct value.

When we run that same query on the database (we are using Toad), we get the correct answer.

Any ideas why the query works fine on the database, but not in BI?

Thanks!

Dennis

[Deleted User] · Answer

Another variable refresh issue?

Dennis Hancy · Answer

I want to provide a quick follow-up on this.

We noticed we had the same issue in Dev.  The BI server variable containing the query we are using to populate the default value was not working correctly. After we redeployed the RPD file, the query started working again.

While this is good news, it sounds like a spin-off of the "reboot" solution.  Something happened to make this stop working. I'm just not sure what that is at the moment.

We're going to try the same thing in Production and keep our fingers crossed that this will work.

Gianni Ceresa · Answer

As long as you executed the same exact query using the same exact account against the same exact database you must remember you also have the local settings playing a role in how a query is executed.

If with all the above mentioned elements being the same your Toad gives you a different result than OBIEE (and you are 200% sure you aren't just hitting the cache because you forgot to manage it and enabled it hoping to fix some performance things) it means your query is a bit badly formed and you maybe need to make it more strict to avoid local settings (like languages etc.) impacts.

But I would definitely look twice at the logs first to be sure your query is still execute correctly on the right database as in general you have that when you have cache in between ...

edit: writing more than Christian in the same time but going in the same direction