From the course: Microsoft Power BI Data Analyst Associate (PL-300) Cert Prep by Microsoft Press (2024)

Choose between DirectQuery, Import, and Dual mode - Power BI Tutorial

From the course: Microsoft Power BI Data Analyst Associate (PL-300) Cert Prep by Microsoft Press (2024)

Start my 1-month free trial Buy for my team

Choose between DirectQuery, Import, and Dual mode

“

- [Instructor] In this sub lesson, we are going to choose between DirectQuery, Import, and Dual mode. So, first off, what are storage modes? In Power BI Desktop, you can specify the storage mode of a table. This lets you control whether Power BI Desktop caches table data in-memory for reports. The most popular way to use data in Power BI is to import it into Power BI semantic model. Importing the data means that the data is stored in the Power BI file and gets published along with the Power BI reports. There are three storage modes inside of Power BI. There is Import, DirectQuery, and what is referred to as Dual. And just as a note, you can set the storage mode for each table individually in your data model. Okay, so I am now over on the Power BI Desktop and what I'm going to do here is I'm going to go to SQL Server and we're going to make a connection into a local SQL server. I'm going to drop the server name in and I'm going to choose DirectQuery as my data connectivity mode. Going to go ahead and click okay and on the next screen here what we're going to be able to do is go ahead and choose the username to connect into that database with. We get the password entered and click Connect. Give it a moment. And Power BI is now looking at SQL Server database to see which database and tables you would like to connect into. All right, so what we're going to do is I'm going to go down to AdventureWorks, and I'm going to go ahead and choose all the tables that I need. Okay, so I've chosen all my tables. And next what I'm going to do here is I'm just going to click load and that's going to bring it over into my Power BI Desktop. We'll see that the tables are getting their connections created. And now that we have that process finished, we can see that we have the report view and the model view only in addition to the tables that we have brought into the actual Power BI Desktop file itself. So, what I'm going to do now is I'm going to go over the model view. And what I'm going to do is just collapse these screens down a little bit, and say fit to screen. And what I'm going to do is just rearrange these tables just a little bit here. Okay, so I have all my tables rearranged here. And if I hover over top of any one of these tables, I can see the storage mode. So, in this case, this is set to DirectQuery. So, all the ones that the solid blue line over the top are set to DirectQuery. So, I'm going to do is just make sure that table's focused and open up the property's pane. And if we go down below into advance, we can also see the storage mode here for the DimCustomer table. Plus, I can also see the other storage modes that are available for this particular table. So, what I'm going to do here now is I'm going to make sure once again the DimCustomer table is chosen. I'm going to go ahead and change this storage mode over to Dual. So, I'm going to go ahead and set to Dual. I'm going to take in a message here saying, Setting the storage mode to Dual has the following implications. Please consider them carefully. This operation will refresh tables set to Dual, which may take time depending on such factors as the data volume. So, I'm going to go ahead and click okay, 'cause I want this setting. So, it'll just take a second here, 'cause this is quite a small table out of my data model. And now, we're going to see I've got a dashed blue line over the top of this table, and now the storage mode is indicated as Dual. Okay, so next up what I'm going to do is I'm going to bring an additional table in that is imported. So, I'm going to go over to Excel, click on Excel workbook, and then I'm going to navigate to the file that I want to bring in. And I've navigated to the path I want and I'm going to choose this file right here called DimSalesTerritory. Just going to go ahead and click open. We'll notice that the table will come in here and bring up the navigator view. So, I'm going to choose DimSalesTerritory as the table that I want to bring in from that Excel spreadsheet. And I'm just going to simply click Load. Give me a notice that there's a potential security risk here saying when you add a second source, information from one source might be included in queries that are sent to other data sources. Only add another source if you trust the owners and admins with that data. So, I'm just going to say, okay, 'cause I trust this model. Go ahead like that. Now, we're going to see DimSalesTerritory is off on the right-hand side here. So, I'm just going to move it over here to the top. And what I need now to do is just quickly indicate to you, it says storage mode of Import. If I ensure this table's highlighted and go over to the properties pane now, you'll see that the only storage mode that's available for an Excel spreadsheet is the Import storage mode. So, I have no ability to go ahead and change this. So, what I'm going to do here is I'm just going to go ahead and build the relationship between DimSalesTerritory and FactInternetSales table. Okay, so to build that relationship, I'm going to find my sales territory key in the DimSalesTerritory table. I'm just going to zoom this in a little bit just so it's a little bit easier to see here. So, let's go like this. And I will find the sales territory key there. I will go down into my FactInternetSales, find that sales territory key, build these two relationships like so. And we will see that the relationship has been detected as a one to many, and the cross filter direction has been set to single. So, I'm just going to go ahead and click okay. And now, we see the relationship has been built between those two tables. All right, so what are some of the benefits of setting the storage mode? Well, first is improved query performance. And if you have a particularly large semantic model, sometimes it's beneficial to use one of the other storage modes such as the DirectQuery when you have a large semantic model versus using the Import mode. It helps with data refresh optimization, it enables near realtime requirements, and it also supports writeback. Some general rules for selecting the storage mode is you generally choose Import for smaller, less frequently updated tables. We generally use DirectQuery for larger tables and/or tables with near real-time use cases. And we use the Dual mode depending on the storage mode of the related table. Some limitations and considerations. Changing the Storage mode of a table to Import is an irreversible operation. So, what is a composite model? A model that combines data from more than one DirectQuery source or that combines DirectQuery with imported data. You can create relationships between tables and any relationships that are cross-source are created with a cardinality of many-to-many, regardless of their actual cardinality.

Contents