PBI offload transformations from PBI to SQL

Hello mjoseph111,

It’s not only about where transformations happen, but also what data Power BI stores in the in-memory model (VertiPaq).
When you switched to a SQL view, you probably changed how much data or how it is typed was sent to Power BI.

Common causes:

• More columns returned

  • Your SQL view may now expose more fields than the Power Query transformations originally kept.

  • Even if those extra columns aren’t used in visuals, Power BI still imports them (and compresses them).

• Less compression due to data types

  • Power BI’s internal VertiPaq engine is highly optimized for compression — especially on text columns converted to categorical or integer keys.

  • SQL views often return raw text, decimals, or datetime types that reduce compression efficiency.

  • Example: converting a country code from integer → varchar(50) can easily double storage.

• No query folding or reduced folding efficiency

  • When transformations were done in Power Query, they might have folded efficiently to SQL.

  • But if the SQL view now returns a pre-aggregated or denormalized dataset, Power BI has to load more data (fewer filters applied upstream).

• Materialized or pre-calculated columns

  • If the SQL view computes derived columns (like CASE WHENs or string concatenations), those columns often have lower cardinality compression performance.

• Data refresh settings

  • If you disabled Load to model for certain tables before, but re-imported them via the new SQL view, you may now be storing more tables in the PBIX file.

• Encoding / locale changes

  • SQL collation and Power BI text encoding differences can slightly inflate model size, especially with Unicode strings (nvarchar).

1. Use DAX Studio → View Metrics

   • Sort by Column size descending to see which fields grew most.

   • Note the Data type and Distinct count — high cardinality columns are main offenders.

2. Profile SQL vs Power Query outputs

   • Run both your old Power Query logic and the SQL view as queries in SSMS or Power BI.

   • Compare:

     • Number of columns

     • Row count

     • Column data types

     • Distinct values per column

3. Check Power Query “Enable load”

   • Ensure you’re only loading necessary tables into the data model.

   • Disable “Enable load” for staging or intermediate queries.

4. Evaluate import vs DirectQuery

   • If data volume is high, consider using DirectQuery or hybrid tables to avoid fully importing large datasets.

5. Run the “Manage Columns” audit

   • Keep only the columns used in visuals, relationships, and measures.

   • Every unused column adds to VertiPaq storage.

Docs : 

- https://daxstudio.org/docs/features/model-metrics/

Hope it can help you!

Best regards,

Antoine

My recommendations are

Optimize SQL View Output – Ensure the SQL view only returns the required columns and filtered rows. Avoid unnecessary joins or calculated fields that increase distinct values and reduce compression.

Check Data Types – Align SQL column data types with Power BI-compatible types (e.g., use INT, NUMERIC, or short text fields). Mismatched or wide data types can inflate PBIX size.

Run VertiPaq Analyzer – Use DAX Studio → View Metrics to identify large tables or high-cardinality columns, then remove or optimize them to reduce model size and improve performance.


This will help you to detemine the actual cause of size increase ?