Newsletter
Join our blog
Join other Azure, Power Platform and SQL Server pros by subscribing to our blog.
Start with the FREE community plan and get your lifetime access to 20+ courses. Get Instant Access Now!
Need help? Talk to an expert: (904) 638-5743
Private Training
Customized training to master new skills and grow your business.
On-Demand Learning
Beginner to advanced classes taught by Microsoft MVPs and Authors.
Bootcamps
In-depth boot camps take you from a novice to mastery in less than a week.
Season Learning Pass
Get access to our very best training offerings for successful up-skilling.
Stream Pro Plus
Combine On-Demand Learning platform with face-to-face Virtual Mentoring.
Certification Training
Prepare and ace your next certification with CertXP.
Private Training
Cheat Sheets
Quick references for when you need a little guidance.
Nerd Guides
Summaries developed in conjunction with our Learn with the Nerds sessions.
Downloads
Digital goodies - code samples, student files, and other must have files.
Blog
Stay up-to-date on all things Power BI, Power Apps, Microsoft 365 and Azure.
Community Discord Server
Start here for technology questions to get answers from the community.
Affiliate Program
Earn money by driving sales through the Pragmatic Works' Training Affiliate Program.
Reseller Partner
It's time to address your client's training needs.
Foundation
Learn how to get into IT with free training and mentorship.
Management Team
Discover the faces behind our success: Meet our dedicated team
Contact Us
How can we help? Connect with Our Team Today!
FAQs
Find all the information you’re looking for. We’re happy to help.
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.
Free Trial
private training
Newsletter
Join other Azure, Power Platform and SQL Server pros by subscribing to our blog.
Leave a comment