Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Missing data on reload

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.

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

28 Replies
flipside
Partner - Specialist II
Partner - Specialist II

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

Not applicable
Author

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?

flipside
Partner - Specialist II
Partner - Specialist II

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

Not applicable
Author

Hi Flipside

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

Not applicable
Author

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

rbecher
MVP
MVP

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

Astrato.io Head of R&D
Not applicable
Author

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.




flipside
Partner - Specialist II
Partner - Specialist II

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

Not applicable
Author

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