Designing a large database with multiple sources
- by CatchingMonkey
I have been tasked with redesigning, or at worst optimising the structure of a database for a data warehouse.
Currently, the database has 4 other source databases (which is due to expand to X number of others), all of which have their own data structures, naming conventions etc. At the moment an overnight SSIS package pulls the data from the various source and then for each source coverts the data into a standardised, usable format. These tables are then appended to each other creating a 60m row, 40 column beast!.
This table is then used in a variety of ways from an OLAP cube to a web front end.
The structure has been in place for a very long time, and the work I have been able to prove the advantages of normalisation, and this is the way I would like to go. The problem for me is that the overnight process takes so long I don't then want to spend additional time normalising the last table into something usable.
Can anyone offer any insight or ideas into the best way to restructure or optimise the database efficiently?
Edit:
All the databases are MS SQL Server 2008 R2
Thanks in advance
CM
