Data Modeling

What is Data Modeling?

Data Modeling is the process of organizing, structuring, and defining how data is stored, managed, and used in a database system. It helps in designing the logical structure of a database to ensure efficient storage, retrieval, and relationships between data.

Why is Data Modeling Important?

• Ensures data consistency and accuracy

• Improves database performance and scalability

• Helps in understanding business requirements

• Reduces redundancy and duplication of data

Types of Data Models

1. Conceptual Data Model

• High-level overview of data structure

• Focuses on business concepts rather than technical details

• Example: Representing Customers, Orders, and Products as entities without defining attributes

2. Logical Data Model

• More detailed than the conceptual model

• Defines attributes, data types, and relationships

• Example: A Customer entity having attributes like Name, Email, and Phone Number

3. Physical Data Model

• Defines how data is stored in a database

• Includes tables, columns, data types, constraints, and indexes

• Example: Implementing a relational database with SQL tables and foreign keys

Key Components of Data Modeling

• Entities – Objects or concepts (e.g., Customers, Orders, Products)

• Attributes – Properties of entities (e.g., Customer Name, Order Date)

• Relationships – Connections between entities (e.g., One customer can place multiple orders)

• Primary Key (PK) – Unique identifier for a record (e.g., Customer ID)

• Foreign Key (FK) – Links one table to another (e.g., Customer ID in the Orders table)

Logical and Physical Layers in Tableau Data Modeling

Tableau's data modeling architecture consists of two layers:

1. Logical Layer (Higher-level data relationships)

2. Physical Layer (Underlying table structures)

Both layers exist within Tableau’s Data Source Tab, specifically in the Relationships & Join Canvas.

---

Logical Layer (Relationship Model)

Definition:

The Logical Layer in Tableau defines how different tables are related to each other without physically merging them. It uses relationships instead of joins, keeping tables separate and dynamically combining data when needed.

Key Features:

• Uses N:N relationships (flexible, avoids duplication)

• Preserves granularity of data

• Supports multiple fact tables in a single model

• Reduces data redundancy

Example:

If you are building a Sales Dashboard, you can define a logical model with:

• Fact Table: Sales

• Dimension Tables: Customers, Products, Date

Note : Tableau automatically determines the best way to join data based on the context of the visualization.

---

Physical Layer (Join Model)

Definition:

The Physical Layer is where tables are physically joined (using INNER, LEFT, RIGHT, or FULL OUTER JOINs) or unioned in the Tableau Data Model.

Key Features:

• Uses Joins & Unions

• Merges tables into a single flat table

• Can cause data duplication or incorrect aggregations if not handled properly

• Provides optimized query performance for certain cases

Example:

If you join Sales Table with Customers Table using a LEFT JOIN, all sales records will be kept, even if no customer data exists for some sales.

---

Logical vs. Physical Layer: Key Differences