Architecture of data warehouse
Last Updated on by Karina Shakya
- Three-tier data warehouse architecture is the most widely used architecture of data warehouse as it produces a well-organized data flow from raw information to valuable insights.
- It consists of the Top, Middle and Bottom Tier.
- The top tier is the front-end client tools that presents results through reporting, analysis, and data mining tools.
- The middle tier consists of the analytics engine that is used to access and analyse the data.
- The bottom tier of the architecture is the database server, where data is loaded and stored.
Bottom Tier:
- The database of the data warehouse servers as the bottom tier.
- It is usually a relational database system.
- Data is cleansed, transformed, and loaded into this layer using back-end tools.
Middle Tier:
- The middle tier in data warehouse is an OLAP server which is implemented using either ROLAP or MOLAP or HOLAP model. –
- For a user, this application tier presents an abstracted view of the database.
- This layer also acts as a mediator between the end-user and the database.
Top Tier:
- The top tier is a front-end client layer.
- It is the tools and API that you connect and get data out from the data warehouse.
- It could be query tools, reporting tools, managed query tools, Analysis tools and Data mining tools.
ETL
- Data warehouse systems use back-end tools and utilities to populate and refresh their data . These tools and utilities include the following functions:
- Data extraction, which typically gathers data from multiple, heterogeneous, and external sources.
- Data cleaning, which detects errors in the data and rectifies them when possible.
- Data transformation, which converts data from legacy or host format to warehouse format.
- Load, which sorts, summarizes, consolidates, computes views, checks integrity, and builds indices and partitions.
- Refresh, which propagates the updates from the data sources to the warehouse.
Also read conceptual modelling of data warehouse.