Partially refreshing(Insert/Update/delete) records of the fact table with a partial reload

[AlexanderStraub]

Hi,

I'm working on a data model for the logistics sector with a large fact table where the items that are currently processed need to be updated frequently (Inserted, updated, and deleted records), while keeping the items which are finished in the fact table for historical KPIs. Which would be the fastest way to do so? Here is my MWE, where I generate a fact table with 20,000,000 records and update today’s 20,000 records. This script runs and achieves the goal. I get around 4 seconds for a partial reload and around 20 seconds for a full reload. Although this is not bad, it ‘feels’ strange with a noconcatenated tmp-table. Would be a better approach? Especially if I do not care about the full load duration, I just want to minimize the duration of the partial reload. Is there an option to enforce Indexing of %Date to speed up the filtering, or an incremental load technique to just update the records where PartialReload = true()?

Here is My Script and the App

let vStartTime = Now();

let vSize = 20000000;

let vRowsPerDate = $(vSize)/1000;

let vPartialReload = IsPartialReload();

Full_Load_Facts:

LOAD

     $(vSize) - RecNo() + 1 as ID,

    date(today() - ceil(RecNo() / $(vRowsPerDate))) as %Date,

    1+ Floor(Rand()*100) as Value,

    Chr(Ord('A') + Floor(Rand() * 26)) as %Dim,

    false() as PartialReload

AUTOGENERATE $(vSize);

Dims:

LOAD

    Chr(Ord('A') + RecNo() - 1) as %Dim,

    Chr(Ord('A') + RecNo() - 1) & Chr(Ord('B') + RecNo() - 1) & Chr(Ord('C') + RecNo() - 1) as DimLabel

AUTOGENERATE 26;

Calendar:

LOAD

    date(today() - RecNo() +1) as %Date,

    Week(date(today() - RecNo())) as Week,

    WeekDay(date(today() - RecNo())) as WeekDay,

    Year(date(today() - RecNo())) as Year,

    Month(date(today() - RecNo())) as Month,

    MonthName(date(today() - RecNo())) as MonthName    

AUTOGENERATE $(vSize)/$(vRowsPerDate) + 1;

IF IsPartialReload() THEN

let vStartTime = Now();

    tmp:

      NoConcatenate

      replace only load 

      *

      resident Full_Load_Facts

    where PartialReload = false();

    drop table Full_Load_Facts;

    rename table tmp to Full_Load_Facts;

    Add only LOAD

      $(vSize) + RecNo() as ID,

      today() as %Date,

      1 + Floor(Rand()*100) as Value,

      Chr(Ord('A') + Floor(Rand()*26)) as %Dim,

      true() as PartialReload

    AUTOGENERATE $(vRowsPerDate);

EndIf;

let vEndTime = Now();

let vReloadDuration = Interval(vEndTime - vStartTime, 's');

Cheers,

Alex

[Vegar]

You could look into the load MERGE functionality in Qlik Sense. It is designed for handling inserts, updates and delete.

See this page for more info: https://help.qlik.com/en-US/sense/November2024/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPref...

[AlexanderStraub]

Hi Vegar,

thanks, took me a while to understand how this would work with MERGE. So my approach would be this one, which is slightly slower during partial reaload, but the code is more compact and easier to read. Would that be the solution with MERGE which you had in mind?

let vStartTime = Now();
let vSize = 20000000;
let vRowsPerDate = $(vSize)/1000;
let vPartialReload = IsPartialReload();

Full_Load_Facts:
LOAD
     $(vSize) - RecNo() + 1 						as ID,
    date(today() - ceil(RecNo() / $(vRowsPerDate))) as %Date,
    1+ Floor(Rand()*100) 							as Value,
    Chr(Ord('A') + Floor(Rand() * 26)) 				as %Dim,
    false() 										as PartialReload
AUTOGENERATE $(vSize);

Dims:
LOAD
    Chr(Ord('A') + RecNo() - 1) 															as %Dim,
    Chr(Ord('A') + RecNo() - 1) & Chr(Ord('B') + RecNo() - 1) & Chr(Ord('C') + RecNo() - 1)	as DimLabel
AUTOGENERATE 26;

Calendar:
LOAD
    date(today() - RecNo() +1) 			as %Date,
    Week(date(today() - RecNo())) 		as Week,
    WeekDay(date(today() - RecNo()))	as WeekDay,
    Year(date(today() - RecNo())) 		as Year,
    Month(date(today() - RecNo())) 		as Month,
    MonthName(date(today() - RecNo())) 	as MonthName    
AUTOGENERATE $(vSize)/$(vRowsPerDate) + 1;


Merge On ID
    LOAD
        'Delete' as Operation,
        ID
 resident Full_Load_Facts
    WHERE PartialReload = true();


Add LOAD
$(vSize) + RecNo()                as ID,
today()                           as %Date,
1 + Floor(Rand()*100)             as Value,
Chr(Ord('A') + Floor(Rand()*26))  as %Dim,
true()                            as PartialReload
AUTOGENERATE $(vRowsPerDate);

let vEndTime = Now();
let vReloadDuration = Interval(vEndTime - vStartTime, 's');