Data modeling - an indispensable basis for precise data analysis

What is data modeling?

Data modeling is the process of creating a structured data model that defines the logical relationships and attributes of the data in a system. It serves to organize and structure data so that it can be efficiently stored, retrieved, and analyzed. Therefore, good data modeling is crucial for integrating data from various sources into a BI system and preparing it for analysis and reporting.

Types of data models

Database design begins at a high level of abstraction and then becomes increasingly concrete. The data model is developed from a conceptual model to a logical model and then to a physical model.

• Conceptual Data Model: A conceptual data model operates at a high level of abstraction, describing the most important entities and their relationships without getting bogged down in technical details. It is used to understand and document business requirements.
• Logical data model: A logical data model further details the conceptual model by defining specific attributes as well as primary and foreign keys. It is still technology-independent but provides a more precise representation of the data structure.
• Physical data model: A physical data model, on the other hand, describes the concrete implementation of the logical model in a specific database management system (DBMS). It takes into account technical aspects such as data types, indexes, partitioning, and performance optimizations.

The process of data modeling

To model data as optimally as possible, an iterative process is often followed, the workflow of which usually looks like this:

1. Identifying Entities: First, data is assigned to specific business objects, known as entities. An entity thus represents a uniquely identifiable object or concept in a data model that stores relevant information. These can include, for example, customers, products, or sales.
2. Identifying Attributes: Each entity can be distinguished from others because it has one or more unique attributes. These are properties and characteristics, such as name, customer number, address, or date. These attributes are assigned to the entities.
3. Defining relationships: Anschliessend wird über Primär- und Fremdschlüssel festgelegt, wie die einzelnen Entitäten und Attribute miteinander in Beziehung stehen, also z.B. welche Verkäufe von welchen Kunden getätigt wurden. Diese Verknüpfungen können viele verschiedene Formen annehmen, wie z.B. Eins-zu-Eins-, Eins-zu-Viele- oder Viele-zu-Viele-Beziehungen.
   Ein Attribut oder eine Kombination von Attributen, die eine Entität eindeutig identifiziert, nennt man Primary Key. The value of the primary key must be unique. For example, a customer number can be the primary key of the “Customer” entity. In contrast, a foreign key is an attribute that establishes a relationship between two entities by referencing the primary key of another entity. For example, a foreign key of the “Order” entity could reference the customer number to indicate which customer placed the order.

Data Modeling Techniques and Methods

Data modeling techniques define the logical structure of data and determine how it is stored, organized, and retrieved. The three most important types are the relational, dimensional, and entity-relationship data models. Other, less commonly used models include the hierarchical, object-oriented, network, and multivalue models.

1. Relational data model

The relational data model is the oldest of the three data modeling techniques, but it is still widely used. It stores data in records with fixed formats and in tables with rows and columns. This data model has two elements:

• Key figures: Numeric values such as quantities and yields are used for mathematical calculations, such as sums or averages.
• Dimensions: In contrast, dimensions are text or numerical values that contain descriptions or locations and are not used for calculations.
  These elements are linked or related to one another using keys.

2. Dimensional data model

Dimensional models are more flexible and focus on context-related data. Therefore, they are ideal for online queries and data warehousing, as is often the case with BI systems. The key elements here are facts and dimensions:

• Facts: Facts are important data elements, such as transaction volumes.
• Dimensions: Facts are linked to reference information, such as product ID or transaction date. This reference information is called dimensions.

In dimensional models, fact tables are primary tables. This structure enables fast queries because the data for a specific activity is stored together. However, missing relationships can make it difficult to use the data, and the data structure is tied to the business function that generates and uses the data. This can make it difficult to combine data from different systems.
Two particularly common dimensional modeling schemas are the star schema and the snowflake schema. In a star schema, a central fact table is directly linked to multiple dimension tables. This structure is simple and efficient for queries, since all dimensions have only one connection to the fact table. This makes the schema easy to understand and well-suited for simple analyses and reports. The snowflake schema is an extension of the star schema in which dimensions are further normalized, meaning redundancies are minimized. To achieve this, dimension tables are divided into several linked tables, which makes the structure more complex. This can improve data integrity but also increase query complexity.

3. Entity-Relationship Model (ER Model)

The ER model visually represents business data structures. It uses symbols to represent entities, activities, and functions, and lines to represent relationships, connections, and dependencies. An ER model serves as the basis for building relational databases, where each row represents an entity and the fields contain attributes. The individual tables are, in turn, linked by keys.

Conclusion

Data modeling forms the foundation for all BI activities. Well-designed data modeling ensures that data is consistent, accurate, and easily accessible. It improves data quality and integrity and ensures that the data meets business requirements. Data organized in this efficient manner can be easily retrieved and analyzed, allowing it to be used optimally for data-driven business decisions. Despite the challenges and complexity associated with data modeling, it is therefore essential for deriving the full benefit from BI initiatives.
However, we at Parm AG are aware that the shortage of skilled professionals and the high complexity of data modeling pose a major challenge for many companies. That is why we are happy to assist you with this task. From developing a concept to data modeling and subsequent report design, our team of experts handles the data modeling for you, allowing you to focus on your core business while we lay the foundation for your BI success. Our OLAP-based solutions ensure that even fast, complex queries, various ways of presenting data, as well as individual and trend analyses are possible.