Data Warehousing & Multi-tier Architecture

Introduction

·        A data warehouse is like a big storage house where information is kept so that people can use it to make better decisions. This information comes from different places.

      The warehouse is a central place where all this data is organized and can be easily analyzed.

     Databases and data warehouses are related but not the same. 

·       In software engineering, multitier architecture (often referred to as n-tier architecture) is a client–server architecture in which presentation, application processing and data management functions are physically separated.

·        It transforms into a format that can be accessed and analyzed. To ensure the efficient functioning of a data warehouse, it must have a multi-tier architecture.

Goals of data warehousing:

1. To help reporting as well as analysis.

2. Maintain the organization's historical information.

3. Be the foundation for decision making.

"How are organizations using the information from data warehouses ?"

• Most of the organizations makes use of this information for taking business decision like :

a) Increasing customer focus: It is possible by performing analysis of customer buying.

b) Repositioning products and managing product portfolios by comparing the performance of last year sales.

c) Analysing operations and looking for sources of profit.

d) Managing customer relationships, making environmental corrections and managing the cost of corporate assets.

Key characteristics of a Data Warehouse:

1. Data is structured for simplicity of access and high-speed query performance.

2. End users are time-sensitive and desire speed-of-thought response times.

3. Large amounts of historical data are used.

4. Queries often retrieve large amounts of data, perhaps many thousands of rows.

5. Both predefined and ad hoc queries are common.

6. The data load involves multiple sources and transformations.

Multitier Architecture of Data Warehouse:

Data warehouse system is constructed in three ways. These approaches are classified the number of tiers in the architecture.

a) Single-tier architecture.

b) Two-tier architecture.

c) Three-tier architecture (Multi-tier architecture).

Need for Multi-tier Architecture

A data warehouse is a complex system. It requires multiple layers to handle the large amount of data involved. 

·       There is a need for a multi-level structure. 

·       Each layer of the system performs its specific function efficiently.

     

     Multi-tier Architecture

 There are four layers in multi-tier architecture.

·      These are : Data Source Layer, ETL Layer, Data Storage Layer, and Data Access Layer. 

·    Data Source Layer:

  It is the first layer of a multi-tier architecture.

 It includes all sources of data that need to be integrated into the data warehouse.

 These sources can be databases, flat files or external sources such as social media platforms. It is responsible for collecting, validating and organizing the data before passing it on to the next layer.

 ETL Layer:

 This is the second layer of the multi-tier architecture.

·        It is responsible for extracting data from data sources.

·        It transforms it into a format suitable for a data warehouse.

·        It also loads it into the data storage layer.

·        This layer ensures the quality and consistency of the data loaded into the data warehouse.

       Data Storage Layer/Logical layer:

·        This is the third layer of the multi-tier architecture.

·        It is responsible for storing the data that has been transformed and loaded by the ETL Layer.

·        This Layer can be divided into two sub-layers: the staging area and the data warehouse. 

·        The staging area is used to store the data temporarily before it is loaded into the data warehouse The data warehouse is the final destination for the data and is used for reporting and analysis

     Data Access Layer:

·        It is the fourth layer of the multi-tier architecture.

·        It is responsible for providing users with accessibility to the data.

·        This layer can be divided into two sub-layers

·        Presentation layer provides a user-friendly interface for users to access and analyze data.

       Application layer is responsible for managing the business logic and ensuring the security and integrity of the data.

     Multi-Tier Data Warehouse Architecture Components:

·        Components: Data Sources, Data Integration Layer, Staging Area, Data Warehouse Database, Data Mart, OLAP Cube, Front-End Tools, Metadata Repository.

·       

·   Advantages of Multi-tier Architecture:

   | Scalability

·        Components can be added, deleted or updated according to the data warehouse's needs.

 | Better Performance

·        Several layers enable parallel and efficient processing for improved performance and reaction times.

|    |    Modularity

·        Modular design allows the creation, testing, and deployment of separate components.

|    |   Security

·        Applying security measures to various layers enhances the data warehouse's overall security.

    |  Improved Resource Management

·        Different tiers can be tuned to use proper hardware resources, reducing expenses and increasing effectiveness.

     |  Easier Maintenance

·        Individual components can be updated or maintained without affecting the entire data warehouse.

     | Improved Reliability

·        Multi-tier architecture offers redundancy and failover capabilities, enhancing the overall reliability of the data warehouse.