Usually, data warehouse adapts the three-tier architecture. In this article, we will discuss on the data warehouse three-tier architecture. You can read about read about two-tier architecture in my other post ‘Data Warehouse Two-tier architecture in details‘
Data Warehouse Three-tier Architecture
Following are the three-tiers of data warehouse architecture:
Bottom Tier
The bottom tier of the architecture is the data warehouse database server. It is usually the relational database (RDBMS) system. Data from operational databases and external sources are extracted using application program interfaces and ETL/ELT utilities. You generally use the ETL or ELT utilities to feed data into the bottom tier.
Read:
- Data Warehouse Two-tier Architecture in Details
- Data Warehouse Project Life Cycle and Design
- Types of Fact Tables in a Data Warehouse
- Database ACID Properties and Explanation
- Data Warehouse Fact Constellation Schema and Design
- Types of Dimension Tables in a Data Warehouse
- Various Data Warehouse Design Approaches:Top-Down and Bottom-Up
The work of ELT/ELT is to Extract, Transform, clean and Load data into designated tables.
Read:
- Star Schema model in Data Warehouse
- Snowflake Schem Model in Data Warehouse
- Step by Step Guide to Dimensional Data Modeling
- Data Warehouse Physical Data Model
- Data Warehouse Logical Data Model
Middle Tier
In the middle-tier, there will be OLAP server. It can be implemented in either of following ways. You can implemet it as Relational OLAP (ROLAP), which is noting but an extended relational database management system.
Other is multidimensional OLAP (MOLAP) model, which directly implements the multidimensional data and operations.
Top-Tier
This tier is the front-end client layer. Top tier is the tools and API that you connect and get data out from the data warehouse. This layer holds the query tools and reporting tools, analysis tools and data mining tools. Various visualisation and reporting tools are used to get data out to the user.
Read:
- Slowly Changing Dimensions (SCD) in Data Warehouse
- Rapidly Changing Dimension in Data Warehouse
- Data Warehouse Conceptual Data Model