Import vs DirectQuery Storage Mode?

When designing a Power BI, SQL Server Analysis Service Tabular or Azure Analysis Services data model, what are the pros and cons for using Import vs DirectQuery storage mode?

At a recent SQL Saturday, someone said they were using DirectQuery mode and some DAX functions were not working very well, how do we resolve this?

With a lot of data and millions of rows, DirectQuery may seem the right thing to do; but my advice is to start with import mode in most, if not all, cases. Some may argue that if they have a fact table with millions of rows and many columns that it’s going to take up a lot of space.

Here’s my advice. First, figure out how much memory it will take and be very conservative about the columns that you import into your model. Make it a point to only use the columns that you actually need based on your current requirements. Keep it small; you can always add additional columns later and expand the scope of your model.

Secondly, you want to avoid unique columns like distinct values that don’t compress well, such as a fact table with a sales key or an order ID. Import mode compresses each individual column which is efficient and can significantly reduce the memory footprint.

If you can keep the total file size under 1GB, then import mode is the way to go. In a case where you may need to drill down from a summary table into a transaction detail table and require up to the minute data updates, you can then use mixed mode or DirectQuery. Although this should be the exception, not the rule.

There’s an advanced level design known as composite model where some tables are import mode and some are DirectQuery. I highly suggest graduating to this model rather than starting with it - you may find it’s not even necessary.

Another tip is we can use partitioning with the incremental refresh feature to keep data up to date. This is now available with a Pro, as well as Premium, license. With incremental refresh you can keep your data up to date within the hour, or even less; no reason to have data that’s days old because you’re using import mode.

If you’d like to learn more about import vs direct query mode, data modelling or anything about Power BI, SSAS or Azure Analysis Services, we’re here to help. Our experts can answer any questions you have or help you plan or implement any of these products and services into your business. Click below to learn more about our consulting offerings.