Total Page Preview:   000000010613

Data Warehouse Three Tier Architecture

In this acticl I am going to explain Data warehouse three tier architucture

Data warehouse adopt a three tier architecture,these are:

These 3 tiers are:
  1. Bottom Tier (Data warehouse server)
  2. Middle Tier (OLAP server)
  3. Top Tier (Front end tools)
1. Bottom Tier:
Data warehouse server fatch only relevant information based on data mining (mining a knowledge from large amount of data) request.
  • It is a warehouse database server.
  • Data is fed using Back end tools and utilities.
  • Data extracted using programs called gateways
  • It also contains Meta data repository.
Bottom Tier Contains:
  • Data warehouse.
  • Metadata Repository.
  • Data Marts.
  • Monitoring and Administration.
Data Warehouse:
It is an optimized form of operational database contain only relevant information and provide fast access to data.
Metadata repository:
It figure out that what is available in data warehouse. 
It contains:
  • Structure of data warehouse.
  • Data names and definitions.
  • Source of extracted data.
  • Algorithm used for data cleaning purpose.
  • Sequence of tranformation applied on data.
  • Data releted to system performance.
Data Marts:
Subset of data warehouse contain only small slices of data warehouse Example: Data partaining to the single department.
Data Marts is two types:
  1. Dependent - sourced directly from data warehouse
  2. Independent - sourced from one or more data sources
Monitoring And Administration:
  • Data Refreshment
  • Data source synchronization
  • Disaster recovery
  • Managing data growth, database performace
  • Controlling the number & range of queries
  • Limiting the size of data warehouse
2. Middle Tier (OLAP Server):
It presents the users a multidimensional data from data warehouse or data marts.
Typically implemented using two models:
  1. ROLAP(Relational OLAP) Model - Present data in relational tables.
  2. MOLAP(Multidimensional) Model - Present data in array based structures means map directly to data cube array structure.
3. Top Tier:
It is front ent client layer. Query and reporting tools:
Reporting Tools: Production reporting tools and Report writers
Managed query tools: Point and click creation of SQL used in customer mailing list.
Analysis tools: Prepare charts based on analysis.
Data Mining Tools: mining knowledge, discover hidden piece of information, new correlatons, useful pattern

Thank You

About Author

Brijesh Kumar

Database Developer

I have more then 6 years Experience in Microsoft Technologies - SQL Server Database, ETL Azure Cloud - Azure SQL Database, CosmosDB, Azure Data Factory, PowerBI, Web Job, Azure Function, Azure Storage, Web Apps, Powershall and Database Migration On-Premise to Azure Cloud.
LinkedIn :


Thank you for the good writeup. It in fact was a amusement account it. Look advanced to far added agreeable from you! By the way, how could we communicate?




Facebook User: