Model design of data warehouse

The data warehouse model design follows the design principle of “top-down, step-by-step refinement”.

The model design is divided into three phases:

1, conceptual model

The abstraction of the scope and use of the business from the height, that is, the division of the subject domain.

Generally divided into 8 subject areas:

Customer, service, service usage, accounting, settlement, resources, customer service, marketing

Why divide the subject domain?

The division of the subject domain is divided according to the application and needs of the business, and is used to achieve the purpose of tightly coupling data and services.

2, the logical model

Refine the topics in the conceptual model, define the relationships between entities and entities, and the properties of entities.

That is, define the role of a specific table, the constraints of a table and a table, and the fields of a table. Form an ER map.

The design of these entities is based on business rules. It can be said that this stage is mainly facing business. That is, "business driven modeling"

3, physical model

According to the logical model, build tables, indexes, and so on in the database. Data warehouse, in order to meet the needs of high performance, can increase the anti-third paradigm operation such as redundancy, hidden table constraints.

This stage is mainly for database, hardware, and performance.

Paradigm:

The first paradigm: the fields of the database table are all single attributes, and can not be subdivided.

Second Normal Form: There is no partial function dependency of a non-key field on any candidate key field in the database table.

(Partial function dependencies refer to the presence of certain fields in a combined keyword that determine a non-key field). That is, all attributes are required to depend on the primary key.

Third Normal Form: There is no transfer function dependency of a non-key field on any candidate key field in the database table.

The paradigm is backward compatible.

E.g:

1) Violation of the first paradigm. Because: the student department can be broken down into: college, department, class

2) Violation of the second paradigm. Because: The key field is the student ID and course ID, but the "course ID" determines the course name and course credits.

3) Violation of the third paradigm. Because: The key field is the student ID, but there are possible names and credits that depend on the "course ID."

Star model and snowflake model

First, they are all composed of a fact table and a set of dimension tables.

A star model, also known as dimensional modeling.

The difference is that:

Star model: The dimension table is directly connected to the fact table, and the pattern is like a star.

For example, districts, counties and cities are in the same market as the same dimension.

* Dimensional preprocessing, dimensions will be pre-classified, sorted and other pre-processing.

Snowflake model: Some dimension tables are not directly connected to the fact table, but are transferred through the dimension table, and the graphics are like snowflakes.

E.g:

Figure 1: Star model

Figure 2 Snowflake model

From the performance point of view, the star model query performance is good.

In order to improve performance, it is possible to violate the third paradigm, appropriate redundancy, and conceal constraints between tables.

Dimensional modeling

The business dimension is incorporated into the data model, hence the name dimension modeling.

In other words, for the convenience of analysis (commercial application requirements), different levels of dimensions (such as city ID, district and county ID) of the same dimension are integrated into the fact table (such as user wide table).

The dimensional model is also a star model.

It emphasizes that the dimensions are pre-processed, and multiple dimensions are grouped into a fact table to form a wide table, such as the user unified view above. Contains more than 20 dimensions. This allows you to combine dimensions to form a flexible report query.

The data warehouse of the telecommunications industry has adopted the principle of layered design.

In general, there are three layers: the interface layer, the intermediate summary layer, and the application layer.

Special emphasis is:

The middle layer is the most important layer of the data warehouse. Directly determines the performance of the data warehouse.

The general approach is:

1) Data summary. Summarize the underlying data by dimension

2) Information aggregation. Aggregate information from multiple tables into one table. The advantage of this is to avoid using table associations and improve query performance.

If layered design is a horizontal design principle, the subject domain is a vertical approach.

The specific approach is to divide the data into different subject domains from the business, a high degree of abstraction and induction.

Benefits after domain: The business is tightly coupled, easy to expand data, and easy to use.

The domain is to have obvious table naming rules, such as:

User information domain - user

Communication behavior - call

Data business - gprs

Accounting - bill

Customer service - serv

Xx sub-system data architecture diagram:

Luxury Airpod Case

Put the Airpord Case in the leather case , it will protect your Airpord .We sell Airpord Pro Case cover,hot selling airpord pro case,customized airpord pro cases,luxury airpord case,etc.

We employ the most creative designers and tech brilliant engineers to make the best cases. We believe our high-quality products with competitive prices will satisfy your needs.

The productive process :

Make the Products Mould –Cutting the fabric –Do the half products – Finish products – Cleaning –QC- Package – Shippment .

luxury airpod case,airpord case,airpord case cover,hot selling airpord case,customized airpord cases

Ysure Leather case 24/7 Support : 86 13430343455 , https://www.ysurecase.com