Historical Data is the back bone of any business for mission critical business decisions. Data is stored in some form of tables in the database. So why the Business Intelligence systems are using Data Warehouse rather than Database to pull historical data? What is the difference between Database and Data Warehouse while both of them have some tables with Data, Index and key etc.? Here are the differences…
- Used for Online Transactional Processing (OLTP). This records the data from the user for history.
- The tables and joins are complex since they are normalized. This is done to reduce redundant data and to save storage space.
- Entity – Relational modeling techniques are used for database design.
- Optimized for write operation.
- Performance is low for analysis queries.
- Used for Online Analytical Processing (OLAP). This reads the historical data for the Users for business decisions.
- The Tables and joins are simple since they are de-normalized. This is done to reduce the response time for analytical queries.
- Data – Modeling techniques are used for the Data Warehouse design.
- Optimized for read operations.
- High performance for analytical queries.
General Data Flow – (Ex: Online Insurance Registration)
- Customer enters the details in the Online Registration form.
- The details are saved into the Database when the customer presses the Submit button in the form.
- Business Intelligence Team of the Insurance Company uses an ETL tool to pull the data from the Database tables to the Data Warehouse tables.
- Business Management uses Business Reporting Tools to pull Data from Data Warehouse tables for generating business reports. For E.g.: To see the customer details entered through the Online Registration Form by the customer