The best design for EDW to cover common and un-common processes

[Niaz]

Thanks for any help, suggestion, advise, etc. in advance...

We have a big initiatives to integrate 20 JDEdward libraries (companies) into one through Oracle Enterprise One application. The OLTP will stay on Oracle Database and data will be migrated to new structure. There will be common processed for module such as HR, financial, Maintenance and Purchasing. However there will be many un-common processes such as Sales, Production, Inventory, etc. For Reporting, we will have Enterprise Data Warehouse and we will use Cognos tool and ETL tool and Reporting tool. The Data warehouse will be on SQL Server 2008.

Here are the list of my questions.
1) What's the best design for EDW? is it one big data warehouse for all 20 companies or having separate 20 Data warehouse, one for each company? I'm thinking about how we can do the maintenance, backup, recovery, separating one from others, moving, etc.

2) I am thinking to have set of tables for Common facts / dimension and separate fact table for uncommon. Let's say in Sales , Sales Order Detail will be common, so we have a table called Sales_order that could be one for all the companies ( separating by Company_SID) or 20 Sales_Orders tables in 20 DW with the same structure. Then Sales Forecast could be exist in only one or two companies and could be different from one to another.

3) What's the best way to keep data dictionary information? in RDBMS in table format? using Excel?

If there is anything that based on your experience I need to address, please share with me.

[ngalemmo]

What does the business want?

If these are all subsidiaries and there is a need to peform analysis across multiple companies, then a common EDW is the proper approach. There will most likely be some integration challenges, but probably not insurmountable.