Tables & Relationships

Types of Tables

Fact Table

Contains numeric, measurable data (transactions).

Examples:

• Sales
  

• Orders
  

• Revenue
  

• Quantity
  

Fact tables usually contain:

• Foreign Keys (CustomerID, ProductID, DateID)
  

• Numeric columns (Sales Amount, Profit)
  

Dimension Table

Contains descriptive information.

Examples:

• Customer
  

• Product
  

• Date
  

• Region
  

Dimension tables contain:

• Primary Key
  

• Descriptive attributes (Name, Category, City)
  

Relationships

Relationships connect Fact & Dimension tables.

Example:
Sales[CustomerID] → Customer[CustomerID]

In Model View of Microsoft Power BI Desktop, you can see table relationships visually.

Types of Relationships

One-to-Many (1:*) ✅ Most Common

One customer → Many sales
One product → Many orders

Best and recommended relationship type.

Many-to-One (*:1)

Reverse view of 1:*

Many-to-Many (:)

Avoid if possible.
Can cause incorrect totals.

Cross Filter Direction

• Single Direction (Recommended)
  

• Both Direction (Use carefully)
  

Use Single direction for better performance and fewer errors.

Star Schema (Best Modeling Approach)

Fact table in center
Dimension tables around it

Example:

   Customer

        |

Product — Sales — Date
|
Region

Benefits:
Faster performance
Easy DAX calculations
Clean structure
Interview-friendly design

Avoid Snowflake schema unless necessary.

Best Practices in Data Modeling

1. Follow Star Schema

Always separate:
Fact Tables
Dimension Tables

Do NOT mix descriptive columns in fact table.

2. Use Surrogate Keys

Use unique ID columns (CustomerID, ProductID)
Avoid using text columns for relationships.

3. Avoid Many-to-Many Relationships

Instead:
Create bridge tables if required.

4. Use Measures Instead of Calculated Columns

Measures:
Dynamic
Faster
Memory efficient

Calculated columns increase file size.

5. Hide Unnecessary Columns

Hide:

• IDs
  

• Helper columns
  

• Technical columns
  

Keep model clean for report developers.

6. Create Proper Date Table

Always create a dedicated Date table.
Mark it as Date Table.

Include:

• Year
  

• Month
  

• Quarter
  

• Week
  

• Month Name
  

Time intelligence functions require proper Date table.

7. Keep Model Simple

Too many relationships
Circular relationships
Bi-directional filtering everywhere

Keep it clean and logical.

8. Naming Conventions

Examples:

Fact_Sales
Dim_Customer
Dim_Product

Measures:
Total Sales
Total Profit

Clean naming improves professionalism.

Typical Modeling Workflow