Forum Discussion
Switching from import to direct query
You cannot switch a data source from Import to Direct Query. You have to start over.
Thanks for lbendlin's concern about this issue.
Hi, hoosha_11
As lbendlin said, DirectQuery mode can be converted to import mode, while import mode cannot be directly converted to DirectQuery mode, you need to connect to the data source using DirectQuery mode again.
If you need real-time data update, DirectQuery is a good choice because it can query the latest data in the database directly without waiting for the data refresh.
However, DirectQuery requires high database performance. If your SQL Server database can respond to queries quickly and can handle a high concurrent query load, then DirectQuery may be viable.Your dataset is very large, containing multiple large tables and calculated columns. directQuery may experience performance bottlenecks when working with large-scale data, and performance optimization is required.
Perhaps consider using a hybrid schema where some of the data is imported into Power BI and the rest uses DirectQuery. this approach would combine the benefits of both schemas for real-time data updates as well as performance.The main reason you mentioned that DirectQuery cannot handle complex DAX queries is because it fetches data directly from the data source instead of importing it into Power BI memory for processing. Complex DAX queries can generate very complex SQL queries that can be very slow to execute in the database. This can lead to longer report loading times and a poor user experience.
Worse, if the query is too complex, the database may not be able to return results in a reasonable amount of time, causing the query to time out.
Second, complex queries consume a lot of database resources, which may affect the overall performance of the database, especially in highly concurrent situations.
In DirectQuery mode, transformations in Power Query are still applicable, but complex transformations may lead to errors, so you should also try to perform transformations in the data source to reduce complex operations in Power Query.I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for lbendlin's concern about this issue.
Hi, hoosha_11
As lbendlin said, DirectQuery mode can be converted to import mode, while import mode cannot be directly converted to DirectQuery mode, you need to connect to the data source using DirectQuery mode again.
If you need real-time data update, DirectQuery is a good choice because it can query the latest data in the database directly without waiting for the data refresh.
However, DirectQuery requires high database performance. If your SQL Server database can respond to queries quickly and can handle a high concurrent query load, then DirectQuery may be viable.
Your dataset is very large, containing multiple large tables and calculated columns. directQuery may experience performance bottlenecks when working with large-scale data, and performance optimization is required.
Perhaps consider using a hybrid schema where some of the data is imported into Power BI and the rest uses DirectQuery. this approach would combine the benefits of both schemas for real-time data updates as well as performance.
The main reason you mentioned that DirectQuery cannot handle complex DAX queries is because it fetches data directly from the data source instead of importing it into Power BI memory for processing. Complex DAX queries can generate very complex SQL queries that can be very slow to execute in the database. This can lead to longer report loading times and a poor user experience.
Worse, if the query is too complex, the database may not be able to return results in a reasonable amount of time, causing the query to time out.
Second, complex queries consume a lot of database resources, which may affect the overall performance of the database, especially in highly concurrent situations.
In DirectQuery mode, transformations in Power Query are still applicable, but complex transformations may lead to errors, so you should also try to perform transformations in the data source to reduce complex operations in Power Query.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you so much for the detailed information.
Just to confirm, does this mean that we can still use the same transformations in Power Query and maintain our measures and calculated columns? However, when using DirectQuery, all those transformations and queries will be automatically translated into SQL queries, which could affect performance because every time a user runs a report, the queries will be executed on the database?
Or does it mean that we should remove all measures and calculated columns and convert their logic into SQL queries by creating a view table and connecting it to Power BI?
Thanks again!
does this mean that we can still use the same transformations in Power Query and maintain our measures and calculated columns?Yessssss....maybe. Some DAX features are not supported, and some transforms can break folding.
all those transformations and queries will be automatically translated into SQL queries, which could affect performance because every time a user runs a report, the queries will be executed on the database?yes. So your SQL Server better be ready for that.
should remove all measures and calculated columns and convert their logic into SQL queries by creating a viewThat's a good plan B assuming your SQL Server can handle the load.
