Power BI – Changing Storage Modes (ie. changing from Direct Query to Import)
BY: Collin Quiring
There are many blogs, articles, WhitePapers and other information floating around the internet in regards to the difference between Power BI’s Direct Query, Import, Dual and Live Connection capabilities. This is either called the storage mode or it is called the connection type. This note is about how to know which one you are in and how to change from one to another.
Not wanting to repeat those other blogs I will not repeat everything they have but I will give a quick definition to make sure we are all at the same starting point:
- Direct Query is where the data resides in the database and the query pulls information when needed
- Import is where the data is pulled into Power BI Desktop (this is the most commonly used method)
- Dual is a hybrid between Import and Direct Query (sometimes called Composite Model)
- Live Connection is where Power BI uses the original data source every time and does not store copies of the data. This is the more limited data source option of the three but might be best for super large data sets.
First, how do you know which type of connection you are using?
As of this writing, there are a couple easy ways to find out that I know about. Normally, if you are in “import” storage mode then there is no notification in the bottom right corner. However, if you are in “Direct Query” there is usually a notice in the bottom right corner of the Desktop Application that says “Storage Mode: DirectQuery (click to change)”
Technically, you can be in a mixed mode as well. In that case, the message will appear as “Storage Mode: Mixed (click to change)”.
Another way to know which Storage Mode a specific table is currently in, select the table, right click and select Properties. The Field Properties column will appear and will show you the current Storage Mode for that table:
What if I didn’t look at either of those things, and I go to the Edit Queries page and I try to create a calculated column? Well, even if you try to create a simple modification you will get this message in the Edit Query page (“This step results in a query that is not supported in DirectQuery mode.” And then it has the button that says “Switch all tables to Import Mode”).
Now that we know how to determine which mode(s) we are in, how do we switch the storage mode? Well, if you click on the “Switch all table to import mode” from the Edit Query page, you will be given an option to actually make the change. You will get a pop up box warning you that this is irreversible. If you are changing to Import, the box states that there are implications, that “setting storage is an irreversible operation. You will not be able to switch it back to DirectQuery”. That is an important note – you may want to save a copy of this file if you ever think that you will want Direct Query. The box will also state that “All tables will be refreshed, which may take time depending on factors such as data volume.”
The message box looks like this:
If you are switching ALL tables to import mode, then just select the OK at the bottom of the box. Wait a bit and all should be well.
The other way to change to another storage mode is to go to the table, right click on properties and then select the mode you want to change it to for that table.
Since you are changing just one table, the message that you will receive is different than before. If you are just changing one table then the message will add “This operation will refresh tables set to Import or Dual, which may take time depending on factors such as data volume.” This message is in addition to it being irreversible. Another part of the box will state “Weak relationships are introduced by this change.” And the weak relationships that Power BI found will be referenced as well. In this case there are three tables (Customer, Geography and Date) that will now have weak relationships. The box will also have a pre-checked “Set affected table to dual” box.
Since you are changing just one table, the message that you will receive is different than before. If you are just changing one table then the message will add “This operation will refresh tables set to Import or Dual, which may take time depending on factors such as data volume.” This message is in addition to it being irreversible. Another part of the box will state “Weak relationships are introduced by this change.” And the weak relationships that Power BI found will be referenced as well. In this case there are three tables (Customer, Geography and Date) that will now have weak relationships. The box will also have a pre-checked “Set affected table to dual” box.
Comments
Leave a Reply
You must be logged in to post a comment.