Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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?

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors