Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
wonga
Continued Contributor
Continued Contributor

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 visualizations and reports.

 

Has anyone tried doing this and found a solution? Thanks.

47 REPLIES 47

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

 

wonga
Continued Contributor
Continued Contributor

@arify After trying out your first suggestion, I get:


rpd 4.PNG

 

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?

wonga
Continued Contributor
Continued Contributor

@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:

 

rpd 5.PNG

That.. looks sad. It doesn't look like this is a properly working driver. Luckily we have other things to try as a workaround 🙂

 

  1. You can try connecting via using connection string instead of DSN. In PBI, when you click ODBC, instead of picking a DSN, you would type a connection string. I'm guessing it would be like: Driver={yourdrivername}; Host=yourservername; Database="Procurement and Spend - Purchase Orders"

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.

 

  1. I setup an ODBC connection using the Oracle BI Server driver to point to the Presentation Layer Subject areas of OBIEE.  The ODBC driver successfully displays the Subject areas.
  2. In Tableau I connect to the ODBC data source and it allows me to view the OBIEE Subject Area and all of the tables and columns (data objects) within the Subject Area.  It also allows me to pull data data  models into Tableau for reporting.
  3. In Power BI I connect to the ODBC data source and it dispalys my subject areas (similiar to the discussion and images contained in this stream).  However, when I click on one of the Subject areas it does not expand to show me the tables and columns within the Subject area (per the discussion and images here).  I do not get any messages other than no data available. 

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...

wonga
Continued Contributor
Continued Contributor

@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)

 

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) :

  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. Untitled.png
  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
Continued Contributor
Continued Contributor

@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.

 

arraydimerror.PNG

 

 

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

wonga
Continued Contributor
Continued Contributor

@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

 

wonga
Continued Contributor
Continued Contributor

@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.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.