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
Anonymous
Not applicable

Can't publish report with two different data sources

I made a report in Power BI Desktop. It has a DirectQuery connection to an SQL Server database. I not setup any personal Gateways. 

 

Publishing the report works fine, until I add an Excel workbook as a data source. The Excel workbook in hosted on a cloud platform.

 

Error message:

There is no gateway to access the date source [SQLdata]. 

 

Other solutions I've read mention setting in the Gateway. I don't have a Gateway that I'm aware of. I tried setting up a personal gateway which didn't help. And it's my understanding that the personal gateway doesn't support DirectQuery?

 

How do I make this work?

7 REPLIES 7
Anonymous
Not applicable

Okay. To clarify, I am an end-user in operations. I've discovered that my IT department does have an On-Premises Data Gateway.

 

I have perfromed the following test:

  1. Create a new .pbi file, connect to SQL database using DirectQuery
  2. Publish to Power BI Service
  3. In the dataset settings I can see the On-Premises Data Gateway. It appears automatically. It is installed on a server somewhere. When I go to "Manage Gateways" in Power BI Service it says "You don't have any data gateways." I do not have the ability to configure the gateway or add data sources to it. I am not an admin of the Gateway. (Note that I do have a Personal On-Premises Data Gateway installed. It's my understanding those don't show up in "Manage Gateways.") All of this is to be expected.
  4. I can refresh my reports, no problem. (I may have had to update credentials for the dataset. I don't remember.)

When I add a local Excel file to the dataset and republish, I cannot refresh the dataset. The error says "Refresh failed because this dataset requires a gateway. Please select a gateway from dataset settings."

 

When I now view the gateway connection in the dataset settings, I see only my Personal Gateway.

 

Do I need to ask IT to add the Excel workbook to the On-Premises Data Gateway? The file is on Box and synced to my laptop. I have been connected to the file via the local address on my machine (C:\Users\myname\... etc.)

Icey
Community Support
Community Support

Hi @Anonymous ,

For refreshing data both from on-premises and cloud data sources, you can refer to this document:

Merge or append on-premises and cloud data sources.

Please ask IT to keep the section below selected and add both the SQL Server and the Excel workbook to the On-Premises gateway. For one dataset, all data sources must be added under one same gateway. And please add you as a user.

refresh-gateway-cluster.png

 

Best Regards,

Icey

 

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

Anonymous
Not applicable

Thank you, I will attempt to add the data source to the gateway today.

 

One other question that I haven't been clear on: the Excel workbook in question is stored on Box. Of course I have to log into Box to view it. Does this still count as a cloud data source, or does it act like an on-premises data source? I understand of course that Box is a cloud storage platform, but I thought I recalled reading something that suggested files like mine behave as on-premise. For example I would need at least a on-premise gateway (personal) to connect to it.

 

And does it make a difference if I try to connect to it using a web url or the address of the synced file on my local machine?

Icey
Community Support
Community Support

Hi @Anonymous ,

Sorry, I think I may have overlooked something before. Your Excel workbook is stored in Box, so it is a cloud data source.

1. If you connect to the local file ((C:\Users\myname\... etc.)), it is just an on-premises data source. So, you need a gateway to refresh data.

2. If you connect to the file in BOX, to my knowledge, Power BI doesn't have a Box connector currently, so you need to use the shared link and choose 'connect to web'. In this case, although it is cloud data source, you might need a data gateway if your data sources are behind a firewall, require a VPN, or are on virtual networks.

 

Best Regards,

Icey

 

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

 

venal
Memorable Member
Memorable Member

@Anonymous 

Can you please try to install the On-Premises Data Gateway and try to configure the data sources to the Gateway.

 

https://docs.microsoft.com/en-us/data-integration/gateway/service-gateway-install

Enable the below options in the Gateway.

 

Path:- Power BI Service >> Settings >> Manage Gateways >> select the Gateway

 

 

Please refer the below link to ocnfigure the data source to the Gateway.

https://docs.microsoft.com/en-us/power-bi/service-gateway-data-sources

 

If you have any concerns, 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?

Icey
Community Support
Community Support

Hi @Anonymous ,

On-premises data gateway (personal mode) doesn't support DircetQuery mode. You need to install an Enterprise mode

For differences between an on-premises data gateway and an on-premises data gateway (personal mode), you can refer to this document and this blog.

For refreshing data from SQL Server, you can refer to these documents: 

Refresh data from an on-premises SQL Server database

Manage your data source - SQL Server.

For refreshing data both from on-premises and cloud data sources, you can refer to this document:

Merge or append on-premises and cloud data sources.

 

Best Regards,

Icey

 

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

GilbertQ
Super User
Super User

Hi there

As soon as you combine 2 different data sources with SQL and Excel you will then need a gateway in order for the data to be refreshed.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

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