So I understand that a data warehouse is used for historic analysis and performance reporting but I don’t really know what it actually is or what the difference is to a database
In: 6
There’s considerable overlap but:
Databases are often for production purposes, where they get lots of small queries and inserts. For example:
Query: Get me order history for this customer
Insert: Create order for this customer
This database will have been designed around delivering the transaction performance required for the application which may be high frequency small transactions.
You *could* ask this database a big question like “what are the total sales by year?” – however that requires a lot of processing and may affect the performance of the system perhaps even crashing it, so such a question is often forbidden since the production system requires consistent performance. However you still need to answer these questions and that’s where a warehouse comes in.
A data warehouse on the other hand, is all about *querying* the data in *bulk* for analysis purposes – and may not be the absolute newest data. Generally you’d take your production data from many databases and perhaps upload new data to a single warehouse every midnight (when the load on the production servers is low.) Most databases don’t allow you to query across databases, but warehouses do, allowing you to ask things like “what are total yearly sales and join that to the locations of our shops (usually in a different database) to get sales by year by location”. It doesn’t matter if this takes an hour and it can’t affect the performance of the production system.
Latest Answers