Different Layers in Data Warehouse Architecture - 【2020】
Data Warehouse Layers

Different Layers in Data Warehouse Architecture

The three-tier architecture model for data warehouse proposed by the ANSI/SPARC committee is widely accepted as the basis for modern databases. The objective of the model is to separate the inner-physical, conceptual-logical and outer layers.

Each layer has a specific purpose to receive the data to be stored, store it in a structured manner and make it available again to the user or the application system.

The transformation rules for the exchange of information between the layers are defined. This structure is important to meet the requirements of a database system.

In the following articles the structure according to the ANSI architecture model is explained and presented in an overview. The design of the database is based on this model. It reflects the layers of the architecture. The database design is necessary for the concrete application of the databases.

In this article we will see:

  • Internal physical layer
  • Logical-conceptual layer
  • Outer layer


Read also
What is a Data Warehouse for a Sales Manager?

What is Inner layer in the 3-layer architecture?

The inner layer of the model describes the physical storage structures and access mechanisms of a database. To this end, the layer implements a data storage and management scheme. This includes, for example, the structure of the data, the storage of the data and the access methods by which the stored data can be retrieved.

This layer describes how the data is stored. These specifications are made by the design of the physical database when a database model is implemented.

To make data available to the higher levels, there are transformation rules between the layers. However, there is only one connection between two layers that are directly above each other.

Transformation process of the internal conceptual layer

This process represents nothing more than a series of rules necessary for the exchange of data between the internal and conceptual schema. This level describes how the data of the internal schema can be accessed

Read also
Data Warehouse vs Data Lake vs Data Mart: Characteristics

The advantage of this procedure is that changes in the internal scheme have no effect on the conceptual level. In this case, only the transformation rules have to be adapted to still allow access to the physically stored data (e.g. by adapting the access paths).

What is Conceptual layer in the 3-layer architecture?

The conceptual layer or level represents the logical structure of relationships in the real world, i.e. the data relevant to the user. In relational databases, the relational database model is used for this purpose.
This schema is usually pre-designed using an ER diagram during the creation of the logical database design.

The conceptual layer is a comprehensive description of all the data that must physically persist and the relationships between them.

Based on this model, summaries and data sections are made available to external schemas or user views.

What is the Process of transformation of the conceptual layer?

The logical-conceptual model is the intermediate layer of the 3-layer architecture and connects the external schema with the internal physical layer. Consequently, there are two transformation processes, one towards the external layer and the other towards the internal layer.

Read also
How to Build a Data Warehouse With OSS

Information is transferred to the external layer about which objects are contained in the logical layer and which data they represent in the physical layer. The inner layer, in turn, knows the access paths and links them to the objects.

The logical sections of the model are provided in the form of views for the outer layer or the user view. Each application or external view contains a section of the data according to its purpose. Views are provided on a user and thematic basis to manage access protection, data protection and access authorizations.

Changes here also have no effect on the external view. Settings are only necessary in the transformation rules if there is a change in the logic model. This guarantees the independence of the data, which a modern database system should guarantee.

What is a External layer in the 3-layer architecture?

The outer layer contains various views for users. Each view describes the properties of a group of users, who thus see part of the stored data. The rest of the data and the entire data model of the logical layer is often hidden from individual users. This ensures that users can only see information or data that they are allowed to see.

Read also
What is Real Time Data Warehouse?

The user cannot access the conceptual layer. Adjustments are usually made and managed by the database creators.

The separation of the external view from the conceptual layer ensures independence between the layers. The views are made available or integrated into the applications.

What is the Process of transformation of the external conceptual layer?

Between the conceptual and internal vision, there is also a process of transformation that includes and carries out the rules of data supply and access. In the transformation, the relationship between the external and the conceptual vision is stored, i.e. what data must be provided.

Layers in Data Warehouse Architecture FAQS

What are the three layers of data warehouse architecture?

All data warehouse architecture includes the following layers: Data Source Layer. Data Staging Layer. Data Storage Layer.


Which data warehouse layer contains information about the data warehouse functioning such as system performance and user access details?

System Operations Layer

Read also
Data Warehouse: Solutions for Small Businesses

This layer includes information on how the data warehouse system operates, such as ETL job status, system performance, and user access history.


Related Words:

  • data layer architecture
  • data warehouse layers
  • what are the different layers in a data warehouse