Oracle Refresh Error using gateway, PBI-Desktop refresh works fine

[soptim_vond]

Hi,

I have a PBI-Report that uses an oracle database as main source. The report has been refreshing fine for the past weeks using the enterprise gateway. However, since this morning I get an error (ORA-01722: Invalid Number). So, in order to locate the field in the data source I refreshed the dataset in PBI-Desktop, but there the refresh works fine...so I have no idea how to fix this since I cannot look into the the details of what goes wrong during the process.

Any ideas?

Thanks a lot in advance,

Nico

Here´s the error message:

Fehler bei der letzten Aktualisierung: Thu Aug 17 2017 06:23:33 GMT+0200
Mit der Mashupdatenquelle kann keine Verbindung hergestellt werden. Weitere Informationen finden Sie in den Fehlerdetails.Details ausblenden

Zugrunde liegender Fehlercode:	-2147467259
Zugrunde liegende Fehlermeldung:	Oracle: ORA-01722: invalid number
DM_ErrorDetailNameCode_UnderlyingHResult:	-2147467259
Microsoft.Data.Mashup.ValueError.DataSourceKind:	Oracle
Microsoft.Data.Mashup.ValueError.DataSourcePath:	proee.soptim.net
Microsoft.Data.Mashup.ValueError.ErrorCode:	-2147467259
Microsoft.Data.Mashup.ValueError.Message:	ORA-01722: invalid number
Microsoft.Data.Mashup.ValueError.Reason:	DataSource.Error
Cluster-URI:	WABI-EUROPE-NORTH-B-redirect.analysis.windows.net
Aktivitäts-ID:	1613cf2c-954e-4953-9fcc-3934dd2bbc6c
Anforderungs-ID:	c5b7d1bc-ab16-464e-a5a4-a525181a204e
Zeit:	2017-08-17 04:23:33Z

[v-qiuyu-msft]

Hi @soptim_vond,

1. The error message "Oracle: ORA-01722: invalid number" indicates the attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Check if any step to convert string to number in your report.

2. Please try to update the on-premise data gateway to the latest version.

3. Please share log files around the time that error occurs:

On-premises data gateway service logs

C:\Users\PBIEgwService\AppData\Local\Microsoft\on-premises data gateway\Gateway*.log

Best Regards,
QiuyunYu

[soptim_vond]

Hi QiuyunYu,

first of all, thanks for the quick reply.

I know what the error message means, however I don´t understand why the conversion from string to number that is apparantly happening somewhere would not be a problem in PBI-desktop (sincer I can refresh without any error), but would crash the refresh via the gateway?

Moreover, it´s hard to find out which transformation-step causes this since I don´t get any info on the table, column or field in question.

I don´t know how to uploag files, so here´s an excerpt from the log file containing the error message:

DM.EnterpriseGateway Warning: 0 : 2017-08-17T04:17:33.6411420Z DM.EnterpriseGateway    c49d8fba-c500-4904-9af3-0b915c8c8b87    c5b7d1bc-ab16-464e-a5a4-a525181a204e    MGGC    1613cf2c-954e-4953-9fcc-3934dd2bbc6c    2477DA7B [DataMovement.PipeLine.GatewayDataAccess] Cannot read EnableFastCombine value
DM.EnterpriseGateway Error: 0 : 2017-08-17T04:17:33.8130072Z DM.EnterpriseGateway    4a2cb4b2-1d62-484c-9150-4d0ac24c3748    c5b7d1bc-ab16-464e-a5a4-a525181a204e    MGEA    1613cf2c-954e-4953-9fcc-3934dd2bbc6c    98861D64 [DM.Pipeline.Common] Non-gateway exception encountered in activity scope: Microsoft.Data.Mashup.MashupValueException (0x80004005): Oracle: ORA-01722: invalid number
   bei Microsoft.Data.Mashup.ProviderCommon.MashupResource.StartEvaluationAndGetResultSource[T](Int32 timeout)
   bei Microsoft.Data.Mashup.MashupCommand.EvaluateAndGetSource[T](String commandText, CommandType commandType, Int32 commandTimeout, MashupParameterCollection parameters, String resultTransform, Boolean forColumnInfo, Boolean executeAction)
   bei Microsoft.Data.Mashup.MashupCommand.ExecuteReader(CommandBehavior commandBehavior, MashupCommandBehavior mashupCommandBehavior)
   bei Microsoft.PowerBI.DataMovement.Pipeline.MashupCommon.MashupUtils.ExecuteReaderAsync(DbCommand command, ExecuteQueryRequest queryRequest)
   bei Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessor.<>c__DisplayClass3_1.<<ExecuteAdoQuery>b__1>d.MoveNext()
--- Ende der Stapelüberwachung vom vorhergehenden Ort, an dem die Ausnahme ausgelöst wurde ---
   bei System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   bei System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   bei Microsoft.PowerBI.DataMovement.Pipeline.Common.Diagnostics.PipelineTelemetryService.<ExecuteInActivity>d__7`1.MoveNext()
DM.EnterpriseGateway Error: 0 : 2017-08-17T04:17:33.8130072Z DM.EnterpriseGateway    4a2cb4b2-1d62-484c-9150-4d0ac24c3748    c5b7d1bc-ab16-464e-a5a4-a525181a204e    MGEA    1613cf2c-954e-4953-9fcc-3934dd2bbc6c    AF779819 [DM.Pipeline.Common.TracingTelemetryService] Event: FireActivityCompletedWithFailureEvent (duration=161, err=MashupValueException, rootcauseErrorEventId=0)
DM.EnterpriseGateway Warning: 0 : 2017-08-17T04:17:33.8130072Z DM.EnterpriseGateway    f83d0dd4-165d-4a35-88c7-16a48f19cb4f    c5b7d1bc-ab16-464e-a5a4-a525181a204e    MGEQ    1613cf2c-954e-4953-9fcc-3934dd2bbc6c    80336231 [DM.GatewayCore] Swallowing exception during Ado query execution attempt and triggering a retry: Microsoft.Data.Mashup.MashupValueException (0x80004005): Oracle: ORA-01722: invalid number
   bei Microsoft.Data.Mashup.ProviderCommon.MashupResource.StartEvaluationAndGetResultSource[T](Int32 timeout)
   bei Microsoft.Data.Mashup.MashupCommand.EvaluateAndGetSource[T](String commandText, CommandType commandType, Int32 commandTimeout, MashupParameterCollection parameters, String resultTransform, Boolean forColumnInfo, Boolean executeAction)
   bei Microsoft.Data.Mashup.MashupCommand.ExecuteReader(CommandBehavior commandBehavior, MashupCommandBehavior mashupCommandBehavior)
   bei Microsoft.PowerBI.DataMovement.Pipeline.MashupCommon.MashupUtils.ExecuteReaderAsync(DbCommand command, ExecuteQueryRequest queryRequest)
   bei Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessor.<>c__DisplayClass3_1.<<ExecuteAdoQuery>b__1>d.MoveNext()
--- Ende der Stapelüberwachung vom vorhergehenden Ort, an dem die Ausnahme ausgelöst wurde ---
   bei System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   bei System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   bei Microsoft.PowerBI.DataMovement.Pipeline.Common.Diagnostics.PipelineTelemetryService.<ExecuteInActivity>d__7`1.MoveNext()
--- Ende der Stapelüberwachung vom vorhergehenden Ort, an dem die Ausnahme ausgelöst wurde ---
   bei System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   bei System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   bei Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessor.<>c__DisplayClass3_0.<<ExecuteAdoQuery>b__0>d.MoveNext()

[AlexBrisebois]

I have similar error. when I change an Oracle view structure (fields in the SELECT part of the view).

It works fine from PBI Desktop, but from PowerBI Online, I get similar error.

Same thing if I change the order of the field in the SELECT part of the view.

I get Mashup error or ORA error saying cast problem (date into integer for example).

For sure there is no problem with the view sine I can call it from an Oracle SQL client (ex: Toad) without problem as well as in PBI Desktop.

Someway somehow, looks like PBI online cached details Oracle queries in a PBI Dataset and things get messy when you change the structure of the input.

Does it make sense?

[fbottazzoli]

Hi all, 

I'm facing the same issue.. was someone able to fix this issue?

Thank you

Francesca