Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 visualizations and reports.
Has anyone tried doing this and found a solution? Thanks.
Have you tried both of my suggestions? I'm assuming the second one I suggested is the one that showed you the null values (even though you set the HierarchicalNavigation to true and removed that #(0000) part).
How about the first one, where you set the HierarchicalNavigaton = false without the 2nd step?
Like,
let Source = Odbc.DataSource("dsn=YOURDSN", [HierarchicalNavigation=false]) in Source
@arify After trying out your first suggestion, I get:
Empty table with some new columns (Item, Schema and Catalog). Thanks.
Oh, that's interesting @wonga . In your DSN, have you set the database parameter?
@arify, By database parameter, do you mean the last page of configuring the DSN where you select a database and input login credentials?
If so, then no I did not do that. I have tried to in the past, but everytime I do it, it pops up with a dialog box and a yellow "!". After checking the settings again, it doesn't look like the credentials were saved or anything.
Example:
That.. looks sad. It doesn't look like this is a properly working driver. Luckily we have other things to try as a workaround 🙂
It probably won't work on your first try, you might need to add more parameters or change some of the keys (for example Server (or Hostname) instead of Host, or Db instead of Database etc.) Unfortunately I don't know about OBIEE and I couldn't find an example connection string on the internet (I only looked for a few minutes).
2. (This is probably easier) On the PBI's ODBC dialog where you pick the DSN, after picking the DSN, you can expand "Advanced Options" and enter a SQL query (e.g. select * from db.tablename)
Hi,
Did you manage to get this option working?
Using web serviecs is another option - OBIEE Metadata Service
Thanks
Has anyone found a solution for this?
I'm experiencing the same issue. Here is a bit of background:
We have been using OBIEE for more about 8 years and have built all of our business models, dashboards and anlaysis using OBIEE. We are now at a point we are looking at other analytics tools to either replace or suplement OBIEE. However, we don't want to lose all of the phsycial and busienss models that we created over that time. Recently we have looked at Tableau and Microsoft Power BI as possible alternatives. As such, I have downloaded desktop versions of each applciation to test.
I would appeciate any suggestinos in solving this issue and guideance for integrating the OBIEE models into Power BI. In addition, if there is a tool for migrating OBIEE RPD metadata into Power BI that would be very usefull as well.
Thanks...
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
@arifyI added the code in the Advanced Editor. It now brings up the hierarchy from the subject area for the tables but does not include the columns. So:
I see the Subject Area that I'm expexting and all of the tables and indented tables but no columns to select data from.
Ex:
SubjectAreaGlobal
-SubAreaGlobalPerson
--TableDimensionPerson
--TableDimensionMeasurementType
--TableDimensionMeasurementDates
--FactMeasurements
I would expect to see columns to select from beneath each of the tables (--) such as PersonID, PersonName, MearuementType, MearuementDate, etc....
The query editor recongizes this because it has indentifed each of the areas as Data Type = Table and ABC Kind = Table.
If I click on the table hyperlink for the row of the table I get mixed results:
I click on one of the dimensions (measurement) and I see the data from the table of the underlying database.
I click on the dimension for Person and I get an error message:
DataSource.Error: ODBC: ERROR [HY000] [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred.
ERROR [HY000] [nQSError: 43113] Message returned from OBIS.
ERROR [HY000] [nQSError: 43119] Query Failed:
ERROR [HY000] [nQSError: 17001] Oracle Error code: 904, message: ORA-00904: "T944"."CHILDLMISPLITMEASURETYPE": invalid identifier
at OCI call OCIStmtExecute.
ERROR [HY000] [nQSError: 17010] SQL statement preparation failed.
I click on one of the Fact tables and it actually interpreted my aggreation for me and returned the appropriate response.
The solution appaers to be working but I have some additional research to do. Will the fix be in future reelase so that we can import the models through the ODBC?
Thanks
Figured out the error message and got it resolved. Here is my current state:
(1) I can create a query using the following Advanced Editor script:
let
Source = Odbc.DataSource("dsn=OBIEE Analytics", [HierarchicalNavigation=false])
in
Source
This gives me a list of the Subject area tables and when I select the table hyperlink it displays the data. I can filter the data in the results and perform other manipulations.
(2) However, this does not solve what I need to which is model the data. When I go to model the data I still get my original ODBC connection which lists the subject area names but does not provide a list of the tables/columns so that I can pull them into the model.
What I think would solve this problem is:
(1) Have the list of tables that was generated in the Query solution show up in the modeling tool
(2) Be able to drag those tables into the model
(3) I would like to have the joins inherited as well but at least if I can get the tables in the modeling tool I would expect that I could create my joins.
Please advise regarding a method to model the OBIEE presentation layer.
Thanks...
Hi @wbsissonii
Sorry for the late reply, I just came back from vacation as well 🙂
When we change the default query by making HierarchicalNavigation=false, we're sacrificing the nice navigator experience where you can load multiple tables in 1 go. But when we do this, it just loads 1 table and you can't pick multiple tables with checkboxes. But it's still very easy to do it, you can just add a new query to the same ODBC datasource to load your other table.
About the Subject Areas (I'm assuming that's the equivalent of a catalog which contains schemas in OBIEE? I don't have experience on OBIEE), when we turn the HierarchicalNavigation off, we're only seeing the default catalog. This means if you want to access other Subject Areas, you can create new DSNs for them, and when you're creating them, pick the desired Subject Area as the default catalog when you're creating the new DSN.
Thanks
Hi,
i'm using below M query to connect to RPD
let
Source = Odbc.DataSource("dsn=MYDSN", [HierarchicalNavigation=false])
in
Source
on month of october it used to work, now if i use the same code it throws error.. i don't know what i'm doing wrong..
Error:
DataSource.Error: ODBC: ERROR [08004] [NQODBC] [SQL_STATE: 08004] [nQSError: 10018] Access for the requested connection is refused.
[nQSError: 43113] Message returned from OBIS.
[nQSError: 43126] Authentication failed: invalid user/password.
Details:
DataSourceKind=Odbc
DataSourcePath=dsn=MYDSN
OdbcErrors=Table
PBI Version:
PBI Version: 2.42.4611.901 64-bit (January, 2017)
my connections works.. i can connect online RPD and able to test ODBC and get list of Subject areas from my machine.
@arify I apologize for not responding sooner but I have been traveling/out the last 2 weeks and am now just getting back to the office. I'm trying to catch up and try the variuos things that have been suggested. I did notice in one of the posts that a different user was experiencing the same issubt but had it working in June. As per your suggestion about versions. I'm using version: Version: 2.37.4464.361 64-bit (July, 2016)
Can you please indicate the version that ou want me to test?
Thanks...
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)
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
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) :
Please let me know how it went 🙂
Thanks
@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.
@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
@arify I just sent a frown with the latest error. Mentioned your PBI community ID in the e-mail, thanks.
@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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |