Forecast Tables Partitioning Strategy

Feature request placeholder as we investigate a partitioning strategy.  Please document overall approach here.

--------------------------------------

Background

We already have partitioning on a number of our "Aggregate" tables which we are regularly purging:

• Weekly Partition, Retain 12 weeks:  
  • Forecasting.MetricDetailIntervalAggregate
  • Forecasting.SalesItemIntervalAggregate
  • Forecasting.InventoryItemIntervalAggregate
• Weekly Partition, Retain 65 weeks:
  • Forecasting.MetricDetailDailyAggregate
  • Forecasting.SalesItemDailyAggregate
  • Forecasting.InventoryItemDailyAggregate
• Monthly Partition, Retain 13 months:
  • Reporting.SalesItemDailyAggregate

There are also these 3 tables which we are constantly purging throughout the day at 1000 rows at a time while retaining 14 days of data (via the MobileForecastingMetricDetailPurgeDelay setting):

• Forecasting.MetricDetail
• Forecasting.SalesItemMetricDetail
• Forecasting.InventoryItemMetricDetail

Proposal

We will need to create a new Partition Scheme for a Daily partition and then apply that to the Forecasting.MetricDetail, Forecasting.SalesItemMetricDetail, and Forecasting.InventoryItemMetricDetail tables. We can then update the purge process to work like the Aggregate tables.

Concerns

1. This new process will leave a little more data in the *MetricDetail tables than the current purge process. The data will remain in the tables until the end of the day rather than slowly deleted throughout the day. This should not be a big concern, but might be worth discussing.
2. This new process would utilize the "IntervalStart" column which contains the Forecast Date along with the Interval Time. The old purge process is actually joining out to the Forecast table and using the "BusinessDay". We may need to do a little research to ensure that the IntervalStart and BusinessDay data is consistently matching.

Transactional Tables

I believe we are looking into Columnstore indexes for all the Transactional tables already, so I am not sure if it is also worth partitioning these tables. We might be able to use this new Daily Partition Scheme on these tables as well, but it is unclear how much that would actually buy us in conjunction with Columnstore indexes.