Get data from different sources

[mcody]

Hi,

I have a question on how to work with multiple sources.

If I can first explain current set-up:

Power Bi Pro
One data source: SQL1 hosted on Server1
Installed Power BI Desktop on Server1
Gateway installed on Server1
Author reports on Server1 using Desktop. Make direct connection to SQL1 to get data.
Publish to service.
Connect gateway in service
Set appropriate dataset refresh policy.
Users consume reports from service.

This all works.

Now client want to use another data source, SQL2, hosted on Server2.
Want to combine data from SQL1 and SQL2 in one report.

Server1 and Server2 cannot talk to each other - diverse networks.

Can install gateway on SQL2.

What way to proceed?

It seems Power BI Desktop cannot access a data source via gateway. Only service can access gateway.

One way I was thinking about doing it is to author a report on SQL2 to create datasets.
Connect Server2 gateway in service.
Then on Server1 get data from dataset in service and author report using "local" data (SQL1) and "remote" data (SQL2 dataset on service).

Will this work?

I feel like there should be a better way, is there?

Any help or suggestions appreciated.

Regards,
Michael

[Anonymous · Accepted solution]

Hi @mcody 

You can connect to SQL Server in Power BI Desktop using SQL authentication credentials. Choose Database option in the following screenshot, then enter your username and password to connect to SQL Server.

You can connect to SQL1 in Sever2 by sa account in SQL1.

Or you can Build two reports by SQL1 and SQL2, then publish two reports to Power BI Service.

Configure gateway for SQL1 and SQL2. 

Then get power bi dataset in desktop and change data model to local model(Direct Query).

Finally you can get two dataset by directquery in Power BI Service.

However, it is not supported to do some transform on data soucre from power bi dataset in Power Query Editor.

It is not supported to show queries in Power Query Editor.

For reference: DirectQuery for Power BI datasets and Azure Analysis Services (preview)

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[PhilipTreacy]

Hi @mcody 

Configure the remote network/firewall where SQL2 resides to allow remote connections to the database.  Configure the SQL Server to do the same. 

Then connect from PBI Desktop to SQL2.

regards

Phil