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

[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. 

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?

[joglidden · Accepted solution]

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!

[v-piga-msft]

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

[joglidden]

@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.