0 Replies Latest reply: Nov 17, 2016 12:11 PM by Saffe Saff RSS

    Anlyze actual vs forecast data different versions, best way?

    Saffe Saff

      Hi everyone,

       

      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)

       

      Facility

      Forecast

      Historical

      Installation_ID

      Version

      Start_Date,

      End_Date,

      Installation_Status,

      Customer_ID

      Installation_ID

      Version

      Month Forecasted

      Consumption

      Installation_ID

      Installations_status

      Customer_ID

      Version

      Month Forecasted

      Consumption

       

      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?