SQL Server Performance Forum – Threads Archive

Designing a datamart

Hi,
I am new to SQL Server and I would like to create a datamart (star schema). But in this scenario all the dimension tables (employee, organization etc) are in one database and the fact tables are in different application specific databases. Given this I cannot create a fact table with proper referential integrity constraints, since SQL Server does not allow cross database foreign key constraints. My question is this a normal desgin practice in the SQL Server world, where the dimensions are in one database and the fact table in a different database. Or are we doing something really crazy. Please advice what is the standard design practice if the same dimension tables are used in mulitple databases. Thank you,
Senthil

As someone who has spent a great deal of time in Quality Assurance, I would say that the number one concern someone in Quality Assurance would have would be with the Quality of the Data. And whenever there are two different "owners" of the data, that would raise a red flag. Because when the data of the Warehouse was in question, the answer would alwasy be "its the other guys fault." Knowing you are just get started and are already considering the quality of the data that you will be reporting, makes the Quality Assurance piece of my heart sing. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> As someone who has sligthly touched Data Warehousing, I would say that the issue wouldn’t be so much with the quality of the data, as the likelihood that what you are saying is that the fact tables are in the OLTP production databases, and that you will be hammering them to pull back and construct reports. You may mean that the Fact tables are already in some other groups Warehouse, and this isn’t an issue. The OLTP side of my heart is crossing its fingers that that is the case. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> (The fact that you mentioned they are in Application specific databases just suggested to me that they are in an OLTP environment.) If you would answer Yes to either of the above, I would suggest you simply replicate/copy/whatever the fact tables out of the OLTP databases/servers and move them to your Warehouse database instead. What you will find out as you start "cleansing" your data and looking for violations, oddities, columns that have NULL values instead of a "real" value, is that the Quality of the data in the OLTP databases is probably not as good as you’ll need it to be. If you control the data in "your own database" you can create the scripts to "cleanse" it as you copy/move it to your versions of the tables, whereas, you may not have control over theirs. If they are in their OLTP environment, you will probably need to manipulate the data because it is likely more of a snowflake design than a Star design because they are likely normalized, which is why FK integrity would be such an issue regarding those tables.

]]>