3.3.1. Removing Unnecessary Data and Reducing Granularity

💡 First Principle: VertiPaq's cost is driven by the number of rows and, above all, by the number of distinct values in each column. Every column you remove, every row you filter, and every high-cardinality column you simplify reduces memory, speeds refresh, and usually speeds queries.

Techniques, roughly in order of impact and ease:

• Remove unnecessary columns in Power Query (Choose Columns / Remove Columns). Audit for columns no report uses: audit fields, GUIDs, comments, duplicated descriptions. Removing them is the single most effective size reduction. Hiding a column doesn't reduce size; only removing it does.
• Remove unnecessary rows: filter out history nobody analyzes (keep three years, not fifteen), test records, cancelled transactions if they're never reported. Use parameters (2.1.4) and incremental refresh for large histories.
• Reduce column cardinality: split Date/Time into separate Date and Time columns (or drop time); round decimals to the precision reported; replace long text with codes where a dimension holds the description; avoid storing unique transaction IDs on facts unless needed for drillthrough.
• Reduce granularity (summarize): if reports never go below daily product totals, Group By date and product in Power Query to aggregate transactions. A table of 50 million transaction lines may become 2 million daily rows. When some reports need detail and others don't, keep a summarized Import table for most visuals and, in advanced designs, use aggregations over a detailed DirectQuery table.
• Choose efficient data types: integers for keys, Fixed Decimal for currency, Date instead of Date/Time when time isn't needed.
• Disable auto date/time and remove unused calculated columns, which also consume memory.
• Disable load for staging queries (2.3.6).

Granularity trade-off: summarizing removes the ability to drill to detail. Confirm the reporting requirement first; the exam often says "users analyze sales by month and product only," which licenses aggregation.

⚠️ Exam Trap: Hiding a column or setting it to Don't summarize doesn't reduce model size. When the qualifier is "minimize model size," the answer removes columns or rows, reduces cardinality, or summarizes, all in Power Query before load.

Reflection Question: A Sales table has a TransactionDateTime column with values to the second and 40 million rows; reports only use daily trends. Name two different transformations that shrink the model and explain why each one reduces memory.