Anlyze actual vs forecast data different versions, best way?
The case evolves around facilities and their consumption. In this case there are around 13000 facilities which equals to 156 000 rows/year which during 5 years results in 780 000 (5*156 000). So each facility should be forecasted every month.
Facilities:1300 rows (per facility) - information about the facility
Forecast: 780 000 rows (per month) - information about the production of the faciltiy
Historical (Actual): 80 000 (normalized table with both facility information and its measures)
Ive tried to join the facility table and the forecasted values and then concatenate it with the historical table but it ended up eating all my memory and takes very long time for this one simple left join (32 GB memory installed, QV version 11.2 SR12).
I have tried to approach this in a different way also. I created a link table between these different tables but it didnt give me the result I wanted.
The forecast is generated 5 years in advance and historical values (actual) is a five years back. The business want to analyze each version of the forecast to the previous one and also compare it with historical values (actual).
My questions is how to best approach this? With regards to the datamodell?