I have created a report in Power BI Desktop. It uses three data sources:
- Dynamics 365
- A web page with anonymous access
- Two Excel workbooks (imported) that I can access using either One Drive for Business or SharePoint.
I have published this report to a Power BI Service workspace for a client. It uses an email address to which they all have access (this is how they wanted it). Because this is for a client and I am at a consulting firm, the machine on which I created the Power BI Desktop report is not inside the client's network. I have credentials to their Dynamics 365 and their One Drive and Sharepoint.
I am completely stumped how to get the report to refresh automatically. At present, I am refreshing the data every morning in Desktop and re-publishing it. That's unsustainable, and I know there must be a way to keep it up to date automatically, but I haven't found it.
I wouldn't think I would need a gateway at all, since none of the data sources is actually on premises. I've read that web data sources require a gateway since sometime in 2016, but all the documentation about gateways I can find seems to require 1) that the machine with the gateway be *inside* the network the gateway will serve, and 2) be specific to on prem data sources inside that network.
How do I get these web- and cloud-based data sources to refresh automatically? Do I need a gateway, and if so, how do I set it up to use these data sources? Do I need to do it from inside the client's network?
Any assistance you can provide will be greatly appreciated. Thank you.
I believe that you would need a data gateway in your case because of teh web page as a data source and that you have multiple data sources in the report. I would think that you would not need to setup the data gateway in the client's network. In your case, it might be best to spin up an Azure VM and install the gateway there.
Proud to be a Datanaut!
Thank you for the suggestion. It seems promising, but I've hit another obstacle.
I want to test this internally before I try to do this with the client. We do not have anything set up in Azure in-house at the moment. I worked with our DevOps team to spin up a Windows server on an AWS VM, and I've had our network admin open the outbound ports mentioned in the gateway documentation.
I've installed the gateway on this AWS VM, but when I go to add the data source for the Dynamics 365 it tells me the credentials I'm using are wrong. They aren't, because they're the same credentials I'm using to access Dynamics 365 in Power BI Desktop on my machine.
I suspect this may be a configuration issue on the Dynamics 365 end, that it may be keeping me out because it doesn't recognize the gateway. But I want to check all the angles. Does this sound familiar to you--or anyone else? What am I missing?
Well, I would imagine that you should be using OAuth if it is Dynamics 365, is that the authentication type that you chose? Also, did you install the Enteprise or Personal version? If I recall correctly, if it is Personal, then you shouldn't need to specify the data source credentials. Can you walk me through the steps you are using to configure the data source for the data gateway, sounds like you are doing Enterprise.
Proud to be a Datanaut!
I'm not on the personal gateway. It looks like Enterprise gateway as a term has gone away, and it's just the gateway that you install in either regular or personal mode. I'm in regular.
While I am in the browser on the VM:
1. I log into Power BI using the account from which the client accesses the report.
2. I go to the dataset for which I want to set up scheduled refresh and I select Schedule Refresh
3. I open the Gateway connection tab
4. I click the arrow on the right next to my gateway under "Actions"
5. I click the "Add to Gateway" link next to the Dynamics 365 data source
6. On the Data Source Settings page, I name the data source and select the defaults of data source type (OData) and URL (the URL for the Dynamics API)
7. I select Windows authentication and enter the username and password that I'm using everywhere else I access their Dynamics.
8. I open Advanced Settings and ensure that Organizational privacy level is selected.
9 I click Add.
10. I get an error. The Error Code is "DMTS_PublishDatasourceToClusterErrorCode." The details include "Invalid connection credentials" and "The credentials provided for the OData source are invalid."
I hope this helps. The fact that the credentials I'm using work everywhere else (from Power BI Desktop, for example) are what has me leaning toward the notion that Dynamics might be blocking me because it doesn't recognize the source of this login attempt. But that aspect of things is not my specialty, so I'm not sure. And I'm just trying to see whether I'm covering all the possible bases on the Power BI end.
I appreciate all your help!
As I know, we don't need to configure the Data Gateway when data source is online.
For online service providers, refresh usually occurs once-a-day. For files loaded from OneDrive, automatic refresh occurs about every hour for data that does not come from an external data source.
For reference, you could have a look at this document Automatic refresh.