Data Warehouse
Data Warehouse (DWH) is the first architectural concept implementing the idea of an enterprise data warehouse (EDW). An EDW is a central repository that stores a large volume of an organization's data collected from various sources and manages this data.
In enterprise data warehouses, information is collected from different departments (systems), meaning it has different structures. In addition, such warehouses contain not only current but also archived data, so their volumes are usually significant. This creates two main tasks that need to be solved when organizing an EDW:
- preliminary processing and normalization of data (bringing it to uniform formats, eliminating duplicates and contradictions) before placing it in the warehouse;
- distributed storage of large volumes of data on different servers and combining them into a single system.
In DWH, structured data from relational DBMSs is collected into a multidimensional OLAP warehouse that provides access to data via SQL queries for subsequent processing. The result of the data transformation is passed to BI systems and analytical reporting services.
The choice of data storage structure plays an important role in building the EDW architecture. Data Warehouse has strict restrictions on structure, so not every data storage model is suitable for building a DWH architecture.
The following models are standard:
- The Kimball model, in which one central table (fact table) stores aggregated data about facts or events, and several related auxiliary dimension tables contain descriptive attributes of various entities.
- The Inmon model, which uses a normalized structure with minimal data redundancy. This model is most often used in pilot and MVP projects, where fast implementation is important and the requirements for the data warehouse are minimal.
- Data Vault 2.0 — a hybrid approach to data structure design that combines the principles of the Kimball and Inmon models.
Model | Advantages | Disadvantages |
|---|---|---|
Kimball |
|
|
Inmon |
|
|
Data Vault 2.0 |
|
|
The specifics of the data models used when designing the warehouse structure in DWH form the limitations of the DWH architecture itself:
- high cost of storage and scaling;
- lack of access to raw data;
- complex logic in ETL processes;
- limited scaling capabilities;
- high development and maintenance costs;
- difficulties in technical support and troubleshooting.
As a result, the IT infrastructure does not have time to adapt to changing business needs, since any infrastructure change (connecting a new data source or changing the storage structure) entails large-scale rework of ETL processes.
The need to simplify interaction with the EDW by reducing the requirements for the data loading and organization process led to the emergence of new solutions:
- Data Lake — an architectural component that allows storing semi-structured data in their original form, without the strict regulation typical of DWH.
- Data Lakehouse — a universal solution for working with big data, combining the advantages of the Data Warehouse and Data Lake architectures.