We currently have multiple systems that we report on. They all contain information on the same topic - but they are systems built externally by different companies.
However one major goal is to be able to report on these systems together.
Here is my general plan within SSIS
1) Create denormalised structure for each system.
2) Delete stuff for that system in unversal tables, and copy over the tables.
Now there are a couple of things that have sprung to mind.
Firstly i am doing step one to minimise the load on the central tables. So it should hopefully avoid much locking if multiple SSIS packages (one for each system) are running in parrallel. Is this the best way to handle this?
Secondly - keys..... some are ints - others are varchar. Hows the best way to split the primary keys? I am planning to have a source column to identify the system. So am i better off having a composite key for every table - or the other option i can think of is to append a letter to signify the system to each and every PK and FK.
I would appreciate any thoughts.