Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
IainGaskin
Regular Visitor

Power BI on-premise gateway ODBC data source timeout

We are attempting to connect an on-premise gateway to our ERP system (QAD 2010SE) using an existing (and working) ODBC connector (64 bit, Progress OpenEdge 10.2B Driver). This DSN works with QlikSense and also in the Power BI desktop.

 

For some reason, when used with the gateway as a data source, we get a timeout error:

 

Capture.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The connection to the ERP server is via two VPN tunnels, but works perfectly well from the ODBC Data Source Administrator, or the Power BI desktop (where we can apply a SQL clause to limit what is returned). It is only the on-premise connection that seems to have an issue.

 

Has anyone encountered a similar problem?

 

 

 

 

11 REPLIES 11
PhilMoz
Regular Visitor

I get exactly the same issue when using an ODBC data source and haven't yet found a solution.

A timeout parameter seems ideal, I don't mind waiting if it's just for this one time activity of adding the data source to the gateway.

 

We are using a Hortonworks Hive ODBC driver and have found that the gateway is trying to enumerate all of the objects on our Hadoop platform. We have thousands of objects...

 

Going to raise this separately and see how it goes, will let you know if I get anywhere. If I don't, will be escalating to Microsoft through our internal channels, it's critical to our use of Power BI as all of our data is in a hadoop data lake.

Thanks for the heads-up PhilMoz, I have raised a support ticket on this one. Hopefully whatever the solution is (I live in hope) will also help you guys.

Hi LainGaskin, did you managed to get this resolved? I have exactly same error when I trying to setup gateway through ODBC. THank you.

Hi thaiken22,

 

We had this investigated by mindtree, who ran some ODBC traces in our environment. The result was that the timeout could not be worked around, but that the problem had been passed up to the product design team.

 

All we can do for now is wait for the next release and hope that it performs better.

 

Regards,

Was this issue ever resolved?  We are having randon ODBC data source timeout issues with scheduled refresh on Power BI service.  It only started happening within the last 30 days.

@IainGaskin Are you using the latest gateway? Do you get any more specific error in the Gateway log files, that might point you to a particular part of the process.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

Thanks for replying Eno,

 

The gateway was downloaded and installed yesterday, so it is the latest version.

 

When I deliberately provide an incorrect password, I get an authentication error, and I see traffic over our firewall from the server hosting the gateway to the ERP system, on the specified port. This leads me to think that the server-ERP communication  is OK, but that the gateway-cloud communication may be the problem.

 

In the gateway log from user PBIEgwService , I see these lines (bold for emphasis):

 

DM.EnterpriseGateway Warning: 0 : 2018-02-01T09:23:10.9810051Z DM.EnterpriseGateway 790b3205-7982-407a-b4b3-a9a1cd93ae84 74a1811d-15de-c373-26cb-8b6155c59790 MGTD 2bc9fc44-122e-4901-a822-cacd7e128dc7 D10C73B5 [DataMovement.PipeLine.MashupCommon] Connection string <pi>DSR="<long hex string>"</pi> is not a valid M connection string
DM.EnterpriseGateway Information: 0 : 2018-02-01T09:23:10.9810051Z DM.EnterpriseGateway 790b3205-7982-407a-b4b3-a9a1cd93ae84 74a1811d-15de-c373-26cb-8b6155c59790 MGTD 2bc9fc44-122e-4901-a822-cacd7e128dc7 9BE2A3E3 [DataMovement.PipeLine.MashupCommon] Setting mashup connection test connection properties
DM.EnterpriseGateway Information: 0 : 2018-02-01T09:23:37.9187998Z DM.EnterpriseGateway 4b681528-b6fc-49bd-970d-223524181acc 3959f7d7-720a-4813-79b6-d794f0ad3dbe MGPP 1043648d-ce41-fc78-50dc-73a265812067 E8162E5D [DM.Pipeline.Common] Pool cleaner connections removed: 0, count: 0, buckets: 0
DM.EnterpriseGateway Information: 0 : 2018-02-01T09:23:37.9187998Z DM.EnterpriseGateway 4b681528-b6fc-49bd-970d-223524181acc 3959f7d7-720a-4813-79b6-d794f0ad3dbe MGPP 1043648d-ce41-fc78-50dc-73a265812067 E8162E5D [DM.Pipeline.Common] Pool cleaner connections removed: 0, count: 0, buckets: 0
DM.EnterpriseGateway Error: 0 : 2018-02-01T09:24:01.0056129Z DM.EnterpriseGateway 790b3205-7982-407a-b4b3-a9a1cd93ae84 74a1811d-15de-c373-26cb-8b6155c59790 MGTD 2bc9fc44-122e-4901-a822-cacd7e128dc7 58C9CA50 [DM.Pipeline.Diagnostics] Exception object created [IsBenign=True]: Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.MashupDataAccessHostingException: A problem hosting the mashup engine was detected. Reason: Timeout. Error code: -2147467259.; ErrorShortName: MashupDataAccessHostingException[ErrorCode=-2147467259,HResult=-2147467259]/Wrapped(MashupHostingException)[ErrorCode=-2147467259,HResult=-2147467259]
DM.EnterpriseGateway Error: 0 : 2018-02-01T09:24:01.0056129Z DM.EnterpriseGateway 790b3205-7982-407a-b4b3-a9a1cd93ae84 74a1811d-15de-c373-26cb-8b6155c59790 MGTD 2bc9fc44-122e-4901-a822-cacd7e128dc7 8AA61592 [DM.Pipeline.Diagnostics] Exception data: DM_ErrorDetailNameCode_UnderlyingErrorCode = <pi>-2147467259</pi>
DM.EnterpriseGateway Error: 0 : 2018-02-01T09:24:01.0056129Z DM.EnterpriseGateway 790b3205-7982-407a-b4b3-a9a1cd93ae84 74a1811d-15de-c373-26cb-8b6155c59790 MGTD 2bc9fc44-122e-4901-a822-cacd7e128dc7 2FABDDEE [DM.Pipeline.Diagnostics] Exception data: DM_ErrorDetailNameCode_UnderlyingErrorMessage = <pi><pi>Timeout expired. The timeout period elapsed prior to completion of the operation.</pi></pi>

 

I shall start googling the message about the M connection string.

 

For reference, I have followed the troubleshooting guide and the tcp connection to watchdog.servicebus.windows.net -Port 9350 succeeded. Switching the gateway to use HTTPS had no effect either - I get the same timeout error.

Additional info:

 

When I connect with desktop, the Navigator shows the connector and the database name almost immediately, but when I expand the database, there is a long delay (the database has 769 tables in it). Eventually all the tables appear and I can start selecting them

 

Could the timeout be due to the gateway trying to load all 769 tables when it first sets up the data source?

@IainGaskin Its possible, the other option would be to open the advanced options and specifically write your queries. That could avoid pre loading all the things.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

Unfortunately the only advanced settings on the gateway data source seem to be for setting the privacy level.

@IainGaskin The reference I made would be to the initial connection to the datasources in the Power BI Desktop file.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors