For more information regarding Storage Modes, refer to Storage Modes.

INTRODUCTION

One of the features of PB is the ability to select how the model we are working with should be save, and as stated, PB support 3 types of storage model. The primary benefits of the storage model is to control how the PB store information in the catches. Caching means temporarily storing data in memory.

Setting the storage model base on the types of data provides alot of advantages for the optimal performance of PB. We can set an individual storage model for each, and this have the followings benefits:

• Enhance query performance: Usually, as users interact with the visuals in the PB, this indicates that Data Analysis Expressions (DAX) queries are submitted to the semantic model, and the PB needs a good data management of catching for those queries to work optimally, thus, by setting appropriate model for each data, it will enhance the query in the PB.
• Good management of Large semantic model: With the right decision of which table to catche, we can effectively manage how the storage space is being utilised by catcihing all the large semantic model that will occupy large space in our PB.
• Data refresh optimatization: The option of either importing or catching the data into PB, it is usually effective to import data that wiil be more of use to facilitate the optimal refreshing rate for the visual display of the PB.
• Near-real time requirements: Tables with near-real time requirements might benefit from not being cached, to reduce data latency.
• WriteBack: The ability to use what-if analysis is another advantages of the having the data imported to the PB, and this allows the users to effectively change the narration of what they are doing effectively without delay from the data. Thus, tables that were not catches can display result immediately as compared to the direct query model which which still have to query the data from the data sources.

The storage mode setting in Power BI Desktop is one of three related features:

1. Composite models: Allows a report to have two or more data connections, including DirectQuery connections or Import, in any combination.

For more information, see Use composite models in Power BI Desktop.

2. Many-to-many relationships: With composite models, you can establish many-to-many relationships between tables. In a many-to-many relationship, requirements are removed for unique values in tables. It also removes prior workarounds, such as introducing new tables only to establish relationships. For more information, see Many-to-many relationships in Power BI Desktop.
3. Storage mode: With 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.

USE THE STORAGE MODE PROPERTY

The Storage mode property is a property that you can set on each table in your model and controls how Power BI caches the table data.

To set the Storage mode property, or view its current setting:

1. In Model view, select the table whose properties you want to view or set.
2. In the Properties pane, expand the Advanced section, and expand the Storage mode drop-down.

You set the Storage mode property to one of these three values:

• Import: Imported tables with this setting are cached. Queries submitted to the Power BI semantic model that return data from Import tables can be fulfilled only from cached data.
• DirectQuery: Tables with this setting aren't cached. Queries that you submit to the Power BI semantic model - for example, DAX queries - and that return data from DirectQuery tables can be fulfilled only by executing on-demand queries to the data source. Queries that you submit to the data source use the query language for that data source, for example, SQL.
• Dual: Tables with this setting can act as either cached or not cached, depending on the context of the query that's submitted to the Power BI semantic model. In some cases, you fulfill queries from cached data. In other cases, you fulfill queries by executing an on-demand query to the data source.

CONSTRAINTS ON DIRECT-QUERY AND DUAL TABLES

The two main constrain that is common to both directQuery and Dual tables are limited M transformations and restricted DAX functions in calculated columns. For more information, see DirectQuery limitations.

PROPAGATION OF THE DUAL SETTING

This involves warning message that you will get when you want to make a changes to the storage model that might impact the performance of the PB. An example below: Consider the following model, where all the tables are from a single source that supports Import and DirectQuery.

Let’s say all tables in this model are initially set to DirectQuery. If you then change the Storage mode of the SurveyResponse table to Import, the following warning window is displayed: A propagation warning will prompt as thus:

The message is giving an advice on how to effectively manage the storage without impacting the performance of the PB display. The followings are some suggested advice:

• You can set the dimension tables (Customer, Geography, and Date) to Dual to reduce the number of limited relationships in the semantic model, and improve performance.
• Limited relationships normally involve at least one DirectQuery table where join logic can't be pushed to the source systems. Because Dual tables can act as either DirectQuery or Import tables, this situation is avoided.
• The propagation logic is designed to help with models that contain many tables. Suppose you have a model with 50 tables and only certain fact (transactional) tables need to be cached. The logic in Power BI Desktop calculates the minimum set of dimension tables that must be set to Dual, so you don’t have to.
• The propagation logic traverses only to the one side of one-to-many relationships

STORAGE MODE USAGE EXAMPLE

Imagine applying the following storage mode property settings:

Setting these storage mode properties results in the following behaviors, assuming that the Sales table has significant data volume:

• Power BI Desktop caches dimension tables, Date, Customer, and Geography, so load times of initial reports are fast when they retrieve slicer values to display.
• Power BI Desktop doesn't cache the Sales table. Power BI Desktop provides the following results by not caching this table:
• Data-refresh times are improved, and memory consumption is reduced.
• Report queries that are based on the Sales table run in DirectQuery mode. These queries might take longer but are closer to real time, because no caching latency is introduced.
• Report queries that are based on the SurveyResponse table are returned from the in-memory cache, and are therefore relatively fast.

CATCHES SHOULD BE KEPT IN SYNC

Queries in the catches should be update for the optimisation of the result. If the catches is outdated, different values can be returned. The Dual storage mode is a performance optimization. It should be used only in ways that don't compromise the ability to meet business requirements. For alternative behavior, consider using the techniques described in the Many-to-many relationships in Power BI Desktop.

DATA VIEW

Data view in the PB desktop is activated if the semantic model has its storage mode set to either import or Dual. Example:

• When you select Dual and Import tables in Data view, they show cached data.
• DirectQuery tables don't show data, and a message is displayed that states that DirectQuery tables can't be shown.

CONSIDERATIONS AND LIMITATIONS

There are a few limitations for the current release of storage mode and its correlation with composite models.

The following live connection (multi-dimensional) sources can't be used with composite models:

• SAP HANA
• SAP Business Warehouse

When you connect to those multi-dimensional sources using DirectQuery, you can't connect to another DirectQuery source or combine it with imported data.

The existing limitations of using DirectQuery still apply when you use composite models. Many of those limitations are now per table, depending upon the storage mode of the table. For example, a calculated column on an imported table can refer to other tables, but a calculated column on a DirectQuery table is still restricted to refer only to columns on the same table. Other limitations apply to the model as a whole, if any of the tables within the model are DirectQuery.