Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dssrun
Starting Member
18 Posts |
 Posted - 2013-04-17 : 07:26:09
|
| Does anyone have experience with creating a database warehouse that merges similar data across different servers? I wanted to know the best way, in a reporting environment where advanced queries are written, to store multiply identifiers. For example, Facility A is in server 1 and server 2. Facility A has a primary key on each server that i want to preserve as well as two system numbers to identify the systems it was on. Is the best method to store it all in one "Facility" table while creating my own primary key and have the columns read FacilityId, Server1Id, Server2Id, System1Id, System2Id, FaciliyName...Or is it better to store these outside identifiers in another table called "FacilityInfo". Therefore, the original "Facility" table would only have FacilityId, FacilityName... and "FacilityInfo" would have FacilityInfoId, FacilityId, IdentifierId, IdentifierKey (IdentifierId would point to another table that references the source of the IdentifierKey. That table would have "Server1", "Server2", "System1" etc..) Thank you in advance. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-18 : 03:03:21
|
| The usual method is to keep them in same Facility table after adding another unique valued field( may be an identity) as surrogate key. the two server records will exist as two separate records and table will have records likeFacilityID ServerID SystemID FacilityNameidvalue serverAidvalue serverAsystemidvalue facility nameidvalue serverBidvalue serverBsystemidvalue facility name------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
dssrun
Starting Member
18 Posts |
Posted - 2013-04-18 : 21:21:40
|
| Thank you, but what if I have many columns for the facility itself (eg address, phone)? Won't I need a facility table to holds all of that to prevent duplicating data? In your example, if facility name is the last column how to I point to the facility table, shouldn't it be facilityid? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-19 : 02:29:11
|
| if you maintain a separate facility table then its just a matter of putting all facility attributes in it and it will a id field (usually an identity based one) which will be included in your above table with other id etc values from both serversSo Facility will only be maintained one time and it will referred in all the records in the above table by means of the FacilityID------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|
|
Database Warehouse for Reporting: Merging Data