Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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:
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.)
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.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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?
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.
@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?
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
58 | |
20 | |
19 | |
18 | |
9 |