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.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Database Warehouse for Reporting: Merging Data

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 like

FacilityID ServerID SystemID FacilityName
idvalue serverAidvalue serverAsystemidvalue facility name
idvalue serverBidvalue serverBsystemidvalue facility name

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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?
Go to Top of Page

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 servers
So 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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -