Power BI Desktop doesnt work with Oracle-Direct Query relationships

[Anonymous]

Hi,

I have two Oracle tables in Direct Query mode:

 Whenever I add a relationship, Power BI enter in a zombie state. Some interactions with the IU works and the mange relationship windows pop-up, but whatever you click dont do nothing. The program only responds when you try to close it and then this error pop-ups:

The details are:

Feedback Type:
Frown (Error)

Error Message:
A Task's exception(s) were not observed either by Waiting on the Task or accessing its Exception property. As a result, the unobserved exception was rethrown by the finalizer thread.

Stack Trace:
   at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.IExecuteProvider.ExecuteTabular(CommandBehavior behavior, ICommandContentProvider contentProvider, AdomdPropertyCollection commandProperties, IDataParameterCollection parameters)
   at Microsoft.AnalysisServices.AdomdClient.AdomdCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.PowerBI.Client.Windows.AnalysisServices.Relationships.ModelQueryExecutor.ReadRowsUsingColumnNames(AdomdCommand adomdCmd, IReadOnlyDictionary`2 columnNamesMap, List`1 data, CancellationToken cancellationToken)
   at Microsoft.PowerBI.Client.Windows.AnalysisServices.Relationships.ModelQueryExecutor.<>c__DisplayClass5_0.<GetRowRange>b__1()
   at Microsoft.PowerBI.Modeler.EngineCancellableCall.Perform()
   at Microsoft.PowerBI.Client.Windows.AnalysisServices.Relationships.ModelQueryExecutor.GetRowRange(Int64 rowIndex, Int32 rowCount, CancellationToken cancellationToken)
   at Microsoft.PowerBI.Client.Windows.AnalysisServices.Relationships.ModelDataRowSource.GetRows(ModelQueryInfo`2 queryInfo, CancellationToken cancellationToken, Boolean useCache)
   at Microsoft.PowerBI.Client.Windows.AnalysisServices.Relationships.ModelDataSource.GetTableData(ModelQueryInfo`2 queryInfo, CancellationToken cancellationToken, Boolean useCache)
(...)
Stack Trace Message:
A Task's exception(s) were not observed either by Waiting on the Task or accessing its Exception property. As a result, the unobserved exception was rethrown by the finalizer thread.

Invocation Stack Trace:
   at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
   at Microsoft.Mashup.Client.UI.Shared.StackTraceInfo..ctor(String exceptionStackTrace, String invocationStackTrace, String exceptionMessage)
   at Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.GetStackTraceInfo(Exception e)
   at Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.ReportException(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, Boolean useGDICapture)
   at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.<>c__DisplayClass14_0.<HandleException>b__0()
   at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.<>c__DisplayClass0_1.<SendAndMarshalExceptions>b__0(Object null)
(...)

InnerException.Stack Trace Message:
La conversión especificada no es válida.. The exception was raised by the IDataReader interface.

InnerException.Stack Trace:
   at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.IExecuteProvider.ExecuteTabular(CommandBehavior behavior, ICommandContentProvider contentProvider, AdomdPropertyCollection commandProperties, IDataParameterCollection parameters)
   at Microsoft.AnalysisServices.AdomdClient.AdomdCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.PowerBI.Client.Windows.AnalysisServices.Relationships.ModelQueryExecutor.ReadRowsUsingColumnNames(AdomdCommand adomdCmd, IReadOnlyDictionary`2 columnNamesMap, List`1 data, CancellationToken cancellationToken)
   at Microsoft.PowerBI.Client.Windows.AnalysisServices.Relationships.ModelQueryExecutor.<>c__DisplayClass5_0.<GetRowRange>b__1()
   at Microsoft.PowerBI.Modeler.EngineCancellableCall.Perform()
   at Microsoft.PowerBI.Client.Windows.AnalysisServices.Relationships.ModelQueryExecutor.GetRowRange(Int64 rowIndex, Int32 rowCount, CancellationToken cancellationToken)
   at Microsoft.PowerBI.Client.Windows.AnalysisServices.Relationships.ModelDataRowSource.GetRows(ModelQueryInfo`2 queryInfo, CancellationToken cancellationToken, Boolean useCache)
   at Microsoft.PowerBI.Client.Windows.AnalysisServices.Relationships.ModelDataSource.GetTableData(ModelQueryInfo`2 queryInfo, CancellationToken cancellationToken, Boolean useCache)
   at Microsoft.PowerBI.Client.Windows.AnalysisServices.Relationships.ModelAsyncDataSource.<>c__DisplayClass13_0.<GetTableData>b__0(CancellationToken cancellationToken)
   at System.Threading.Tasks.ContinuationResultTaskFromTask`1.InnerInvoke()
   at System.Threading.Tasks.Task.Execute()
--- 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.Client.Windows.AnalysisServices.Relationships.RelationshipManager.<GetTopValues>d__48.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.Client.Windows.AnalysisServices.Relationships.RelationshipManager.<DetectCardinality>d__49.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.Client.Windows.AnalysisServices.Relationships.RelationshipManager.<DetectRelationshipCardinality>d__47.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.Client.Windows.FloatingDialog.KoEditRelationshipDialog.<>c__DisplayClass37_1.<<UpdateRelationshipColumns>b__1>d.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.Client.Windows.FloatingDialog.KoEditRelationshipDialog.<HandleAsyncDataExceptions>d__53.MoveNext()

InnerException.Invocation Stack Trace:
   at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
   at Microsoft.Mashup.Client.UI.Shared.FeedbackErrorInfo.GetFeedbackValuesFromException(Exception e, String prefix)
   at Microsoft.Mashup.Client.UI.Shared.FeedbackErrorInfo.CreateAdditionalErrorInfo(Exception e)
   at Microsoft.Mashup.Client.UI.Shared.FeedbackErrorInfo..ctor(String message, Exception exception, Nullable`1 stackTraceInfo, String messageDetail)
   at Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.ReportException(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, Boolean useGDICapture)
   at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.<>c__DisplayClass14_0.<HandleException>b__0()
   at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.<>c__DisplayClass0_1.<SendAndMarshalExceptions>b__0(Object null)
   at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
   at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
   at System.Delegate.DynamicInvokeImpl(Object[] args)
   at System.Windows.Forms.Control.InvokeMarshaledCallbackDo(ThreadMethodEntry tme)
   at System.Windows.Forms.Control.InvokeMarshaledCallbackHelper(Object obj)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Windows.Forms.Control.InvokeMarshaledCallback(ThreadMethodEntry tme)
   at System.Windows.Forms.Control.InvokeMarshaledCallbacks()
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
   at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
   at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
   at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
   at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
   at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
   at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
   at Microsoft.Mashup.Client.UI.Shared.WebDialogs.WebDialog.<>n__0(IWindowHandle owner)
   at Microsoft.Mashup.Client.UI.Shared.WindowManager.ShowModal[T](T dialog, Func`1 showModalFunction)
   at Microsoft.Mashup.Client.UI.Shared.Ux.FloatingDialog.SimpleDialog.MessageDialog.ShowYesNoCancel(IWindowHandle owner, IUIHost uiHost, LocalizedString title, LocalizedString message, LocalizedString yesText, LocalizedString noText, LocalizedString cancelText)
   at Microsoft.Mashup.Client.UI.Shared.Ux.WindowService.ShowYesNoCancelDialog(LocalizedString title, LocalizedString message, LocalizedString yesText, LocalizedString noText, LocalizedString cancelText)
   at Microsoft.PowerBI.Client.Windows.MainWindow.ShowYesNoCancelSaveChangesPrompt()
   at Microsoft.PowerBI.Client.Windows.MainWindow.<TrySaveAndClose>d__191.MoveNext()
   at System.Runtime.CompilerServices.AsyncTaskMethodBuilder.Start[TStateMachine](TStateMachine& stateMachine)
   at Microsoft.PowerBI.Client.Windows.MainWindow.TrySaveAndClose()
   at Microsoft.PowerBI.Client.Windows.MainWindow.<>c__DisplayClass186_0.<<OnWindowClosing>b__1>d.MoveNext()
   at System.Runtime.CompilerServices.AsyncTaskMethodBuilder.Start[TStateMachine](TStateMachine& stateMachine)
   at Microsoft.PowerBI.Client.Windows.MainWindow.<>c__DisplayClass186_0.<OnWindowClosing>b__1()
   at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.<HandleAwaitableAsyncExceptions>d__1.MoveNext()
   at System.Runtime.CompilerServices.AsyncTaskMethodBuilder.Start[TStateMachine](TStateMachine& stateMachine)
   at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.HandleAwaitableAsyncExceptions(IExceptionHandler exceptionHandler, Func`1 asyncFunc)
   at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.<HandleAsyncExceptions>d__0.MoveNext()
   at System.Runtime.CompilerServices.AsyncVoidMethodBuilder.Start[TStateMachine](TStateMachine& stateMachine)
   at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.HandleAsyncExceptions(IExceptionHandler exceptionHandler, Func`1 asyncFunc)
   at Microsoft.PowerBI.Client.Windows.MainWindow.OnWindowClosing(CancelEventArgs e)
   at Microsoft.Mashup.Host.Models.EventHandlerExtensions.RaiseEvent[TEventArgs](EventHandler`1 handler, Object sender, TEventArgs args)
   at System.Windows.Forms.Form.CheckCloseDialog(Boolean closingOnly)
   at System.Windows.Forms.Form.WmClose(Message& m)
   at Microsoft.Mashup.Client.UI.Windows.WebDialogWindow.WndProc(Message& message)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
   at System.Windows.Forms.UnsafeNativeMethods.CallWindowProc(IntPtr wndProc, IntPtr hWnd, Int32 msg, IntPtr wParam, IntPtr lParam)
   at System.Windows.Forms.UnsafeNativeMethods.CallWindowProc(IntPtr wndProc, IntPtr hWnd, Int32 msg, IntPtr wParam, IntPtr lParam)
   at System.Windows.Forms.NativeWindow.DefWndProc(Message& m)
   at System.Windows.Forms.Form.DefWndProc(Message& m)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.Form.WmSysCommand(Message& m)
   at Microsoft.Mashup.Client.UI.Windows.WebDialogWindow.WndProc(Message& message)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
   at System.GC._WaitForPendingFinalizers()
   at System.GC._WaitForPendingFinalizers()
   at Microsoft.Mashup.Client.UI.Windows.HostedWebBrowser.Dispose(Boolean disposing)
   at System.ComponentModel.Component.Dispose()
   at Microsoft.Mashup.Client.UI.Shared.WebDialogs.WebDialog.Dispose(Boolean disposing)
   at Microsoft.PowerBI.Client.Windows.FloatingDialog.KoEditRelationshipDialog.ShowCreate(IWindowService windowService, IUIHost uiHost, IRelationshipManager manager, String fromTableName, String fromColumnName, String toTableName, String toColumnName)
   at Microsoft.PowerBI.Client.Windows.Commands.ApplicationCommands.ModelingCommands.<>c__DisplayClass31_0.<CreateRelationship>b__1()
   at Microsoft.PowerBI.Client.Windows.AnalysisServices.Relationships.RelationshipExceptionHelpers.ConvertOperationExceptions(Action action)
   at Microsoft.PowerBI.Client.Windows.Commands.ApplicationCommands.ModelingCommands.<>c__DisplayClass31_1.<CreateRelationship>b__0(IExtendedModelChangeScope modelChangeScope)
   at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.<>c__DisplayClass0_1.<SendAndMarshalExceptions>b__0(Object null)
   at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
   at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
   at System.Delegate.DynamicInvokeImpl(Object[] args)
   at System.Windows.Forms.Control.InvokeMarshaledCallbackDo(ThreadMethodEntry tme)
   at System.Windows.Forms.Control.InvokeMarshaledCallbackHelper(Object obj)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Windows.Forms.Control.InvokeMarshaledCallback(ThreadMethodEntry tme)
   at System.Windows.Forms.Control.InvokeMarshaledCallbacks()
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
   at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
   at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
   at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
   at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
   at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
   at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
   at Microsoft.Mashup.Client.UI.Shared.WebDialogs.WebDialog.<>n__0(IWindowHandle owner)
   at Microsoft.Mashup.Client.UI.Shared.WindowManager.ShowModal[T](T dialog, Func`1 showModalFunction)
   at Microsoft.PowerBI.Client.Program.<>c__DisplayClass4_0.<Main>b__1()
   at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.<>c__DisplayClass3_0.<HandleExceptionsWithNestedTasks>b__0()
   at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
   at Microsoft.PowerBI.Client.Program.Main(String[] args)


Model Default Mode:
DirectQuery

Snapshot Trace Logs:
C:\Users\myuser\Microsoft\Power BI Desktop Store App\FrownSnapShot382427415.zip

Performance Trace Logs:
C:\Users\myuser\Microsoft\Power BI Desktop Store App\PerformanceTraces.zip

Enabled Preview Features:
PBI_NewWebTableInference
PBI_showManageAggregations
PBI_improvedFilterExperience
PBI_keyDrivers

Disabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_SpanishLinguisticsEnabled
PBI_showIncrementalRefreshPolicy
PBI_qnaLiveConnect
PBI_userFavoriteResourcePackagesEnabled

Disabled DirectQuery Options:
TreatHanaAsRelationalSource

Cloud:
GlobalCloud

Formulas:


section Section1;

shared BI_MVW_HECHOS_EPC = let
    Source = Oracle.Database("oracle_server/dbbi", [HierarchicalNavigation=true]),
    OLAP = Source{[Schema="OLAP"]}[Data],
    BI_MVW_HECHOS_EPC1 = OLAP{[Name="BI_MVW_HECHOS_EPC"]}[Data]
in
    BI_MVW_HECHOS_EPC1;

shared BI_MVW_DIM_CENTRO = let
    Source = Oracle.Database("oracle_server/dbbi", [HierarchicalNavigation=true]),
    OLAP = Source{[Schema="OLAP"]}[Data],
    BI_MVW_DIM_CENTRO1 = OLAP{[Name="BI_MVW_DIM_CENTRO"]}[Data]
in
    BI_MVW_DIM_CENTRO1;

Works with relations between tables in import and/or dual mode, but whenever one of the tables is in Direct Query the error trigger.

The conector version installed is the recomended here.

Any one of you have the same problem?

[v-qiuyu-msft]

Hi @Anonymous, 

Based on my test in Power BI desktop version 2.70.5494.761 64-bit (June 2019), I'm able to create a relationship between these two table when they are both in DirectQuery mode, or one is Import mode and the other is DirectQuery mode. Please run the same Power BI desktop version as ours then test again. 

Best Regards,
Qiuyun Yu 

[Anonymous]

Hi @v-qiuyu-msft,

Thanks for the answer.

I think I just find the problem.

Reviewing the documentation I have seen that Direct Query only supports Oracle version 12 or higher and my Oracle db is 11g.

Cheers,