If I want to build an architected data warehouse, otherwise known as a star
constellation of multiple facts and conformed dimensions, I'm trying to figu
re out how to separate the physical tables by database. Think about modelin
g the constellation in ERwi
n. You want to have mutiple fact tables with foreign key relationships to s
hared conformed dimensions. The question is - AS A GENERAL RULE, when does
a single database have too many tables? Does each star in the constellation
(for each business process
) merit its own database in the SQL Server instance? If so, how do you set
foreign key relationships across database boundaries, since not having the r
elationships will cause performance to suffer? I have found no way to set f
oreign key relationships ac
ross database boundaries in ERwin.I'm not sure why you would want to have multiple databases. You can already
use filegroups to isolate certain tables or non-clustered indexes across var
ious spindles. Regardless, you cannot have FK constraints that span database
s.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment