aggregate fact table

[Anonymous]

I have a very huge big fact table , about 1 billions lines .

How could I aggragate the previous years and not aggregate this year ?

Vente:

LOAD

     PrixUnitaireArticle,

     PrixArticleHT,

     PrixRevientTTC,

     MargeHT,

     MargeTTC,

     ValeurRevientHT,

     ValeurRevientTTC,

     ValeurMargeHT,

     ValeurMargeTTC,

     PrixVenteHT,

     ID,

     N°tickets,

     EAN,

     [Code Caisse],

     [Code Site],

     [Prix de vente TTC],

     Quantité,

     [Date Time],

     DateOfHire,

     Promo,

     [Code Article],

     [Code Structure],

     Alimentaire,

     [Prix de Revient],

     TVA,

     DivKey,

     Date ,

     DateTest ,

     nbrClient,

     KeyVenteSite,

     code4 ,

     Comp,

     datecol,

     QuantiteCasse,

     QuantiteAjust,

     oc1,

     [PrixPermanentTTC],

     [PrixPermanentHT],

     Valeur

FROM

E:\QVD\QVD\Vente.qvd

(qvd);

[maleksafa]

depending on the level of details that you want from previous year.

you can do the very basic step, by first loading the data aggregated using sum and group by, with a 'Where' condition to take the previous year (using today() function). and then load the rest of the data in normal mode.

on a side note, if you are loading a big fact table, consider removing unused fields as it will save a lot of space and enhance the performance.

[marcus_sommer]

I suggest you makes two loadings - one is the load of this table with a where-clause on the current year and the second would be an aggregation load over all needed previous years but with only a few fields like an ID and a Year and sum(value) the aggregation loads with so many fields and this amount of data will be very expensive.

- Marcus