cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
fvicente
Frequent Visitor

Connect datasets to 2 gateways

Hi,

 

I have a report that I've connected to a SQL Server database and an Oracle database.  I need to combine data from both databases on the report.  The SQL Server database is on-prem and the Oracle database is remote on a vendor's server.  I've installed an on-prem gateway locally for the SQL Server database and the vendor has installed an on-prem gateway on their end.  I can import the data from both using separate reports and connecting each dataset in the Power BI portal to each gateway.  i.e.  SQL Server report to on-prem gateway (SQL Server) and Oracle report to vendor's on-prem gateway.  I can't do the same if I combine the data from both databases into one report and attempt to connect the data sources to each gateway.  Here is a screenshot of the Gateway connection in the Power BI portal:

 

Getway Connection.png

 

Do both data sources need to go through the same gateway?  If yes, is my only option to replicate the data locally then?

 

Thanks in advance for any help or insight you can provide.

 

Fernando

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

Hi @fvicente ,

If your dataset contains multiple data sources, each data source must be added in the same gateway.

You can refer to this document.

 

Best Regards,

Icey

 

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

 

View solution in original post

5 REPLIES 5
dbowman
Frequent Visitor

Hello,

 

I found this thread while trying to connet to vendor source as well.

In your case, would it make sense to create a dataflow within your premium space calling your vendors data. Your on premise source will use your on premise gateway, and the vendor data in your report will be called via the dataflow which uses the vendors gateway. Seeing as dataflow sources in a dataset do not need a connection setup directly it sounds like this may work for you.

venal
Memorable Member
Memorable Member

@fvicente 

If you want to combine the both databases data into report, then you can access those databases's from the Power BI Desktop and create a report and publish.

 

Then you need to configure those database's to the gateway by clicking on "Add to gateway" in dataset settings.

 

Note:- You need to use the same credentials in Power BI Desktop, Service, Gateway.

If you have any queries, please let us know.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more
If this post was helpful may I ask you to mark it as solution and click on thumb symbol?

fvicente
Frequent Visitor

Thanks very much for your reply.

 

I am using the same credentials in the Power BI Desktop as the service and gateway.  Locally I do not have direct access to the Oracle database.  I have a copy of the Oracle schema and tables locally that I can connect to and import the data from.  This issue comes when I publish.

 

If I connect to the SQL Server database in one report and connect to the Oracle database in a separate report I can connect to each of the databases and import the data without an issue.  The problem arises when I try to connect to both in the same report.  As you can see in the screenshot from my previous post, there are 2 gateway connections possible.  One has the SQL Server connection successful (green) and the Oracle one not (red) while the other gateway has the Oracle connection successful (green) and the SQL Server one not (red).  I can't include them in the same gateway as the SQL Server database is connected to an on-prem gateway locally.  The Oracle database is connected to a vendor's AWS server.

 

It seems like the dataset can only connect to one gateway.  That is the reason for my question.  Can I connect the dataset from the one report to 2 databases where one has a connection through one gateway and the other has a connection through a different gateway?

 

Hope this provides a little more insight and detail into my problem.  I appreciate your help.  Thanks,

 

Fernando

Icey
Community Support
Community Support

Hi @fvicente ,

If your dataset contains multiple data sources, each data source must be added in the same gateway.

You can refer to this document.

 

Best Regards,

Icey

 

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

 

fvicente
Frequent Visitor

Hi @Icey 

 

Thanks for the clarification.  I was hoping that that wasn't the case but everything I've run into with troubleshooting and testing seems to prove this to be true.  Thanks for the replies from both you and @venal 

 

FVicente

 

 

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors