QMC - get fresh data from DB as soon as possible even in case of delays

[DoctorPolidori]

Background

My apps update daily around 8 AM, reading from a Data Warehouse (DW) via an Oracle Database that is supposed to have all data ready by 7 AM. Sometimes, the updates are delayed several hours, up to 8 hours. The DW exposes an Oracle table that keeps a load status in real time.

Question

Using QMC in Qlik Sense Enterprise on Windows Server, what are my options to ensure my Qlik apps get fresh data only when the Data Warehouse is ready and as soon as possible, even in case of delays?

Thank you in advance.

[maxgro]

Create a new Qlik task that from 7 AM reads the loading status from the Oracle table every n minutes (you can use a loop with sleep in Qlik); the task ends successfully only when the DW loading finishes. Ends the task with an error after a fixed period of time.

The reading tasks from the Oracle db will then have to depend on the success of this new task.

[marksouzacosta]

Hi @DoctorPolidori,

In addition to @maxgro solution, you could add a trigger/job/stored procedure in your DW that executes a QMS API that starts a QlikView task. I wrote an article about that using PowerShell: Calling QlikView Tasks from Qlik Sense on-prem - Qlik Community - 2455358

[DoctorPolidori · Accepted solution]

We have used this approach:

//---------------------------------------------------------------------------------------------------------
// Sub CheckDW. reads DW table and tests if loading went well, vOK = 1.
//---------------------------------------------------------------------------------------------------------
Sub CheckEDW;

LIB CONNECT TO 'DW ';

STATUS_DW:
LOAD
MAPPING, 
STATUS, 
    DATUM,       
    1 as OK; // OK = 1 om allt är OK.
SQL SELECT 
"MAPPNING",
"STATUS",
"LOAD_DATE"
FROM STATUS_DW
WHERE DATUM = '$(vToday)' and 
MAPPING = 'Exchange' and   // all tables successfully updated
STATUS = 'COMPLETED';

If Peek('OK') = 1 Then // if batch load status is 'COMPLETED'. vOK = 1.
      LET vOK = 1; //  1 = COMPLETED.
Else // batch load status 'FAILED'. vOK = 0.
LET vOK = 0; // 0 = FAILED.
EndIf;
    Drop Table STATUS_DW;
    
End Sub;

//---------------------------------------------------------------------------------------------------------
// Loop runs till vOK = 1 or  vLoop value reaches 9 = 4 hours waiting.
//---------------------------------------------------------------------------------------------------------
SET vOK = 0;
SET vLoop = 1;
LET vDagenD = ToDay();

Do Until vOK = 1 or vLoop = 9 // loop until ok or time over
Call CheckEDW; // calling Sub
    If vOK = 1 Then  // DW ready, vOK = 1.
        Exit Do;        // Loop finishes because DW load is ready
    Else
    vLoop = vLoop + 1; // vLoop increments
Sleep 1800000; // wait 30 minutes. 1800000 = 30 min, Max = 3600000 = 1 hour.
    EndIf;
Loop;

If vLoop = 9 Then // no OK status (vOK = 1) after time run out
Load * From UnexistingTableThatGeneratesTaskFail; // Unexisting table that generates task fail so that next linked load task does not run   (using On task event trigger)
EndIf;
//---------------------------------------------------------------------------------------------------------