Home » Data Warehouse

Data Warehouse Architecture

Here, we are going to learn about the Data Warehouse Architecture and its various layers.
Submitted by IncludeHelp, on January 19, 2021

A data warehouse is a big and complex information system that contains organized data; it collects data from multiple sources to make strategic decisions. A compilation of organizational data aggregated from one or more sources can be considered as a Data Warehouse. A data warehouse is a rich source of the business analytical process due to this it can be considered as a business analytical instrument.

Data Warehouse allows users to analyze data and compared it to solve working challenges and enhance business processes. Data warehouse architecture is a set of technologies for decision support aimed to allow more and quick results to be taken by the intelligence working system (executive and analyst). It acts as a physical application of a data model for the decision support system and holds the data from which strategic decisions can be taken by an organization.

DW Architecture

Data warehouse architecture is a means of describing the data transformation amongst multiple components using well define data warehouse architecture.

Every data warehouse is different, but both are distinguished by standard elements. Manufacturing applications such as the purchase, payroll, accounts and inventory management are configured for the handling of online transactions using OLTP. Using DW architecture the details from day-to-day activities can be accessed.

Data Warehouse applications are developed to support ad-hoc data and allow multiple users to access and execute it as per their credentials, this feature makes online operations (OLAP) and analytical things more flexible.

The Data Warehouse Architecture is complex since it is a data infrastructure comprising numerous sources of historical data. To construct Data Warehouse at various levels; there are three approaches most widely used these are –

1. Single Tier,
2. Two Tier, and
3. Three Tier.

This Data Warehouse architecture tire is explained below.

1) Single-Tier Architecture

A single layer aims to minimize the data volume in a data warehouse. This aim is to eradicate the duplication of records. In practice, this architecture is not commonly used. Single-tier data warehouse architecture focuses on generating a dense collection of data and reducing the amount of data storage. This design is not ideal for organizations due to their complex data specifications and multiple data sources, but it is useful for removing redundancies. This is where the 2-tier and 3-tier data warehouse architecture came into existence, as they all work with more diverse data sources.

2) Two-Tier Architecture

The two-tier Data Warehouse Architecture, which distinguishes physically accessible sources and data storage. This architecture will not be generalized and does not accommodate a wide number of end-users. Due to network constraints, it has various compatibility concerns.

3) Three-Tier Architecture

The three-tier architecture consists of the top layer, the reconciled layer, and the layer of the data warehouse. This contains a multiple source system, containing both data warehouses and data marts. Data reconciliation refers to a technology that processes information using mathematical models. Data reconciliation is expressed during data migration as a data verification process. Target data is compared with source data, to make sure that the proposed architecture is useful to move data.

For extensive, organizational structure, this architecture is extremely useful. The redundant reconciled layer is a drawback of this system. The computational methods are still a bit more away from being real-time.

Fig: Data Warehouse Architecture

Components of data warehouse architecture

1. Operational Data: An operational data is a fundamental database used for reporting purposes; it also supports real-time reporting needs for an organization.
2. External Data: The data which is coming from the external environment in the company is called external data. It is used for its accessibility as per the company’s requirement. For example- customer data, which comes from the customer end and reaches the company for analysis or other business purposes.
3. Reconciled Data: It is known as a data verification process that is used during the migration of data. The targeted data is compared with source data to make sure that the migration process is conducting smoothly.
4. Data Repository: Data repository refers to the storage of data that can be used further for analytical purposes to find out the key insights from data.
5. Data Marts: Data mart is a data storage unit used to keep records of a particular product, department, or any specific object. Within an organization, data marts are often constructed and managed by a single department.

It is important to set up what kind of database the organization or company wants and how we plan to collaborate with it when searching for insights. Evaluating who can audit information and what references they require when contemplating the architecture of the data warehouse is also important. In this reference, a data mart may be used for various groups, departments, and as per their requirements.