How to combine gateway data and web data in Power BI Service

Power BI
1

Hi,
Is there a way to combine data from a Gateway connector (ODBC) with excel data from OneDrive Business on Power BI Service?
When I create a pbix file containing only excel data from OneDrive the scheduled refresh works perfect but as soon as I add the on-premise data via a gateway connector (ODBC) I run into an error. The error message advise me to install a personal gateway.
I run PBI Desktop on a Windows server. Is it possible to have both on-premise and personal mode installed on the same server?
If not, is there a way to exclude the excel data to be reloaded on the scheduled refresh?

Thanks in advance
/robert

2

Hi Robert,

Is your Gateway up to date? It sounds like a configuration- or rights issue?
Here are several articles related to using the gateway, best go over these first…

  1. Download and install the gateway on a local computer.
  2. Configure the gateway based on your firewall and other network requirements.
  3. Add gateway admins who can also manage and administer other network requirements.
  4. Use the gateway to refresh an on-premises data source.
  5. Troubleshoot the gateway in case of errors.

.

3

Thank you for a quick respond. Just a clarification, both data sources runs perfect when they are separated. It’s when I try to combine them the reload fails.
/robert

4

.
When you right click on a Query name in the Queries pane you can disable “Include in report refresh”

5

Hi again,
Should it be possible to start a refresh from Power BI Online, Dataset Refresh now, which ignore a query where you have disabled “Include in report refresh”?
I get the same kind of error message when I try to reload online
/robert

6

Can you post a print screen of the error?

7

Power BI Online Dataset pressing the “Refresh now” icon…

Refresh%20failed
Refresh failed.png1044×543 46 KB

To me it looks like it’s the last item that fails. The ODBC Gateway works as expected. It also works when I reload a report that only contains the last item , the excel file. It seems to me that combining the two sources is the problem

8

Please see if under Gateway Cluster Settings, Allow user’s cloud data sources to refresh through this gateway cluster is enabled.

.
Hopefully this will resolve your issues.

9

Hi,
…and sorry for late respond. I’ve tried, with succes, to do what I want to achieve in another environment. So at least I know that it would work to combine Gateway data with excel-data stored on OneDrive Business . It seems that my problem is when I open option Dataset|More options| Settings for my report, the alternative “Data source credential” is greyed out and I don’t know why :frowning:

10

Check “Data Source Settings”, the line corresponding to the Excel file in OneDrive is the path to the file from your local machine? If so, open the Excel file in sharepoint, copy its URL from there and replace the path in the Data Source settings.

11

Hi,
Maybe a misunderstanding. The connection to the excel file on OneDrive works perfect as long as I don’t combine it with the ODBC data source. The problem appears when I try to combine the two sources. The ODBC data source contains the enterprise transactional data and the excel file contains Budget data

12

Okay well if you’ve checked that… I’m all out of ideas @robsmi