I've been doing a lot of reading on Data Warehouse architecture as we are thinking about reforming our current setup feeding our BI. The 'maturity' of the Data Warehouse is quite low, despite being rapidly deployed.

I'm still confused over a "Data Warehouse" vs a "Data Mart."

What we currently do:

Go to source systems. Use an ETL process to insert "cleaned + transformed" data into data tables. We named these tables thinks like surveymonkey.FactSurvey. IE, the schemas are named after the data sources to aid in comprehensibility or 'what is this, where did it come from'. Tables are labeled as fact or dimension tables.

Currently we have 13-14 of these 'schema-sources' each with say 8-9 tables in one giant database.

How do we integrate data? Conformed dimensions, of course. Either dimensions are "cleaned/ conformed" via logic in the ETL scripts, or they are "cleaned" post-load in views atop the tables.

In my view, this giant database is a "data warehouse" - it feeds our business intelligence OLAP cubes.

What exactly is a data mart, and what is the purpose? I thought we were already following the Kimball approach -- fact and dimension tables. ETL from source to database. A series of 'views' denormalize the data to feed into the cubes. But isn't it the Kimball approach that says "well a data warehouse is simply all the data marts combined together" -- that sounds kind of like what we're doing. Dump all the transformed source data into one database. Views join table within source schema and between them (integrating business data).

I guess I'm fairly lost at what defines a "data mart" in the logic here.

Maybe I should post a separate question here, but then does one divide "data marts" via schema, or different databases?

I'm not sure the logic of dividing a Data Warehouse into separate Data Marts -- they all feed ONE BI application we are using. This BI application imports ALL data once a day into itself. If Finance uses their Finance data 10,000x more often than Marketing queries the Marketing table, that is completely irrelevant to our database structure as they aren't directly querying/ hitting our Data Warehouse whatsoever.

And what is the logic of separating Data Marts via Department or Function, generally? Is it a performance issue or a UX issue? If performance, then Department shouldn't be the divisor, IO load or usage should be ...

Does anyone know what the difference is here in terms of architecture and use cases? I've read a lot online, but honestly, it seems like most authors are waxing philosophical and not talking about specific tables or brass tacks. Maybe it would be easier if the literal architecture/ db/ schema structure were explained.

  • Okay I'm going to comment on my own question after further research. From what it appears, the idea of a Data Mart only applies in BI applications or custom-created front-ends where there is pretty much a live connection to the data. ... Or as best I can tell, the main purposes are A: restricting the end-users data access or visibility (this can be configured at the BI application level instead of the DB) ... and distributing sql queries/ reads to different hardware containing the marts. Also irrelevant if end-user is reading cubes, not the DB directly. Am I wrong here? – user45867 Sep 26 '17 at 17:34
  • A data mart is a parti of a data warehouse. If you run a hugh company, not every BI app needs access to all the data. The data warehouse is the "one central truth", which can feed into multiple "case oriented" data marts. Customer Service does not need supply channel pricing data, for example. – TomTom Sep 26 '17 at 18:08
  • Right. We use ONE application. The BI system is to an extent "offline" - it gets fed data once a day. It's a cube based BI system. Hell even if we updated it every 5 minutes, the end user requests for data never touch the database. Data Warehouse A > feeds BI B on a recurring time basis, not user demand (this is app specific). User request C ping BI B. The end user requests never directly impact the database. That separation is done in the app itself – user45867 Sep 26 '17 at 18:32
  • @user45867 Yes... with a cube in Analysis Services, you can use the Perspectives feature to offer a more limited view. – Paul Holmes Sep 26 '17 at 18:33
  • Again it appears Data Marts are only relevant in cases where user requests at some point hit the database. In this application, they don't. The other case might be 'admin' of the respective marts -- but honestly, every department having their own DBA to mess with their little corner? Sounds like an utter ridiculous nightmare. The 'owner' might be an exec who has no idea what's important in a database or data warehouse. Data Marts must be for relatively "live" applications. Or for BI systems that don't have built-in permissions at an intermediary "cube", report, collection, webpage, etc. level – user45867 Sep 26 '17 at 18:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.