I have a very basic question. My understanding is Data mart is essentially a database for a business segment per say and data warehouse is a warehouse of multiple data marts and other sources of data combined in a way that allows ease of analysis and reporting.

My question is: In case of a single database/data mart. Is it a good idea to create a data warehouse with a different schema (e.g. star or snowflake), just to enhance analysis efficiency?
If not, should the current database be modeled as a data warehouse or a data mart?

  • by schema do you mean 'table design' or actual sql server schema (security object?) what do you mean by 'enhance analysis efficiency' Do you mean from a user standpoint or db performance standpoint? If you are referring to schemas in the security object sense, yes it's a good idea if every object 100% belongs in one schema only. The thing is if you are sharing dimensions across data marts, then they don't belong in only one schema – Nick.McDermaid May 3 '17 at 5:59

This is a pretty good question. This topic, many times, gets over-thought. It really all comes down to business needs.

If the need to compartmentalize data in to different subject areas (Finance, Sales, Manufacturing, Inventory, etc...) If this is far out of scope for the business cases, then you should probably stick with your standard database schema.

From my view point, a single data mart is not terrible useful except for in fringe cases where a single department has needs for a data warehouse like reporting infrastructure... Then a data mart might be useful.

Not the answer you're looking for? Browse other questions tagged or ask your own question.