What is the difference between a database and a data warehouse?
Aren't they the same thing, or at least written in the same thing (ie. Oracle RDBMS)?
+3
A:
From the 2nd link:
Database
- Used for Online Transactional Processing (OLTP) but can be used for other purposes such as Data Warehousing. This records the data from the user for history.
- The tables and joins are complex since they are normalized (for RDMS). This is done to reduce redundant data and to save storage space.
- Entity – Relational modeling techniques are used for RDMS database design.
- Optimized for write operation.
- Performance is low for analysis queries.
Data Warehouse
- Used for Online Analytical Processing (OLAP). This reads the historical data for the Users for business decisions.
- The Tables and joins are simple since they are de-normalized. This is done to reduce the response time for analytical queries.
- Data – Modeling techniques are used for the Data Warehouse design.
- Optimized for read operations.
- High performance for analytical queries.
- Is usually a Database.
It's important to note as well that Data Warehouses could be sourced zero to many databases.
TheCloudlessSky
2010-08-05 21:35:33
Wow, you're fast.
Data Man
2010-08-05 21:37:51
Data modeling is a generic term and does not only apply to data warehouses. (Perhaps in the second #3 they meant "dimensional modeling" as that is, in my experience, the most popular way to build them.)The last line doesn't make sense: how does a data warehouse "contain" a database? I might say a data warehouse is *sourced* from 0 to many (OLTP) databases.
Patrick Marchand
2010-08-05 22:03:27
@Patrick - yeah I wasn't sure how to write the last line... changed it.
TheCloudlessSky
2010-08-05 23:13:48
-1 for saying that a database is only used for OLTP. Most data warehouses are stored in databases. Also, database is a *generic* term - normalisation and relational modelling techniques are used in *relational* databases.
Mark Bannister
2010-08-06 09:59:05
@Mark - Where does it say databases are ONLY for OLTP? It is showing how databases relate to data warehouses.
TheCloudlessSky
2010-08-06 13:16:30
@TheCloudlessSky: Point 1 of Database from your answer: "Used for Online Transactional Processing (OLTP)." No explanation that Databases can be used for other purposes; no explanation that data warehouses normally are databases; contrast between database and data warehouse sections implies that they are two different things, whereas normally the latter is a subset of the former.
Mark Bannister
2010-08-06 13:30:20
@Mark - Good point, I'll update the post since I didn't actually write it initially.
TheCloudlessSky
2010-08-06 13:55:54
Downvote removed.
Mark Bannister
2010-08-06 14:11:02
Can the down voter give a reason?
TheCloudlessSky
2010-08-07 16:55:21
A:
You can find a great explanation here: http://it.toolbox.com/blogs/opensource-analytics/database-vs-data-warehouse-8286
Rafa
2010-08-05 21:36:03
+1
A:
The simplest way to explain it would be to say that a data warehouse consists of more than just a database. A database is an collection of data organized in some way, but a data warehouse is organized specifically to "facilitate reporting and analysis". This however is not the entire story as data warehousing also contains "the means to retrieve and analyze data, to extract, transform and load data, and to manage the data dictionary are also considered essential components of a data warehousing system".
kniemczak
2010-08-05 21:40:16
+1
A:
A datawarehouse is a TYPE of database.
In addition to what folks have already said, datawarehouses tend to be OLAP, with indexes, etc. tuned for reading, not writing, and the data is de-normalized / transformed into forms that are easier to read & analyze.
Some folks have said "databases" are the same as OLTP -- this isn't true. OLTP, again, is a TYPE of database.
Other types of "databases": Text files, XML, Excel, CSV..., Flat Files :-)
dave
2010-08-05 21:48:28
A:
From a Non-Technical View: A database is constrained to a particular applications or set of applications.
A data warehouse is an enterprise level data repository. It's going to contain data from all/many segments of the business. It's going to share this information to provide a global picture of the business. It is also critical to integration between the different segments of the business.
From a Technical view: The word "Data Warehouse" has been given no recognized definition. Personally, I define a data warehouse as a collection of data-marts. Where each data-mart consists of one or more databases where the database is specific to a specific problem set (application, data-set or process).
Simply put a database is a component of a data-warehouse. There are many places to explore this concept, but because there is no "definition", you will find challenges with any answer you give.
Markus
2010-09-03 14:37:50