The primary difference between a transactional database and a data warehouse database 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 your business.
Transactional databases are on-line transaction processing (OLTP) 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 transaction gets recorded within the time you stand before the ATM machine. This system is write-optimized and queries (read operations) take a lot of time on such a system.
A data warehouse (DW) on the other end, is a database designed for facilitating querying and analysis. Often designed as online analytical processing (OLAP) systems, these databases contain read-only data that can be queried and analyzed 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 BI solution is scalable, meaning that your bank and ATMs don’t go down just because the CFO asked for a report.
DW is also better documented and managed, so you can avoid the situation of the poor novice who is given the application database diagrams and asked to locate the needle of data in the proverbial haystack of table proliferation. It can also answer questions far more efficiently and frequently.
The creation of a DW leads to a direct increase in the quality of analyses as the table structures are simpler, standardized, and often de-normalized. Therefore, you keep only the needed information in simpler tables, well-documented table structures, and reduce the linkages between tables and the corresponding complexity of queries, as explained later on. A DW drastically reduces the ‘cost-per-analysis’ and thus permits more analysis per FTE.
Having a well-designed DW is the foundation that successful BI and analytics initiatives are built upon.