Multiple Datamart Architecture

Is it good to have multiple datamarts in the same physical database and separate each datamart with different schema name ?

Two questions occur to me:

1. Why wouldn't you have all your datamarts in the same DB and why would you want them in separate schemas?
2. Given that datamarts are logical constructs, not physical ones, where would you hold conformed dimensions that are used across multiple datamarts if not in the same, single, DB/schema that should hold the whole of your Enterprise Datawarehouse?

Have I missed the point of your question?

Thanks for the reply. Just trying to understand if that will work. I thought data marts are physical ones. Given the data marts are logical, how you will differentiate one with another, just in DW model diagram.
I have seen forums where they will store conformed dimensions in multiple places with different rows of data(data related to the particular datamart), so i thought it is a physical one. So i believe then we got to have only one conformed dimensions with all data int it?

What is your definition of 'data mart'?

Since this is the Kimball Forum group, I'll answer based on Ralph's definition (there are probably 100's of others, by the way). A mart is a single star schema, a fact table and its related dimensions. Since a dimensional data warehouse is defined as a collection of marts (star schema), then one would assume they are all on the same server.

There are certainly architectures that distribute subsets across local servers, but the localized stars should be a resonably cohesive collection to avoid the need to join across separate servers, which can be very time consuming and network intensive.

I am using 'server' rather than 'database' because depending on which DBMS you are taking about, 'database' can be a simple logical subdivision of the system. On such systems it is common to break the tables into different databases for security and maintenance reasons. Joining across databases on such systems is no different than joining tables within the same database provided the reside on the same server.

And, as life isn't so simple in this business, 'server' can be a collection of machines supporting a single DBMS environment (such as server clusters).

Thanks, it is more clear. So in case if i am splitting the datamart into separate database within the same server, will i got to have conformed dimensions in all its associated datamarts( separate database) ?
And another question, say a star schema has three fact tables sales1, sales2, and sales 3. All related to sales, will all 3 facts and associated dimensions will be considered has one data mart?

conformed dimensions in all its associated datamarts( separate database) in the sense, do i need to have one copy or multiple copy of the conformed dimension with its own associated data related to its datamart ?

Within the constraint that a datamart must be a cohesive whole, it can be whatever you want it to be. Whether you consider the 3 fact tables (and their associated Dims) to be one datamart or each is its own datamart is entirely up to you and should make no difference to anyone else - as it is entirely a logical construct. For example, you could have a Sales Datamart and a Marketing Datamart that both contain 90% of the same tables - you would just describe (e.g. use a picture of) the Sales one when talking to Sales people and the Marketing one when talking to Marketing people - so that they are not distracted by objects/tables they are not interested in.

You would only have one copy of any Dimension (or any other table for that matter) - there are possibly exceptions to this rule but they would only be relevant in highly specialised circumstances and would be outside the scope of this discussion.

Can I go back to my original question and ask why you raised this in the first place? Given that the simplest solution would be to put your entire warehouse on the same server/database/schema, I'm not clear why you are considering doing anything else - as any other design is going to be more complicated to build and support. So I'm assuming that you have a reason for considering a more complicated design?

My main vision to have a separate database for each datamart, if we have more fact tables for a datamart or if we create more fact tables for a datamart in future, having separate database will be easily scalable. And i can join them inside the dsv for the cubes or inside a view for the reports on top of datamarts using 3 part naming convention. I feel having seperate database will be easily scalable.
And all datamart will be in the same sever and it can be same schema(dbo).

I with Nick on this one. If you are hand writing SQL, its really annoying if you have to reference a table in another database. If you are using a BI tool it can be a lot of unnecessary extra work to build the metadata.

I don't get your 'scalable' argument. Everything is running on the same server(s) with the same set of disks. It has no effect on scalability.

That's true it will be easier to put under the same database, i guess i am wrong. Thanks for the help.
And one more question regarding staging area of DW, is it ok to keep the staging area in the same server in a different database or it will be good to put it in a different server ?

Same server, different database.

Same server because it is easier to access, particularly if you are using SQL. If you are using an ETL tool, some offer push-down optimization options that you can take advantage of if it was on the same server.

Different database because your backup requirements would be different (staging tables are usually transient) and you don't want your DW database messed up with a bunch of temporary or intermediate tables. Overall, its just easier to manage that way.

Understood. Responses are pretty quick. Thanks for the help guys.