Is it possible to utilize a cross-database SQL Query in Power BI's Online Service? For example, I have a query that I am importing (NOT using DirectQuery) into a Power BI Desktop File that follows this structure:
Select a.*, b.*
FROM Server1.Database1.Table1 a
INNER JOIN Server1.Database2.Table2 b on b.id = a.id
It is merging data from two different databases within the same SQL Server. When importing the query into Power BI Desktop I have to explicitly list the Server and Database as Server1 and Database1, respectively. The refresh works fine in the Desktop version but when I upload to the Online Service and attempt to set-up a refresh schedule I receive an error because the Gateway connection pointing to Database1 cannot access Database2 under the current security context. I receive the "Unable to connect to the data source undefined" error (Class: 14, Number: 916 - DataSource Error).
Therefore, I was wondering if it is possible to create a gateway connection for a SQL Query that accesses multiple databases within the same server? Or is there a Gateway setting that would allow this to happen? I would assume there are multiple case scenarios of Users importing cross-database queries into Power BI's online service. Has anyone else had this issue/come up with a solution?
I do not want to create two data source connections pointing to each table individually and merging in the BI User Interface as they are both very large fact tables (Also connects to multiple dimensional tables). Would I require a SQL View that accesses both tables or would the issue still persist?
Any help is appreciated!
