Data model best practices in Power BI - Deep Dive

Overview - Data model best practices

What is it?

A data model is a structured way to organize and connect data tables so that you can analyze information easily. Data model best practices are guidelines to build these models efficiently, making reports faster and easier to understand. They help ensure your data is accurate, consistent, and ready for insightful visualizations. Good data models reduce errors and improve performance in tools like Power BI.

Why it matters

Without good data models, reports can be slow, confusing, or wrong. Imagine trying to find answers in a messy pile of papers versus a well-organized filing cabinet. Poor models make it hard to trust your insights and waste time fixing problems. Best practices help you build models that save time, avoid mistakes, and make your data work for you.

Where it fits

Before learning data model best practices, you should understand basic data concepts like tables, columns, and relationships. After mastering these practices, you can learn advanced topics like DAX calculations, performance tuning, and complex data transformations.

Mental Model

Core Idea

A good data model is like a clean, well-organized map that connects all your data points clearly and efficiently for fast and accurate insights.

Think of it like...

Think of a data model like a city's road map: well-planned roads (relationships) connect neighborhoods (tables) so you can travel (analyze) quickly without getting lost or stuck in traffic.

┌─────────────┐     ┌─────────────┐
│  Customers  │────▶│  Orders     │
└─────────────┘     └─────────────┘
       │                   │
       ▼                   ▼
┌─────────────┐     ┌─────────────┐
│  Geography  │     │  Products   │
└─────────────┘     └─────────────┘

Build-Up - 7 Steps

1

Foundation

Understand tables and columns

Concept: Learn what tables and columns are and how they store data.

In Power BI, data is stored in tables, similar to spreadsheets. Each table has columns that hold specific types of information, like names, dates, or numbers. For example, a 'Customers' table might have columns for Customer ID, Name, and City.

Result

You can identify and organize your raw data into tables with clear columns.

Understanding tables and columns is the base for building any data model because all data lives here.

2

Foundation

Learn relationships between tables

Concept: Discover how tables connect using relationships to combine data.

Relationships link tables by matching values in columns, like connecting Customer ID in 'Customers' to Customer ID in 'Orders'. This lets you analyze data across tables, such as finding all orders for a customer.

Result

You can join data from different tables to answer complex questions.

Knowing how relationships work lets you combine data meaningfully instead of keeping it isolated.

3

Intermediate

Use star schema design

🤔Before reading on: do you think having many-to-many relationships is better than a star schema? Commit to your answer.

Concept: Learn to organize tables in a star schema with fact and dimension tables for simplicity and speed.

A star schema has one central fact table with numeric data (like sales) connected to dimension tables with descriptive data (like customers or products). This design reduces complexity and improves query speed.

Result

Your model becomes easier to understand and faster to query.

Using star schema avoids confusing many-to-many links and helps Power BI optimize calculations.

4

Intermediate

Avoid calculated columns when possible

🤔Before reading on: do you think calculated columns always improve performance? Commit to your answer.

Concept: Understand when to use measures instead of calculated columns to keep models efficient.

Calculated columns add new data to tables but increase model size and slow refresh. Measures calculate results on the fly without extra storage. Use measures for calculations that depend on filters or user selections.

Result

Your reports refresh faster and use less memory.

Knowing when to use measures over calculated columns helps keep your model lean and responsive.

5

Intermediate

Set correct data types and formats

Concept: Ensure each column has the right data type and format for accuracy and performance.

Data types like text, number, date, or boolean tell Power BI how to handle data. For example, dates should be date type, not text. Correct types enable proper sorting, filtering, and calculations.

Result

Your data behaves correctly in visuals and calculations.

Correct data types prevent errors and improve model efficiency.

6

Advanced

Optimize model size and performance

🤔Before reading on: do you think more columns always mean better insights? Commit to your answer.

Concept: Learn techniques to reduce model size and speed up reports.

Remove unused columns, reduce cardinality by grouping values, and avoid unnecessary calculated columns. Use aggregations and incremental refresh for large datasets. These steps make your model smaller and queries faster.

Result

Reports load quickly and handle large data smoothly.

Optimizing size and performance ensures your reports stay fast and scalable.

7

Expert

Manage relationships and filter directions carefully

🤔Before reading on: do you think setting all relationships to both directions is always best? Commit to your answer.

Concept: Understand how relationship directions and cross-filtering affect calculations and model behavior.

Relationships can filter data one way or both ways. Both directions can cause ambiguous filters and slow performance. Use single direction by default and only enable both directions when needed for specific calculations.

Result

Your model avoids confusing filter paths and runs efficiently.

Mastering filter directions prevents subtle bugs and performance issues in complex models.

Under the Hood

Power BI stores data in a compressed, columnar format called VertiPaq. It builds relationships by linking keys between tables, enabling fast filtering and aggregation. When you create visuals, Power BI uses these relationships and the VertiPaq engine to quickly retrieve and calculate data based on user interactions.

Why designed this way?

The star schema and columnar storage were chosen to optimize speed and simplicity. Columnar compression reduces memory use, and clear relationships avoid complex joins. This design balances flexibility with performance, unlike older row-based or flat table models.

┌───────────────┐
│   VertiPaq   │
│  Compression │
└──────┬────────┘
       │
┌──────▼────────┐
│  Column Store │
└──────┬────────┘
       │
┌──────▼────────┐
│ Relationships │
│  (Keys & FKs) │
└──────┬────────┘
       │
┌──────▼────────┐
│  Query Engine │
│  (Filters &   │
│  Aggregations)│
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Do you think adding more calculated columns always improves report speed? Commit to yes or no.

Common Belief: More calculated columns make reports faster because calculations are pre-made.

Reality: Calculated columns increase model size and slow refresh times, often making reports slower.

Why it matters: Using too many calculated columns can cause slow report loading and poor user experience.

Quick: Do you think relationships set to both directions always fix filtering issues? Commit to yes or no.

Common Belief: Setting relationships to both directions is always better for filtering data correctly.

Reality: Both directions can cause ambiguous filters and slow performance; single direction is safer unless both are needed.

Why it matters: Misusing filter directions can cause wrong results and hard-to-debug errors.

Quick: Do you think a flat table with all data is better than a star schema? Commit to yes or no.

Common Belief: Putting all data in one big table is simpler and better for analysis.

Reality: Flat tables are harder to maintain, slower to query, and less flexible than star schemas.

Why it matters: Ignoring star schema design leads to complex, slow, and error-prone models.

Quick: Do you think data types don’t affect performance or accuracy? Commit to yes or no.

Common Belief: Data types are just labels and don’t impact how data works or performs.

Reality: Wrong data types cause errors in calculations and slow down queries.

Why it matters: Incorrect data types can produce wrong insights and confuse users.

Expert Zone

1

Using inactive relationships with USERELATIONSHIP in DAX allows flexible model behavior without cluttering the model with multiple active links.

2

Reducing cardinality in columns (like grouping many unique values) drastically improves compression and query speed, often overlooked by beginners.

3

Composite models let you combine DirectQuery and Import modes, balancing real-time data needs with performance, but require careful design to avoid slowdowns.

When NOT to use

Avoid star schema when working with highly normalized source systems requiring many-to-many relationships; instead, use snowflake schema or advanced DAX techniques. Also, avoid complex bidirectional filters in large models; use explicit measures or aggregation tables instead.

Production Patterns

Professionals often build a clean star schema with fact and dimension tables, use measures over calculated columns, optimize model size by removing unused columns, and carefully manage relationship directions. Incremental refresh and aggregations are common for large datasets to keep reports responsive.

Connections

Database Normalization

Data model best practices build on normalization principles to organize data efficiently.

Understanding normalization helps you design dimension tables that avoid redundancy and improve data quality.

Software Engineering Modular Design

Both concepts emphasize breaking complex systems into clear, manageable parts.

Seeing data models as modular components helps maintain and scale BI solutions like well-designed software.

Urban Planning

Like city planners design roads and zones for smooth traffic, data modelers design tables and relationships for smooth data flow.

Recognizing this connection highlights the importance of clear structure and flow in complex systems.

Common Pitfalls

#1 Creating many-to-many relationships without understanding their impact.

Wrong approach:

Setting multiple many-to-many relationships active between tables without filters.

Correct approach:

Use bridge tables or star schema design to avoid many-to-many relationships or set them inactive and use DAX to control filtering.

Root cause:

Misunderstanding how many-to-many relationships affect filter context and performance.

#2 Using text columns as keys in relationships.

Wrong approach:

Linking tables using text columns like customer names instead of numeric IDs.

Correct approach:

Use unique numeric keys (IDs) for relationships to ensure accuracy and performance.

Root cause:

Not knowing that text keys are slower and prone to errors due to duplicates or typos.

#3 Leaving unused columns in the model.

Wrong approach:

Importing entire source tables with all columns, even those not used in reports.

Correct approach:

Remove unused columns during data load to reduce model size and improve speed.

Root cause:

Assuming more data is always better without considering performance impact.

Key Takeaways

A well-designed data model organizes data into clear tables connected by relationships, enabling fast and accurate analysis.

Using a star schema with fact and dimension tables simplifies your model and improves performance.

Measures are usually better than calculated columns for calculations because they keep the model smaller and faster.

Setting correct data types and managing relationship directions carefully prevents errors and slowdowns.

Optimizing model size by removing unused columns and reducing cardinality keeps reports responsive and scalable.