Forum Discussion

wonga's avatar
wonga
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
wbsissonii's avatar
wbsissonii
Frequent Visitor
10 years ago

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's avatar
arify
Microsoft Employee
9 years ago

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

  • Murthy's avatar
    Murthy
    New Member
    9 years ago

    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.