Storage Modes
Power BI Lets you control the storage modes in Power BI Desktop. Storage Modes defines weather or not the Power BI desktop caches the data in In-memory for reports. Storage mode specifies how the data is queried with the cached data or from direct data source.
You Specify storage modes in following two ways :
- Specify Storage mode for whole dataset(At the Time of data Load)
- Specify Storage mode for each dataset (After Data Load)
Advantages
The Efficiency of your reports and datasets depends on Storage mode you select . Storage mode provides the following advantages :
Query performance:
When user interacts with the visual , the DAX(Data analysis expressions ) are submitted to the dataset. By using the storage modes properly , you can cache data into memory , and increase the query performance. Correct Data Storage mode increases the query performance.
Large Datasets :
Suppose you have a transactional table that has millions of data , that is too large and expensive to store in memory by using import data. You can use direct query storage mode. So in this way , you can specify which table is worth caching.
Data refresh optimization:
Data refresh time depends on the size of cache. The less data is cached ,the less time refresh will take.
You do not need to refresh the data that is not cached. You can reduce the refresh time by caching the data that is necessary to meet your business requirements.
Types of Storage Modes:
There are basically three types of storage modes :
Import : Import mode cache you data from data source to in memory cache .when you set submit a DAX(Data Analysis Expression) query to power bi dataset , it will return you the result from the cached data.
To keep data updated you have to refresh you datasets accordingly.
Direct Query :
When you set storage mode to direct query data is not cached in your memory. When you submit a DAX (Data Analysis Expression) Query , it will return the result by querying the data source directly. the performance of your reports is heavily depends on the speed and efficiency of you data source.
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 dataset. 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.
Use the Storage mode
- Specify Storage mode for whole dataset
1. At the time of Get Data , Any of Storage mode you choose for your dataset , is set for whole dataset. you can change later if you have selected DirectQuery Mode.
Note : Changing the Storage mode of a table to Import is an irreversible operation. After this property is set, it can’t later be changed to either DirectQuery or Dual.
2. Specify Storage mode for each dataset
You can also set the storage mode property to specify storage mode for each table. also control how power bi caches you data.
Steps to set the storage mode for Specific table:
- Go to the model view tab in power bi desktop.
- Click on the table that you want to set the storage mode.
- Go to the properties pane in side bar.
- Expand the Advance option , here you can set the storage modes.
- If you have import storage mode , then other options are disabled. Because after selecting import mode you can not switch to other storage modes.
Hope this article is really useful for you. and you have got what you want to learn. if you have any question then write down in comments below . you can also learn more about POWER BI . so stay tuned.
