Forum Discussion

Continued Contributor

10 years ago

Connect to OBIEE RPD using ODBC Connection

I'm trying to connect to the repository for OBIEE using an ODBC connection and the list of subject areas appears to load in Power BI, but I am unable to load any data into Power BI in order to make v...

Need Help

arify

Microsoft Employee

10 years ago

Hi wbsissonii,

Please make sure you installed the latest version (that got released a few days ago) and then do this workaround:

Go to Get Data > Blank Query > (In the query editor) Advanced Editor > Paste this:

let
    Source =  Odbc.DataSource("dsn=YOURDSN", [HierarchicalNavigation=false])
in
    Source

Please let me know how it went.

Thanks

wonga

Continued Contributor

10 years ago

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
Microsoft Employee
10 years ago
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) :
Open your table in the Query Editor & get the error
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.
Now you should be seeing the columns. Right click on the column you think is the big one and click "Remove".
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").
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
Continued Contributor
10 years ago
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
Microsoft Employee
10 years ago
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
Continued Contributor
10 years ago
arify I just sent a frown with the latest error. Mentioned your PBI community ID in the e-mail, thanks.
arify
Microsoft Employee
10 years ago
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
Continued Contributor
10 years ago
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
Microsoft Employee
10 years ago
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
Continued Contributor
10 years ago
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
Microsoft Employee
10 years ago
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
arify
Microsoft Employee
10 years ago
wonga, we have another customer who is using the same driver & datasource but they aren't getting this failure. In Excel, there are 2 things you can use to connect to ODBC, under Data tab, they're called "Microsoft Query" and "Data Connection Wizard". Can you try to connect with them and let us know if that works?
wonga
Continued Contributor
10 years ago
arify I only had 32-Bit versions of Office to test with and it isn't able to load any tables (says something along the lines of "There are no visible tables"). There might be something in the Oracle RPD that is not exposing the tables to 3rd party applications possibly...
aassem
Microsoft Employee
9 years ago
Dears,
Has there been any solution to that error of "Array dimensions exceeded supported range"? I am facing the same error whether I am connecting from the normal ODBC connector or connecting using the Advanced Editor.

Yours,
Ahmed Assem

Forum Discussion

Connect to OBIEE RPD using ODBC Connection

Related content

Connection failed using a Folder connection in a pipeline

Power BI Oracle Connection Error – ORA-50201 (Failed to connect / parse connect string)

connection to dataflows

dataflow connection

Database connectivity.