Architecture of data warehouse

  • 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.
Architecture of data warehouse

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.


  • 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.
Architecture of data warehouse

Also read conceptual modelling of data warehouse.

Facebook Comments

Comments are closed, but trackbacks and pingbacks are open.