Adding forecasting to existing sales DW

I am adding forecasting to an existing sales DW:

Existing Dimensions:
Dim Date (typical date dimension attributes)
Dim Customer (customer #, forecasting group, sales territory )
Dim Product (SKU, description, various product attributes)

Existing Fact Table:
Fact Product Sales (grain is daily sales by product by customer)

New Fact Table:
Fact Sales Forecast (grain is monthly by product by customer forecasting group)

I know that the established best practice for handling budget/forecast/quota data that occurs on a monthly basis is to pick a day (typically first or last day of the month) to use as the date key representing that month in the fact table, allowing budget numbers to correctly aggregate at the month level (or quarter, half, year) and thus supporting drill-across queries between fact tables in order to report on actual vs. budget.

But I am debating the best way to handle the difference in grain with the customer dimension. Sales are loaded by individual customer, but forecasts are by forecasting group (a collection of customers rolls up into a forecasting group, a forecasting group rolls up into a sales territory.)

Should I…

(a.) pick a customer out of each forecasting group to represent the forecasting group for forecasting purposes? This is the closest parallel to the established best-practice with the date dimension, but doesn’t feel right. For one thing, I can see users doing a query and mistakenly thinking that the forecast numbers apply to that one customer instead of the group. I suppose the same thing could happen on the date side, but they are already used to associating budget numbers with the first/last days of the month from existing accounting practices.

(b.) create a new record in the customer dimension to represent each forecasting group? Forecast facts would always relate to these new records; sales facts would always relate to individual customer records. Queries at the forecasting group level (or above, such as sales territory) would always roll up correctly.

(c.) create a new Dim Customer Forecasting Group dimension. I think this would be a mini-dimension off of Dim Customer, with one record for each forecasting group. Sales facts would point to both Dim Customer and Dim Customer Forecasting Group; forecasting facts would point only to Dim Customer Forecasting Group. Drill-across would be based on Dim Customer Forecasting Group attributes.

Recommend approach?

c

Why (c.) over (b.)?

C is simpler and it is clearer what the projections represent.

However, I would probably not add a FK from sales to the forecasting group dimension. I wouild suspect that customer assignment to a forecast group may change over time, which may or may not lead to issues when doing historical reports. Instead, I would rely on the value of the forecast group attribute in the customer dimension to tie sales to forecasts based on the group attribute value in the forecasting group dimension.

Drilling across using the forecasting group atttribute makes sense... and further simplifies/clarifies the design.

You hit the nail on the head that customer assignments to forecasting groups will change over time--typically on an anual basis as part of sales territory realignments. This "looseley coupled" approach should facilitate dealing with these changes.

As always, thank you ngalemmo for your expert advice!