none
Oracle Source - Same server but different credentials RRS feed

  • Question

  • Hi,

    Using Excel 2016 Get & Transform, i need to get data from Oracle.

    My data are on the same server but on different schema.

    I create my 1st data source, targeting my server, i filled the user/password for the 1st schema i wish to load data from.

    No problem.

    Through the wizard, i again choose Oracle Database as a source, specified the same server and then ...My query is enabled to run because it's supposed to target another schema but it's still using the credentials from the first schema  (as it's the same server).

    How in a single workbook we can source many time the same Oracle server but with different user/password for different schema ?

    Thanks for your help!

    Regards,


    Bertrandr

    Tuesday, June 14, 2016 2:05 PM

Answers

  • It's my understanding that aliases from tnsnames.ora should work, but again, I'm not an Oracle expert.
    • Marked as answer by Bertrandr Monday, June 20, 2016 2:31 PM
    Friday, June 17, 2016 1:45 PM

All replies

  • I'm afraid you can't easily do this today, because we use the server name as a key to lookup the credentials. What you probably need to do to work around this is to use multiple server names to refer to the same underlying server instance. Then each one could be associated with a different set of credentials. One way of doing this might be to use both a name and an IP address -- but the details depend on how you define Oracle server names on the client, and I'm not really familiar with that.
    Tuesday, June 14, 2016 9:23 PM
  • Hi Curt,

    Thx for your answer even if it's not really pleasing me :(

    Unfortunately, If the Oracle database connector can't go further than the actual behavior it's pretty useless.

    It's like being able to source only once a SQL Server instance for one database per Workbook with Power Query.

    My Customer choose to get rid of his BO Universe and instead to use PowerBI.

    BO Universe sources are on a single Oracle server on different schema.

    In my case I have 3 schemas to source and load in PowerBI. All 3 on the same server. I can’t do it….

    It’s a bummer ... really.


    Bertrandr

    Wednesday, June 15, 2016 2:35 PM
  • In Excel, From Other Sources, we can create OLEDB connection targeting Oracle.

    And it's possible to create as many as needed targeting the same server but different schema.

    Would be great to be able to use this Connection as sources for Power Query.

    Maybe it's possible but i don't know how ...


    Bertrandr

    Thursday, June 16, 2016 11:59 AM
  • The only way i can think of this kind of workaround would be if the Oracle Database connector was using tnsname aliases.

    This way you can have as many aliases in your tnsname targeting the same server.


    Bertrandr

    Friday, June 17, 2016 1:25 PM
  • It's my understanding that aliases from tnsnames.ora should work, but again, I'm not an Oracle expert.
    • Marked as answer by Bertrandr Monday, June 20, 2016 2:31 PM
    Friday, June 17, 2016 1:45 PM
  • Curt,

    You're the man ! :)

    It was this.

    I just changed my tnsname to have as many aliases as needed, all pointing to the same server but different schema.

    And now i can't have as many Oracle Database sources as i wish.

    Perfect.

    Thanks a lot for your help !


    Bertrandr

    Monday, June 20, 2016 2:31 PM
  • I can confirm this works. You make multiple tnsnames.ora entries pointing to the same server (e.g. named <Server>_<Schema>) and then Power Query / Get & Transform will treat them as separate sources. You specify the actual schema and password when you first use the connection.
    Thursday, April 20, 2017 10:29 AM