EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

Data Warehouse Architecture

By Priya PedamkarPriya Pedamkar

Home » Data Science » Data Science Tutorials » Data Warehouse Tutorial » Data Warehouse Architecture

Data-Warehouse-Architecture

Introduction to Data Warehouse Architecture

The Data Warehouse Architecture can be defined as a structural representation of the concrete functional arrangement based on which a Data Warehouse is constructed that should include all its major pragmatic components, which is typically enclosed with four refined layers, such as the Source layer where all the data from different sources are situated, the Staging layer where the data undergoes ETL processing, the Storage layer where the processed data are stored for future exercises, and the presentation layer where the front-end tools are employed as per the users’ convenience.

Data Warehouse Architecture

OLAP-Server

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

The Data Warehouse Architecture generally comprises of three tiers.

  • Top Tier
  • Middle Tier
  • Bottom Tier

Top Tier

  • The Top Tier consists of the Client-side front end of the architecture.
  • The Transformed and Logic applied information stored in the Data Warehouse will be used and acquired for Business purposes in this Tier.
  • Several Tools for Report Generation and Analysis are present for the generation of desired information.
  • Data mining which has become a great trend these days is done here.
  • All Requirement Analysis document, cost, and all features that determine a profit-based Business deal is done based on these tools which use the Data Warehouse information.

Middle Tier

  • The Middle Tier consists of the OLAP Servers
  • OLAP is Online Analytical Processing Server
  • OLAP is used to provide information to business analysts and managers
  • As it is located in the Middle Tier, it rightfully interacts with the information present in the Bottom Tier and passes on the insights to the Top Tier tools which processes the available information.
  • Mostly Relational or MultiDimensional OLAP is used in Data warehouse architecture.

Bottom Tier

The Bottom Tier mainly consists of the Data Sources, ETL Tool, and Data Warehouse.

1. Data Sources

The Data Sources consists of the Source Data that is acquired and provided to the Staging and ETL tools for further process.

2. ETL Tools

  • ETL tools are very important because they help in combining Logic, Raw Data, and Schema into one and loads the information to the Data Warehouse Or Data Marts.
  • Sometimes, ETL loads the data into the Data Marts and then information is stored in Data Warehouse. This approach is known as the Bottom-Up approach.
  • The approach where ETL loads information to the Data Warehouse directly is known as the Top-down Approach.

Difference Between Top-down Approach and Bottom-up Approach

Top-Down Approach Bottom-Up Approach
Provides a definite and consistent view of information as information from the data warehouse is used to create Data Marts Reports can be generated easily as Data marts are created first and it is relatively easy to interact with data marts.
Strong model and hence preferred by big companies Not as strong but data warehouse can be extended and the number of data marts can be created
Time, Cost and Maintenance is high Time, Cost and Maintenance are low.

Data Marts

  • Data Mart is also a storage component used to store data of a specific function or part related to a company by an individual authority.
  • Datamart gathers the information from Data Warehouse and hence we can say data mart stores the subset of information in Data Warehouse.
  • Data Marts are flexible and small in size.

3. Data Warehouse

  • Data Warehouse is the central component of the whole Data Warehouse Architecture.
  • It acts as a repository to store information.
  • Big Amounts of data are stored in the Data Warehouse.
  • This information is used by several technologies like Big Data which require analyzing large subsets of information.
  • Data Mart is also a model of Data Warehouse.

Different Layers of Data Warehouse Architecture

Below are the different layers:

ETL

There are four different types of layers which will always be present in Data Warehouse Architecture.

1. Data Source Layer

  • The Data Source Layer is the layer where the data from the source is encountered and subsequently sent to the other layers for desired operations.
  • The data can be of any type.
  • The Source Data can be a database, a Spreadsheet or any other kinds of a text file.
  • The Source Data can be of any format. We cannot expect to get data with the same format considering the sources are vastly different.
  • In Real Life, Some examples of Source Data can be
  • Log Files of each specific application or job or entry of employers in a company.
  • Survey Data, Stock Exchange Data, etc.
  • Web Browser Data and many more.

2. Data Staging Layer

The following steps take place in Data Staging Layer.

Step #1: Data Extraction

The Data received by the Source Layer is feed into the Staging Layer where the first process that takes place with the acquired data is extraction.

Step #2: Landing Database

  • The extracted data is temporarily stored in a landing database.
  • It retrieves the data once the data is extracted.

Step #3: Staging Area

  • The Data in Landing Database is taken and several quality checks and staging operations are performed in the staging area.
  • The Structure and Schema are also identified and adjustments are made to data that are unordered thus trying to bring about a commonality among the data that has been acquired.
  • Having a place or set up for the data just before transformation and changes is an added advantage that makes the Staging process very important.
  • It makes data processing easier.

Step #4: ETL

  • It is an Extraction, Transformation, and Load.
  • ETL Tools are used for integration and processing of data where logic is applied to rather raw but somewhat ordered data.
  • This data is extracted as per the analytical nature that is required and transformed to data that is deemed fit to be stored in the Data Warehouse.
  • After Transformation, the data or rather an information is finally loaded into the data warehouse.
  • Some examples of ETL tools are Informatica, SSIS, etc.

3. Data Storage Layer

  • The processed data is stored in the Data Warehouse.
  • This Data is cleansed, transformed, and prepared with a definite structure and thus provides opportunities for employers to use data as required by the Business.
  • Depending upon the approach of the Architecture, the data will be stored in Data Warehouse as well as Data Marts. Data Marts will be discussed in the later stages.
  • Some also include an Operational Data Store.

4. Data Presentation Layer

  • This Layer where the users get to interact with the data stored in the data warehouse.
  • Queries and several tools will be employed to get different types of information based on the data.
  • The information reaches the user through the graphical representation of data.
  • Reporting Tools are used to get Business Data and Business logic is also applied to gather several kinds of information.
  • Meta Data Information and System operations and performance are also maintained and viewed in this layer.

Conclusion

An important point about Data Warehouse is its efficiency. To create an efficient Data Warehouse, we construct a framework known as the Business Analysis Framework.

Popular Course in this category
All in One Data Science Bundle (360+ Courses, 50+ projects)360+ Online Courses | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (3,220 ratings)
Course Price

View Course

Related Courses
Business Intelligence Training (12 Courses, 6+ Projects)Data Visualization Training (15 Courses, 5+ Projects)

There are four types of views in regard to the design of a Data warehouse.

1. Top-Down View: This View allows only specific information needed for a data warehouse to be selected.

2. Data Source View: This view shows all the information from the source of data to how it is transformed and stored.

3. Data Warehouse View: This view shows the information present in the Data warehouse through fact tables and dimension tables.

4. Business Query View: This is a view that shows the data from the user’s point of view.

Recommended Articles

This has been a guide to Data Warehouse Architecture. Here we discussed the different Types of Views, Layers, and Tiers of Data Warehouse Architecture. You can also go through our other suggested articles to learn more –

  1. Career in Data Warehousing
  2. How JavaScript Works
  3. Data warehouse Interview Questions
  4. What is Pandas
  5. Complete Guide to Data Warehouse Testing
  6. Guide to Three Tier Data Warehouse Architecture

All in One Data Science Bundle (360+ Courses, 50+ projects)

360+ Online Courses

1500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

1 Shares
Share
Tweet
Share
Primary Sidebar
Data Warehouse Tutorial
  • Basic
    • What is Data Warehouse
    • Data Warehouse tools
    • Career in Data Warehousing
    • Benefits of Data Warehouse
    • Data Warehouse Architecture
    • Data Warehouse Design
    • Data Warehouse Implementation
    • Data Warehouse Modeling
    • Data Warehouse Software
    • Types of Data Warehouse
    • 10 Popular Data Warehouse Tools
    • Data Lake Architecture
    • Three Tier Data Warehouse Architecture
    • Data Warehouse Process
    • Database Parallelism
    • What is OLTP
    • What is OLAP
    • OLAP Tools
    • Types of OLAP
    • Operations in OLAP
    • MOLAP
    • HOLAP
    • Data Warehouse Schema
    • Snowflake Schema
    • What is Star Schema
    • Galaxy Schema
    • What is Fact Table
    • Kimball Methodology
    • Data Warehouse Testing
    • Operational Data Stores
  • ETL
    • What is Data Mart
    • What is Data Cube
    • What is a Data Lake
    • What is Data Integration
    • What is ETL
    • What is ETL Testing
    • ETL Testing Tools
    • Dimension Table
    • Multidimensional Data Model
    • Fact Constellation Schema
    • ETL Process
  • Interview Questions
    • Data Warehouse Interview Questions
    • ETL Interview Questions
    • ETL Testing Interview Questions
    • Data Warehousing Interview Questions

Related Courses

Business Intelligence Course

All in One Data Science Course

Data Visualization Certification Courses

Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

© 2020 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA Login

Forgot Password?

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you
Book Your One Instructor : One Learner Free Class

Let’s Get Started

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

Special Offer - All in One Data Science Bundle (360+ Courses, 50+ projects) Learn More