Is maintaining optimized QVD load always the best approach?

[Jacob_Poole]

So obviously an optimized load is much faster, but I'm curious how other developers feel about this scenario.  I'm merging fact tables from multiple accounts, and after the merge the data needs some modifications. So I then load from the resident and make the modifications, generate the AutoNumberHash256() dimension keys, etc.  Currently there are 33M rows in the fact table and it will keep growing...  I've heard doing resident load on a fact table with millions of rows, unless absolutely necessary, may not be the best idea.   

What are your thoughts? 

How might you approach this scenario?

Any slick tricks you've used?

[rwunderlich]

It's all relative. I think the first priority is write clear and maintainable script code.  Optimization second. If it completes in an acceptable amount of time, then you are good. 

Curious, why you are using AutoNumberHash256() to generate keys?

-Rob

[Jacob_Poole]

Out of habit maybe.  The keys are made up of multiple fields and concatenated keys didn't seem like the most efficient option.  

Example key:  

AutoNumberHash256(DatabaseName, wcrvu_year, doc_no_tmp, cpt_no_tmp, cpt_mod_tmp, fac_no_tmp) AS %Key_wcrvu

Maybe one of the other auto number options would be better suited?  Or something else entirely?

[Jacob_Poole · Accepted solution]

Thanks for the perspective Rob. 

I started testing changes in the script.  As of now,  I'm sticking with the resident loads,  and implementing a mapping technique I picked up from your blog https://qlikviewcookbook.com/ a while back.  It helped clean up the script, and improved the load time a little bit.

I guess I'll test the impacts of switching the keys next.

[rwunderlich]

Hi Jacob,

I recommend using the AutoNumber statement to generate the keys.  You can place the statement at the end of your script and not interfere with the LOAD.

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com