Switching from import to direct query

[hoosha_11]

Hello,

I've never used Direct Query before and would like to know if it's worth/possible switching to it. I’ve done some research and found that there are limitations regarding complex DAX queries and data models when using Direct Query + performance issues.

Currently, we have a dataset with over 20 tables, including 2 fact tables and the rest as dimension tables. Our dataset comprises over 500 columns, including native SQL columns, 40 calculated columns, and around 100 measures. A few tables are large, with over 10 million records, while the others contain between 500,000 and 1.5 million records. We are currently using Import Mode to retrieve data from our SQL Server database and have scheduled a refresh every 30 minutes through Power BI Service.

We also have 10 different reports connected live to this semantic model, which we refresh every 30 minutes.

Given the size of the dataset and our need for frequent refreshes, do you think it is feasible or worthwhile to convert to Direct Query?

What does it mean when we say that Direct Query cannot handle complex DAX queries?

Do transformations in Power Query and the data model still apply with Direct Query?

Thanks for your help.

[lbendlin · Accepted solution]

You cannot switch a data source from Import to Direct Query.  You have to start over.

[v-fenling-msft · Accepted solution]

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.

[hoosha_11]

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!

[lbendlin]

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 view 

That's a good plan B assuming your SQL Server can handle the load.

[lbendlin · Accepted solution]

You cannot switch a data source from Import to Direct Query.  You have to start over.