Partitioning - best approach?

[c6lapsteel]

Hi - wondering if anyone has determined best way of handling partitioning in DataManger (DecisionStream).

I am pulling in Invoice data from 30 sites and would like to achieve the following:

• Daily Fact Table - all invoices at daily level but only for the last 3 years
• Monthly Aggregate table (updated daily)
• Archive table - off line back up of daily records > 3 years old

What is the best approach? Update monthly and daily tables in same fact delivery? Separate deliveries? What is your experience?

Thanks
c6

[MFGF]

Hi,

If you are going to completely replace the rows in the daily fact table each day (typically using Truncate refresh type), then you should probably aggregate and deliver the monthly aggregates as part of the same build - use level filters to partition off the details and aggregates to different table deliveries (in that way you can guarantee that the aggregates and detail rows tally.)

If you're not replacing all rows in the detail table every day, but just appending the new ones and deleting the obsolete ones, then you are probably best doing the add/archive in one build, then following this with a separate build to re-calculate the aggregates and populate the aggregate table.

Best regards,

MF.