28 Replies Latest reply: Sep 26, 2013 10:59 PM by Mihai Petcu RSS

Missing data on reload

mcphee78

Hi All

 

I was wondering if any of you have had the same problem,

 

Am connecting to MS SQL 2005 using OLEDB 32 bit connection with QV10 SR3 and at random time there are chunks of missing data.

As you can imagine this is really compromises all my reports where it's got to the point am asking can i trust what

qlikvew is producing.

 

If any one else has had this probem and can give me some insight as to why this happens please do.

  • Missing data on reload
    flipside

    I haven't noticed any issues connecting to MSSQL Server myself.

     

    I'm assuming you're using the OLEDB Provider for SQL Server so my initial thoughts could be processes being blocked on the SQL side affecting data returned.

     

    Other questions ..

     

    1) Is it happening at certain times of the day?

    2) Can you see any blocks or deadlocks?

     

     

    flipside

    • Missing data on reload
      mcphee78

      Am using the SQL Server OLEDB provide.

       

      It's not happening at any certain time of the day it is random as far as i can tell i haven't spotted any pattern to it as yet.

       

      If i get any deadlock i get an error aand the file doesn't run, the files are running are if every thing has completed

       

      What are block i've never came across these before?

      • Missing data on reload
        flipside

        Blocking occurs when one connection to SQL Server locks one or more records, and a second connection to SQL Server requires a conflicting lock type on the record or records locked by the first connection.

         

        Source: http://www.sql-server-performance.com/2006/blocking/

         

        You have said you DO get deadlocks, so I would say this is a good place to start your investigations, perhaps run some diagnostics (SQL Profiler) to see if there's any correlation between the times you get missing data and high activity or long-running queries on your server.

         

        IF this IS the cause, then I don't know if you can use the NOLOCK hint in a Qlikview SQL query (don't have one here to test atm), but you could try using stored procedures on the SQL server and call that instead with that hint.  However, this makes the transaction READ UNCOMMITTED so could result in dirty reads.

         

        flipside

        • Missing data on reload
          mcphee78

          Hi Flipside

           

          Cheers for the info and advise i'll start looking into what you've said.

          • Missing data on reload
            andrew fundrey

             

             

            Hi mcphee78.

            I'm really interested that you are experiencing the same problem that we are. 

            Did you manage to find a solution to it?

             

            We first noticed this happening a year ago (see my old post http://community.qlik.com/message/135263) when we were connecting our QV server document to an Oracle database.  We had hoped it was a problem specific to our slightly old Oracle setup. 

             

            However since then we have built a Data Warehouse on MSSQL 2008R2 and are witnessing the same problem.  Like you the reload script returns a success result when it hasn't loaded all the data, and the log even shows the  incorrect number of loaded records.  Like you we are also worried that we won't be able to rely on what QlikView is reporting.

             

            We have even created a Data Loss KPI QV document that reloads every 30 minutes, extracts the data from an isolated and static copy of our source database, then counts the number of records it retrieved and stores the counts in a QVD file for comparison.  Nothing else is using the database so we know there are no deadlocks. 

             

            Because the data is static we would expect the same number of records loaded into QV every time, but instead we are seeing intermittent Data Loss at various times of day across different source tables.  Often the numbers are low but Data Loss of any kind without an error or warning isn't acceptable. 

             

            So far we have worked with our supplier to increase CPU and memory on the QV and database servers and switched to using the SQL Native driver (SQLNCLI10.1).  We thought the new driver and extra resources had resolved it until today when, for the first time since the change, we experienced Data Loss!

             

            They are saying that QlikTech haven't seen this problem before and that it's specific to our environment, so I'm pleased (sorry!) that you are having the same problem as maybe QT will begin to take it seriously!

             

            I will post a copy of our Data Loss KPI Document onto this forum tomorrow as it has been invaluable at detecting and reporting the problem.  It uses document Alerts to send an email whenever it detects Data Loss.

             

            But please let me know if you have found a solution !

            thanks

            Andrew

            • Missing data on reload
              Ralf Becher

              Hi Andrew,

               

              you could try out our JDBC Connector and compare the results:

               

              http://www.tiq-solutions.de/display/enghome/ENJDBC

               

              Use and config the MS SQL JDBC 4 driver:


              http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=19847

               

              config_SQLServer.png

              If you get the same strange behavior I presume an error in qvconnect(64).exe.

               

              - Ralf

            • Missing data on reload
              mcphee78

              Hi Andrew

               

              As of yet i haven't been able to pin this problem down it's starting to get very worrying as it is so intermittent am finding it very hard catch when this happens, I'd be interested in see the kpi file you put together to monitor this so i could impliment something similar my self.

               

              Our provider have tried say it's are enviroment as QT have never been informed of this problem or been able to recreate it.

               

              Well QT are going to have to take this seriously as it jepardises qlikview in general, the question is how many other people is this happen to who haven't spotted it and are getting wrong information,

               

              If you have any update please let me know on this post and I will vice versa.


               


               


              • Missing data on reload
                flipside

                I briefly tried to replicate this at home yesterday without success, but that was half-expected as you and Andrew have indicated it's a random problem. 

                 

                Did you manage to set up any Profiler traces? This would form strong evidence to prove it is or isn't deadlocking. You would need to keep it running until you had the issue occur again, but the trace can be set to a maximum file size and can overwrite earlier, unneeded trace data.

                 

                In my (basic) set up, if you have a long-running query in Qlikview, then fire off multiple similar queries in SQL, the QV query will pause until the SQL queries have completed.  Also, when I put the SQL query in an uncommited transaction, Qlikview still ran the query (whereas another SQL query would wait until the first query was committed).

                 

                I would also run a Select count(1) command immediately before the actual QV query to trap if there's any differences.

                 

                flipside

  • Re: Missing data on reload
    andrew fundrey

    Attached is our Data Loss KPI document.  Amend the script as per the comments and edit the document Alerts if you want to get an email notification when the fault occurs.

    I recommend running it against an ilosated copy of your source database so you will detect any data loss no matter how small.

    Let me know how it goes.

    Andrew

  • Re: Missing data on reload
    john Callan

    Hi everyone and thanks for your dialog on this topic.

     

    Bug 47189 – Inconsistent amount of data returned - has been
    fixed in QlikView 10 SR5, which was released on June 28th 2012. QlikTech’s
    investigation of this bug reinforced what has been mentioned in this thread:
    that it has been seen to occur in rare circumstances (less than 1% of all
    reloads tested), has been somewhat random in nature and has impacted a tiny
    percentage of data.

     

    We very much appreciate the active involvement of the
    QlikCommunity to continually highlight areas of improvement and are happy to
    have addressed this particular issue now.

     

     

    Regards,

     

    John Callan, Sr. Director Global Product Marketing, QlikTech

  • Re: Missing data on reload
    john Callan

    Many thanks for your replies folks.

     

    Angus - We don't feel that the issue was related to the locking and blocking as suggested before as nothing in our testing pointed us to this being a SQL Server -related issue only. 

     

    Rob and Tom: our Support team currently employes a manual process for collating the list of people who have sumbitted a bug and cross references that with our internal customer databases to build the list of people to whom to send an email to. It's possible in your instances that you were missed in error as part of that manual process and I will reach out to our Support team and investigate. Going forward our goal is to have a much more robust automated system in place.

     

    Many thanks again,

     

    John

  • Re: Missing data on reload
    Neville Gerard

    I, too, am seeing this issue.  My load is from SQL server and one table is 90 million rows, selected with no where clause.  On my desktop, where I do all of my development, each load will only return between 20 and 30 million records.  When I open the document in QV11 on the server and perform the reload, the correct amount of records are returned. 

     

    So...same document on two different computers runs differently.  The only difference is OS - both 64 bit, but Windows 7 on the desktop and Windows Server 2008 R2 Standard on the server.

     

    This is with QV11.  Has the bug been addressed in 11?

    • Re: Missing data on reload
      andrew fundrey

      Our particular bug (number 47298) is available as a patch in V11 SR1 and is now included in the latest update V11 SR2.  This bug caused intermittent data loss from random tables on the SAME computer.  Your issue sounds unrelated and may be due to differences in memory between your desktop and the server.   Good luck

  • Re: Missing data on reload
    Neville Gerard

    Mayday! Mayday!

     

    I upgraded to V11SR2 and I am still getting the incorrect number of records retrieved.  In fact, this time it retrieved FEWER!

     

    And now...the last reload on the server also missed records.  Not nearly as many, but I fear this may be the beginning of something bad.