Connecting Power BI to Oracle

By Andrew BilsdonData, Microsoft, Oracle, Power BI, SQL

Ok, a quick post on connecting Power BI to Oracle. I wanted to do some simple testing by looking at row counts across a Oracle database and a SQL Server database to make sure Data Factory was keeping them in sync.

I under lined simple as I am aware there are many short comings in this approach, however, it serves a purpose.

  • Obtain the connection details for the Oracle db. In my case, I am using Oracle SQL Developer and have existing connections set up. These are the fields you will need if you are using a similar approach
Oracle Connection Parameters

Option A – Connection String

  • Open Notepad, or similar and transpose the data obtain from SQL Developer, or elsewhere, in the following format:

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host2a)(PORT=1630))(CONNECT_DATA=(SERVICE_NAME=sales.us.acme.com)))

https://docs.oracle.com/cd/B12037_01/network.101/b10776/tnsnames.htm

Connection String

Option B – Connection String

  • If you experience an error with the connection string above being longer that 128 characters use option B

  • Open Notepad, or similar and transpose the data obtain from SQL Developer, or elsewhere, in the following format:

    • HOST_NAME:PORT_NUM/SERVICE_NAME

    Launch Power BI and navigate to Get data

    Navigate to Power BI
    • Paste the connection string into the Server Name
    Server Name
    • If you get an error that the connection string is too long (e.g. greater that 128 characters) use option B above
    • Enter the user name and password under the database tab
    User Name and Password
    • Click connect, and if the Oracle connection gods are smiling on you today you will see this:

    Good luck!!