DW SQL Server question

View previous topic View next topic Go down

DW SQL Server question

Post  TheNJDevil on Fri Mar 23, 2012 11:34 am

I am sure I missed something along the way. I'm pretty new at this and was wondering what the most correct way was.

The DW should be build business process by business process. In the SQL Server 2008 R2 server environment, would you keep business processes in separate DB (on same server), and put conformed dimensions in their own DB? Or, do you group business processes by functional departments into a DB, and have separate conformed dimensions DB? Or, do you keep all tables from every business process in the same DB?

My thinking is the first way described, but am up to hearing why something else would be better.


TheNJDevil

Posts : 68
Join date : 2011-03-01

View user profile

Back to top Go down

Re: DW SQL Server question

Post  ngalemmo on Fri Mar 23, 2012 12:53 pm

It really doesn't matter from a usage point of view. It is more an operational issue as far as backup and recovery is concerned. If you are dealing with very large amounts of data (multiple terabytes) it makes sense to break things up (facts, which are the largest tables, would infer a process oriented division, with dimensions together in one db). But, if it is a matter of a few hundred GB, its a lot simpler just to keep everything together.

From a security standpoint, it is common to create DBs that contain nothing but synonyms and views to control access for various user groups. This makes the physical arrangement of tables transparent.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: DW SQL Server question

Post  Jeff Smith on Mon Apr 16, 2012 10:40 am

We use SQL Server.

I've found that seperate databases on the same server is not the way to go. I would go with different schemas before I would go with different databases. The reason is that you can define foreign_key/primary_key relationships across schema's but not across databases. This can impact performance.

I think different schemas can get you the same benefits as different databases without the drwabacks.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: DW SQL Server question

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum