Connect to OBIEE RPD using ODBC Connection

[wonga]

@arify@wbsissonii

I get the following error when trying to access the Oracle environment through ODBC:

Error Message:
Array dimensions exceeded supported range.
Stack Trace:
Microsoft.Mashup.Evaluator.Interface.ErrorException: Array dimensions exceeded supported range. ---> System.OverflowException: Array dimensions exceeded supported range. ---> System.OverflowException: Array dimensions exceeded supported range.
at Microsoft.Mashup.Engine1.Library.Odbc.OdbcPageReader.OdbcPage.GetData(SQL_C cType, Int32 rowIndex, Int32 columnIndex, Int64 pos)
at Microsoft.Mashup.Engine1.Library.Odbc.OdbcPageReader.OdbcPage.LoadCellData(ColumnInfo columnInfo, Column column, Int32 rowIndex, Int32 rowSetIndex, Int32 columnIndex)
at Microsoft.Mashup.Engine1.Library.Odbc.OdbcPageReader.OdbcPage.SyncColumnsPage(Byte* boundColumnsDataPtr)
at Microsoft.Mashup.Engine1.Library.Odbc.OdbcPageReader.OdbcPage.Read(OdbcStatementHandle statement, RowRange rowRange)
at Microsoft.Mashup.Engine1.Library.Odbc.OdbcPageReader.Read(OdbcPage page)
at Microsoft.OleDb.Serialization.PageReaderDataReader.Read()
at Microsoft.Mashup.Engine1.Library.Common.DbData.Serialize(IDataReader reader, Stream stream)
at Microsoft.Mashup.Engine1.Library.Odbc.OdbcCachingService.OdbcCachingConnection.CacheReader(String key, IDataReader dataReader)
at Microsoft.Mashup.Engine1.Library.Odbc.OdbcCachingService.OdbcCachingConnection.Cache(String[] keyParts, Func`2 func)
at Microsoft.Mashup.Engine1.Library.Odbc.OdbcTracingService.OdbcTracingConnection.<>c__DisplayClass17.<GetTables>b__16(IHostTrace trace)
at Microsoft.Mashup.Engine1.Library.Common.Tracer.Trace[T](String method, Func`2 func)
at Microsoft.Mashup.Engine1.Library.Odbc.OdbcTracingService.OdbcTracingConnection.GetTables(String catalogName, String schemaName, String tableName, String tableType)
at Microsoft.Mashup.Engine1.Library.Odbc.OdbcHierarchicalNavigationTableValue.OdbcCatalogLevelNavigationTableValue.<get_CatalogNames>b__f(IOdbcConnection connection)
at Microsoft.Mashup.Engine1.Library.Odbc.OdbcDataSource.<>c__DisplayClassc`1.<Connect>b__b()
at Microsoft.Mashup.Engine1.Library.Common.DbExceptionHandler.RunWithRetryGuard[T](Int32 maxRetryAttempts, Func`1 action)
at Microsoft.Mashup.Engine1.Library.Common.DbExceptionHandler.Invoke[T](Int32 maxRetry, Func`1 action)
at Microsoft.Mashup.Engine1.Library.Odbc.OdbcHierarchicalNavigationTableValue.OdbcCatalogLevelNavigationTableValue.get_CatalogNames()
at Microsoft.Mashup.Engine1.Library.Odbc.OdbcHierarchicalNavigationTableValue.OdbcCatalogLevelNavigationTableValue.GetEnumerator()
at Microsoft.Mashup.Engine1.Language.Query.SkipTakeEnumerable.GetEnumerator()
at Microsoft.Mashup.Engine1.Language.Query.QueryTableValue.GetEnumerator()
at Microsoft.Mashup.Engine1.Runtime.TableValue.Microsoft.Mashup.Engine.Interface.ITableValue.GetEnumerator()
at Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.ValuePreviewValueSource.TableValuePreviewValueSource.SerializeRows(Int32 count)
at Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.ValuePreviewValueSource.TableValuePreviewValueSource.get_SmallValue()
at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.<>c__DisplayClass5.<RunStub>b__3()
at Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Action action)
--- End of inner exception stack trace ---
at Microsoft.Mashup.Evaluator.EvaluationHost.<>c__DisplayClass7.<TryReportException>b__6()
at Microsoft.Mashup.Common.SafeExceptions.IgnoreSafeExceptions(IHostTrace trace, Action action)
at Microsoft.Mashup.Evaluator.EvaluationHost.TryReportException(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Exception exception)
at Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Action action)
at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.RunStub(IEngineHost engineHost, IMessageChannel channel, Func`1 getPreviewValueSource)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.<>c__DisplayClass28`1.<OnBeginGetResult>b__25()
at Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Action action)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.OnBeginGetResult[T](IMessageChannel channel, BeginGetResultMessage message, Action`1 action)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.OnBeginGetPreviewValueSource(IMessageChannel channel, BeginGetPreviewValueSourceMessage message)
at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.OnMessageWithUnknownChannel(IMessageChannel baseChannel, MessageWithUnknownChannel messageWithUnknownChannel)
at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.EvaluationHost.Run()
at Microsoft.Mashup.Container.EvaluationContainerMain.Run(Object args)
at Microsoft.Mashup.Evaluator.SafeThread2.<>c__DisplayClass15.<CreateAction>b__14(Object o)
at Microsoft.Mashup.Container.EvaluationContainerMain.SafeRun(String[] args)
at Microsoft.Mashup.Container.EvaluationContainerMain.Main(String[] args)
--- End of inner exception stack trace ---
at Microsoft.Mashup.Evaluator.EvaluationHost.OnException(IEngineHost engineHost, IMessageChannel channel, ExceptionMessage message)
at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.Interface.IMessageChannelExtensions.WaitFor[T](IMessageChannel channel)
at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.WaitFor(Func`1 condition, Boolean disposing)
at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.get_SmallValue()
at Microsoft.Mashup.Host.Document.Analysis.PackageDocumentAnalysisInfo.PackagePartitionAnalysisInfo.SetPreviewValue(EvaluationResult2`1 result, Func`1 getStaleSince, Func`1 getSampled)
Invocation Stack Trace:
at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
at Microsoft.Mashup.Client.ClientShared.StackTraceInfo..ctor(String exceptionStackTrace, String invocationStackTrace)
at Microsoft.Mashup.Client.ClientShared.DataImporter.HandleImportEvaluationException(ExceptionResult exceptionView, Query query, String sourceID, String formulaTitle, Boolean isNewQuery, Boolean isFromEditor)
at Microsoft.Mashup.Client.ClientShared.DataImporter.OnGetPreviewResult(PreviewResult preview, Query query, String sourceID, String formulaTitle, Nullable`1 explicitImportDestination, Boolean isNewQuery, Boolean isFromEditor)
at Microsoft.Mashup.Client.ClientShared.DataImporter.<>c__DisplayClass17.<OnQuerySettingsResolved>b__14()
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
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.ClientShared.WindowManager.ShowDialog[T](T form, IWin32Window owner)
at Microsoft.PowerBI.Client.Program.<>c__DisplayClassc.<Main>b__0()
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
at Microsoft.PowerBI.Client.Program.Main(String[] args)

[arify]

Hi @wonga, this looks like a new bug. Can you please send us a frown? We'll fix this soon. The reason is, (at least) one of the columns is really big. Do you have a guess which column is the big one?

In the meantime, I can give you a work around to remove that column for now (hopefully you don't need that column immediately) :

1. Open your table in the Query Editor & get the error
2. In the ribbon, there's a Keep Rows button. Click "Keep Top Rows" in there and enter 0 to the "Number of rows" field. Click OK.
3. Now you should be seeing the columns. Right click on the column you think is the big one and click "Remove".
4. On the right side where you see the "Applied Steps", click the X next to the step that removes the rows (if you're using English PowerBI Desktop, the step's name is probably "Kept First Rows").
   1. 
5. It'll give you a warning. Click "Delete" on the prompt. Now you should be seeing your table without that column.

Please let me know how it went 🙂

Thanks

[wonga]

@arify I am not able to load any tables when connecting through ODBC. After I input the query as you laid out, it gives me that error regarding array dimensions. 

The "Keep Rows Button" is greyed out, see below screenshot for more information. A frown was sent on 7/25 regarding this issue.

Thanks.

[arify]

@wonga , I believe the stack trace you sent us was from the previous attempt (where HierarchicalNavigation was set to true).

Can you please give me the stack trace of the latest error? (when HierarchicalNavigation is set to false)

Thanks

[wonga]

@arify I just sent a frown with the latest error. Mentioned your PBI community ID in the e-mail, thanks.

[arify]

@wonga Thanks, I received it. This error is happening in somewhere I wouldn't expect it to happen. I'm wondering if it's a driver issue.

Can you try this?

let
    Source = Odbc.Query("dsn=YOURDSN", "select * from YOURTABLENAME")
in
    Source

Thanks

[wonga]

@arify

The ODBC connection is to an OBIEE Application Server I think, so I'm not sure what I should be putting in for table name.

I would be selecting items from the presentation layer which would consist of Subject Areas.

[arify]

@wonga, do you have another tool to connect your OBIEE server to see the table names? Maybe you can see what tables you need from there, and then type that in PBI?

[wonga]

@arify

I do have a tool to do just that, but the point of using this ODBC connection is to avoid having to reference table names and instead reference the subject areas (which contain all the aggregates). If I were to reference the table names, I would just connect directly to the Oracle Database using the built-in connector.

Interesting thing is that before in June's release of PBI Desktop, I was able to connect to the Application Server using ODBC but like @wbsissonii mentioned, no data shows up, only the names of the subject areas.

[arify]

@wongain that case, you might need to wait for the fix to be released. Meanwhile, can you still do that just so we can diagnose the problem correctly and make sure we're not missing another problem?

Thanks