cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
arify Established Member
Established Member

Re: Connect to OBIEE RPD using ODBC Connection

@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 Established Member
Established Member

Re: Connect to OBIEE RPD using ODBC Connection

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

wbsissonii Frequent Visitor
Frequent Visitor

Re: Connect to OBIEE RPD using ODBC Connection

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

wbsissonii Frequent Visitor
Frequent Visitor

Re: Connect to OBIEE RPD using ODBC Connection

@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

wbsissonii Frequent Visitor
Frequent Visitor

Re: Connect to OBIEE RPD using ODBC Connection

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

arify Established Member
Established Member

Re: Connect to OBIEE RPD using ODBC Connection

Hi @wbsissonii

 

Sorry for the late reply, I just came back from vacation as well Smiley Happy

 

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

Murthy New Member
New Member

Re: Connect to OBIEE RPD using ODBC Connection

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.

Highlighted
aassem Frequent Visitor
Frequent Visitor

Re: Connect to OBIEE RPD using ODBC Connection

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

Shankar_R Frequent Visitor
Frequent Visitor

Re: Connect to OBIEE RPD using ODBC Connection

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

kegauvre Occasional Visitor
Occasional Visitor

Re: Connect to OBIEE RPD using ODBC Connection

Hi all!

 

Any development regarding the error of "Array dimensions exceeded supported range"? Where are facing the same issue.

 

Thx

 

Kevin