Data Warehouse vs Database
There are a number of fundamental differences which separate a data warehouse
from a database. The primary difference betwen an application database and a data warehouse is that while the former is designed (and optimized) to record , the latter has to be designed (and optimized) to respond to analysis questions that are critical for a business.
A D V E R T I S E M E N T
Basically a database is any system which keeps data in a table format. A computer database is a structured collection of records or data that is stored in a computer system so that a computer program or person using a query language can consult it to answer queries. The records retrieved in answer to queries are information that can be used to make decisions.
A data warehouse is a specially setup database designed to hold large amounts of data for reporting purposes. While a normal database is optomized for transactional activity (while keeping a small amount of history) a data warehouse is optomized for large scale reporting.
Within a data warehouse data from several systems are typically be merged together to present a global enterprise view. Data warehouses typically keeps a very long history from several years to the entire life of the company so that very long term trends can be viewed.
All data warehouses are databases, not all databases are data warehouses.
Application databases are OLTP (On-Line Transaction Processing) systems where every transaction has to be recorded, and super-fast at that. Consider the scenario where a bank ATM has disbursed cash to a customer but was unable to record this event in the bank records. If this started happening frequently, the bank wouldn't stay in business for too long. So the banking system is designed to make sure that every trasaction gets recorded within the time you stand before the ATM machine. This system is write-optimized.
A Data Warehouse (DW) on the other end, is a database that is designed for facilitating querying and analysis. Often designed as OLAP (On-Line Analytical Processing) systems, these databases contain read-only data that can be queried and analysed far more efficiently as compared to your regular OLTP application databases. In this sense an OLAP system is designed to be read-optimized.
Separation from your application database also ensures that your business intelligence solution is scalable, better documented and managed, and can answer questions far more efficietly and frequently.
Creation of a DW leads to a direct increase in quality of analyses as the table structures are simpler (only the needed information in simpler tables), standardized (well-documented table structures), and often denormalized (to reduce the linkages between tables and the corresponding complexity of queries). A DW drastically reduces the 'cost-per-analysis' and thus permits more analysis per FTE. Having a well-designed DW is the foundation successful BI/Analytics initiatives are built upon.