topic Partially refreshing(Insert/Update/delete) records of the fact table with a partial reload in Connectivity & Data Prep

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

AlexanderStraub — Tue, 29 Apr 2025 17:13:14 GMT

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

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

Vegar — Wed, 30 Apr 2025 04:05:05 GMT

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/ScriptPrefixes/Merge.htm

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

AlexanderStraub — Fri, 02 May 2025 16:59:27 GMT

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');