Data Modeling, Microsoft Certification, PL-300, Power BI

Improve Performance by Reducing Granularity (PL-300 Exam Prep)

 
This post is a part of the PL-300: Microsoft Power BI Data Analyst Exam Prep Hub; and this topic falls under these sections:
Model the data (25–30%)
   --> Optimize model performance
      --> Improve Performance by Reducing Granularity

Note that there are 10 practice questions (with answers and explanations) at the end of each topic. Also, there are 2 practice tests with 60 questions each available on the hub below all the exam topics.

Overview

Reducing granularity is a key performance-optimization technique in Power BI. It involves lowering the level of detail stored in tables—particularly fact tables—to include only the level of detail required for reporting and analysis. Excessively granular data increases model size, slows refreshes, consumes more memory, and can negatively affect visual and DAX query performance.

For the PL-300 exam, you should understand when high granularity is harmful, how to reduce it, and the trade-offs involved.

What Is Granularity?

Granularity refers to the level of detail in a dataset.

Examples:

  • High granularity: One row per transaction, per second, per sensor reading
  • Lower granularity: One row per day, per customer, per product

In Power BI models, lower granularity usually results in better performance, provided it still meets business requirements.

Why Reducing Granularity Improves Performance

Reducing granularity can:

  • Decrease model size
  • Improve query execution speed
  • Reduce memory consumption
  • Speed up dataset refresh
  • Improve visual responsiveness

Power BI’s VertiPaq engine performs best with fewer rows and lower cardinality.

Common Scenarios Where Granularity Is Too High

PL-300 scenarios often test your ability to recognize these situations:

  • Transaction-level sales data when only daily or monthly trends are required
  • IoT or log data captured at seconds or milliseconds
  • Fact tables containing unnecessary identifiers (e.g., transaction IDs not used for analysis)
  • Snapshot tables with excessive historical detail that is never reported on

Techniques to Reduce Granularity

1. Aggregate Data During Data Preparation

Use Power Query to group rows before loading:

Examples:

  • Aggregate sales by Date + Product
  • Aggregate events by Day + Category
  • Pre-calculate totals, averages, or counts

This is often the best practice approach.

2. Remove Unnecessary Transaction-Level Tables

If reports never analyze individual transactions:

  • Eliminate transaction tables
  • Replace them with aggregated fact tables

3. Use Aggregation Tables (Import Mode)

Create:

  • A summary table (lower granularity)
  • A detail table (higher granularity, optional)

Power BI can automatically route queries to the aggregated table when possible.

This approach is frequently tested conceptually in PL-300.

4. Reduce Date/Time Granularity

Instead of:

  • DateTime with hours, minutes, seconds

Use:

  • Date only
  • Pre-derived columns (Year, Month)

This reduces cardinality significantly.

5. Eliminate Unused Detail Columns

Columns that increase granularity unnecessarily:

  • Transaction IDs
  • GUIDs
  • Row-level timestamps

If they are not used in visuals, relationships, or DAX, they should be removed.

Impact on the Data Model

AspectEffect
Model sizeSmaller
Refresh timeFaster
DAX performanceImproved
Visual load timeFaster
Memory usageLower

However:

  • Over-aggregation can limit analytical flexibility
  • Drill-through and detailed visuals may no longer be possible

Common Mistakes (Often Tested)

  • Keeping transaction-level data “just in case”
  • Reducing granularity after building complex DAX
  • Aggregating data in DAX instead of Power Query
  • Removing detail needed for drill-through or tooltips
  • Aggregating dimensions instead of facts

Best Practices for PL-300 Candidates

  • Optimize before writing complex DAX
  • Aggregate data in Power Query, not in measures
  • Match granularity to actual reporting needs
  • Use aggregation tables when both detail and performance are required
  • Validate that reports still answer business questions after aggregation

Exam Tips

You may be asked:

  • Which action improves performance the most?
  • Why a model is slow despite simple visuals
  • When aggregation tables are appropriate
  • How to reduce model size without changing visuals

The correct answer often involves reducing fact table granularity, not adding more DAX.

Practice Questions

Go to the Practice Exam Questions for this topic.

Leave a comment