Selecting a Storage Mode

The best way for using data in Power BI is by importing it into a Power BI dataset. Moreover, importing the data indicates that the data is stored in the Power BI file and gets published along with the Power BI reports. In this manner, it helps to make it simpler for us to interact directly with our data. But, this method may not be well suited for all the organizations.

Case Study: Let us suppose you are planning to build a Power BI reports for the Sales department at XYZ Traders, such that data import is not considered the best method. Given the following information –

• Firstly, you are required to accomplish and create your datasets in Power BI to build visuals and other report elements.
• The Sales department has many different datasets of varying sizes.
• Also, for security reasons, you are not permitted to import local copies of the data into your reports, therefore direct data import is not an option.
• Further, you need to create a direct connection to the Sales department’s data source.

You are required can ensure that these business requirements are satisfied when you are importing data into Power BI. Even then, there may be security requirements around your data that make it impossible to directly import a copy or the datasets may simply be too large and takes too long to load into Power BI. You must avoid creating a performance bottleneck.

Solution: Power BI helps to resolve these problems by using the DirectQuery storage mode, which allows you to query the data in the data source directly and not import a copy into Power BI. Moreover, DirectQuery is considered useful since it ensures you are always viewing the most recent version of the data.

Types of storage modes

• Import
• DirectQuery
• Dual (Composite)

We can also access storage modes by switching to the Model view,

• Firstly, select a data table
• Then choose the Properties pane
• Next, select which mode you want to use from the Storage mode drop-down list.

Import mode 

• Firstly, the Import mode allows creating a local Power BI copy of your datasets from our data source
• Secondly, we can use all Power BI service features with this storage mode, like Q&A and Quick Insights.
• Then, data refreshes must be done manually.
• Import mode is the default for creating new Power BI reports.

DirectQuery mode 

• This mode is useful when we do not want to save local copies of the data since our data will not be cached.
• Moreover, we can query the specific tables that are required using native Power BI queries, and the required data will be retrieved from the underlying data source.
• Primarily, we are focus on creating a direct connection to the data source.
• Also, this model ensures that we are always viewing the most up-to-date data such that all security requirements are met.
• In addition, this mode is preferred when we have large datasets to pull data from.
• We can use DirectQuery to create a connection to the source, solving data latency issues as well, instead of slowing down performance by having to load large amounts of data into Power BI.

Dual (Composite mode) 

• Firstly, under the Dual mode, you can identify some data that can be directly imported and other data that must be queried.
• Secondly, any table that is brought in to our report is a product of both Import and DirectQuery modes.
• Lastly, the use of Dual-mode permits Power BI to choose the most efficient form of data retrieval.