Data Model Size Reduction

Why this matters

As a BI Developer, model size directly impacts refresh time, memory usage, and query speed. Smaller models load dashboards faster, reduce costs on shared capacity, and make DAX/SQL engines scan less data. Typical tasks include trimming wide fact tables, choosing efficient data types, offloading high-cardinality text to dimensions, and building aggregate tables for heavy reports.

• [ ] Speed up refreshes from 60 min to under 15 by reducing model size
• [ ] Fit your model into memory limits (e.g., shared capacity or on-prem servers)
• [ ] Improve end-user experience with faster visuals and fewer timeouts

Concept explained simply

Modern BI engines are mostly columnar and dictionary-encoded. Each column stores a dictionary of unique values and references to them. The bigger the dictionary or the more segments to store, the larger and slower your model.

Core techniques (actionable)

1. Drop and hide
   • Remove columns not used by visuals, measures, filters, or relationships.
   • Hide columns that are only needed for relationships from report view.
2. Use compact data types
   • Prefer integers for keys instead of GUIDs or long text.
   • Use date instead of datetime if time is not needed.
   • Reduce decimals to the necessary precision (e.g., 2 decimal places).
   • Use booleans/flags (0/1) instead of "Yes/No" text.
3. Reduce cardinality
   • Split descriptive text into a dimension table; keep only the integer key in the fact.
   • Bucket or round continuous values when appropriate (e.g., ages into ranges).
   • Trim whitespace and normalize casing to avoid duplicate distincts ("US" vs "us").
4. Right-size granularity
   • Filter rows you truly do not need (e.g., old inactive entities, test data).
   • Create aggregate tables for common queries (e.g., daily product sales) while keeping a detailed fact for drill-through.
5. Shift calculations out of the model
   • Prefer measures over calculated columns for on-the-fly logic.
   • Precompute stable derived fields in ETL so the BI model stores less.
6. Model structure
   • Use a star schema: narrow fact + descriptive dimensions.
   • Avoid many-to-many unless it is necessary and well-understood.

Worked examples

Example 1: Replacing text keys and trimming text

Before: fact_sales has CustomerId as a 36-char GUID and ProductName as text. Both are in the fact.

After: Create dim_customer (INT surrogate key) and dim_product; replace GUID and name with their INT keys in fact_sales. Keep text in dimensions only.

Result: Fact table shrinks significantly; dimensions handle descriptive text once.

Example 2: Reducing datetime precision

Before: OrderTimestamp stores second-level precision but reports filter by date.

After: Split into OrderDate (DATE) and OrderTime (TIME) only if needed. If time is not used, keep just OrderDate.

Result: Fewer distinct values and smaller dictionaries.

Example 3: Aggregation table

Before: Queries sum over 500M fact rows by day and product.

After: Build fact_sales_daily (one row per product, per day). Point summary visuals to the aggregate table; keep detailed fact for drill-through.

Result: Scans are against a much smaller table, speeding up visuals and reducing memory pressure.

Practice exercises

Complete the exercises below. Then check your answers in the provided solutions.

• [ ] Do Exercise 1: propose a reduction plan
• [ ] Do Exercise 2: implement with SQL

Common mistakes and how to self-check

• Mistake: Keeping long text in the fact table.
  Fix: Move to a dimension; keep only integer keys in the fact.
• Mistake: Using datetime when date is enough.
  Fix: Convert to date; split time only if required.
• Mistake: Calculated columns for dynamic logic.
  Fix: Use measures or precompute in ETL to avoid inflating the model.
• Mistake: High precision decimals for currency.
  Fix: Round to cents or store as integer minor units (e.g., cents).
• Mistake: Not removing unused columns.
  Fix: Audit dependencies; drop columns not used in visuals/relationships.
• Mistake: Over-aggregating and losing drill detail.
  Fix: Keep a detailed fact for drill-through and a separate aggregate table for visuals.

Practical projects

1. Retail Sales Slim-Down: Reduce a 200M-row sales model by moving all text attributes to dimensions, converting GUIDs to INT, and introducing a daily aggregate table. Measure size and refresh time before vs after.
2. Support Tickets Cleanup: For a ticketing model, replace free-text statuses with a dimension, bucket resolution times into ranges, and remove attachments/blobs. Validate no visual breaks.
3. Finance Precision Review: Convert currency decimals to two places or store as integer cents. Prove no material metric change and document performance impact.

Mini challenge

You inherit a fact table with 150 columns, including email, phone, notes, and JSON blobs. You must deliver a dashboard with daily revenue, by product and region, with drill to customer level. In one paragraph, outline your reduction plan: what to drop, what to dimensionalize, which aggregates to create, and any type changes. Keep it specific.

Who this is for

BI Developers, Analytics Engineers, and Data Analysts who build models for dashboards and need faster refresh and query performance.

Prerequisites

• Basic SQL (SELECT, JOIN, GROUP BY)
• Understanding of star schema (facts and dimensions)
• Familiarity with your BI tool's data types and relationships

Learning path

1. Review model usage: visuals, filters, measures
2. Apply data type optimization and column pruning
3. Split text to dimensions; add surrogate keys
4. Introduce aggregate tables for common rollups
5. Validate metrics, refresh, and performance

Next steps

• Complete the exercises below and take the Quick Test
• Run your self-check routine on a real model at work
• Document changes and their impact for your team

Progress & test

The quick test is available to everyone. If you log in, your exercise and test progress will be saved automatically.