Support T-1 roll-ups on package download statistics to reduce storage requirements

[dtivel]

Problem
Currently, we store 6 weeks' worth of raw data in the statistics database. The smallest unit of time that we report is a week. Every time we generate a report we process most (~98%) of the raw data again. Storing this much raw data is costly, and reprocessing the raw data repeatedly is unnecessary.

Solution
Roll up the previous days' worth of data with dimensions. Instead of having 42 days' worth of raw data, we should have 41 days of daily rollups and 1 day's worth of raw data. Capacity and DTU usage should both drop significantly.

Notes

1. Consider perform this daily rollup on the top n most active packages, as there may be little or no benefit to less active packages.
2. The Fact_Download_NCI_LogFileName index on the Fact_Download table can likely be dropped, thereby reducing storage requirements further.
3. PowerBI dashboard queries may need updating.

[shishirx34]

@xavierdecoster - This bug can be used to track your work.

[xavierdecoster]

For this t1-rollup to be efficient and cost-effective, we need to reduce the number of sql indices on the facts tables, as they are a source of bad performance during roll-ups.

Will create separate issues to track suggestions, as we still retain lots of data we don't actually need in any functionality today.

[xavierdecoster]

Status update

Database size at start of investigation: 621.7 GB

Size reductions by:

• (Measured) index rebuilds: 199.09 GB (down to 422.61 GB) - completed on 2017-03-06
• (Estimated) performing all recommended improvements as listed below: 108.13 GB (down to 314.48 GB)

Total estimated database size after performing all recommendations = 314.48 GB (or a 25.59 % reduction vs. the re-indexed original database!)

Separate improvement suggestions:

• Recompute database statistics
• Enable statistics recompute on existing indices
• [Statistics] Reduce DB size by removing unused data - edge server IP [Statistics] Reduce DB size by removing unused data - edge server IP #3622
• [Statistics] Reduce DB size by removing unused data - project type [Statistics] Reduce DB size by removing unused data - project type #3623
• [Statistics] Reduce DB size by removing unused data - original UserAgent-strings [Statistics] Reduce DB size by removing unused data - original UserAgent-strings #3624
• [Statistics] Reduce DB size by removing unused data - Operation dimension [Statistics] Reduce DB size by removing unused data - Operation dimension #3625
• [Statistics] Reduce DB size by removing unused data - Platform dimension [Statistics] Reduce DB size by removing unused data - Platform dimension  #3626

[skofman1]

We decided to try a different solution. Will reopen as needed.