A data warehouse is “physically” a database mainly used for reporting and analysis of a business (helping the decision process).
If we take an example of a car company: How to be able to see, in one page of a document (report), which car is the most sold today ?
Every car dealer has it’s own database which might be synchronized to a central one. But these databases are aim to the operational business (day to day work) of the brand.
Therefore, the data structure is not fitted enough to serve reporting or decision making reports.
The tactic is to create an appropriate structure and fill it with the right type of data coming from the operational data stores or from the production databases. The procedure to process data to the data warehouse is summarized by the extraction, transformation and loading actions (ETL).
Before loading the data into the data warehouse, we transform it to be able to build efficiently the report that should give us the sales trend of a car in a blink of an eye.
A data warehouse is part of a data warehousing system and all make part of the IT fieldwork called Business Intelligence (IT technics to help the decision making process by transforming data into information).
Here are some advantages of a data warehouse :
- Can preserve the history of data (where it can be only transactional data in the ODS)
- Makes it possible to merge several data sources
- Improves data quality (and could even help in correcting the ODS)
- Provides a common model for all business necessity
- Data is structure to make sense to the final user
- Provides a quick way to cross information where it can’t be done on the several sources
ETL: Extraction, Transformation and Loading
ODS : Operational Data Store : Integrates operational data in a single structure but not as aggregated as a data warehouse. It might help in making some work on the operational data and/or to provide an intermediary to the data warehouse. In our example, we can think of the databases of the dealers which are merged in the company’s headquarters database.
Hope this helped.