Storage mode in Power BI Desktop
In Microsoft Power BI Desktop, you can specify the storage mode of tables. Storage mode lets you control whether Power BI Desktop caches table data in-memory for reports.
Setting the storage mode provides many advantages. You can set storage mode for each table individually in your model. This action enables a single dataset, which provides the following benefits:
- Query performance: As users interact with visuals in Power BI reports, Data Analysis Expressions (DAX) queries are submitted to the dataset. Caching data into memory by properly setting the storage mode can boost the query performance and interactivity of your reports.
- Large datasets: Tables that aren't cached don't consume memory for caching purposes. You can enable interactive analysis over large datasets that are too big or expensive to completely cache into memory. You can choose which tables are worth caching, and which aren't.
- Data refresh optimization: Tables that aren't cached don't need to be refreshed. You can reduce refresh times by caching only the data that's necessary to meet your service level agreements and your business requirements.
- Near-real time requirements: Tables with near-real time requirements might benefit from not being cached, to reduce data latency.
- Writeback: Writeback enables business users to explore what-if scenarios by changing cell values. Custom applications can apply changes to the data source. Tables that aren't cached can display changes immediately, which allows instant analysis of the effects.
The storage mode setting in Power BI Desktop is one of three related features:
- Composite models: Allows a report to have two or more data connections, including DirectQuery connections or Import, in any combination.
- Many-to-many relationships: With composite models, you can establish many-to-many relationships between tables. Many-to-many relationships removes requirements for unique values in tables. It also removes prior workarounds, such as introducing new tables only to establish relationships.
- Storage mode: You can now specify which visuals require a query to back-end data sources. Visuals that don't require a query are imported even if they're based on DirectQuery. This feature helps improve performance and reduce back-end load. Previously, even simple visuals, such as slicers, initiated queries that were sent to back-end sources. Storage mode is described further in this article.
Note
You can see the current storage mode by selecting Model view, selecting the table of interest, and then in the Advanced section of the Properties pane, view the Storage mode value.
