Multiple fact tables

I have forecast and actuals information about budget. They would all share most of the dimensions and would have the same fact attributes. What are the benefits/ downsides of having a fact table for forecast, and another fact table for actuals vs. having one fact table and a dimension attribute that differentiates whether the fact record is a forecast or actual?

It really depends on wether they share the same grains and dimensionality in both actual and budget facts. Normally the budget has been made on aggregate level (eg. month) and the actuals may take place at more granular level, say daily. So the consolidated fact might only be applicable at aggregate level for the actuals. You could also use views to cater for the same objective if they live in separate tables.

As Hang mentioned its all about the grain.

If this is GL data (account balances, not journals), then you should be able to use a single fact with a ledger type dimension (actual, budget(s), forecast(s)). If you are maintaining journal data (actuals only) this would be in its own fact. If you have both, the journal fact should include the ledger type dimension so you can easily reconcile the journal facts with the balances.