Data warehouses
Data warehouse is a centralized repository which is designed to store, manage and analyze large volumes of current and historical data from various departments on an organization. They are optimized for analytical processing and business intelligence
characteristics of a warehouse
they can handle massive amounts of data
they not only deal with current data but also historical data
the system architecture prioritizes query performance and data retrieval speed over transactional processing, making complex analytical queries execute efficiently
they consolidate information from multiple data sources
business intelligence support because of its ability to analyze and visualize data
the structure allows the data in it to be easily accessible.
benefits
improved decision making
enhanced performance
data quality and consistency
OLTP VS OLAP
Online transactional processing systems are designed to handle real-time transactional operations that occur in day-to-day business activities.eg banking systems.
online analytical processing are optimized for complex analysis, reporting and business intelligence activities.
Key Differences and Implications
Transaction vs. Analysis: OLTP systems excel at processing individual transactions quickly and accurately, while OLAP systems specialize in analyzing patterns across large datasets.
Data Freshness: OLTP systems work with real-time data, whereas OLAP systems typically work with data that may be hours or days old, depending on the ETL schedule.
Concurrency Requirements: OLTP systems must handle many simultaneous users performing transactions, while OLAP systems typically serve fewer concurrent users running complex queries.
Failure Impact: OLTP system downtime directly affects business operations, while OLAP system unavailability impacts reporting and analysis capabilities.
data modelling
data modelling is the systematic process of creating abstract representation of data structures, relationships and constraints to support a specific business requirement and analytical needs.
data modelling is like creating an architectural blueprint before constructing a blueprint.
types of models
conceptual model - provides high-level, business oriented view of data requirements without technical implementation details.
Example Elements:
Customer entity related to Order entity
Product entity connected to Category entity
Employee entity associated with Department entity
logical model - this adds technical detail while remaining independent to specific database management systems.
Additional Elements:
Customer_ID (Integer, Primary Key)
Customer_Name (VARCHAR(100), NOT NULL)
Order_Date (DATE, NOT NULL)
Physical Model - this specifies how data will be stored in a particular database system
dimensional modelling for data warehousing
it is a preferred approach of designing a data warehouse structure because it optimizes for analytical query performance while maintaining business user comprehension.
fact tables - serves as the central repo for measurable business metrics and form the core dimensional tables.
dimensional tables - it provides the descriptive context that makes fact table measurements meaningful and analyzable
Top comments (0)