Brief summary of Data Warehouse vs Database

This article briefly summarizes the following two questions aimed at a quick understanding of the "Data Warehouse"

1. What is a "Data Warehouse"?

2. What is the difference between "Data Warehouse" and "database"?

Here is a detailed explanation:

1. What is a "Data Warehouse"?

"A data warehouse is typically a topic-oriented , integrated , time-dependent , non-modifiable (and can be added) set of data that is used to support the management decision process. ”

This sentence contains several key points: theme-oriented, integrated, time-related, non-modifiable, the specific meaning of welcome message exchange.

2. The main difference between Data warehouse and database  

Business data processing is broadly divided into two categories:

One kind is the operation type processing, also called the online transaction processing, it is for the specific business in the database online daily operation, usually carries on the query to the few records, modifies.

The other is analytical processing, which is generally based on the historical data of some topics and supports management decisions.

The main differences are as follows:

(1) database is a transaction-oriented design, the Data Warehouse is subject-oriented design.

(2) database is generally stored online transaction data, Data Warehouse storage is generally historical data .

(3) database design is to avoid redundancy as far as possible, generally adopt the rules to design in accordance with the paradigm; Data Warehouse design is intentionally introduced redundancy, the use of anti-paradigm approach.

(4) The database is designed for capturing data , the Data Warehouse is designed for analyzing data , and its two basic elements are dimension tables and fact tables. Dimension is to look at the angle of the problem, such as time, department, the dimension table is the definition of these things, the fact table is the data to be queried, and the ID of the dimension.

Conceptually, it's somewhat obscure. Any technology is for the application service, combined with the application can be easily understood. Take the banking business for example. The database is the data platform of the transaction system , and every transaction that the customer makes in the bank is written to the database, which is recorded, and can be simply interpreted as a database accounting. The Data Warehouse is the data platform of the analysis system , it obtains the data from the transaction system, makes the summary, the processing, provides the decision-making basis for the decision-maker. For example, how many transactions occur in a branch of a bank for one months, and what is the current deposit balance of the branch? If there are more deposits and more consumer transactions, there is a need for an ATM in the region.

Clearly, banks ' trading volume is huge, usually measured in million or even millions. The transaction system is real-time, which requires timeliness, the customer saves a sum of money need dozens of seconds is unbearable, this requires the database can only store a short period of time data. The analysis system is an afterthought, and it provides all the valid data for the time period of attention. The data is massive and the aggregate is slower to calculate, but as long as you can provide effective analytical data to achieve the goal.

The Data Warehouse is attached as follows: