Re: Qlikview Reload does not pull all database rec... - Qlik Community

Report Inappropriate Content · ‎2014-03-17

We have a Qlikview document that is scheduled for reload via Qlikview Server. When it runs at 6:10am via the server the log indicates only 60K records were pulled for one of the DB queries in the Edit Script. This is incorrect as there are over 105K records that should be returned from the query. Rerunning the task later in the day corrects the issue and the log shows the 105K records. Also running the exact same query via SQL Mgt Studio at 6:00am, just prior to the scheduled reload, returns the 105K records. The Qlikview log file does not indicate any failure, nor do the Qlikview server logs.

Looking for any suggestions here...Thanks in advance.

Here is a portion from the log when it does not return the correct number of records (the eCW_Patient is the table in question):

3/17/2014 6:10:02 AM: 0062 WHERE EncounterDeleteFlag = 'N'

3/17/2014 6:10:02 AM:        28 fields found: LabsEncounterKey, RxHubEncounterKey, VitalsEncounterKey, StructuredDataFactEncounterKey, DiagnosisFactEncounterKey, ClaimFactEncounterKey, FactEncounterKey, DimEncounterKey, Visit Type, Visit Status, Encounter Type, EncounterDimProviderKey, Rendering Provider, DimPatientKey, DimPatientDataKey, Encounter Patient, PatientRegistryAccountNo, EncounterFacilityName, EncounterFacilityGroupName, FactEncounterID, Appointment Date, Encounter Appointment Calendar Date, EncounterAppointmentCalendarDate_YY, EncounterAppointmentCalendaYear, EncounterAppointmentCalendarQuarterWithYear, EncounterAppointmentScheduledStartTime, EncounterAppointmentReason, EncounterDeleteFlag, 1,123,715 lines fetched
3/17/2014 6:10:09 AM: 0065 eCW_Patient:
3/17/2014 6:10:09 AM: 0066 LOAD
3/17/2014 6:10:09 AM: 0067   DimPatientKey,
3/17/2014 6:10:09 AM: 0068   DimPatientKey as VaccinationsDimPatientKey,
3/17/2014 6:10:09 AM: 0069   DimPatientKey as ReferralDimPatientKey,
3/17/2014 6:10:09 AM: 0070   DimPatientKey as PatientDataPatientKey,
3/17/2014 6:10:09 AM: 0071   DimPatientKey as AdmissionEventDimPatientKey,
3/17/2014 6:10:09 AM: 0072   DimPatientKey as PatientInsuranceDimPatientKey,
3/17/2014 6:10:09 AM: 0073   PatientPID as [Patient PID],
3/17/2014 6:10:09 AM: 0074   PatientAccountNo as [Patient Account No],
3/17/2014 6:10:09 AM: 0075   MeditechSourceID as [Meditech Source ID],
3/17/2014 6:10:09 AM: 0076   MeditechPatientID as [Meditech Patient ID],
3/17/2014 6:10:09 AM: 0077   SystemFlag as [System Flag],
3/17/2014 6:10:09 AM: 0078   PatientMRN as [Patient MRN],
3/17/2014 6:10:09 AM: 0079   PatientFullName as [Patient FullName],
3/17/2014 6:10:09 AM: 0080   DATE(PatientBirthDate, 'MM/DD/YYYY') as [Patient Birth Date],
3/17/2014 6:10:09 AM: 0081   PatientAddress1 as [Patient Address1],
3/17/2014 6:10:09 AM: 0082   PatientAddress2 as [Patient Address2],
3/17/2014 6:10:09 AM: 0083   PatientCity as [Patient City],
3/17/2014 6:10:09 AM: 0084   PatientState as [Patient State],
3/17/2014 6:10:09 AM: 0085   PatientZip as [Patient Zip],
3/17/2014 6:10:09 AM: 0086   PatientDeleteFlag as [Patient Delete Flag],
3/17/2014 6:10:09 AM: 0087   PatientDeceased,
3/17/2014 6:10:09 AM: 0088      DATE (PatientDeceasedDate) as [DeceasedDate],
3/17/2014 6:10:09 AM: 0089   PatientSSN as [Patient SSN],
3/17/2014 6:10:09 AM: 0090   PCP as [Patient PCP],
3/17/2014 6:10:09 AM: 0091   eCWPCP as [Patient eCW PCP],
3/17/2014 6:10:09 AM: 0092   ProvidersMatch as [Patient PCP Match Flag]
3/17/2014 6:10:09 AM: 0094 SQL SELECT
3/17/2014 6:10:09 AM: 0095     [DimPatientKey]
3/17/2014 6:10:09 AM: 0096        ,[PatientPID]
3/17/2014 6:10:09 AM: 0097        ,[PatientAccountNo]
3/17/2014 6:10:09 AM: 0098        ,p.[MeditechSourceID]
3/17/2014 6:10:09 AM: 0099        ,[MeditechPatientID]
3/17/2014 6:10:09 AM: 0100     ,CASE
3/17/2014 6:10:09 AM: 0101     WHEN PatientPID <> -1 and MeditechPatientID <> '' THEN 'Both'
3/17/2014 6:10:09 AM: 0102     WHEN PatientPID <> -1 and MeditechPatientID = '' THEN 'eCW Only'
3/17/2014 6:10:09 AM: 0103     WHEN PatientPID = -1 and MeditechPatientID <> '' THEN 'Meditech Only'
3/17/2014 6:10:09 AM: 0104     ELSE 'Unknown'
3/17/2014 6:10:09 AM: 0105    END AS SystemFlag
3/17/2014 6:10:09 AM: 0106        ,[PatientMRN]
3/17/2014 6:10:09 AM: 0107        ,[PatientFullName]
3/17/2014 6:10:09 AM: 0108        ,[PatientBirthDate]
3/17/2014 6:10:09 AM: 0109        ,[PatientAddress1]
3/17/2014 6:10:09 AM: 0110        ,[PatientAddress2]
3/17/2014 6:10:09 AM: 0111        ,[PatientCity]
3/17/2014 6:10:09 AM: 0112        ,[PatientState]
3/17/2014 6:10:09 AM: 0113        ,[PatientZip]
3/17/2014 6:10:09 AM: 0114        ,[PatientDeceased]
3/17/2014 6:10:09 AM: 0115        ,[PatientDeceasedDate]
3/17/2014 6:10:09 AM: 0116        ,[PatientDeleteFlag]
3/17/2014 6:10:09 AM: 0117        ,[PatientSSN]
3/17/2014 6:10:09 AM: 0118        ,prov.ProviderFullName PCP
3/17/2014 6:10:09 AM: 0119     ,eCWprov.ProviderFullName as eCWPCP
3/17/2014 6:10:09 AM: 0120     ,CASE
3/17/2014 6:10:09 AM: 0121     WHEN prov.ProviderFullName = eCWprov.ProviderFullName THEN 'Y'
3/17/2014 6:10:09 AM: 0122     ELSE 'N'
3/17/2014 6:10:09 AM: 0123     END as ProvidersMatch
3/17/2014 6:10:09 AM: 0124 FROM "DW_DDS"."bi_dds".DimPatient p
3/17/2014 6:10:09 AM: 0125 LEFT JOIN "DW_DDS"."bi_dds".DimProvider prov
3/17/2014 6:10:09 AM: 0126   ON p.PatientPCPKey = prov.DimProviderKey
3/17/2014 6:10:09 AM: 0127 LEFT JOIN "DW_DDS"."bi_dds".DimProvider eCWprov
3/17/2014 6:10:09 AM: 0128   ON p.PatienteCWPCPKey = eCWprov.DimProviderKey
3/17/2014 6:10:09 AM: 0129 WHERE DimPatientKey <> -1
3/17/2014 6:10:09 AM: 0130 AND [PatientDeleteFlag] = 'N'
3/17/2014 6:10:09 AM:        26 fields found: DimPatientKey, VaccinationsDimPatientKey, ReferralDimPatientKey, PatientDataPatientKey, AdmissionEventDimPatientKey, PatientInsuranceDimPatientKey, Patient PID, Patient Account No, Meditech Source ID, Meditech Patient ID, System Flag, Patient MRN, Patient FullName, Patient Birth Date, Patient Address1, Patient Address2, Patient City, Patient State, Patient Zip, Patient Delete Flag, PatientDeceased, DeceasedDate, Patient SSN, Patient PCP, Patient eCW PCP, Patient PCP Match Flag, 60,298 lines fetched
3/17/2014 6:10:10 AM: 0132 PatientData:
3/17/2014 6:10:10 AM: 0133 LOAD
3/17/2014 6:10:10 AM: 0134      PatientDataPatientKey,
3/17/2014 6:10:10 AM: 0135      DATE(LastOfficeVisitDate) as [LastOfficeVisitDate],
3/17/2014 6:10:10 AM: 0136      DATE(NextOfficeVisitDate) as [NextOfficeVisitDate]
3/17/2014 6:10:10 AM: 0137 SQL SELECT DimPatientKey as PatientDataPatientKey,
3/17/2014 6:10:10 AM: 0138      LastOfficeVisitDate,
3/17/2014 6:10:10 AM: 0139      NextOfficeVisitDate
3/17/2014 6:10:10 AM: 0140 FROM "DW_DDS"."bi_dds"."vwPatientData_QV"
3/17/2014 6:10:31 AM:        3 fields found: PatientDataPatientKey, LastOfficeVisitDate, NextOfficeVisitDate, 68,739 lines fetched

Here is a portion of the log from a subsequent run where it does:

3/17/2014 8:07:58 AM: 0062 WHERE EncounterDeleteFlag = 'N'

3/17/2014 8:07:58 AM:        28 fields found: LabsEncounterKey, RxHubEncounterKey, VitalsEncounterKey, StructuredDataFactEncounterKey, DiagnosisFactEncounterKey, ClaimFactEncounterKey, FactEncounterKey, DimEncounterKey, Visit Type, Visit Status, Encounter Type, EncounterDimProviderKey, Rendering Provider, DimPatientKey, DimPatientDataKey, Encounter Patient, PatientRegistryAccountNo, EncounterFacilityName, EncounterFacilityGroupName, FactEncounterID, Appointment Date, Encounter Appointment Calendar Date, EncounterAppointmentCalendarDate_YY, EncounterAppointmentCalendaYear, EncounterAppointmentCalendarQuarterWithYear, EncounterAppointmentScheduledStartTime, EncounterAppointmentReason, EncounterDeleteFlag, 1,087,709 lines fetched
3/17/2014 8:08:08 AM: 0065 eCW_Patient:
3/17/2014 8:08:08 AM: 0066 LOAD
3/17/2014 8:08:08 AM: 0067   DimPatientKey,
3/17/2014 8:08:08 AM: 0068   DimPatientKey as VaccinationsDimPatientKey,
3/17/2014 8:08:08 AM: 0069   DimPatientKey as ReferralDimPatientKey,
3/17/2014 8:08:08 AM: 0070   DimPatientKey as PatientDataPatientKey,
3/17/2014 8:08:08 AM: 0071   DimPatientKey as AdmissionEventDimPatientKey,
3/17/2014 8:08:08 AM: 0072   DimPatientKey as PatientInsuranceDimPatientKey,
3/17/2014 8:08:08 AM: 0073   PatientPID as [Patient PID],
3/17/2014 8:08:08 AM: 0074   PatientAccountNo as [Patient Account No],
3/17/2014 8:08:08 AM: 0075   MeditechSourceID as [Meditech Source ID],
3/17/2014 8:08:08 AM: 0076   MeditechPatientID as [Meditech Patient ID],
3/17/2014 8:08:08 AM: 0077   SystemFlag as [System Flag],
3/17/2014 8:08:08 AM: 0078   PatientMRN as [Patient MRN],
3/17/2014 8:08:08 AM: 0079   PatientFullName as [Patient FullName],
3/17/2014 8:08:08 AM: 0080   DATE(PatientBirthDate, 'MM/DD/YYYY') as [Patient Birth Date],
3/17/2014 8:08:08 AM: 0081   PatientAddress1 as [Patient Address1],
3/17/2014 8:08:08 AM: 0082   PatientAddress2 as [Patient Address2],
3/17/2014 8:08:08 AM: 0083   PatientCity as [Patient City],
3/17/2014 8:08:08 AM: 0084   PatientState as [Patient State],
3/17/2014 8:08:08 AM: 0085   PatientZip as [Patient Zip],
3/17/2014 8:08:08 AM: 0086   PatientDeleteFlag as [Patient Delete Flag],
3/17/2014 8:08:08 AM: 0087   PatientDeceased,
3/17/2014 8:08:08 AM: 0088      DATE (PatientDeceasedDate) as [DeceasedDate],
3/17/2014 8:08:08 AM: 0089   PatientSSN as [Patient SSN],
3/17/2014 8:08:08 AM: 0090   PCP as [Patient PCP],
3/17/2014 8:08:08 AM: 0091   eCWPCP as [Patient eCW PCP],
3/17/2014 8:08:08 AM: 0092   ProvidersMatch as [Patient PCP Match Flag]
3/17/2014 8:08:08 AM: 0094 SQL SELECT
3/17/2014 8:08:08 AM: 0095     [DimPatientKey]
3/17/2014 8:08:08 AM: 0096        ,[PatientPID]
3/17/2014 8:08:08 AM: 0097        ,[PatientAccountNo]
3/17/2014 8:08:08 AM: 0098        ,p.[MeditechSourceID]
3/17/2014 8:08:08 AM: 0099        ,[MeditechPatientID]
3/17/2014 8:08:08 AM: 0100     ,CASE
3/17/2014 8:08:08 AM: 0101     WHEN PatientPID <> -1 and MeditechPatientID <> '' THEN 'Both'
3/17/2014 8:08:08 AM: 0102     WHEN PatientPID <> -1 and MeditechPatientID = '' THEN 'eCW Only'
3/17/2014 8:08:08 AM: 0103     WHEN PatientPID = -1 and MeditechPatientID <> '' THEN 'Meditech Only'
3/17/2014 8:08:08 AM: 0104     ELSE 'Unknown'
3/17/2014 8:08:08 AM: 0105    END AS SystemFlag
3/17/2014 8:08:08 AM: 0106        ,[PatientMRN]
3/17/2014 8:08:08 AM: 0107        ,[PatientFullName]
3/17/2014 8:08:08 AM: 0108        ,[PatientBirthDate]
3/17/2014 8:08:08 AM: 0109        ,[PatientAddress1]
3/17/2014 8:08:08 AM: 0110        ,[PatientAddress2]
3/17/2014 8:08:08 AM: 0111        ,[PatientCity]
3/17/2014 8:08:08 AM: 0112        ,[PatientState]
3/17/2014 8:08:08 AM: 0113        ,[PatientZip]
3/17/2014 8:08:08 AM: 0114        ,[PatientDeceased]
3/17/2014 8:08:08 AM: 0115        ,[PatientDeceasedDate]
3/17/2014 8:08:08 AM: 0116        ,[PatientDeleteFlag]
3/17/2014 8:08:08 AM: 0117        ,[PatientSSN]
3/17/2014 8:08:08 AM: 0118        ,prov.ProviderFullName PCP
3/17/2014 8:08:08 AM: 0119     ,eCWprov.ProviderFullName as eCWPCP
3/17/2014 8:08:08 AM: 0120     ,CASE
3/17/2014 8:08:08 AM: 0121     WHEN prov.ProviderFullName = eCWprov.ProviderFullName THEN 'Y'
3/17/2014 8:08:08 AM: 0122     ELSE 'N'
3/17/2014 8:08:08 AM: 0123     END as ProvidersMatch
3/17/2014 8:08:08 AM: 0124 FROM "DW_DDS"."bi_dds".DimPatient p
3/17/2014 8:08:08 AM: 0125 LEFT JOIN "DW_DDS"."bi_dds".DimProvider prov
3/17/2014 8:08:08 AM: 0126   ON p.PatientPCPKey = prov.DimProviderKey
3/17/2014 8:08:08 AM: 0127 LEFT JOIN "DW_DDS"."bi_dds".DimProvider eCWprov
3/17/2014 8:08:08 AM: 0128   ON p.PatienteCWPCPKey = eCWprov.DimProviderKey
3/17/2014 8:08:08 AM: 0129 WHERE DimPatientKey <> -1
3/17/2014 8:08:08 AM: 0130 AND [PatientDeleteFlag] = 'N'
3/17/2014 8:08:08 AM:        26 fields found: DimPatientKey, VaccinationsDimPatientKey, ReferralDimPatientKey, PatientDataPatientKey, AdmissionEventDimPatientKey, PatientInsuranceDimPatientKey, Patient PID, Patient Account No, Meditech Source ID, Meditech Patient ID, System Flag, Patient MRN, Patient FullName, Patient Birth Date, Patient Address1, Patient Address2, Patient City, Patient State, Patient Zip, Patient Delete Flag, PatientDeceased, DeceasedDate, Patient SSN, Patient PCP, Patient eCW PCP, Patient PCP Match Flag, 105,688 lines fetched
3/17/2014 8:08:11 AM: 0132 PatientData:
3/17/2014 8:08:11 AM: 0133 LOAD

Report Inappropriate Content · ‎2014-03-17

A few suggestions:

Since when did this problem start occurring. All of a sudden or since beginning
The issue sounds very interesting. Can you try running at other times, maybe like earlier 4am or 5am. You can also play around with the reload time and see during what all times this is occurring
If you have permission to run from your local machine please try running it at different times and see if you face similar issues

Thanks

AJ

giakoum · ‎2014-03-17

It could be that db is still updating at the time, especially if those are views you are reading from. So maybe most of the records are locked so not read. If those are tables, try using (with read uncommited) or nolock in your SQL query.

QV does not select what it reads, it reads whatever is available, based on the where clauses. I believe you should concentrate on the database side.

rwunderlich · ‎2014-03-17

What build (Version & SR) of QV?

-Rob

giakoum · ‎2014-03-17

well it looks like I might be wrong...

Intermittent data loss (QV not loading the full dataset from source)

So Rob's question makes a lot of sense....

A bit disappointed from Qliktech here if this holds...

Report Inappropriate Content · ‎2014-03-17

Ioannis,

Looks like I cannot get to the link you posted. I get an authorization error for some reason. Can you post any relevant of the text here.

giakoum · ‎2014-03-17

For over a year now we have experienced an intermittent problem where our QV reload script returned a success result but had not loaded the full dataset from a source table.

This was eventually proven by repeatedly running the load script against a static and isolated database and comparing the number of records loaded. Occassionally (e.g. 1 in 20 reloads) the loaded data was missing some rows. Often the loss was only a few rows (tens or hundreds) but occassionally the loss was thousands. Usually on the next reload the full data was loaded. We have seen the problem on QV10 and QV11 and experienced it with Oracle, MSSQL and Access data sources but haven't tried it with others. We have recreated the problem on QV desktop and QV server. Increasing memory or changing the connection driver didn't resolve the problem.

QlikTech have acknowledged this as a high priority bug 47189 and are currently working on a fix. They have not suggested a work around or said it is something unusual in our script but our load script is VERY simple. For each table in our source database we select all the data and write it to a QVD file for consumption in other QVW documents.

TableA:

LOAD *;

SQL SELECT * FROM DataTableA;

STORE TableA INTO "DataFiles/TableA.qvd";

DROP Table TableA;

A discussion exists in the general QlikCommunity here if you want to know more

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

Now fixed in QlikView 10 SR5, which was released on June 28^th 2012:

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

maxgro · ‎2014-03-17

Interesting issue (a bit worrying); I would add, for trace, the same select but only with a count of records before and after that statement.

Report Inappropriate Content · ‎2014-03-18

I know we are on 11 but I'm trying to get the specifics. Will post back once I do.

I've been told we are on 11.0.11282.0.

Qlikview Reload does not pull all database records