I have a bit of a challenging dilemma I'd like to throw out there.
I have a report that is created weekly that generates a variety of data, one element of which I have particular interest.
A) Each report has a specific date associated with it.
B) Each report shows forecast data, monthly, for 24 months.
Over a 6-month timeframe (for example), I'll have had 24 reports - each showing actual 'sales' and a 24-month forecast of sales based on a trending algorithm.
What I'm looking for is a graph that shows 3 trend lines:
1) Actual sales (historical only)
2) Current forecast (future only, obviously)
3) forecast from date X - a line that showed the forecast as of date x (user-selected)
I have built already a forecast calendar. I'm assuming I'd need to build one as well for ReportDate.
(note: this data has multiple dates, I have a calendar for each of them as well as a datebridge and canonical calendar)
From a data modeling perspective, I would suspect that each report that comes in, there would need to be a crosstable that has the following fields for each 'Product':
Today, from 1 report, that crosstable has 1M rows. Obviously as time goes on, we're looking at adding 1M rows to that each week. Assuming we maintain 2 years of data, that's >100M rows. Do we think that would be a problem?
I'm wondering what the expressions might look like within that line graph or what else I might be missing?
