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 figure out how to separate the physical tables by database. Think about modeling the constellation in ERwi
n. You want to have mutiple fact tables with foreign key relationships to shared 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 relationships will cause performance to suffer? I have found no way to set foreign 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 various spindles. Regardless, you cannot have FK constraints that span databases.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment