locked
truncate and load of fact vs better solution RRS feed

  • Question

  • Hi,

    I have a requirement where there are 3 tables with fact data.These 3 tables are each sourcing a view that produces facts and these views are queried in ssis package.The ssis truncates the old data and reloads 3 tables entirely daily by agent jobs.The reports that users build query this table time to time and report dataset refreshes everyday once after the sql agent job load completes.I know the problems with this approach-unlike in cube where old data will still be available when processing happens after ssis packages finish reloading data,my method of truncating sql data and reloading everyday calls for a downtime (meaning report dataset doesnt refresh until the table has refreshed) .

    Is there a way where we do a full load of sql table but still make old data available without downtime? Considering that the data is fact data and not dimension.Is there a way to load fact data incrementally when there is no last update date for records ?

    Sunday, June 14, 2020 3:14 PM