The best design for EDW to cover common and un-common processes
2 posters
Page 1 of 1
The best design for EDW to cover common and un-common processes
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.
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.
Niaz- Posts : 1
Join date : 2009-11-16
Re: The best design for EDW to cover common and un-common processes
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.
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.
Similar topics
» Common Key
» dimensional model best practice
» Common attributes across multiple facts
» indepndent hierarchies with common shared leafs
» Multiple different grain fact tables with lot of common dimensions.
» dimensional model best practice
» Common attributes across multiple facts
» indepndent hierarchies with common shared leafs
» Multiple different grain fact tables with lot of common dimensions.
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum