Data Warehouse Architecture

In this article, we learn about data warehouse architecture.

DATA WAREHOUSE

• Data warehousing indicates the whole set of interrelated activities involved in designing, implementing, and using a data warehouse.
• The data warehouse itself, together with additional data marts. Data marts contain the data and the functions that allow the data to be accessed visualized and perhaps modified.

DATA WAREHOUSE ARCHITECTURE

DESIGN APPROACHES OF DATA WAREHOUSE

TOP-DOWN

• The top-down methodology is based on the overall design of the data warehouse.
• It is time-consuming and more time required for the initial set up.

BOTTOM-UP

• This model contains consistent data marts and these data marts can be delivered quickly.
• Data is cleansed, transformed, and loaded into this layer using back-end tools.

MIXED

• The mixed methodology is based on the overall design of the data warehouse within a prototyping approach.
• This approach is highly practical and usually preferable.

ETL TOOLS

• ETL performs three main functions Extraction Transformation and Loading of data into the data warehouse.
• Extraction- Data are extracted from the available internal and external sources.
• Transformation- The goal of the cleaning and transformation phase is to improve the quality of data extracted from the different sources.
• Loading- After extraction and transformation, data are loaded into the tables of the data warehouse.

METADATA

• Metadata is simply defined as data about data. The data that is used to represent other data is known as metadata.
• In the Data Warehouse Architecture, meta-data plays an important role as it specifies the source, usage, values, and features of data warehouse data. It also defines how data can be changed and processed. It is closely connected to the data warehouse.
• Metadata helps in the decision support system for the mapping of data when data is transformed from the operational environment to the data warehouse environment.
• Metadata also helps in summarization between lightly detailed data and highly summarized data.

If you are a newbie to database learning — SQL Server recommended is the following must-watch video: -