Understanding Data Modeling In The Data Warehouse

The data model of the data warehouse describes the reporting and presentation of data. It is described as an abstract framework understood by the relevant domains.

The data warehouse data model provides concrete and meaningful definitions of business data. It also describes how this data will be stored and processed in the data system. The data warehouse data model is the basis for the database model.

Data Model

The DWH data model is largely independent of the target system and focuses on the conceptual level. The data model is divided into three different levels: Semantic, Logical, and Physical.

Semantic:

The semantic data model is closely linked to the real world and reflects business relationships. The best-known conceptual model is the model based on relationships between entities.

Logical:

Logical data models are also based on the real world and differ from technical implementation.

Physical:

Physical data models have a technical purpose, namely to represent data in a model.

Data Schema Modeling In Data Warehouse

Star Schema Modeling

A star schema, as the name suggests, is a star structure and is suitable for analytical applications in a data warehouse environment.

Events on a star schema are also relevant for data analysis in this model. It aims to present important relationships in a quantitative and concise way.

The dimensions of the star shape allow events to be projected in different ways. They serve as a technical reference for the quantitative values of the star schema.

You can group events and analyze them in a star schema. The so-called hierarchies allow you to observe the level of clustering. Since a star schema does not allow direct hierarchization of dimensions as in the case of a snowflake schema, dimensions can be hierarchized for controlled redundancy.

The star schema consists of event tables and measurement tables. The event table contains the corresponding indicators on one side and the external keys of the measurement tables on the other side.

Benefits of the Star Schema

The star schema has the advantage of an intuitive data model and a reduced number of aggregations of business information.

Disadvantage Of Star Schema

The main disadvantage is poor responsiveness in large networks, but this can be overcome by following important modeling rules.

Snowflake Schema Modeling

The snowflake schema is another way of storing information in multidimensional data spaces. The structure of the data model is similar to the structure of the third normal form data model.

The snowflake schema is a continuation of the star schema. In the snowflake schema, each additional hierarchical level in the database is implemented by an additional table.

Therefore, the number of SQL calls in the snowflake schema, as opposed to the star schema, increases linearly with the number of aggregation paths.

Advantages of Snowflake Schema

Key numbers are stored in the event table. Depending on the level of detail of the data cube, the event table contains references to external key numbers of the lower hierarchy levels of the different dimensions.

Disadvantage Of Snowflake Schema

Each additional hierarchy level shall be created in a separate table and linked to the previous hierarchy level by a !:n relationship. Using snowflake schemas creates a very complex snowflake data model.

Data Vault Modeling

Data vault provides more flexibility and streamlined solution for building a data warehouse than traditional methods such as dimensional modeling. In addition, implementation is easier than many other data management solutions. The need for data warehousing increases as the necessity for faster and more reliable access to data increases.

Advantage Of Data Vault Modeling

Data vault is a straightforward but robust solution for data management that can be easily implemented in any size business.

Disadvantage Of Data Vault Modeling

In a data vault, the data warehouse management can be very expensive if not automated. Before data can be added to the data warehouse, it must be cleaned and transformed.

Comments are closed