xsevensinzx (11/8/2016)
Interesting debate came up on data warehousing. Is it good to have multiple data warehouses or just one?...Personally, I see no problem with multiple data warehouses if there is justification and they are both not fighting for resources and causing contention on the target data streams. If they are supporting totally different data requirements, it seems logical to have the split and allow them to grow separately while still working together. Ideally, it would be great to stick to one data warehouse where if you needed separate data specific needs, it would result in a new data mart to be developed for those needs. Yet, that still requires the data warehouse to support those needs at the source.
Maybe we need some clarification on what you mean by multiple data warehouses? In general, a data warehouse can consist of multiple fact tables, each of which relates to multiple dimensions. A fact table is usually modeled based on some business process.
Offhand it sounds like you just need a different fact table within the same data warehouse. You probably would not want to change the grain of an existing fact table as that would affect all users who currently do reporting and analysis on it. The new fact table will likely be able to reuse some of your existing dimensions though.
There is lots of good information about designing data warehouses available from the Kimball group: