Data is at every point of any software application or computer program. It is essential for software developers to be informed about the technologies present at our back-end systems. These systems are responsible for storing, organizing, and processing data for users to promptly find and extract relevant information. They are available with various sizes and features. Not only are there various databases but the technology of Data Warehouses was also introduced. Having the right knowledge helps to select the technology required for fulfilling the users’ requirement.
Most commoners and at times even professionals tend to wonder: If I already have a database why would I need a Data warehouse?
We must understand the applications and the uses of both of these, only then we will be able to implement them wisely. Databases and data warehouses are used to generate different types of information and information generated by both are used for different purposes.
Consider an organisation which has to process hundreds of thousands of transactions every day and additionally must store the history of the transaction that has occurred. Such an organisation most definitely requires both a database and a data warehouse. Now in this situation, it is important to understand which data will be processed for which system.
Let us understand more about both of these systems.
Database vs Data warehouse
| Database | Data warehouse |
| Supports operational processes like storage, retrieval and accessibility | Supports analysis and performance reporting |
| Capture and maintains accessibility to the data | Explore the data |
| 100MB-GB | 100GB-TB |
| Short simple transition queries | Complex queries |
| Current data | Historical data |
| Updated when a transaction occurs | Updated on scheduled processes |
| Optimized for read-write operations through single-point-transaction | Optimized for retrieval of large data-sets to aggregate the data |
| ER based | Star/Snowflake |
| Application-oriented | Subject oriented |
| Data is normalized, no duplication of data | Data is denormalized and stored in a simple structure |
| Primitive and highly detailed. | Summarized and consolidated |
| Observes data accuracy when updating real-time data. | Ensures a vast range of data which is used over-time for analysis purposes. |
| Less number of data accessed | A large number of data accessed |
| Examples are OLTP, CSV, text files, excel spreadsheets and XML files | Example is OLAP |
Why do we need databases?
Database in simple terms is a collection of related elements in a designated way. It forms a critical building block of the application. The structured information is stored electronically in a computer and controlled by a database management system (DBMS) and performs specific tasks like storage, accessibility, and retrieval.
The major task of database systems is to perform online transaction and query processing.
The database is not responsible for analyzing the data. To generate patterns or knowledge from data, databases are of no help. They simply help store data in an easily accessible and structured way. A database is typically constrained to a single application.
However, both OLTP and OLAP systems store and manage data in the form of tables, columns, indexes, keys, views, and data types and both use SQL to query the data
It offers the security of data and its access control along with the task of removing redundancies. A database offers a variety of techniques to store and retrieve data and acts as an efficient handler to balance the requirement of multiple applications using the same data.
A DBMS provides integrity constraints to get a high level of protection to prevent access to prohibited data and ensuring a hundred per cent confidentiality to customer information. It follows the ACID compliance, which stands for Atomicity, Consistency, Isolation, Durability from the very core. Entity-Relationship models are used to deploy a database. System failure can result in chaos as it is directly related to the frontend of the system.
Why do we need a Data warehouse?
A data warehouse was described as a subject-oriented, integrated, time-variant and non-volatile collection of knowledge in support of management’s deciding process by W.H. Inmon. A data warehouse is a top layer on databases, which procures (Extracts, transforms and loads) data from them and stores them for analysis. This data is then processed for its insights.
It is maintained separately from the organisation’s operational database and is meant to provide a platform for information processing and historical analysis of data. Data warehousing was the process of creating and using data warehouses. They are constructed by integrating multiple, heterogeneous data sources.
Data here doesn’t need an operational update and only two operations of loading and accessing data are required, data is loaded in static format and doesn’t need any modification. Data warehouses don’t contain the most recent information. It serves the purpose of correlation between data from different source systems.
A data warehouse is used for online analytical processing (OLAP) which serves knowledge workers in the role of decision making and data analysis. It provides a simple concise view around a particular subject by excluding data that is not useful for the process. It is used to extract important insights and streamline business processes and is an important element in business intelligence. It enables business users to access query-relevant data faster and also improves data consistency and quality.
Data warehousing has applications in various fields of Finance, Telecommunications, Transport and many more.
Author
Shriya Upasani
MIT World Peace University
