OData connection error using Data Gateway

[Anonymous]

Hi All,

I would like to test the ODATA connector using PowerBI as a source with "data gateway"

(1) In powerbi.com - I have successfully configured the Data Gateway Cluster on the machine (On-Premise Data Gateway)
(2) In powerbi.com - In the menu "Manage Gateway", I try to insert as a datasource ODATA - basic authentication with the URL
http://localhost:9090/tagetikcpm/api/aih/v1/Datasources and it works correctly.

(3) In powerbi.com - In the menu "Manage Gateway", I try to insert as ODATA datasource - basic authentication with different URL
http://localhost:9090/tagetikcpm/api/aih/data/v1/TGK_06/001/Datasource_000008()

but it returns me the following error:

Unable to connect: We encountered an error while trying to connect to . Details: "We could not register this data source for any gateway instances within this cluster. Please find more details below about specific errors for each gateway instance."Hide details
Activity ID: 243fb8a8-adc9-4398-9a1e-b93e2ca84059
Request ID: 6f204009-6f1c-d4a5-3a42-2273740eba83

Status code: 400
Error Code: DMTS_PublishDatasourceToClusterErrorCode
Time: Tue Mar 02 2021 15:13:13 GMT+0100 (Ora standard dell’Europa centrale)
Service version: 13.0.15480.60
Client version: 2102.4.05076-train
TGKDEMO: Unable to connect to the data source undefined.
Underlying error code: -2147467259
Underlying error message: We couldn't parse OData response result. Error: The context URI 'http://localhost:9090/tagetikcpm/api/aih/data/v1/TGK_06/001/$metadata#Collection(CCHTagetik.Aih.type_000008)' is not valid for the expected payload kind 'Collection'.
DM_ErrorDetailNameCode_UnderlyingHResult: -2147467259
Microsoft.Data.Mashup.ValueError.DataSourceKind: OData
Microsoft.Data.Mashup.ValueError.DataSourcePath: http://localhost:9090/tagetikcpm/api/aih/data/v1/TGK_06/001/Datasource_000008()
Microsoft.Data.Mashup.ValueError.Reason: DataSource.Error
Troubleshoot connection problems

However, the same URL(3) works correctly via the web browser and it returns XML Data.
It should be not an infrastructure issue because in the second point the connection works correctly.

Could someone tell me why?

Thanks for your support!

[v-janeyg-msft]

Hi, @Anonymous 

Have your problem been solved? If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

Best Regards

Janey Guo

[Anonymous]

Hi @v-janeyg-msft ,

not yet, the issue is open.. 

Regards

[v-janeyg-msft]

Hi, @Anonymous 

I have reminded you that the format of your url data source is not recognized and supported by pbi service. Not all data that can be refreshed in the desktop can be refreshed in the service, such as some custom data sources. Can you try a personal gateway? If it doesn’t work, then consider replacing the data source.

Best Regards

Janey Guo

[Anonymous]

[v-janeyg-msft]

Hi, @Anonymous 

Have you connected with the url 'http://.../Datasource_000008()' in desktop successfully?

Can you change the name 'Datasource_000008()' without using '_' and '()'? Maybe it can't be recognized.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-janeyg-msft 

The URL http://localhost:9090/tagetikcpm/api/aih/data/v1/TGK_06/001/Datasource_000008 

without "()" works correctly with Power BI Desktop in the local machine (without Data Gateway service) but it doesn't work with Data Gateway.
I can't remove the char "_" because it is not a valid URL.  

Regards, 

[v-janeyg-msft]

Hi, @Anonymous 

This shows that the problem is indeed the url format, because this kind of custom URL may not be accepted by PBI service. I want to know if the error in the service after you remove the (), is it still the same as before, or is it different? Have you tried changing odbc connector to web connector?

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-janeyg-msft ,
I have deleted the parantheses "() "and the error is as follows:

Unable to connect: We encountered an error while trying to connect to . Details: "We could not register this data source for any gateway instances within this cluster. 
Please find more details below about specific errors for each gateway instance."Hide details
Status code: 400
Error Code: DMTS_PublishDatasourceToClusterErrorCode
TGKDEMO: Unable to connect to the data source undefined.
Underlying error code: -2147467259
Underlying error message: We couldn't parse OData response result. Error: The context URI 'http://localhost:9090/tagetikcpm/api/aih/data/v1/TGK_06/001/$metadata#Collection(CCHTagetik.Aih.type_000002)' 
is not valid for the expected payload kind 'Collection'.
DM_ErrorDetailNameCode_UnderlyingHResult: -2147467259
Microsoft.Data.Mashup.ValueError.DataSourceKind: OData
Microsoft.Data.Mashup.ValueError.DataSourcePath: http://localhost:9090/tagetikcpm/api/aih/data/v1/TGK_06/001/Datasource_000002
Microsoft.Data.Mashup.ValueError.Reason: DataSource.Error

I have tried with web connector, it works fine but the data output is not correct (the structure does not reflect the data present in the XML). In local PowerBI, I see the correct result only when I use the ODATA Connector. For this reason,  I need to use the ODATA type as data source for the data gateway.

[v-janeyg-msft]

@Anonymous 

 Please try to upgrade the gateway to the latest version or use a personal gateway.

Reference: Update an on-premises data gateway | Microsoft Docs

If it doesn't work, I think there is nothing I can do, because probably pbi service can't accept this form of url in odata.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-janeyg-msft , 

I have upgraded the gateway and it's already connected but I have the same error. 

I extract this log from the data gateway service... it might help us...
thanks 

DM.EnterpriseGateway Error: 0 : 2021-05-28T06:30:41.0858693Z DM.EnterpriseGateway	00000000-0000-0000-0000-000000000000	00000000-0000-0000-0000-000000000000	0000	00000000-0000-0000-0000-000000000000	00000000-0000-0000-0000-000000000000	00000000-0000-0000-0000-000000000000	25E31978 [DM.Analytics] Performance counters not installed, application performance metrics will not be traced (category = Microsoft Power BI Enterprise Gateway)

Starting trace on 5/28/2021 7:35:53 AM UTC
UserDomainName: NT SERVICE
UserName: PBIEgwService
MachineName: DH-TEMPLATE


DM.EnterpriseGateway Error: 0 : 2021-05-28T07:55:46.8108664Z DM.EnterpriseGateway	00000000-0000-0000-0000-000000000000	00000000-0000-0000-0000-000000000000	0000	00000000-0000-0000-0000-000000000000	00000000-0000-0000-0000-000000000000	00000000-0000-0000-0000-000000000000	25E31978 [DM.Analytics] Performance counters not installed, application performance metrics will not be traced (category = Microsoft Power BI Enterprise Gateway)
DM.EnterpriseGateway Error: 0 : 2021-05-28T08:16:56.1782060Z DM.EnterpriseGateway	1df837c5-441c-4efa-8d0a-4535b4b5ea81	34ca72cb-2844-ebdb-43d5-e7f2d7cc9db3	MGTD	607425aa-07c7-461a-8a0d-72993ff5ac97	b5555749-b87c-4516-99c9-3940c2cb167a	b5555749-b87c-4516-99c9-3940c2cb167a	727CA6CB [DM.Pipeline.Diagnostics] Exception object created [IsBenign=True]: Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.MashupDataAccessValueException: Mashup expression evaluation error. Reason: .; ErrorShortName: MashupDataAccessValueException[ErrorCode=-2147467259,HResult=-2147467259]/Wrapped(MashupValueException)[ErrorCode=-2147467259,HResult=-2147467259]
DM.EnterpriseGateway Error: 0 : 2021-05-28T08:16:56.1782060Z DM.EnterpriseGateway	1df837c5-441c-4efa-8d0a-4535b4b5ea81	34ca72cb-2844-ebdb-43d5-e7f2d7cc9db3	MGTD	607425aa-07c7-461a-8a0d-72993ff5ac97	b5555749-b87c-4516-99c9-3940c2cb167a	b5555749-b87c-4516-99c9-3940c2cb167a	8AA61592 [DM.Pipeline.Diagnostics] Exception data: DM_ErrorDetailNameCode_UnderlyingErrorCode = <pi>-2147467259</pi>
DM.EnterpriseGateway Error: 0 : 2021-05-28T08:16:56.1782060Z DM.EnterpriseGateway	1df837c5-441c-4efa-8d0a-4535b4b5ea81	34ca72cb-2844-ebdb-43d5-e7f2d7cc9db3	MGTD	607425aa-07c7-461a-8a0d-72993ff5ac97	b5555749-b87c-4516-99c9-3940c2cb167a	b5555749-b87c-4516-99c9-3940c2cb167a	57671F5E [DM.Pipeline.Diagnostics] Exception data: DM_ErrorDetailNameCode_UnderlyingErrorMessage = <pi><pi>We couldn't parse OData response result.  Error: The context URI 'http://localhost:9090/tagetikcpm/api/aih/data/v1/TGK_06/001/$metadata#Collection(CCHTagetik.Aih.type_000002)' is not valid for the expected payload kind 'Collection'.</pi></pi>
DM.EnterpriseGateway Error: 0 : 2021-05-28T08:16:56.1782060Z DM.EnterpriseGateway	1df837c5-441c-4efa-8d0a-4535b4b5ea81	34ca72cb-2844-ebdb-43d5-e7f2d7cc9db3	MGTD	607425aa-07c7-461a-8a0d-72993ff5ac97	b5555749-b87c-4516-99c9-3940c2cb167a	b5555749-b87c-4516-99c9-3940c2cb167a	6A5E25DC [DM.Pipeline.Diagnostics] Exception data: DM_ErrorDetailNameCode_UnderlyingHResult = <pi>-2147467259</pi>
DM.EnterpriseGateway Error: 0 : 2021-05-28T08:16:56.1782060Z DM.EnterpriseGateway	1df837c5-441c-4efa-8d0a-4535b4b5ea81	34ca72cb-2844-ebdb-43d5-e7f2d7cc9db3	MGTD	607425aa-07c7-461a-8a0d-72993ff5ac97	b5555749-b87c-4516-99c9-3940c2cb167a	b5555749-b87c-4516-99c9-3940c2cb167a	869CC7B9 [DM.Pipeline.Diagnostics] Exception data: Microsoft.Data.Mashup.ValueError.DataSourceKind = <pi>OData</pi>
DM.EnterpriseGateway Error: 0 : 2021-05-28T08:16:56.1782060Z DM.EnterpriseGateway	1df837c5-441c-4efa-8d0a-4535b4b5ea81	34ca72cb-2844-ebdb-43d5-e7f2d7cc9db3	MGTD	607425aa-07c7-461a-8a0d-72993ff5ac97	b5555749-b87c-4516-99c9-3940c2cb167a	b5555749-b87c-4516-99c9-3940c2cb167a	776F86BB [DM.Pipeline.Diagnostics] Exception data: Microsoft.Data.Mashup.ValueError.DataSourcePath = <pi>http://localhost:9090/tagetikcpm/api/aih/data/v1/TGK_06/001/Datasource_000002()</pi>
DM.EnterpriseGateway Error: 0 : 2021-05-28T08:16:56.1782060Z DM.EnterpriseGateway	1df837c5-441c-4efa-8d0a-4535b4b5ea81	34ca72cb-2844-ebdb-43d5-e7f2d7cc9db3	MGTD	607425aa-07c7-461a-8a0d-72993ff5ac97	b5555749-b87c-4516-99c9-3940c2cb167a	b5555749-b87c-4516-99c9-3940c2cb167a	7BA84428 [DM.Pipeline.Diagnostics] Exception data: Microsoft.Data.Mashup.ValueError.Reason = <pi>DataSource.Error</pi>
DM.EnterpriseGateway Error: 0 : 2021-05-28T08:16:56.1801627Z DM.EnterpriseGateway	1df837c5-441c-4efa-8d0a-4535b4b5ea81	34ca72cb-2844-ebdb-43d5-e7f2d7cc9db3	MGTD	607425aa-07c7-461a-8a0d-72993ff5ac97	b5555749-b87c-4516-99c9-3940c2cb167a	b5555749-b87c-4516-99c9-3940c2cb167a	1225614D [DM.Pipeline.Common.TracingTelemetryService] Event: FireActivityCompletedWithFailureEvent (duration=2055, err=MashupDataAccessValueException, rootcauseErrorEventId=0)
DM.EnterpriseGateway Error: 0 : 2021-05-28T08:16:56.1801627Z DM.EnterpriseGateway	ac9af32c-d963-4947-97e6-21cd5a85c0ac	34ca72cb-2844-ebdb-43d5-e7f2d7cc9db3	MGEC	607425aa-07c7-461a-8a0d-72993ff5ac97	b5555749-b87c-4516-99c9-3940c2cb167a	b5555749-b87c-4516-99c9-3940c2cb167a	669B8A7A [DM.Pipeline.Common.TracingTelemetryService] Event: FireActivityCompletedWithFailureEvent (duration=2085, err=MashupDataAccessValueException, rootcauseErrorEventId=0)
DM.EnterpriseGateway Error: 0 : 2021-05-28T08:16:56.1811409Z DM.EnterpriseGateway	5438b017-9d88-41e0-a808-edd376cbd666	34ca72cb-2844-ebdb-43d5-e7f2d7cc9db3	MGPP	607425aa-07c7-461a-8a0d-72993ff5ac97	b5555749-b87c-4516-99c9-3940c2cb167a	b5555749-b87c-4516-99c9-3940c2cb167a	171C71E8 [DM.Pipeline.Common.TracingTelemetryService] Event: FireActivityCompletedWithFailureEvent (duration=2090, err=MashupDataAccessValueException, rootcauseErrorEventId=0)
DM.EnterpriseGateway Error: 0 : 2021-05-28T08:16:56.2465725Z DM.EnterpriseGateway	af4d5279-7eb4-4fa9-bd91-c5144bc256ec	34ca72cb-2844-ebdb-43d5-e7f2d7cc9db3	MDSR	607425aa-07c7-461a-8a0d-72993ff5ac97	b5555749-b87c-4516-99c9-3940c2cb167a	b5555749-b87c-4516-99c9-3940c2cb167a	ED937467 [DM.GatewayCore] Error processing request: [0]Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.MashupDataAccessValueException: Mashup expression evaluation error. Reason: .

GatewayPipelineErrorCode=DM_GWPipeline_Gateway_MashupDataAccessError
GatewayVersion=
Reason= --->
[1]Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.GatewayPipelineWrapperException: Substituted: MashupValueException:<pi>Microsoft.Data.Mashup.MashupValueException (0x80004005): We couldn't parse OData response result.  Error: The context URI 'http://localhost:9090/tagetikcpm/api/aih/data/v1/TGK_06/001/$metadata#Collection(CCHTagetik.Aih.type_000002)' is not valid for the expected payload kind 'Collection'.
   at Microsoft.Data.Mashup.ProviderCommon.MashupResource.<>c__DisplayClass46_2`1.<StartEvaluationAndGetResultSource>b__0()
   at Microsoft.Mashup.Security.Impersonation.RunAsProcessUser[T](Func`1 func)
   at Microsoft.Data.Mashup.MashupCommand.EvaluateAndGetSource[T](String commandText, CommandType commandType, Int32 commandTimeout, MashupParameterCollection parameters, String resultTransform, Boolean forColumnInfo, Boolean executeAction)
   at Microsoft.Data.Mashup.MashupCommand.ExecuteReader(CommandBehavior commandBehavior, MashupCommandBehavior mashupCommandBehavior)
   at Microsoft.Data.Mashup.MashupCommand.ExecuteReader()
   at Microsoft.Data.Mashup.DataSourceReference.TestConnection(String connectionString)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayDataAccess.GatewayDataAccessMashupUtils.DSRTestConnectionAsync(String dsrJson, MashupCredential credential, Nullable`1 activityId, Int32 testConnectionTimeout, String[] exemptions, String containerPool)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayDataAccess.MashupOleDbConnectionProvider.<TestConnectionAsyncImpl>d__5.MoveNext()</pi>

GatewayPipelineErrorCode=DM_GWPipeline_UnknownError
GatewayVersion=
InnerType=MashupValueException
InnerMessage=<pi>We couldn't parse OData response result.  Error: The context URI 'http://localhost:9090/tagetikcpm/api/aih/data/v1/TGK_06/001/$metadata#Collection(CCHTagetik.Aih.type_000002)' is not valid for the expected payload kind 'Collection'.</pi>
InnerToString=<pi>Microsoft.Data.Mashup.MashupValueException (0x80004005): We couldn't parse OData response result.  Error: The context URI 'http://localhost:9090/tagetikcpm/api/aih/data/v1/TGK_06/001/$metadata#Collection(CCHTagetik.Aih.type_000002)' is not valid for the expected payload kind 'Collection'.
   at Microsoft.Data.Mashup.ProviderCommon.MashupResource.<>c__DisplayClass46_2`1.<StartEvaluationAndGetResultSource>b__0()
   at Microsoft.Mashup.Security.Impersonation.RunAsProcessUser[T](Func`1 func)
   at Microsoft.Data.Mashup.MashupCommand.EvaluateAndGetSource[T](String commandText, CommandType commandType, Int32 commandTimeout, MashupParameterCollection parameters, String resultTransform, Boolean forColumnInfo, Boolean executeAction)
   at Microsoft.Data.Mashup.MashupCommand.ExecuteReader(CommandBehavior commandBehavior, MashupCommandBehavior mashupCommandBehavior)
   at Microsoft.Data.Mashup.MashupCommand.ExecuteReader()
   at Microsoft.Data.Mashup.DataSourceReference.TestConnection(String connectionString)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayDataAccess.GatewayDataAccessMashupUtils.DSRTestConnectionAsync(String dsrJson, MashupCredential credential, Nullable`1 activityId, Int32 testConnectionTimeout, String[] exemptions, String containerPool)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayDataAccess.MashupOleDbConnectionProvider.<TestConnectionAsyncImpl>d__5.MoveNext()</pi>
InnerCallStack=   at Microsoft.Data.Mashup.ProviderCommon.MashupResource.<>c__DisplayClass46_2`1.<StartEvaluationAndGetResultSource>b__0()
   at Microsoft.Mashup.Security.Impersonation.RunAsProcessUser[T](Func`1 func)
   at Microsoft.Data.Mashup.MashupCommand.EvaluateAndGetSource[T](String commandText, CommandType commandType, Int32 commandTimeout, MashupParameterCollection parameters, String resultTransform, Boolean forColumnInfo, Boolean executeAction)
   at Microsoft.Data.Mashup.MashupCommand.ExecuteReader(CommandBehavior commandBehavior, MashupCommandBehavior mashupCommandBehavior)
   at Microsoft.Data.Mashup.MashupCommand.ExecuteReader()
   at Microsoft.Data.Mashup.DataSourceReference.TestConnection(String connectionString)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayDataAccess.GatewayDataAccessMashupUtils.DSRTestConnectionAsync(String dsrJson, MashupCredential credential, Nullable`1 activityId, Int32 testConnectionTimeout, String[] exemptions, String containerPool)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayDataAccess.MashupOleDbConnectionProvider.<TestConnectionAsyncImpl>d__5.MoveNext()
  ([1]Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.GatewayPipelineWrapperException.StackTrace:)
   at Microsoft.Data.Mashup.ProviderCommon.MashupResource.<>c__DisplayClass46_2`1.<StartEvaluationAndGetResultSource>b__0()
   at Microsoft.Mashup.Security.Impersonation.RunAsProcessUser[T](Func`1 func)
   at Microsoft.Data.Mashup.MashupCommand.EvaluateAndGetSource[T](String commandText, CommandType commandType, Int32 commandTimeout, MashupParameterCollection parameters, String resultTransform, Boolean forColumnInfo, Boolean executeAction)
   at Microsoft.Data.Mashup.MashupCommand.ExecuteReader(CommandBehavior commandBehavior, MashupCommandBehavior mashupCommandBehavior)
   at Microsoft.Data.Mashup.MashupCommand.ExecuteReader()
   at Microsoft.Data.Mashup.DataSourceReference.TestConnection(String connectionString)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayDataAccess.GatewayDataAccessMashupUtils.DSRTestConnectionAsync(String dsrJson, MashupCredential credential, Nullable`1 activityId, Int32 testConnectionTimeout, String[] exemptions, String containerPool)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayDataAccess.MashupOleDbConnectionProvider.<TestConnectionAsyncImpl>d__5.MoveNext()
   --- End of inner exception stack trace ---
  ([0]Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.MashupDataAccessValueException.StackTrace:)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayDataAccess.MashupOleDbConnectionProvider.<TestConnectionAsyncImpl>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayDataAccess.MashupOleDbConnectionProvider.<TestConnectionAsync>d__4.MoveNext()

[Anonymous]

Hi,

In Power BI Desktop with direct use of ODATA connector, inside the machine, the URLs seem to work. 
In this case, I would like to read the data using the "Data gateway" service and the Dataflow (with ODATA connector) as the data source for Power BI dashboard.
I have attached the first example with URL http://localhost:9090/tagetikcpm/api/aih/v1/Datasources  (It seems work) and the second example http://localhost:9090/tagetikcpm/api/aih/data/v1/TGK_06/001/Datasource_000008()  
with the error added in the post above. 
Error context URI 'http://localhost:9090/tagetikcpm/api/aih/data/v1/TGK_06/001/$metadata#Collection(CCHTagetik.Aih.type_000008)' is not valid for the expected payload kind 'Collection'.
The second URL works correctly via browser inside the machine (I have attached the results).
Thanks for your help!
Best Regards

[v-janeyg-msft]

Hi, @Anonymous 

Can you establish a connection in desktop? Can you share the screenshot you configured in the gateway?

It seems that the following parameters can't be recognized, and cloud links may not be supported.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.