Which Storage Mode to use with Datamart?

[awarriorpoet]

I have a very large datamart that is currently pulling 8+ tables from excel files on sharepoint.  Some of them are 2M+ rows in total (~3 to 4 GB).  Some of the excel files are updated weekly. The largest file is rarely refreshed but will need to be on occassion.  I have a PRO license.

I was making changes to some of the modelling and noticed there was a storage mode option.  DIrectQuery, Import or Dual.

I read the document here that discusses some of the caveats but I still not sure if it when it is a good idea to change to import or dual.

Documentation is here: https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-storage-mode

Can someone help with the decision criteria on which storage mode option is best

[v-tangjie-msft]

Hi @awarriorpoet ,

The storage mode you choose in Power BI depends on several factors, such as the size of your data, the frequency of data updates, the complexity of your data model, and your performance requirements.

In your case, since you have very large tables and some of them are updated weekly, you may want to consider DirectQuery mode or Dual mode. However, you'll need to test your queries and monitor performance to ensure that your reports are responsive and accurate.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[awarriorpoet]

Thank you for the reply.

It is currently in Direct Query and performance is usually OK.  It is certainly starting to slow down with some visuals taking a while to come back up.   If I understand what I read, once I go to Dual Mode - there is no going back.  Is that correct?

What, if any, are the risks or downside to going Dual Mode? 

Primary database is about 2.8GB.
About 10 other tables between 30MB and 100MB. in size.

[v-tangjie-msft · Accepted solution]

Hi @awarriorpoet ,

You can switch back from Dual mode to DirectQuery mode if you want.

However, you may lose some of the changes you made in Dual mode, such as calculated columns, calculated tables, or relationships. You may also need to reconfigure some of the settings, such as the DirectQuery connection, the query options, or the refresh schedule.

The risks or downside of going Dual mode are mainly related to the performance and complexity of your model. For example:

- You may experience slower query performance or higher load on your data source when using DirectQuery mode for some tables. You may need to optimize your queries, your data source, or your network to improve the performance.
- You may encounter errors or inconsistencies when using different storage modes for different tables. You may need to validate your model and your results to ensure accuracy.
- You may have reduced functionality or compatibility when using Dual mode. You may not be able to use some Power Query or DAX functions, or some Power BI service features, for the tables in DirectQuery mode.
- You may have increased difficulty or maintenance when using Dual mode. You may need to carefully design your model and test your queries to ensure optimal performance and accuracy. You may also need to monitor and update your model and your data source regularly to keep them in sync.

Therefore, you should weigh the pros and cons of using Dual mode for your model, and decide whether it is worth the trade-off. You can also use the [Performance Analyzer] or the [Query Diagnostics] tools in Power BI Desktop to measure and improve the performance of your model.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.