Creating a "load trigger" :)

[#1]

Hi people!

Take a look at the attached qvd.

Based on the fields in the "DWH_MANAGEMENT" table I want to construct a logic that reloads the rest og the script if there is a new entry (fresh data) in this table. And if not, sends an unsuccessful msg to the server to prevent the rest of the qvd's to run. (on the server)

(I could probably figure this out myselves, but I guess something similar has been done before )

Thanks in advance!

Best regards
Trond Erik

[Anonymous]

There are two tasks here - recognize situation "there are no new records", and "do not reload".

1. You can check this using QVD.  That is, store the DWH_Management table into QVD.  Next time, read QVD first, check the number of rows (in a variable), append new rows from the database table (probably using exists() function), check number of rows again (another variable), compare variables.  If they are the same, there are no new records.

2. If there is new data, run the rest of the script.  If there is not...  maybe run a script that is incorrect by purpose, that is designed to fail.  Other reloads can be made dependent on this reload, and will run only if this one is successful.

Not sure if it makes much sense...  Let us know what's you solution after all 

Regards,

Michael

[#3]

I did it like this, and it works like a clock!

CONNECT32 TO [Provider=SQLOLEDB.1;Persist Security Info=True;User ID=testDWH;Data Source=sintefmac02;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=SINTEFPC5076;Use Encryption for Data=False;Tag with column collation when possible=False]
Last_Reload: //Henter maxdato fra DWH_MANAGEMENT i datavarehuset
LOAD
max(Date(Floor("LAST_LOAD_END"))) as DWHLastLoadDate,
max(Time("LAST_LOAD_END")) as DWHLastLoadTime,
    SOURCE as DWHSource
Group By SOURCE;
SQL SELECT *
FROM "maconomy_dwh".dbo."DWH_MANAGEMENT";
Join
LOAD
   date(max(%Date)) as StageLastLoadDate,
   time(max(LoadEnd)) as StageLastLoadTime
FROM
$(PathDataStage)\Data Stage Load History.qvd
(qvd);

Trigger: //Sjekker om ny last er klar. Hvis triggeren er større eller lik 1 skal det gjøres en reload.
LOAD sum(Reload) as Reload;
LOAD
           *,
          if(DWHLastLoadTime>StageLastLoadTime,1,0) as Reload
Resident Last_Reload;

DROP Table Last_Reload;

Let vReloadTrigger=FieldValue('Reload',1);
DROP Table Trigger;

if vReloadTrigger>=1 then
Test:
LOAD
ReloadTime() as ReloadTime
AutoGenerate(1);
LOAD ReloadTime
FROM
$(PathDataStage)\TriggerTest.qvd
(qvd);
STORE  Test into $(PathDataStage)\TriggerTest.qvd (qvd);

ELSE
Load Cast UnsuccessfullLoad; // to generate an error in the load process

end if;