Our gateway is installed and working - we have datasets using it.
We have one report though that uses two data sources, one is SQL Server, so we need the gateway. The other is a CSV file we get that is stored in a Sharepoint directory.
I googled a bit and came across one suggestion that we split the dataset in two, but I don't recall anything about that in the course, and the info online didn't say how do to that. The explanation was the HTTP refreshing (how OneDrive and Sharepoint refreshes work) couldn't be commingled with gateway datasources.
That makes no sense to me as to why that would be.
Bottom line, is there any way to make such a report automatically refresh? It is critical both data sources be in it. The CSV file is an export file from one of our customers and is the meat of the report, but we need to link data in that CSV file (via PowerQuery) to master tables in our SQL Server to the report has things like item number descriptions, etc.
Obviously manual refreshes work fine, but that is a pain.
Hi Ed,
I'm not aware of combining on-premises and online data sources being a problem. I know that when you combine sources (online and on-prem) you must use the gateway to refresh both. You also must use Power Query to load the data either into Excel and then into Power BI Desktop, or direct to Power BI Desktop. As stated in session 7.01.
Have you actually tried to set up a refresh schedule and check if your updates are pulling through to the Power BI service? If so, did you get an error message?
Mynda
Here is what it shows:
The CSV file in Sharepoint is being processed by the PBI Desktop file, as well as the merges with the SQL Server data. The "Use data gateway" is grayed out. I cannot even try to configure it. When I publish from PBI Desktop for data that is 100% SQL server, then I can use the Data Gateway and those reports work fine.
I think this is the issue brought up on "User Voice" here. So maybe I am doing nothing wrong, it simply doesn't work, but that is killing me with this report.
Hi Ed,
That UserVoice post is referring to the Enterprise gateway. The post linked to below on the personal gateway specifically cites an example of online and on-prem data needing a gateway (see heading "Do I need a gateway" and then "Online Data Source" under the Q&A. It specifically refers to an example of a file containing both online and on-prem data. It does not state you can't refresh such a file.
https://powerbi.microsoft.com/en-us/documentation/powerbi-personal-gateway/
Your error messages state the gateway is offline. That suggests you need to login to the gateway. Have you tried to refresh your 100% SQL PBI connection to ensure the error messages about the gateway being offline are incorrect?
Mynda
Sorry if I wasn't clear. We are using the enterprise gateway, not personal gateway. I think it is Enterprise. If I look at the PBI reports that are updating properly and go to the "schedule refresh" it shows that we are using a data gateway that does not have the "(Power BI - Personal)" set.
To my knowledge, I never set up a personal gateway. We only installed the Gateway software one time, and that was on a server which is the one being reported below as "PowerBI Ed" and online.
Is the solution right now to install the gateway also on my laptop as a personal gateway and then connect these reports with both corporate data (SQL Server) and web data (Sharepoint) until the issue at UserVoice is fixed?
The article you linked to actually says you can install a personal gateway and enterprise gateway on the same machine. I guess we could try that. I am just a bit nervous about munging something up that is working. 8-|
thanks.
Thanks for clarifying, Ed.
Does the server that has the Gateway have permission to access to the SharePoint directory that contains the CSV file?
Have you tried just loading the CSV file and setting up a scheduled refresh using the Gateway on the server?
If those things don't work can you please send screenshots of the relevant settings that are both greyed out for the CSV file and active for the SQL server.
Once I have those answers I should be able to go to Microsoft and ask on your behalf.
Mynda
