Is it normal when loading fact table incrementally with new data, that it can take up to one hour? Are there any tips to have this incremental load to perform better?
Moving records from staging table to fact table fact table is 140 million records, new load is around 500 thousand records with new timestamp
here is the execution log overview
the table is not that complex.
17 fields are moved over, from staging to fact table
1 field is from keystore
3 fields are have custom value script
table settings are..
we are running
TX, version. 6346.1 Azure database, running with 6 cpu
Best answer by daniel
Dear @gislihei ,
What i've found is that the supernatural key might be the bottleneck in this case.
Supernatural keys will check every record every incremental load for changes and that keeps the keys at full integrity, but it will take a long time.
I've posted a similar topic here:
I'm using the option that @erik.van.mastrigt offers here:
using supernatural keys in the dimension and doing a lookup on the business key to the fact table.
Compression might also be an issue, but that really depends on several other factors. You might want to test that but i'm pretty sure the supernatural key is the performance killer here
What i've found is that the supernatural key might be the bottleneck in this case.
Supernatural keys will check every record every incremental load for changes and that keeps the keys at full integrity, but it will take a long time.
I've posted a similar topic here:
I'm using the option that @erik.van.mastrigt offers here:
using supernatural keys in the dimension and doing a lookup on the business key to the fact table.
Compression might also be an issue, but that really depends on several other factors. You might want to test that but i'm pretty sure the supernatural key is the performance killer here
