- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi! Did anyone manage to get this working. Using Oracle BI Server ODBC connection and then displaying the tables after expanding a subject area?
I have installed a complete Oracle OBIEE test environment on my PC. The last step is to get this to work. The workaround
let
Source = Odbc.DataSource("dsn=#YOURDSNNAME#", [HierarchicalNavigation=false])
in
Source
is not a good workaround in a self service perspective, the main goal would be for user to see available subject areas and then see the available tables to connect to.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I anm trying to run that script from Advanced Options => SQL Statement:
let
Source = Odbc.DataSource("dsn=OBIPRD", [HierarchicalNavigation=false])
in
Source
But receive Unable to Connect error: Details: "ODBC: ERROR [HY000] [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred.
[nQSError: 43113] Message returned from OBIS.
[nQSError: 27002] Near <Source>: Syntax error [nQSError: 26012] .
"
Please advise
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi! I spent a lot of time struggling to get everything around Oracle to work. One thing that was not obvious was that Microsoft for example did not like the syntax of tnsnames.ora file, It wanted the syntax there exactly as in example on microsoft documentation.
Also I made sure I tested user access and roles in SQL Developer before using it i Power BI.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you to your responce: but I have all the necessary connections and roles. I added this script trying to fix the problem that is the main subject all this long discussion: When I open OBIEE RPD in PowerBI - I cannot drill off a Subject Level. I can use Select query as a data source & Power BI run it (i.e it finds all the necessary tables & columns from the physical Model) , but when open RPD itself I cannot click and expand any subject area.
This topic is discussed and somebody earlier suggested to add script
let
Source = Odbc.DataSource("dsn=OBIEE Analytics", [HierarchicalNavigation=false])
in
Source
to the Advanced Connection: I added it to the SQL Box of Advane Option and got connection error. So my question was : Have I added it to the proper place and if so - what may be wrong?
Appreciate any help
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
This thread seems to have died ....
To be clear. This is for the OBIEE BI Server ODBC connection not the Oracle DB connection.
The BI Server hosts a meta data model over one or more physiucal data stores eg DBs, Excel, WS, etc. The ODBC driver provides a SQL interface that model.
We can use the driver to execute logical SQL against the BI Server model. However, what is more desirable is to be able to select 'tables' from the model and load those into PBI. As has been noted elsewhere, we can see all of the subject areas in the model when we just connect to the BI Server ie no SQL in the Advanced box. But we can not see any lower to be able to select whole tables.
Using the Advanced editor script noted in other posts with the Dec 2018 release.
I can see all the meta data about the tables in the model.
There is a table column. If Iselect a specific row in the data set (for a given table) and then expand the table field. I then get all of the columns in that table. However, all I then get is a single row of data with null values.
It would be good even if this process worked but its not. The desirable behaviour is to select whole tables in the UI.
Thanks
Tim
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi
BI Connector (Power BI Certified) connects Power BI to OBIEE and OAC data sources.
Here're step-by-step tutorials for various scenarios:
Connect Power BI to OBIEE Reports via Import
Connect Power BI to OBIEE Reports via Direct Query
Connect Power BI to OBIEE Subject Areas via Import
Connect Power BI to OBIEE Subject Areas via Direct Query
For more info, check here: https://www.biconnector.com/visualize-obiee-data-in-power-bi/
Cheers!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@arify have we got any updates on this one? We really need this RPD connection to work.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi all!
Any development regarding the error of "Array dimensions exceeded supported range"? Where are facing the same issue.
Thx
Kevin
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@wonga @arify @wbsissonii @Murthy @aassem
An easy way to visualize OBIEE Subject areas and reports with Power BI is by using BI Connector. It's a simple process and it saves a significant amount of time. BI connector integrates Power BI to OBIEE Presentation layer and reuses the metadata and security model.
Here's a video on how it works:
You can sign up for the free beta here: https://www.biconnector.com/blog/connect-power-bi-obiee-rpd-reports/?utm_source=power_bi&utm_campaig...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Was there ever a resolution for this (I'm experiencing the same issue). A bit of history:
- Been working with Oracle OBIEE for about 8 years and are looking at additional/alternative reporting tools but don't want to give up all of the Business Models that we have created in OBIEE
- Testing Tableau and Microsfot Power BI Desktop as alternatives that could then translate to long term solutions
- Setup a OBIEE ODBC connection that connects to OBIEE and dislays a list of Presentation Layer Subject Areas
- Setup a data source in Tableau which then brings up a sujbect area (dsignated) with a list of all of the tables and data objects within the subject area, which I can select into a report (Tableau workins using the ODBC connection)
- In Power BI I select Get Data and then select Other and then ODBC as my data source. I select the same ODBC connection for Power BI that I did for Tableau. It brings up a list of the OBIEE Subject areas but when I click to expand the list to get to the table and data elements no items are displayed. I would have expect a list of subject area tables and then subject area columns (data elements) to be selected to be displayed.
I would include screen shots but they would be indentical to other screen shots in the discussion forum. Please advise how and if there is a solution for this issue. Because we have 8 years of OBIEE data modeling it is important for us to find a way to leverage those models going forward. We would also consider a tool that could migrate those data models, the Oracle RPD, to Power BI.
Thanks...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Are you seeing an error? Can you provide more details?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @arify, no error pops up but please see picture below:
As you can see, I'm trying to expand the "Purchase Orders" subject area but nothing shows up below it implying that the subject area is empty or something. Of course this isn't the case, so it looks like Power BI isn't digging deep enough or something in regards to accessing the RPD through an ODBC connection.
Also notice that the "Load" button is greyed out, implying that there is nothing to load. I can force it to load by right clicking the subject area and clicking "Load", but the end result is an import of a table with no data in it at all, as seen in the below picture:
Expanding the "Data" column results in nothing to expand to, which further implies that there is no data in the subject area from Power BI's point of view.
Thanks.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
We're working on handling a driver bug, this 'could' be one of those bugs we don't handle yet.
Can you open Advanced Query window, and in the M code you see, you'll see something like HierarchicalNavigation=true. Can you set it to false and try again?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@arify, this is the error I get when implementing your suggested change:
Thanks.
EDIT: In reference to your follow up question, I believe the ODBC connector is configured to connect to an Oracle BI Application Server and NOT an Oracle Database.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
This is a new issue, I haven't seen this before. Can you help us fix this?
Can you send us a frown, including your
- M query when HierarchicalNavigation=false,
- PowerBI desktop traces (Options > Diagnostics > Enable tracing and then reproduce this failure. Then, get those files that appeared in the Traces folder)
- ODBC traces
Thanks!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @arify, I sent the requested information earlier today. Is there any way you can confirm receipt of my error submission?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
We got your email, thanks 🙂
Actually, when you set the HierarchicalNavigation to false, can you also remove the last step?
So, your M query should be like this:
let Source = Odbc.DataSource("dsn=#YOURDSNNAME#", [HierarchicalNavigation=false]) in Source
See if that works 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@wonga I also noticed this:
let
Source = Odbc.DataSource("dsn=#YOURDSNNAME#", [HierarchicalNavigation=true]),
#"Procurement and Spend - Purchase Orders#(#)(0000)_Database" = Source{[Name="Procurement and Spend - Purchase Orders#(0000)",Kind="Database"]}[Data]
in
#"Procurement and Spend - Purchase Orders#(#)(0000)_Database";
Looks like we're picking a null character at the end of the database name. From your M query (where HierarchicalNavigation is still set to true) can you remove the #(0000) I striked above?
Note: the code I'm pasting here is incomplete, because of a forum bug it swallows rest of the code after a closing curly bracket character. So, instead of copying this code, change your existing code 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@arify I've tried your suggestion and it doesn't look like it changed anything. One interesting thing to point out is that when you click on the "Source" step under "Applied Steps", it shows a list of elements from the physical layer (e.g. Oracle Data Warehouse). I'm given the option to expand a "Data" column, but the result of that is all "null" values.
Thanks.
EDIT: @arify Do you know if the steps regarding exposing key fields in this page need to be done in order for an ODBC connection to the Oracle BI Server?
http://kb.tableau.com/articles/knowledgebase/tableau-and-obiee

Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - February 2025
Check out the February 2025 Power BI update to learn about new features.

Subject | Author | Posted | |
---|---|---|---|
07-19-2024 01:41 PM | |||
09-20-2024 06:01 AM | |||
09-13-2024 06:09 AM | |||
06-24-2024 12:56 PM | |||
07-22-2024 06:50 AM |
User | Count |
---|---|
87 | |
81 | |
53 | |
38 | |
35 |