Microsoft Power BI Quiz: Model Size Optimization
10 questions · exam conditions
0:00
Model Size OptimizationQuestion 1 of 10

An imported Sales table contains 80 million rows and 42 columns. Report visuals use 14 columns, and model relationships require 3 additional key columns. The remaining columns are retained only because they exist in the source. Refresh duration is acceptable, but the semantic model is approaching its capacity limit.

Which action will most directly reduce the in-memory size of the model without changing report results?

Hide the unused columns in the model and set their default summarization to Do not summarize.
Remove the unused columns in Power Query while retaining all columns required by visuals and relationships.
Move the unused columns into a separate one-to-one table and keep that table loaded in the model.
Clear the data categories of the unused columns and place them in a collapsed display folder.
← Back to quizzes

Microsoft Power BI Quiz

Microsoft Power BI Quiz: Model Size Optimization

Practice Model Size Optimization in Microsoft Power BI with focused quiz questions that help you check what you know, review explanations, and build confidence with test-style prompts.

What this quiz covers

This quiz focuses on Model Size Optimization, giving you a quick way to practice the rules, question types, and explanations that matter most for Microsoft Power BI.

How to use this quiz

Try each quiz question before looking at the correct answer. Use the explanations to review missed ideas, then come back to similar questions until the pattern feels familiar.

All questions

Question 1

An imported Sales table contains 80 million rows and 42 columns. Report visuals use 14 columns, and model relationships require 3 additional key columns. The remaining columns are retained only because they exist in the source. Refresh duration is acceptable, but the semantic model is approaching its capacity limit.

Which action will most directly reduce the in-memory size of the model without changing report results?

  1. Hide the unused columns in the model and set their default summarization to Do not summarize.
  2. Remove the unused columns in Power Query while retaining all columns required by visuals and relationships. (correct answer)
  3. Move the unused columns into a separate one-to-one table and keep that table loaded in the model.
  4. Clear the data categories of the unused columns and place them in a collapsed display folder.
Explanation: When you encounter questions about semantic model size in Power BI, the key distinction to make is between visibility and storage. Data loaded into an imported (VertiPaq) model occupies RAM regardless of whether it's hidden, formatted, or organized into folders — only removing it from the query entirely prevents it from being stored. That's why B is correct. Removing unused columns in Power Query means those columns are never loaded into the VertiPaq engine in the first place. With 80 million rows and 25+ unnecessary columns, eliminating them at the Power Query stage directly reduces the in-memory footprint, bringing the model back within capacity limits without affecting any report visuals or relationship keys. A is a common trap. Hiding columns and setting summarization to "Do not summarize" are purely presentational changes — they affect what users see in the field list, not what the engine stores. All 42 columns still consume memory. C is similarly flawed. Moving unused columns into a separate one-to-one table and keeping it loaded accomplishes nothing for capacity. The data still lives in memory; you've just reorganized it across two tables, potentially adding overhead from the relationship itself. D is another cosmetic-only action. Clearing data categories and collapsing display folders are organizational tools for the report authoring experience. Like hiding, they have zero impact on the VertiPaq in-memory store. Study tip: On Power BI exam questions about model size, always ask yourself, "Does this action affect what gets loaded into memory, or just how it's displayed?" Only changes made in Power Query or data source queries can reduce storage; model-layer formatting cannot.

Question 2

An imported telemetry table contains a ReadingTimestamp column with one distinct value for almost every row. Business users analyze readings only by calendar date and never use the time of day. The column is currently displayed using a date-only format.

What should you do to reduce the column's cardinality and model size?

  1. Keep the Date/Time values and change the column's data category to Uncategorized.
  2. Transform ReadingTimestamp to the Date data type in Power Query before loading it. (correct answer)
  3. Keep ReadingTimestamp and create an additional calculated column that extracts the calendar date.
  4. Change the display format to show the year, month, and day without showing the time.
Explanation: When working with Power BI model optimization, cardinality is the key concept: the number of distinct values in a column directly impacts model size and compression efficiency. When you see a question about reducing cardinality, ask yourself — where does the redundant uniqueness come from, and how early in the pipeline can you eliminate it? Here, ReadingTimestamp is a Date/Time column with nearly one distinct value per row, because even tiny time differences make each timestamp unique. Business users only care about the calendar date, so the time portion is pure noise that inflates cardinality. The fix is to transform the column to the Date data type in Power Query before loading (answer B). This strips the time component at the source, collapsing potentially millions of distinct timestamps into just a few hundred distinct dates. Because this happens before the data loads into the model, the VertiPaq engine compresses a much smaller set of distinct values from the start. Answer A changes only metadata — the data category label has zero effect on how values are stored or compressed. Answer C actually makes things worse: you keep the original high-cardinality column and add a second column, increasing model size instead of reducing it. Answer D is the most tempting trap — changing the display format to hide the time looks like it removes it, but formatting is purely cosmetic. The underlying Date/Time values with full uniqueness remain in memory unchanged. The key study tip: in Power BI, transformation beats formatting. If a question asks about reducing model size or cardinality, the solution must change the actual data — ideally in Power Query — not just how it appears.

Question 3

A customer dimension contains 12 million distinct GUID values, and a 300-million-row fact table stores the corresponding GUID as a relationship key. The source system team can provide a durable integer surrogate key for each GUID and include that key in both tables. Reports do not display the GUID.

Which model change will provide the best size optimization while preserving relationship behavior?

  1. Split each GUID into multiple shorter text columns and create a composite relationship from those columns.
  2. Keep each GUID column, hide it from report view, and disable automatic summarization for the column.
  3. Generate independent index columns in the two Power Query queries and relate the tables by those indexes.
  4. Use the shared integer surrogate key for the relationship and exclude the GUID columns from the model. (correct answer)
Explanation: When optimizing Power BI model size, your primary lever is column cardinality and data type width. GUIDs are 36-character strings — storing 12 million of them in a dimension and 300 million in a fact table creates enormous dictionary and column segment overhead in VertiPaq's columnar engine. The fix is replacing them with the smallest data type that maintains uniqueness: an integer. Option D is correct because replacing the GUID relationship key with a shared integer surrogate key directly attacks the root cause. Integers compress dramatically better than strings in VertiPaq — they require less memory per value, encode more efficiently, and reduce dictionary size. Excluding the GUID columns entirely removes that overhead from both tables, yielding the maximum size reduction while keeping the relationship fully intact. Option A (splitting GUIDs into multiple text columns with a composite key) actually increases model complexity and storage — you now have multiple string columns instead of one, and composite relationships add query overhead. Option B (hiding the GUID and disabling summarization) is purely a cosmetic and UX change; hiding a column or changing its summarization setting has zero effect on VertiPaq storage — the data is still loaded. Option C (generating independent index columns in Power Query) is the critical trap here: indexes built separately in each query have no guaranteed alignment — row 1 in the dimension may not correspond to row 1 in the fact table, breaking relationship integrity entirely. Study tip: On Power BI optimization questions, always distinguish between display-layer changes (hiding, formatting) and storage-layer changes (data types, column removal). Only storage-layer changes meaningfully reduce model size.

Question 4

A 150-million-row Sales table contains SalesAmount and CostAmount. It also has an imported calculated column named MarginAmount whose expression is SalesAmount minus CostAmount. MarginAmount is used only in visuals that aggregate it by summation; it is not used for relationships, grouping, filtering, sorting, or row-level export.

Which change will reduce model size while preserving the required analysis?

  1. Keep MarginAmount and change its default summarization from Sum to None.
  2. Move MarginAmount into a one-to-one extension table related to Sales by transaction key.
  3. Remove MarginAmount and create a measure that subtracts total cost from total sales. (correct answer)
  4. Keep MarginAmount but reduce its displayed decimal places in the model formatting settings.
Explanation: When working with large Power BI models, you should always ask: does this column need to store a value for every row, or can the result be computed on the fly at query time? Calculated columns are materialized — they consume RAM and disk space for every single row in the table. Measures are not stored; they are calculated dynamically only when a visual requests them. With 150 million rows, removing the MarginAmount calculated column and replacing it with a DAX measure like Margin = [Total Sales] - [Total Cost] eliminates 150 million stored values from the model. Because the column is used exclusively for sum aggregation — never for filtering, grouping, relationships, or row-level export — a measure produces identical visual results. This makes C the correct answer: it reduces model size while fully preserving the required analysis. A is a trap. Changing default summarization to None is purely a UX hint to report authors; it does not remove the column from storage. All 150 million values remain in memory. B moves the column into a related extension table, but the data still exists — now with the added overhead of a relationship and a second table. Model size does not shrink; it likely grows. D is another cosmetic change. Formatting settings control display precision only; the underlying stored values and their memory footprint are completely unaffected. Study tip: On Power BI exam questions, whenever a calculated column is used only for aggregation in visuals, a measure is almost always the right optimization. Think of calculated columns as "pay at load time" and measures as "pay at query time" — for large tables, paying at query time is usually cheaper.

Question 5

A sensor model imports 200 million measurements as Decimal Number values with six decimal places. The approved reporting requirement uses values rounded to the nearest tenth, and no calculation, filter, or export may expose greater precision.

Which change is most likely to improve VertiPaq compression without violating the requirement?

  1. Format the existing measurement column to display one decimal place while retaining the source values.
  2. Round the measurements to one decimal place in Power Query and omit the original precision. (correct answer)
  3. Add a calculated column containing one-decimal values and keep the original column for future use.
  4. Change the existing column to Fixed Decimal Number while preserving all six source decimals.
Explanation: When you see a question combining VertiPaq compression with a data precision requirement, think about two things simultaneously: what reduces cardinality in the storage engine, and what the business rule actually demands. VertiPaq compresses columns by encoding distinct values. A column with 200 million measurements stored to six decimal places has enormous cardinality — potentially millions of unique values. Rounding to one decimal place collapses that cardinality dramatically (values like 3.141592 and 3.141599 both become 3.1), giving VertiPaq far fewer distinct entries to encode and store. Answer B — rounding in Power Query and discarding the original precision — achieves exactly this. The source data never enters the model at high precision, so the requirement that no calculation, filter, or export exposes greater precision is fully satisfied. This is the correct approach. Answer A is a classic trap: applying a display format only changes how numbers look in visuals. The underlying six-decimal values remain in the model and remain queryable, violating the precision requirement and providing zero compression benefit since cardinality is unchanged. Answer C doubles the problem. Keeping the original high-precision column alongside a new calculated column means full-cardinality data still lives in the model, compression doesn't improve meaningfully, and the precision violation persists in the original column. Answer D changes the data type to Fixed Decimal Number but explicitly preserves all six decimals, so cardinality is unchanged, compression doesn't improve, and the requirement is still violated. Study tip: On Power BI exam questions about VertiPaq, always ask "does this actually reduce distinct values in storage?" — display formatting and data types alone rarely do; transformation in Power Query does.

Question 6

A Sales fact table contains CustomerKey, CustomerName, CustomerSegment, and CustomerRegion. It has 250 million rows. A related Customer dimension contains the same customer attributes, with one row per customer. All reports can retrieve those attributes through the existing many-to-one relationship.

Which change best optimizes the model's column storage?

  1. Retain the customer attributes in Sales and remove them from Customer to reduce relationship navigation.
  2. Keep the attributes in both tables but hide the Sales copies from the report field list.
  3. Concatenate the three Sales attributes into one text column and remove the separate copies.
  4. Remove the customer attributes from Sales and use the copies stored in the Customer dimension. (correct answer)
Explanation: When optimizing column storage in Power BI, your first instinct should be to examine data redundancy — specifically, whether the same values are stored repeatedly across millions of rows when they could live in a smaller, separate table. Here's the core principle: Power BI uses VertiPaq columnar compression, which compresses columns based on cardinality. A column with few distinct values (like CustomerSegment or CustomerRegion) compresses extremely well in a small dimension table. But when those same low-cardinality values are embedded in a 250-million-row fact table, they consume enormous storage — even with compression — because the engine must process far more data. D is correct because removing CustomerName, CustomerSegment, and CustomerRegion from the Sales fact table eliminates hundreds of millions of redundant values. Since reports already access these attributes through the existing relationship to the Customer dimension, nothing is lost functionally. The model becomes leaner and faster. A is wrong because it reverses the star schema logic entirely — keeping attributes in the 250M-row fact table while deleting them from the dimension destroys the proper model structure and sacrifices all the compression benefits of a small dimension table. B is wrong because hiding columns from the report field list does nothing to reduce storage. Hidden columns are still fully loaded into memory — you've only changed visibility, not model size. C is wrong because concatenating multiple columns into one text column actually increases cardinality and makes compression less effective, not more. Combining values creates more unique strings, which VertiPaq compresses poorly. Study tip: On Power BI optimization questions, always distinguish between visual changes (hiding fields) and structural changes (removing columns). Only structural changes affect storage and performance.

Question 7

A Power Query query named StagingSales imports and cleans source data. A second query named Sales references StagingSales and performs the final transformations. Both queries currently have Enable load selected, but only Sales is used by relationships, measures, or reports.

Which action should you take to reduce the semantic model size without removing the reusable staging logic?

  1. Hide StagingSales in report view and leave Enable load selected for both queries.
  2. Duplicate StagingSales again and use the duplicate as the source of the final Sales query.
  3. Clear Enable load for StagingSales while keeping the query available as a transformation dependency. (correct answer)
  4. Remove the Sales query and report directly from the loaded StagingSales table instead.
Explanation: When building Power BI semantic models with multi-step query chains, you should always ask: "Does every loaded query need to exist as a table in the model?" Loading a query creates a physical table that consumes memory and storage — but a query can still function as a transformation step without being loaded. This is exactly what disabling Enable load accomplishes. When you clear Enable load for StagingSales, Power Query still executes it as an intermediate step whenever Sales refreshes — it simply stops materializing StagingSales as a separate table in the data model. The staging logic remains fully intact and reusable, but you eliminate the redundant table that was inflating your model size. Answer C is correct for this reason. A is wrong because hiding a table in report view is purely cosmetic — it does nothing to reduce model size. The StagingSales table still loads into memory and contributes to the semantic model's footprint. B is a distractor that introduces unnecessary complexity. Duplicating StagingSales creates yet another query and potentially another loaded table, which would increase model size rather than reduce it. It also defeats the purpose of having clean, maintainable staging logic. D inverts the intended architecture. Removing the Sales query and reporting from StagingSales means losing the final transformation layer, which likely exists for a reason — and it doesn't eliminate the underlying problem of an extra loaded table anyway. A useful rule of thumb: any query that exists solely to feed another query — not reports, relationships, or measures — should have Enable load disabled. This keeps your model lean while preserving your transformation pipeline.

Question 8

An imported clickstream fact table contains 500 million event rows, including a nearly unique EventID. Reports require only monthly totals by Product and Region. Users do not need event-level drillthrough, distinct-event calculations, or row-level exports. The source supports grouping before data is loaded.

Which design will produce the greatest model-size reduction while meeting the stated requirements?

  1. Group the source data by month, product, and region, calculate the required totals, and omit EventID. (correct answer)
  2. Import every event, hide EventID, and create measures that aggregate the rows at report time.
  3. Import every event, convert EventID to text, and reduce the displayed precision of numeric measures.
  4. Create a monthly calculated table while retaining the complete event table for relationship integrity.
Explanation: When a question asks about model-size reduction in Power BI, your primary focus should be on how many rows actually need to be stored in the model — because row count is the dominant driver of file size and memory consumption. The most powerful optimization here is pre-aggregating at the source. If reports only ever need monthly totals by Product and Region, there is no reason to import 500 million individual event rows. By grouping before load — summing metrics at the Month/Product/Region grain and dropping EventID entirely — you reduce what might be hundreds of millions of rows down to potentially thousands. This is exactly what A does, and it directly leverages the passage's note that "the source supports grouping before data is loaded." The result is a dramatically smaller model with no loss of required reporting capability. B is a classic trap: hiding a column removes it from the report surface but does not remove it from the in-memory VertiPaq store. You still pay the full storage cost for 500 million rows. Aggregations happen at query time, not at load time. C converts EventID to text, which actually worsens compression in most cases — VertiPaq compresses integers far better than high-cardinality strings. Reducing displayed precision affects visuals only, not stored data. D creates a calculated table for aggregates but retains the full event table "for relationship integrity" — a justification that doesn't hold since event-level drillthrough isn't required. You'd be storing the worst of both worlds. Your study tip: on Power BI design questions, always ask where the aggregation happens — source, load time, or query time. Earlier is almost always better for model size.

Question 9

A model imports AccountCode as text in both a 60-million-row fact table and an Account dimension. Every code consists of exactly eight numeric characters, such as 00018427. The code is used only as a relationship key, is never displayed, and converting it to a number preserves a one-to-one mapping between existing codes.

Which optimization is most appropriate?

  1. Convert AccountCode to Whole Number in both tables and continue using it as the relationship key. (correct answer)
  2. Retain AccountCode as text because leading zeros always make a numeric conversion unsafe for relationships.
  3. Convert AccountCode only in the fact table and allow Power BI to coerce the relationship values.
  4. Create a calculated numeric copy in both tables and retain the original text columns for validation.
Explanation: When optimizing Power BI data models, storage efficiency and relationship performance are closely tied to column data types. The core principle here is that Whole Number columns consume significantly less memory than text columns in VertiPaq (Power BI's storage engine), because text requires dictionary encoding for every unique string value, while integers compress far more efficiently. On a 60-million-row fact table, this difference is substantial. Answer A is correct because all account codes are exactly eight numeric characters with a guaranteed one-to-one mapping to integers. Converting both tables to Whole Number preserves relationship integrity, dramatically reduces memory footprint, and speeds up join operations — all without any data loss or ambiguity. Answer B is the most tempting distractor, and it contains a partial truth: leading zeros are dangerous when converting codes that will be displayed or used inconsistently. However, the passage explicitly states codes are never displayed and the mapping is one-to-one. The integer 18427 and the string "00018427" map to the same account — the relationship still works correctly as long as both sides use the same integer value. Answer C is flawed because mismatched data types across a relationship force Power BI to perform implicit type coercion at query time, degrading performance rather than improving it. You should always match types explicitly on both sides. Answer D adds unnecessary complexity. Retaining the original text columns for "validation" doubles storage consumption and defeats the entire purpose of the optimization. Your study tip: whenever a text column is used only as a key and holds numeric content, ask whether converting both sides to Whole Number is safe. If the mapping is one-to-one and display isn't a concern, conversion is almost always the right move.

Question 10

An imported model contains OrderDate, ShipDate, and DueDate. Auto date/time is enabled, so Power BI creates hidden date structures for these columns. The model already includes a complete corporate Date dimension that can support all required date calculations and role-playing relationships.

What should you do to minimize unnecessary date-related model storage?

  1. Disable Auto date/time for the file and use the corporate Date dimension for date analysis. (correct answer)
  2. Keep Auto date/time enabled and hide the three source date columns from report authors.
  3. Convert all three date columns to text and relate them to text versions of the corporate dates.
  4. Create calculated year and month columns for each date and retain the automatic date structures.
Explanation: When a Power BI model has Auto date/time enabled, Power BI silently generates a hidden date table for every date column in the model. These hidden tables consume real storage — three date columns means three separate hidden date hierarchies sitting in memory, even if you never use them. This question tests whether you understand how to avoid that redundancy when a proper Date dimension already exists. The right move is A: disable Auto date/time for the file and rely on your corporate Date dimension. Since the corporate table already supports all required date calculations and can be related to each date column as a role-playing dimension, the hidden tables add zero analytical value while wasting storage. Disabling Auto date/time removes those hidden structures entirely, directly minimizing model size. B is a common trap — hiding the source date columns from report authors doesn't remove the hidden date tables from storage; it only changes report visibility. The bloat remains. C converts date columns to text, which destroys Power BI's native date intelligence, breaks time-intelligence DAX functions, and doesn't reduce hidden date table creation in any meaningful way. It's the worst of all worlds. D compounds the problem. Adding calculated year/month columns while retaining Auto date/time structures means you're adding storage on top of existing bloat, not reducing it. A good study tip: on Power BI exam questions about model optimization, Auto date/time is almost always the culprit when hidden storage is mentioned. If a proper Date table exists, disabling Auto date/time is the standard best practice — remember the phrase "one Date table rules them all."