Deliver an effective and flexible Data Warehouse solution: Part 2nd: Warehouse Design and data modeling

Brief introduction

The business environment is changing rapidly, and so is the type of business data. A successful Data warehouse solution is based on flexible design that adapts to changing business data. The architecture of data Warehouse and the modeling of warehouse data are the core process of warehouse design.

The schema of the Data Warehouse

When you use the data model to capture business requirements, you have completed some of the work in the Data Warehouse design. However, the formal data warehouse design should start with the architecture of the Data Warehouse.

A warehouse architecture is a key decision based on a number of factors, including the current infrastructure, business environment, desired management and control structures, the commitment and scope of implementation, the capabilities of the technology environment used by the enterprise, and the resources available.

Schema selection

The warehouse schema determines the location of the Data Warehouse and data mart itself, as well as the location where the control resides, or vice versa. For example, data can reside in a centrally managed central location. Alternatively, the data can reside in a centralized or independently managed distributed local and/or remote location.

There are some of the following schema choices:

Data Warehouse for Business scope (business-wide)

A separate data mart

Interconnected data marts

These schema selections can also be used in combination. For example, a data warehouse schema can be physically distributed or centrally managed.

Business-wide Data Warehouse architecture

A business-wide data Warehouse is a data warehouse that will support an entire or a large portion of the business, requiring a more fully integrated data warehouse with higher data access and usage across departments and lines of business (line of business). This is to design and construct the warehouse based on the entire business requirement. It can be viewed as a common repository of decision support data that can be used across the enterprise, or a large subset of them. The term "business scope (business-wide)" Used here reflects the scope of data access and use, not the physical structure. In the whole enterprise, the business scope Data Warehouse can be centralized or distributed in physics.

Independent Data Mart Architecture

A stand-alone data mart architecture implies a separate data mart that is controlled by a particular workgroup, department, or line of business and is built solely to meet its needs. In fact, they do not even have any connectivity to data marts in other workgroups, departments, or lines of business.

Figure 1. Data Warehouse Schema Selection

Interconnected Data mart schemas

The Interconnected data mart architecture is basically a distributed implementation. Although different data marts are implemented in specific workgroups, departments, or production lines, they can be integrated and interconnected to provide a more global view of the business scope of the data. In fact, at the highest level of integration, they can become business-wide data warehouses. This means that end users in one department can access and use data from data marts in another department.

Which schema should you choose?

If your customer's business and data sources are relatively centralized, the business-wide centralized Data Warehouse architecture is the smartest choice. This is in fact a common situation for intermediate-market companies. Otherwise, interconnected data marts and business-wide distributed data warehouses are a more practical option for geographically widely distributed businesses.

A stand-alone data mart architecture is not a good idea, because it violates the key concept of data warehousing: data integration.