Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Folks,
We are having ShopFloor reports , which we are triggering every 3 hours from QMC , but sometimes during QVD Generation we are missing data. Let me explain this with an example : Suppose we have triggered task from QMC at 6:00 AM on Monday, but at that time because of XYZ reasons data was not available and this leads to no data inside report for Monday ,later on Tuesday data got updated for Monday in DB , but as we are generating data based on last reload time (Incremental load) ,we don't have possibilities to see Monday's missing data on Tuesday , because each trigger will load new records and will not take care of missing records.
In General we are using below script :
LET vStartDate = date(makedate(2016,1), 'DD.MM.YYYY hh:mm:ss');
Set vQVD = '\\TXKAPPAZU025\d$\qvprod\QV_QVD';
Let vQVDCurrentYear = '\\TXKAPPAZU025\d$\qvprod\QV_QVD\'& year(today());
Let vStart = now();
Let vReloadStart = now();
///$tab STICHPROBE
if filesize('$(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd') >0 then
trace >>>>>> $(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd ;
MAX_TIME:
load
max(DTTSPROBE) as LAST_LOAD_TIME
from $(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd (qvd) ;
let vLAST_LOAD_TIME = timestamp(peek('LAST_LOAD_TIME',0,'MAX_TIME'), 'DD.MM.YYYY hh:mm:ss');
trace >>>>>> LAST_LOAD_TIME: $(vLAST_LOAD_TIME);
drop table MAX_TIME;
else
LET vLAST_LOAD_TIME = '$(vStartDate)';
trace >>>>>> LAST_LOAD_TIME: $(vLAST_LOAD_TIME);
end if
// load new values
STICHPROBE:
LOAD
'$(vPlant)' as Plant,
*;
SQL SELECT
NSPCIDNR,
NTOOLNR,
DTTSPROBE,
DTTSERF,
NSTPFEHLERFLAG,
DXMIN,
DTO,
DTU,
DXQ
FROM $(vPlantAutor).STICHPROBE
WHERE DTTSPROBE > to_timestamp('$(vLAST_LOAD_TIME)', 'DD.MM.YYYY HH24:MI:SS');
// load prior values
if filesize('$(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd') >0 then
concatenate(STICHPROBE)
load
*
from $(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd (qvd);
end if
// store all values
store STICHPROBE into $(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd;
LET vSTICHPROBE_LOAD_DURATION = Interval(now() - vStart) ;
LET vStart = now();
LET vSTICHPROBE_Size = num(filesize('$(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd')/1024,'00.0') & ' KB';
LET vSTICHPROBE_Records = QvdNoOfRecords ('$(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd');
drop table STICHPROBE;
To overcome this issue what we thought of re-generating QVDs , so on every 5th day of Week .i.e Friday we will be doing full load from Monday of same week so that we can again recall missing records during whole weeks in QVDs on Friday. To achieve this I'm trying below changes in above script , but looks like this is not working 100%.
*****New Changes are in red
LET vStartDate = date(makedate(2016,1), 'DD.MM.YYYY hh:mm:ss');
Set vQVD = '\\TXKAPPAZU025\d$\qvprod\QV_QVD';
Let vQVDCurrentYear = '\\TXKAPPAZU025\d$\qvprod\QV_QVD\'& year(today());
Let vLoadFrom = timestamp(If(Weekday($(vLAST_LOAD_TIME)) >= 5, WeekStart($(vLAST_LOAD_TIME)), $(vLAST_LOAD_TIME)), 'DD.MM.YYYY HH24:MI:SS');
Let vStart = now();
Let vReloadStart = now();
///$tab STICHPROBE
if filesize('$(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd') >0 then
trace >>>>>> $(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd ;
MAX_TIME:
load
max(DTTSPROBE) as LAST_LOAD_TIME
from $(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd (qvd) ;
let vLAST_LOAD_TIME = timestamp(peek('LAST_LOAD_TIME',0,'MAX_TIME'), 'DD.MM.YYYY hh:mm:ss');
trace >>>>>> LAST_LOAD_TIME: $(vLoadFrom);
drop table MAX_TIME;
else
LET vLAST_LOAD_TIME = '$(vStartDate)';
trace >>>>>> LAST_LOAD_TIME: $(vLoadFrom);
end if
// load new values
STICHPROBE:
LOAD
'$(vPlant)' as Plant,
*;
SQL SELECT
NSPCIDNR,
NTOOLNR,
DTTSPROBE,
DTTSERF,
NSTPFEHLERFLAG,
DXMIN,
DTO,
DTU,
DXQ
FROM $(vPlantAutor).STICHPROBE
WHERE DTTSPROBE > to_timestamp('$(vLoadFrom)', 'DD.MM.YYYY HH24:MI:SS');
// load prior values
if filesize('$(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd') >0 then
concatenate(STICHPROBE)
load
*
from $(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd (qvd);
end if
// store all values
store STICHPROBE into $(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd;
LET vSTICHPROBE_LOAD_DURATION = Interval(now() - vStart) ;
LET vStart = now();
LET vSTICHPROBE_Size = num(filesize('$(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd')/1024,'00.0') & ' KB';
LET vSTICHPROBE_Records = QvdNoOfRecords ('$(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd');
drop table STICHPROBE;
Please advice if any changes required to support missing data scenario or what should be the best approach in such cases.
Thanks in advance!
AS
any suggestion???
Thanks,
AS
Are you facing any problem over here? What was the issue.
Issues:
- Data in Qvds is not correct
-Generation of QVds take much longer time-
- Tasks from QMC is getting hanged
Thanks,
AS
Hi Amit,
If you are getting failed that qvd generator on monday, may be you can try the 'ScriptErrorCount' Variable.
Like
If ScriptErrorCount = 0 then
Let vLAST_LOAD_TIME= date(now()-1);
End if
HI,
I have Question Regarding DTTSPROBE ,
if data is not available then how come it points to Next day,
lets say your QVD load missed on Monday then max(DTTSPROBE) date should be Sunday Correct?
else How this date is getting populated in your raw data?
Hi Amit,
I would say that you are not correct about how you load Your data. "but as we are generating data based on last reload time (Incremental load)" this is not true. You use Max timestamp in Your qvd.
Here you find max of DTTSPROBE which is the correct way of doing a incremental reload. You also save it into variable vLAST_LOAD_TIME.
MAX_TIME:
load
max(DTTSPROBE) as LAST_LOAD_TIME
from $(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd (qvd) ;
let vLAST_LOAD_TIME = timestamp(peek('LAST_LOAD_TIME',0,'MAX_TIME'), 'DD.MM.YYYY hh:mm:ss');
Later on you use this variable in Your SQL statement, here:
FROM $(vPlantAutor).STICHPROBE
WHERE DTTSPROBE > to_timestamp('$(vLAST_LOAD_TIME)', 'DD.MM.YYYY HH24:MI:SS');
I have 2 question here,
1 what is $(vPlantAutor). cant find the variable somewhere in script
2 why are you transform the date to 'DD.MM.YYYY hh:mm:ss' in Your Variable but use 'DD.MM.YYYY HH24:MI:SS' in SQL ? I should create the variable so you could use WHERE DTTSPROBE >= '$(vLAST_LOAD_TIME)'
So far I dont find any big problem, should work.
In last step, you should use you keys in a where not exists.
if filesize('$(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd') >0 then
concatenate(STICHPROBE)
load
*
from $(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd (qvd)
Where not Exists(YourKey);
Even if you dont get any transactions on monday, it should find Max timestamp in Your qvd and from that timestamp reload New transactions.
Hi Staffan,
Sorry you are correct incremental load is based on max(DTTSPROBE).
We are missing some records on day basis , which might got updated either the same day or might be next days,so sorry I should mention that we never had a case of missing entire Monday's data ...we are just missing few records and to tackle with this situation we are looking for full load on Fridays , so that we can accommodate all missing records during weekly full load and not from scratch.
Answer to Your Questions:
Thanks,
AS
we are having daily data , it just matter of missing few records. Please refer my reply to Staffan.
Thanks,
AS
Ok Amit,
When you says that records are missing on monday but comes in later on. Will this means that the timestamp on these records is earlier than Your max(DTTSPROBE)? Then I see Your problem.
If you dont have any transactiondate which will be when the transaction get into the database then it could be a little bit tricky.
I should have test some inner join With the FROM $(vPlantAutor).STICHPROBE table. Then you have to know the keys in Your table. Something like this maybe:
concatenate(STICHPROBE)
load
Key, *
from $(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd (qvd)
Where not Exists(Key);
if weekday()=5 then //If Friday then check for missing records
inner join
load Key;
SQL SELECT Key FROM $(vPlantAutor).STICHPROBE;
end if