orka wrote:What duplication are you talking about? Run the script and check frequencies.
When I do that, I can see that both StartDate and EndDate are duplicated when people share the same vacation days, just like I suggested. Actually, it's WORSE than I thought, as it shows FOUR copies each, not the two I expected. Ah, that's caused by a script error. Where you have this:
left join (Vacation)
load StartDate
,EndDate
,autonumber(StartDate & '-' & EndDate) as DateRangeId
resident Vacation;
It needs to be this:
left join (Vacation)
load DISTINCT StartDate
,EndDate
,autonumber(StartDate & '-' & EndDate) as DateRangeId
resident Vacation;
That fixed, we're back to the two copies I would expect. "So if fifty employees go on vacation together, you will duplicate the start and end date information for each employee", specifically in the Vacation table. That probably wasn't clear.
orka wrote:This post was not about NF, if you want to discuss it we can open another topic.
OK, the word "denormalize" is unnecessary if you think it's off topic. We'll stick to your wording. In regards to your first intervalmatch solution, you say:
"cons: vacation record duplication for number of dates in the range"
In regards to your final solution, I can say:
"cons: vacation date range duplication for number of people with identical vacations"
Now the duplication in your final solution is much less significant, I'll admit. But it seems consistent to at least state it, particularly since it may affect OTHER data sets much more significantly. And if data duplication is a stated con, the synthetic key approach doesn't have this con. We could also solve it without using synthetic keys by using the script in my previous post. So two options for avoiding this con.
orka wrote:Synthetic key kills more memory than autonumber. And for 10 millions vacation during 10 years this synthetic key will grab a half of your application.
In my real application by doing this I've reduced my QVW from 15M to 10M and performance was raised from 10 sec to 1 sec for a chart, may be less.
THIS is what surprised me. I wouldn't have thought the synthetic key would cause problems AT ALL, much less problems of THIS magnitude. Seemed worth testing.
So I wrote a script that generates 10 million vacations for 500,000 employees over a 10 year period. I'm not sure what sort of chart you're using, but I threw together a big line chart that uses most of the data - for each date, how many employees are on vacation? I started with a fresh copy of QlikView to make sure nothing was cached, then opened up the minimized chart, then checked the CalcTime. I'm told it would be more accurate to write some sort of macro using activedocument.getapplication.getcpumilliseconds, but I figured that might be overkill. Going through the various solutions in order, here's the raw data:
load time file KB RAM KB CalcTime
intervalmatch and join out of memory on a 64-bit machine
intervalmatch synthetic key 5:19 200,477 387,080 116,312 ms
your solution fixed 15:26 29,253 106,924 22,938 ms
my solution 14:01 49,507 77,248 46,000 ms
There is a LOT of information to be gained here. We can see that the intervalmatch solutions ARE terrible, both with a join and with a synthetic key. We can see that your solution DOES solve the problem, both from a memory and chart calculation time perspective. My solution appears to do the same, with significantly less RAM but also with double the CalcTime for the chart. Might be specific to that chart, or it might not. Hard to know.
However, looking over my logs as well, I can see that the Period table in "my solution" compressed 10 million vacations down into about 50,000 distinct vacation date ranges. I just used random vacation dates, so in a real world application, you'd probably see even greater compression due to correlation between the vacation dates of different employees (clustering around holidays and weekends, for instance).
Now, your first two solutions joined your calendar either explicitly (left join) or implicitly (synthetic key) to the full table of 10 million vacations instead of to a MUCH smaller table of 50,000 distinct vacation date ranges. Then you proposed a third solution that explicitly created and then joined to a date range table instead of to the vacation table. This produced phenomenally less data, and therefore vastly increased performance and reduced memory requirements. So even from ONLY that perspective, it is no wonder that we're seeing such a big difference.
Still, we haven't nailed down which of these plausible factors is really causing the trouble - intervalmatch, synthetic keys, or joining to the "wrong" table. Fortunately, it's easy to check. "My solution" did away with the intervalmatch and the synthetic key just like yours, but used a STRUCTURE of the data that we could duplicate with an intervalmatch and a synthetic key. Like so:
Period:
LOAD DISTINCT
StartDate
,EndDate
RESIDENT Vacation
;
LEFT JOIN (Period)
INTERVALMATCH (Date)
LOAD
StartDate
,EndDate
RESIDENT Period
;
That leads us to this comparison:
load time file KB RAM KB CalcTime
my solution 14:01 49,507 77,248 46,000 ms
new intervalmatch synthetic key 3:27 49,401 77,160 44,797 ms
As we can see from this, staying away from intervalmatch and synthetic keys did nothing for us. It appears that the synthetic key mimics creating our own table tying together a date range ID with the start and end dates, just like I would expect. They both use similar amounts of memory, and take similar amounts of time to process, just like I would expect. The main difference is that letting QlikView do all the work via intervalmatch and a synthetic key allows us to load the data about four times faster. So I'd just let QlikView do the work.
Still, the chart in your solution loads twice as fast, so it seems that your data structure is probably better for this specific chart. It's possible that your data structure is better for your own real world data and charts as well. QlikView is often happier with certain kinds of data denormalizations. But regardless, the problem you were having wasn't with the intervalmatch or the synthetic key. The problem you were having was with joining data in inefficient ways.
TL;DR - After much testing and thought, I still think that the best general solution is a more normalized data structure with a synthetic key created by intervalmatch. The DateRangeId solution with duplicated StartDates and EndDates on the Vacation table may be better for specific charts if RAM and reload times are not issues.
