cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
joglidden
Helper IV
Helper IV

Is it true that PowerBI still cannot handle more than one DirectQuery data source in one report?

I niavely assumed that I could use DirectQuery to connect to two different Azure SQL Server datasources in the same report. Of course, this is no trouble at all in my (Azure ASP.net) web app. Could someone confirm this for me?

 

If this is true, then this is a MAJOR shortcoming in Power BI, and I hope you MS engineers are working on implementing this. Otherwise, I have to go back to building dashboards in my web app. I was really hoping to use Power BI to get away from that. 

 

I suppose another solution would be to run shell scripts to bulk copy data from one datasource to the other, but this would be a waste of resources. 

 

Another option may be to "switch to import mode" for the datasource with less data, and then I suppose periodically refresh the report. Again, a waste of time and resources. 

 

Does anyone have any suggestions for tackling this problem, if indeed it is a problem?

1 ACCEPTED SOLUTION
joglidden
Helper IV
Helper IV

You have to run a query that does all your selects, joins, etc and write it to a table in the same (one and only) db that PBI connects to. Or, you can just create an Azure function or Web job that periodically copies the table over. Waste of resources!

View solution in original post

3 REPLIES 3
joglidden
Helper IV
Helper IV

You have to run a query that does all your selects, joins, etc and write it to a table in the same (one and only) db that PBI connects to. Or, you can just create an Azure function or Web job that periodically copies the table over. Waste of resources!

View solution in original post

v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @joglidden

 

I niavely assumed that I could use DirectQuery to connect to two different Azure SQL Server datasources in the same report. Of course, this is no trouble at all in my (Azure ASP.net) web app. Could someone confirm this for me?

 

If this is true, then this is a MAJOR shortcoming in Power BI, and I hope you MS engineers are working on implementing this. Otherwise, I have to go back to building dashboards in my web app. I was really hoping to use Power BI to get away from that. 


 

Based on the official document, if you use Direct Query Module, it only support a single source.

 

Limited to a single source

When importing data, it is possible to combine data from multiple sources into a single model, for example, to easily join some data from a corporate SQL Server database with some local data maintained in an Excel file. This is not possible when using DirectQuery. When selecting DirectQuery for a source, it will then only be possible to use data from that single source (such as a single SQL Server database).

 

For your idea, you could vote this similar idea here which has been submitted in Power BI ideas forum and add your comments there to improve Power BI and make this feature coming sooner.

 

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-piga-msft(Cherry), thanks for the response. I already beat you to it and voted for both ideas. I think if MS hired me right now, I'd have this problem solved in a week or two. I can do it with my web apps NO PROBLEM. It can't be that hard. 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.