SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Database Warehouse for Reporting: Merging Data
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dssrun
Starting Member

18 Posts

Posted - 04/17/2013 :  07:26:09  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 04/18/2013 :  03:03:21  Show Profile  Reply with Quote
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 - 04/18/2013 :  21:21:40  Show Profile  Reply with Quote
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?

Edited by - dssrun on 04/18/2013 22:00:15
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/19/2013 :  02:29:11  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000