For all of you who ever worked with Power BI, at least once there was a dilemma: should I use Direct Query or Import mode? In this post I will explain “pros” and “cons” for both options, also giving you a “wild card” solution – using both of them…Yeah, in the same report!
Import Mode
This is the “purest” way of working with data in Power BI. You simply import data from various sources (relational databases, such as SQL Server, MySQL, PostgreSQL, Oracle, etc; non-relational databases, Excel files and whole other bunch of different sources…) and build your own model within Power BI, using enormous capabilities of Power Query.
By importing data directly into Power BI, you should benefit from getting lightning fast performance, thanks to Vertipaq engine in the background. Of course, such a lightning performance comes at a cost, so here are pros and cons for using Import mode when working with Power BI:
Pros
- Combine Data from multiple sources
- Fully functional DAX formulas and calculations
- High-speed performance
Cons
- Limited memory size (up to 1GB for Pro license, up to 10GB for Premium license)
- Data latency – you need to manually refresh underlying data, or to set up scheduled refresh in order to have latest data
When should I use it?
Shortly, whenever you can:)…Power BI works in most “natural” way with Import mode.
Direct Query
With Direct Query, you are establishing direct connection with the data source. Data stays stored on the source side, not in Power BI model (Power BI stores only meta-data), which implies that there is no limitation for memory size, limit is set on the source. In this scenario, Power BI performs more like a visualization layer.
Every time a user clicks on visual, whole set of queries are being generated in the background and sent to source system in order to retrieve the data.
Pros
- Scalability – No limitations for file size, you can query really BIG data
- Always current data – No need to manually refresh/schedule refresh data model
Cons
- Limited number of data sources
- Limited usage of DAX functions and calculations
- Data must come from a single source (not possible to combine multiple sources)
- Performance considerations
When should I use it?
You should CONSIDER using Direct Query mode in cases you have really, really big data set (so big that data can’t fit in 1 GB file for importing), or in case you can’t afford any data latency and need to have latest data available in your reports in every moment.
More about Direct Query advantages and limitations can be found here
Composite Model
As name says, Composite model combines best from both worlds of Import Mode and Direct Query.
It was not always part of Power BI (it still isn’t for some flavors, like Power BI Desktop for Report Server). It was announced as Preview feature in summer of 2018 and became generally available later that year.
With composite model, you can create data model consisted of big tables from your SQL Server data warehouse (which you will Direct Query-ing) and, let’s say, Excel file which you will import directly into Power BI.
Every table in a composite model has a storage mode which shows whether the table is based on Direct Query or Import. Storage mode can be checked and modified in the Property pane. To check the storage mode, right-click a table and select Properties.
More about storage modes can be found here and I strongly recommend to read this article carefully if you plan to use composite models for your reports.
Of course, composite models still come with some limitations (mostly on data sources which can be used) and considerations (this article explains it in more details), but with careful planning you can use this option to create your perfect data model.
Last Updated on December 31, 2019 by Nikola
