cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
IainGaskin Frequent Visitor
Frequent 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
Super User
Super User

Re: Power BI on-premise gateway ODBC data source timeout

@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.

Near SE WI? Join our PUG Milwaukee Brew City PUG
IainGaskin Frequent Visitor
Frequent Visitor

Re: Power BI on-premise gateway ODBC data source timeout

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.

IainGaskin Frequent Visitor
Frequent Visitor

Re: Power BI on-premise gateway ODBC data source timeout

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?

Super User
Super User

Re: Power BI on-premise gateway ODBC data source timeout

@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.

Near SE WI? Join our PUG Milwaukee Brew City PUG
IainGaskin Frequent Visitor
Frequent Visitor

Re: Power BI on-premise gateway ODBC data source timeout

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

Super User
Super User

Re: Power BI on-premise gateway ODBC data source timeout

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

Near SE WI? Join our PUG Milwaukee Brew City PUG
PhilMoz Visitor
Visitor

Re: Power BI on-premise gateway ODBC data source timeout

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.

IainGaskin Frequent Visitor
Frequent Visitor

Re: Power BI on-premise gateway ODBC data source timeout

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.

thaiken22 Frequent Visitor
Frequent Visitor

Re: Power BI on-premise gateway ODBC data source timeout

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

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 232 members 2,815 guests
Please welcome our newest community members: