Hello everyone, I hope y’all are having or will have a great day today. In this blog post I will be covering the differences between In-Memory Import and DirectQuery from its effects on the front-end (your report and DAX) as well as the effects on the back-end (Power Query Editor and loading data processes). Without further ado, let’s get into it.
To start, we are going to look deeper into the difference between In-Memory Import versus DirectQuery. For the sake of not typing out In-Memory Import and DirectQuery a lot, I am going to use IMI for In-Memory Import and DQ for DirectQuery (not to be confused with Dairy Queen). Most of the time that you are going to be loading a dataset (that isn’t a database) into Power BI, it will automatically use IMI to load your data. The only time you get a choice between IMI and DQ is when you are loading databases into Power BI. This is because Power BI wants to be able to give you the option to choose how the data is loaded into your report based on anything from the size of the dataset or the frequency of updates to said datasets.
Now to get into their differences. When choosing the IMI method of loading the data into Power BI, a snapshot of the dataset(s) will be what gets loaded into the file. IMI also allows for the full suite of transformations and DAX functions to be used for your report. IMI also has options to counteract data going stale by allowing for periodic refreshes, but it is still not as effective as DQ in this regard. This all sounds great that you get to use all the features that Power BI has to offer in terms of application of the Power Query Editor and DAX code but because Power BI takes that snapshot of your dataset(s), this will take up a lot more space since all the data will be stored locally in a moderately compressed version.
With regards to DQ, this method allows for Power BI to use real time/ near-real time data within your Power BI report. You are probably asking now, “So what does that look like?” well I will tell you. When using DQ, whenever you make changes to your report or to your data, the data will automatically refresh, and that covers the real time aspect. For the near-real time, this takes effect every fifteen minutes. On these increments, your data will automatically refresh in order to display the newest data that your source holds. With that being said, using DQ prohibits the use of some of the functionality of the Power Query Editor and also restricts the capabilities of DAX working in your report since it tries to make these transformations while the data is coming over to you. One way to look at the difference between In-Memory Import and DirectQuery is that IMI is an ELT (Extract->Load->Transform) process while DQ is an ETL )Extract->Transform-> Load) process.
That will be all for today’s blog and I appreciate everyone who took the time to read it. If you have any questions or comments, please leave a message below. Now, when it comes to In-Memory Import vs DirectQuery, you know what you NEED to know about loading databases. Thank you for your time and have a blessed day.
Bailey McDonald
Data Engineer, Patriot Consulting
Email: bkmcdonald@patriotconsultingcorp.com | Blogs: Patriot Consulting Blogs
LinkedIn: Personal: BaileyMcDonald | Company: Patriot Consulting
