topic Data loads can silently partially fail in QlikView

Data loads can silently partially fail

Fri, 13 Sep 2013 10:08:39 GMT

- We have noticed that data loads into QlikView Personal Edition can silently fail.

- That is, when we press control-R to reload data from a SQL Server database, we have seen cases where not all data is loaded, but no error message is displayed. We only notice this by carefully comparing counts of number of rows loaded versus counts in the corresponding database views.

- We have a .qvw file, which is storing data to .qvd files.

- Has anyone else seen this?

Re: Data loads can silently partially fail

marcus_sommer — Fri, 13 Sep 2013 11:02:29 GMT

If you could exclude conditions-reason in where-clauses and changes in the database after loading from qv it could be that a very high workload on the database and/or temporary missing network and/or error-values in certain records caused the missing records. Normally it should give a breakdown or an error-message but it must not if for example the data-connection-driver relativ fault-tolerant.

You should enable the load-logging (with timestamp). Here you could see the count of loaded records and also the appropriate time-spent. Often need such silent fails more or less time.

- Marcus

Re: Data loads can silently partially fail

Thu, 26 Sep 2013 16:58:02 GMT

Hi Marcus,

I suspect high workloads on the database are not the cause, because these are development databases with few or no users. Intermittent network issues could be a cause, but they would be hard to pin down. Issue is that if there are problems the load should fail. If it did, we would be happy. Where it causes a problem is if there is no error, but still missing data.

We did enable load-logging and watch the count of row, which is how we had to troubleshoot when we realised what was happening.

Re: Data loads can silently partially fail

Anonymous — Thu, 26 Sep 2013 19:40:36 GMT

I don't know if this is the case, but many times my load processes (via Script) fail because of some file being hold by the system due to a previous wrong ending.

Nothing will be lost if you inspect this situation.

Good Luck.

Re: Data loads can silently partially fail

Fri, 27 Sep 2013 07:14:01 GMT

Thanks. But when a load fails with an error, that is fine. Our problem here is silent failures, with no error but incomplete data.

Re: Data loads can silently partially fail

marcus_sommer — Fri, 27 Sep 2013 07:47:21 GMT

What show the log-files? Could you compare the data in the database and which qv loaded - which records are missing? Some values (errors or special chars) in those records could be problematic.

Are there any aggregations, where-clause or joins in this load? A reduction of the complexity an doing this later in further load-processes could avoid problems with the workload and made it easier to find any differences.

Sometimes is it useful to separate such data and loads in dummy databases/applications and runs these with higher frequency and/or with various parameter could lead to the cause of your issue.

- Marcus

Re: Data loads can silently partially fail

Fri, 27 Sep 2013 08:48:11 GMT

The problem is intermittent and rare, so I cannot reproduce at will. We will attempt to capture log files corresponding to the failure when we can catch it, but I don't expect this will be quickly.

The load is simple. It only uses load, select and store statements. There is one case statement in one of the select statements. We'll try to remove this in case its related, but I doubt it.

Thanks.

Re: Data loads can silently partially fail

Anonymous — Fri, 27 Sep 2013 08:56:21 GMT

Owen

Using the QlikView 11.20 [and other versions] Desktop I have noticed that it appears to have some kind of sticky cache, which I posted as a discussion - albeit with no replies.

http://community.qlik.com/message/393312#393312

Could your issue be somehow related ?

We both have one of those infamous random & intermittent issues, that can be hard to diagnose & sort.

Best Regards, Bill

Re: Data loads can silently partially fail

Fri, 27 Sep 2013 09:17:07 GMT

Interesting Bill, thanks. I don't know if these are related.

Re: Data loads can silently partially fail

Fri, 27 Sep 2013 09:24:28 GMT

I am not sure.. But it may help you..

Are you using Set ErrorMode=0.. That may be a reason why it didn't revert any error..

Karthik

Re: Data loads can silently partially fail

Fri, 27 Sep 2013 09:44:31 GMT

No, we are not using Set ErrorMode.

Re: Data loads can silently partially fail

Mon, 28 Oct 2013 20:17:37 GMT

Have you made any progress on this issue. I think we are experiencing something similar, and in our case it occurs when reloading data to a .qvw via an EDX call from a script within an SSIS package. Over a period of 6 weeks it has occurred on three different .qvws. Like the author of this thread, we receive no error indication either from the SQL Server/SSIS or from the Qlikview server. We have only been able to uncover by performing row counts against the .qvw and comparing to row counts from corresponding table(s) in SQL DB.

The .qvw's that are failing to load are among our largest data tables. Sometimes they load and sometimes they don't. In our testing so far, if we re-run via the SSIS scripted EDX call the reload is sometimes successful and sometimes silently fails. However so far if we reload directly through the Qlikview application we have not experienced this failure.

At this point, we are searching for any type of ideas as to the cause.

-Brookes

Re: Data loads can silently partially fail

Tue, 29 Oct 2013 08:33:39 GMT

We have not solved this issue yet, as its very interrmittent.

Re: Data loads can silently partially fail

marcus_sommer — Tue, 29 Oct 2013 09:01:04 GMT

How earlier mentioned I suggest first to exclude defintily potential errors directly in values or expressions. Some of them could lead to ignore single records or to terminate the load/script without any message. Therefor I would add some routines which first count the rows in source and after loading counts again. Is there a different check about the source record-id which records are missing and write this result with a timestamp in a additionally table. Later you can check this table and search directly in this records which are missing to find any pattern.

Other reasons could be errors in RAM ... but this is more difficult. If you have several server in your environment you could move your app/task or execute it additionally parallel.

- Marcus

Re: Data loads can silently partially fail

saumyashah90 — Tue, 29 Oct 2013 09:30:18 GMT

this can happen when Set ErrorMode=0

Re: Data loads can silently partially fail

Tue, 29 Oct 2013 13:37:35 GMT

I will look into this. We are also seeing this on a new server in a virtual environment and our systems team has made the decision to allocate additional memory to the SQL DB server. Because of the way we process data into our SQL data warehouse tables and then into Qlikview documents, and as many as 7 SSIS packages at any one time are processing data into different SQL tables and then initiating a reload of Qlikview documents via scripted EDX calls, the systems admins believe that this may be overwhealming memory on the SQL DB server when our largest tables are processed.

Re: Data loads can silently partially fail

Tue, 29 Oct 2013 14:25:13 GMT

We don't use Set ErrorMode at all in the scrpts for the Qlikview documents which are silently failing. Therefore according to my reading our scripts should be running in default ErrorMode = 1, however since we see no errors reported it sure seems like our scripts are running in ErrorMode = 0. Is there another way this could have been set as a default if it does not appear in the Qlikview scripts?

Re: Data loads can silently partially fail

marcus_malinow — Tue, 29 Oct 2013 14:35:23 GMT

If you're using MS SQL Server, have you tried adding the server hint (nolock) to your sql select statements?

E.g. SELECT * FROM TABLE (nolock)

If you're retrieving data at the same time that SQL Server is committing it you may find that some of your data is not being returned. Nolock may be able to help in this situation.

Marcus

Re: Data loads can silently partially fail

Tue, 29 Oct 2013 16:26:36 GMT

We use nolock. But this issue was being seen when no commits were being made.

Re: Data loads can silently partially fail

Fri, 15 Nov 2013 15:54:52 GMT

No solution for us yet, however we are being told that the issue may result from our use of SQL Server 2012 with Qlikview 10, which is not officially supported. We are not sure the issue lies here, but our attempt to open an incident ticket with Qlikview was rejected as they will not troubleshoot an issue involving Qlikview 10 and SQL Server 2012. We are evaluating our upgrade process, but we have yet to get the new EDX call working as we need it, and were trying to hold out for new server hardware before upgrading to Qlikview 11. Now we will have to upgrade in place and then in a couple of months migrate to another server.