Data Warehouse vs Data Mart


I have been asked many times the difference between a data mart and a data warehouse. I have often heard the terms used interchangeably. But let's face it, we can't all be right all the time. So, below is my break down of data warehouses and data marts.

Data Warehouse

 A data warehouse is simply a database.  The exact kind of database technology you use for business applications can be used for a data warehouse; you can use Oracle or SQL Server.  But stop before going to tell your boss all of your databases are data warehouses.  The difference between your application database and your data warehouse is how the schema is designed and the source of the data.  A data warehouse is specifically designed to make reporting easier and to ensure reporting does not place an undue burden on the transactional source system. Data warehouses can include data from many sources all brought together for convenience.  Note: A data warehouse is not a data graveyard for old data you no longer care about. Historical data in a warehouse is there for analysis, not because no one knew where else to put it.

So, if that is a data warehouse then what is a data mart?  Is it a different type of database technology?

Data Mart

A data mart is simply a database designed for ease of reporting.  Like a data warehouse, a data mart can be implemented using the database technology of your choice.  It is also not a place to put data when you are ready to put it out to pasture. The prescribed schema for a data mart is a star schema consisting of a central fact table with keys for dimensions. The dimension tables are your slicers. If you want to see anything "by" then it is a slicer.  For example, if you want to see revenue by quarter by region by product line then quarter, region, and product line are your slicers and are probably one or more dimension tables. Arranged properly, the table structure resembles a hub and spoke layout very loosely resembling a star.


The Difference

 The major difference between a data mart and a data warehouse is the scope of the data.  A data warehouse will contain the combined and aggregated data from many subject areas in one place while a data mart is subject specific.  For example, a data mart may only contain data necessary for reporting on Finance or Human Resources.  Other differences are subject to the specific implementations.

There are data architectures in which a collection of data marts can be used to create a data warehouse.  But, a collection of data warehouses is...a collection of data warehouses.


Popular posts from this blog

Power BI Report Server: "An Error Has Occurred" or Power BI Report Server: "My Power BI Report is Coming up Blank" or Power BI Report Server: "401 and 403” Errors From Nowhere

Image
The information below is an extremely brief summary of the information collected to solve the elusive problem.  There are points I left out because I could not find supporting documentation and I did not want to release suppositions into the universe (why does an NTLM only issue impact a system using kerberos?).  I also want to thank my friend Josh Corrick for staying the course and seeing this through to the end.  See Josh's blog here:  https://corrick.io/blog/ Without warning, our Power BI Report Server began having issues presenting Power BI reports.   The reports would appear to render but would eventually return a blank report canvas.   There was not an error presented and this was impacting all Power BI reports.   An important note is the Power BI reports had worked as expected previously and no changes had been made to the report server such as upgrades or configuration changes. Subsequently, and seemingly unrelated, an error notification was presented when clicking
Read more

SQL SERVER 2017: STRING_AGG

STRING_AGG is a function released in SQL Server 2017.   The Oracle equivalent LIST_AGG has been available since 11gR2.  In the meantime, SQL Server users have resorted to black magic in order to get string values from separate rows. STRING_AGG is very welcome but there are some gotchas to be aware of.  Below is a simple example that works just like you think it would. SELECT STRING_AGG(FIrst_Name, ', ') WITHIN GROUP (ORDER BY First_Name) LIST FROM             (SELECT DISTINCT   First_name FROM [dbo].[Employee]     WHERE First_Name like 'A%' OR First_Name like 'B%') A ORDER BY LIST; You can run the query on any database you have string values and you will get something like this: A David, Aaren, Aaron, Abdul, Bob, Brian... If you want to split by the first letter of the name, do not make the mistake of using an over clause.  You will get an error stating STRING_AGG is not a window function. The function 'STRING_AGG' is not a valid win
Read more

Dynamic Label Positions With SSRS

Image
On a recent tabular "dashboard" with several bar charts in a small area our team found vertical positioning of the labels to be troublesome.  When the labels were set to "bottom" and the bar was too short the label would truncate the significant digits.  On the other hand, when the labels were set to "top" the numbers would be truncated if the column was too tall. Example of bar chart with label set to bottom. Same chart with the middle column label set to top. First we thought about using Smart Labels  but we quickly found that smart labels are not so smart when the labels are turned 90 degrees to make them vertical.  As you can see in the examples above the size of the numbers would make horizontal labels messy. So the obvious choice was to use a function to place the label on top when the bar was small and on the bottom once the bar was tall enough. In Report Builder, go to your chart and select a specific column - not just the chart.
Read more