How can we improve Power BI?

Combine enterprise gateway and online data sources

At this time, a gateway will not function properly if you are using data sources and one is online and one is on-prem. As soon as the online data source is added, the gateway is designed to gray out because Power BI interprets that it is no longer needed. I would like to be able to combine data sources from both on-prem and online sources together with the use of gateways.

1,334 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Rflad shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    228 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...
      • Shelley commented  ·   ·  Flag as inappropriate

        I am having the same problem. I have lots of reports using data from our SQL server AND Excel files on Sharepoint and OneDrive, and they work just fine, refreshing over an Enterprise Gateway. However, this is the first report where I try to merge queries, looking up values in a SQL table onto the Excel tables, and now it will not refresh no matter what I try. Although I did have one random refresh on demand work in 6 minutes. It refreshes on my Desktop in 5 minutes. I have a ticket in with Microsoft.

      • Carl commented  ·   ·  Flag as inappropriate

        Microsoft support have told me that this idea/feature does not yet work on Enterprise Gateway. It only works on the Personal Gateway for now. I've asked them to update this ideas to page to reflect the fact that is is not yet "Complete".

      • Sokon commented  ·   ·  Flag as inappropriate

        Support: "After looking in to the verbatim we found that you are trying to combine an On premise datsource with cloud datsource (WEB). This is not possible by using the On premise data gateway in enterprise mode. In order to combine the data you should be using the On premise data gateway (personal Mode)."

        We can't use personal mode in our case - please allow this feature for enterprise mode as well!

      • Antonio Palomas commented  ·   ·  Flag as inappropriate

        It is very important that Power BI technical team responds to this problem that is impacting our business processes.

        I have this problem also with SQL database on premisses and Sharepoint online.

      • Anonymous commented  ·   ·  Flag as inappropriate

        Same as Carl.."We also tried refreshing datasets which merge on-prem source (SQL server) and online source (Excel on SharePoint online), and this is still not working, despite enabling the Gateway setting “Allow user’s cloud data sources to refresh through this gateway cluster" Any news about this_??

      • Dalton commented  ·   ·  Flag as inappropriate

        Hi All,
        I am getting this same error message after connecting my datasources to the On-premise Gateway for other sources in the report.
        The SharePoint datasource is giving me the error "SharePoint: Request failed: Unable to connect to the remote server" when I initialize a refresh from the Power BI service or when i schedule a refresh through the Gateway.
        If i initialize a refresh through the Power BI Desktop, it works perfectly fine.
        Also it does not complain about the file that I am loading but complains about the another table where I am merging the SharePoint file with another file
        All the steps mentioned in this document has been performed and still the same error message.
        Please if you can help.
        Thanks

      • Carl commented  ·   ·  Flag as inappropriate

        We also tried refreshing datasets which merge on-prem source (SQL server) and online source (Excel on SharePoint online), and this is still not working, despite enabling the Gateway setting “Allow user’s cloud data sources to refresh through this gateway cluster.”

      • Marina commented  ·   ·  Flag as inappropriate

        Hi,
        It doesn't work with SharePoint Online et SQL Server On-premise.

      • Sokon commented  ·   ·  Flag as inappropriate

        Refreshing datasets containing queries that merge data from a web source with an on-Prem source still does not work. The linked documentation describes a procedure that doesn't solve the problem. This is not finished an marking it as "completed" is wrong.

        I ran a couple of tests and documented them in an issues post where people can vote for it:
        https://community.powerbi.com/t5/Issues/Gateway-data-refresh-fails-when-data-is-merged-from-web-and-on/idi-p/594579#M36791

        This is frustrating, we've been waiting for this fix for more than a year!

      • Daniel Davis commented  ·   ·  Flag as inappropriate

        It's convenient this is marked complete, when it doesn't fully work. I opened a support request trying to trace this issue and was sent tracing in circles providing logs and network traffic captures to no avail.

      • Daniel Davis commented  ·   ·  Flag as inappropriate

        Has anyone found any workaround for this? It is crippling my ability to publish our latest dataset since migrating to using CSV files in Sharepoint to filter and augment database results

      • David Cresp commented  ·   ·  Flag as inappropriate

        I have just come up against this problem. One year on from starting it still seems to be a major issue. Please fix this one MS.

      • Melissa Coates commented  ·   ·  Flag as inappropriate

        In the settings for the On-Premises Data Gateway, there's an option to “Allow user’s cloud data sources to refresh through this gateway cluster.” You can see that option shown on the 2nd screenshot on this page: https://docs.microsoft.com/en-us/power-bi/service-gateway-mashup-on-premises-cloud. Hopefully that option will help some of the people still commenting on this being an issue.

      • Charles commented  ·   ·  Flag as inappropriate

        I've been struggling with this issue for the last two days. One source is SQL on-prem using an enterprise data gateway, other source is an Excel file hosted on Sharepoint online. Use either one by itself, it works as it should. If you try to use the two data sources together, Power BI service breaks down and gets confused as to what to do and how to authenticate.

        Very surprised at this limitation, seems like it should be a basic functional requirement.

      • Anonymous commented  ·   ·  Flag as inappropriate

        What is the status of this "Combine enterprise gateway and online data sources"?

      • Anonymous commented  ·   ·  Flag as inappropriate

        My dataset has Salesforce as its only data source. It says I don't need a gateway but that I can use one for enhanced control. When I tried selecting my enterprise gateway, I get the error message "Salesforce{"loginServer":"https://login.salesforce.com/","classInfo":"report-detail"} Cannot be added to gateway". Why? If I don't use a gateway, I am able to do Refresh Now.

      • maRK commented  ·   ·  Flag as inappropriate

        Please let me clarify this issue, because some people are saying that it has been fixed - IT HAS NOT.
        Yes- you can mix sources from Web based ( sharepoint etc) AND Sqlserver .... SO LONG AS THEY ARE ISOLATERD in your PBIX file . However once you append or merge those 2 sources, then the gateway will disappear. That is the bug, and in my opinion a very serious bug because the chances are , if you have more than one data source, you almost certainly want to combine them!
        Come on MS - how much longer do we have to wait ... 'will finish soon' in Nov 2017???

      • Rayno commented  ·   ·  Flag as inappropriate

        In general with regards to connecting to the exchange online mailbox hasn't been smooth in the cloud services. The desktop refresh works but once uploaded it either works for while then breaks, leave it for a few weeks then it works. Been through this cycle 3 - 5 times. I was supper excited that it started working again a couple of days ago "Yes no more manually uploads" . Due to it working I started merging on premise data with the exchange online contents hence we here. I tried to use the personal gateway with no success and I'm back in the cycle, here is the error "Underlying error code: -2147467259 Table: Mail.
        Underlying error message: ErrorItemNotFound: The specified object was not found in the store., The process failed to get the correct properties." This error was in the cycle that I mentioned. Support team you got it right on the enterprise gateway, just needs to be addressed on the personal gateway. Still hope but back to manual uploads.

      ← Previous 1 3 4 5 11 12

      Feedback and Knowledge Base

      Ready to get started?

      Try new features of Power BI today by signing up and learn more about our powerful suite of apps.