Calculate dimension or process in a load script?

[#1]

Hey folks!

I have just one simple question regarding dates in a source data and date dimensions in a presentation layer.

Allmost every dashboard (document) has some kind of a date dimension included.

Example: I have data in some kind of a source DB and dates are saved in a filed of type date. When you you import data from source to document you can than use diferent functions to extract  month, quarter, year... and get three diferent fields in a data model.

Another option is to use CALCULATE DIMENSION from date field. That takes some additional CPU resources, but on the other hand the data model is "smaller" and the time needed to fetch data from a source is quicker..

I made up these two examples. I want to know what is best practice in general (Preproccess or use calculate dimesion).

Can you please give me your opinion and some examples.
Thank you!   

[Gysbert_Wassenaar · Accepted solution]

Best practice is to create a calendar table in the script. This will move the performance penalty from the user to the load process. If you don't want users wasting their time and complaining to you about lousy performance you'll choose to calculate the calendar in the script.

[#3]

Creating a master calendar in Qlikview takes seconds, yes it uses RAM because it is additional fields but it doesn't hit the CPU.  Calculated dimensions are for the most-part CPU monsters and if you add that to section access and all the other calculations you will hit the CPU threshold before you even touch the RAM thresolds

[#4]

In case of using QVDs than you calculate calendar in loading QVDs from a sources to achieve an optimized qvd load?

[#5]

thanks!

[Gysbert_Wassenaar]

A qvd is nothing more then a qlikview table stored into a file. Any calculations are done in Qlikview, not the qvd file. You can calculate a calendar in Qlikview in the script and then store that calendar table in a qvd. You can then reuse that qvd for any document where you want to use that calendar table.

If you load a date field from a qvd and use that field to calculate a calendar in that load then you won't have an optimized load for the qvd where your date field comes from.