SQL Server Performance

Finding relationships w/in DB

Discussion in 'SQL Server 2008 General DBA Questions' started by dmatt, Mar 9, 2011.

  1. dmatt New Member

    I have a general question. I'm using ETL s/w to move data between source db and a datawarehouse; you select the tables+fields you want from the source and it builds records for you in the datawarehouse (which for my purpose is a MySQL db). Interestingly the ETL system doesn't need any information about the source db (which is in SQL Server 2005) and in this particular db there are no pre-existing relationships defined in this db. Essentially it builds an INSERT ... SELECT query on its own.
    Is there some SQL Server trick -- or general relational db trick -- that allows the ETL s/w to build this query without knowing what the relationships are in the source (i.e.,SQL Server) db?
    As I said there are no existing relationships in the SQL Server db. Because the naming conventions used were rather odd, FK names don't match PKs.
  2. FrankKalis Moderator

    Welcome to the forum!
    I would be very suprised to find any ETL system doing this kind of magic on its own without any additional "external" knowledge. And do it correctly, I maybe should add. I would expect it to go to the metadata of the source and inspect any existing PK-FK constraints and/or suggest them based on identical column names and data types. Based on this I would expect the ETL system to build its own mapping from source to target and make this mapping available to the user for editing. From there then it is easy to generate INSERT...SELECT statements, and since there are no FK constraints defined, the order in which these statements are executed, doesn't really matter.
    However, I am not sure I would trust answers from a database for which no referential integrity constrains are defined.
  3. dmatt New Member

    Thanks, Frank.
    I didn't really think there was a trick but don't know SQL Server particularly well so who knows. It never occurred to me that the ETL is wrong, either -- stupid me.
    I'm new at current company so not sure what's up with the lack of constraints and defined relationships -- can't wait to find out.
    Thanks again for your comments...

  4. mmarovic Active Member

    I am not sure I understood the explanation. From the quote I am not sure which of next two sentences is correct:1. There are no pk and fk constraints defined in source db. It is not even possible to guess it because of odd naming convention.2. There are pk and fk constraints in source db, however you can not match it using their odd naming convention.If it is case 2, then it is possible to figure it out from information_schema views. However, Frank suggestion about mapping between source and destination stays correct.
  5. satya Moderator

    In the generic OLAP world using surrogate keys can solve the problem (to some extent) because there will be no need to enforce DRI at the database level. I think I read about this in one of the Kimball's DW books, let me see if I can find it.

Share This Page