Part 3: Types of Data Warehouse Architectures

Data Warehouse Architectures

In this series, the previous post was about Part 2: Data Warehousing Concepts - OLTP Vs Data Warehouse. Please go through it if you did not read.

Below are some of the widely used Data Warehouse architectures.

• Basic Data Warehouse Architecture
• Data Warehouse Architecture with Staging Area
• Data Warehouse Architecture with Staging Area and Data Marts

Basic Data Warehouse Architecture

In the Basic Data Warehouse, the data from the transaction system (OLTP) is loaded in to the Data Warehouse and finally it reaches the end users.

Advantages

• Development effort is comparatively less.
• By the use enterprise Data Warehouse, provide single version truth for analysis.

Disadvantages

• Depends on OLTP system.
• OLTP down times affects the Data Warehouse load.
• Data Warehouse load may slow down the OLTP performance.
• Data needs to be loaded before the next OLTP up time.

Data Warehouse Architecture with Staging Area

In the Data Warehouse with Staging Area, the data from the OLTP System is loaded in to a Staging Area first. The loaded in the Staging Area is cleansed to some extent and the cleansed data is loaded to the Data Warehouse. The frequency of load will be daily, weekly, monthly, etc based on the business requirement.

Advantages

• Less dependent on OLTP System since the data is staged.
• OLTP Down times will not affect the Data Warehouse load.
• Will get enough time to complete the Data Warehouse load.
• Data Warehouse load will not affect the OLTP performance.

Disadvantages

• Development time is more.

Data Warehouse Architecture with Staging Area and Data Marts

In the Data Warehouse with Staging Area and Data Mart, architecture is same as the previous architecture. The only difference here is the Data  Marts. A data mart is a subset of data warehouse that is designed for a particular line of business; such as sales, Purchase, Stock, etc. Each data mart is built from the Enterprise Data Warehouse. Only some group of users will have access to the specific Data Marts. For example, Only Analysts from Sales department will have access to the Sales Data Mart.

Advantages

• Less dependent on OLTP System since the data is staged.
• OLTP Down times will not affect the Data Warehouse load.
• Will get enough time to complete the Data Warehouse load.
• Data Warehouse load will not affect the OLTP performance.
• User specific Data Marts will speed up the performance for each department.

Disadvantages

• Development effort is more.
• Not all department will have access to the entire data or Enterprise Data Warehouse.

In my next post, I will explain about the types of Data Warehouses.

If my post helped you understand the concept. Please like and share with your friends and also share your comments with me to make the content better. Thanks for visiting my Blog.