Storage modes and data connections
Choosing how Power BI connects to data decides speed, freshness and size. The exam describes requirements and expects you to match the mode.
The modes
| Mode | Data lives | Strength | Weakness |
|---|---|---|---|
| Import | In the model (VertiPaq cache) | Fastest visuals, full DAX | Data is as old as the last refresh; size limits |
| DirectQuery | At the source | Near real time, no size limit | Slower; some DAX and Power Query features limited |
| Dual | Both | Behaves as Import or DirectQuery as needed | Needs composite model |
| Live connection | In Analysis Services or a published semantic model | Reuses a central model | You cannot change the model |
A composite model mixes storage modes in one model, for example a big fact table in DirectQuery and small dimensions in Dual. You can also add aggregation tables in Import that answer common queries, with the detail table in DirectQuery.
Choosing quickly
- “Fastest report”, “minimize query time”, “data refreshed daily”: Import.
- “Real time”, “data too large to import”, “always the latest”: DirectQuery.
- Dimension next to a DirectQuery fact table: Dual.
- Real-time dashboards from devices: a streaming dataset, where tiles on a dashboard update as data arrives.
Refresh and gateways
Import models refresh by schedule (up to 8 times a day on Pro, 48 on Premium or Fabric capacity) or on demand. Data sources behind a firewall need an on-premises data gateway for scheduled refresh and for DirectQuery. Cloud sources such as Azure SQL or SharePoint Online usually need no gateway. Use personal mode only for single-user needs; the standard mode is for shared use.
Incremental refresh loads only new or changed partitions, using the reserved parameters RangeStart and RangeEnd and a date filter in Power Query. It cuts refresh time on large tables.
Dataflows
A dataflow holds Power Query logic in the service so several semantic models can reuse the same cleaned tables. Use it when many reports need the same transformation, instead of repeating it in each PBIX file.
Reducing model size
- Remove unused columns and rows you never analyze.
- Lower the grain by summarizing before load.
- Replace high-cardinality columns, such as a datetime, with separate date and time columns or drop the time.
- Use integer types rather than text for keys.
- Turn off Auto date/time when you have your own date table.
Exam traps
- Import data does not update when the source changes; a refresh does.
- DirectQuery is not automatically better for freshness if the requirement also says fast rendering.
- A gateway is for on-premises or private sources, not for every refresh.
- Live connection and DirectQuery are different: a live connection has no local model.
- Disabling query folding never improves performance.
Practice questions
Choose an answer, then review the explanation to check your understanding.
1. A report must show data up to the previous day, render fast and minimize online processing against the source. Which connection mode fits?
Show answer
Answer: B.Import caches data in the model, so visuals are fast and nothing hits the source at view time. A daily refresh covers up to yesterday.
2. A model contains a large fact table in DirectQuery and small dimension tables. How do you speed up slicers on the dimensions?
Show answer
Answer: A.Dual tables act as Import or DirectQuery depending on the query, which avoids slow cross-source joins and keeps slicer queries local.
3. A dataset on an on-premises SQL Server must refresh on a schedule in the Power BI service. What is needed?
Show answer
Answer: B.The service cannot reach a private network on its own, so scheduled refresh needs an on-premises data gateway.
4. Refresh fails with "exceeds the maximum allowable memory allocation for your tier" on an invoice table. What is the best fix?
Show answer
Answer: B.Memory limits are about model size. Aggregating rows and removing unused columns shrinks the table.