topic Missing data on reload in QlikView

Missing data on reload

Fri, 02 Mar 2012 13:47:15 GMT

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 — Fri, 02 Mar 2012 14:24:36 GMT

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.

Missing data on reload

Fri, 02 Mar 2012 15:03:07 GMT

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 — Fri, 02 Mar 2012 15:29:04 GMT

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

Fri, 02 Mar 2012 15:35:02 GMT

Hi Flipside

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

Missing data on reload

Thu, 29 Mar 2012 16:53:47 GMT

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

rbecher — Thu, 29 Mar 2012 18:40:29 GMT

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

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

- Ralf

Missing data on reload

Fri, 30 Mar 2012 08:13:19 GMT

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 — Fri, 30 Mar 2012 09:56:33 GMT

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

Fri, 30 Mar 2012 10:37:22 GMT

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

Thu, 03 May 2012 11:33:48 GMT

I can update this thread about the intermittent problem we have been experiencing.

After extensive monitoring and tests our supplier has now recreated this problem outside of our environment. QT have also recreated the problem on their own environments and are currently investigating whether it is something unusual with our scripts (e.g. the way we are using QVD files) or some other cause. So I'm hopeful they will provide an explanation and suggest a work around or fix soon. We don't believe it is a problem with the source database as SQL profiling has shown that to be working correctly. I'll keep this thread updated with news.

Andrew

Re: Missing data on reload

Thu, 14 Jun 2012 13:59:32 GMT

The latest update on this is that QlikTech have found the cause of the fault and are now working on the fix. No definite release date has been given yet but hopefully it will be soon. We have asked for a fix on the latest version of QlikView desktop and QlikView server so we are busy upgrading our environments to version 11 SR1 in readiness. So good times are ahead!

Re: Missing data on reload

rbecher — Fri, 15 Jun 2012 07:34:04 GMT

Hi Andrew,

do you have a bug number? Maybe we should add this to the QlikBug group if others run into this:

http://community.qlik.com/groups/qlikbug

- Ralf

Re: Missing data on reload

Fri, 15 Jun 2012 15:30:37 GMT

Yes, it's bug 47189

Re: Missing data on reload

Fri, 29 Jun 2012 09:03:57 GMT

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 28^th 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

tseebach — Fri, 29 Jun 2012 12:00:07 GMT

Its so annoying that we never hear from QlikTech something new is released - other than reading forum threads!

Thanks!

Re: Missing data on reload

Fri, 29 Jun 2012 15:32:08 GMT

Hi – thanks for your comment. You’ve a valid point and one that we are actively looking at remedying for future Service Releases in fact. As a matter of course right now, our Support team sends an email to those persons that submitted bugs that were ultimately fixed in an SR. Our goal is to extend that to the wider community.

Many thanks for your continued input!

John Callan

Sr. Director, Global Product Marketing

Re: Missing data on reload

gussfish — Sun, 01 Jul 2012 23:14:41 GMT

Hi John,

was this ultimately related to locking & blocking, as Flipside had suggested? The reason I'm asking for more information is that my company is on QV10 SR2 and are cautious about upgrading (we've had some bad experiences trying to upgrade even between Serview Releases), so it would be helpful to understand the underlying issues and so be able to better assess the likelihood that this problem will affect us.

Thanks.

Angus.

Re: Missing data on reload

rwunderlich — Mon, 02 Jul 2012 00:30:29 GMT

Has anyone received an email like John mentioned, a notification that a bug is fixed on a specific SR?

-Rob

Re: Missing data on reload

tombombadil — Mon, 02 Jul 2012 08:08:22 GMT

Nope never, and we submitted a couple of bug reports.

BR,

Tom