Hi all,
I am trying to establish the best method of storing and actively reporting on quite a large volume of data with the following attributes/requirements:
- I have 30,000 product items that i need to track the status of each one for every day of the year.
- On any day i need to be able to report on the historical statuses of each product (YTD) for each day until today, and the future statuses of the product for each of the remaining days of the year (YTG) based on known or forecasted performance.
- Each product can have between 1 and 4 (inclusive) different status attributes for each day.
- I need to be able to state the position of the report for every historical week for which the report has refreshed to date. (i.e. i need to be able to look back at the snapshot of the report as of any specific week point in the past in order to compare that reported position to the current day, or any other report date)
For context, the statuses relate simply to sales/rentals of the given products both in the past, and contracted sales/rentals going forward. As we move through the year, the sales and rentals of the products either solidify, or don't.
Aggregating the data by product category isn't really an option, as i need to be able to see the product specific changes from reporting period to reporting period.
As a result of these requirements, i can potentially have 43.8M records for each run of the data (30,000 x 365 x 4). So, around 2.3B records (max) for the 52 weeks of the year.
I am also not (yet) on premium, so incremental loads are out.
How would you helpful lot go about dealing with this amount of data and maintaining some sort of performance in PBI, on gateway refresh, as well as managing the storage of it...?
