Total Page Preview:   000000009596

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?




Recent Posts

How to get the alternate rows or records from table in sql server
Data Warehouse Three Tier Architecture
SQL Server - Fragmentation on azure sql database
Limitations of Azure SQL Database
PIVOT and UNPIVOT in SQL Server with Example
SQL Server - Find unused indexes on azure SQL database scripts
SQL Server - Find all index with key columns on Azure SQL Database
Find Resource usage in Azure SQL Database using script or T-SQL
How to Set client IP Address using the firewall security in Azure SQL Using Portal
What are the types of User Define Function and difference between a nested UDF and recursive UDF in SQL server?
Top 60 SQL Server Interview Questions Answers for freshers and experienced
Top 16 performance query optimization techniques in SQL Database?
What is a Trigger and type of trigger in SQL server?
How many types of dimension tables available in data warehouse?
Data warehouse Interview Questions Answers for freshers and experienced
What are the differences between OLTP and OLAP?
What is Table Variable? Explain its advantages and disadvantages?
What are the different types of Error Handling in SQL Server?
Cloud Data Engineer SQL Server Interview Questions Answers for freshers and experienced
What are characteristics and advantages of stored procedure?

Facebook User: