Connecting to two different Azure SQ databases

[anandav]

Hi,

I have two different datbases in Azure SQL server- Adventure Works and the new WWI sample databases on the same server.

I can connect to the database from Power BI desktop but only to one database at atime from a given PBIX file.

Steps:

1. Estbalish a DirectQuery connection to WWI database - this works.

2. Establish a DirectQuery connection to Advneture Works databse - the connection works but it gives me a message that it needs to switch to "Import Mode".

I understand this is a restriction in Power BI you can only connect to one databse in DirectQuery mode.

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-use-directquery/

1] If I need to get data from mulitple databases using DirectQuery, what are my options?

2] Since I cannot mix DirectQuery and getting data from Excel, is there any other way to get data from other files when using DirectQuery mode?

Thanks,

Anand

[Seth_C_Bauer · Accepted solution]

@anandav

1] If I need to get data from mulitple databases using DirectQuery, what are my options?

Have the connections on the database back end. Write a view that pulls the information from the other database. 

2] Since I cannot mix DirectQuery and getting data from Excel, is there any other way to get data from other files when using DirectQuery mode? Not to my knowledge, you would need a way to create a database object Procedure/Function/View that could reference the external object. I was just thinking of using OPENROWSET(), but doesn't work in Azure SQL.

[anandav]

Hi @Seth_C_Bauer,

Thank you for your suggestions.

Though not ideal, I think as you sugested I may be able to achieve some of the functionality of mixing database data and file data using Transact-SQL Rowset functions in the databse backend. I'd probably need to read a lot to understand how it can be done but atleast your answer gave me some direction.

I was also thinking of Azure Factory to load file data.

But it would have made it very appealing for small businesses if data could be loaded from multiple databases and files into a single PBIX file/project in DirectQuery mode.

Thanks,

Anand