4 Replies Latest reply on Sep 19, 2017 2:18 PM by Christian Berg

    BI Query not giving same results as Database Query

    Dennis Hancy

      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

        • 1. Re: BI Query not giving same results as Database Query
          Christian Berg

          What have you analyzed so far? Checked the logs? The logical SQL? The physical SQL? The cache?

           

          Because things don't just magically change.

          a) there was a change and

          b) you're most likely not looking at "that same query"

          • 2. Re: BI Query not giving same results as Database Query
            Gianni Ceresa

            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

            • 3. Re: BI Query not giving same results as Database Query
              Dennis Hancy

              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.

              • 4. Re: BI Query not giving same results as Database Query
                Christian Berg

                Another variable refresh issue?