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
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
Option B – Connection String
- If you experience an error with the connection string above being longer that 128 characters use option B
HOST_NAME:PORT_NUM/SERVICE_NAME
Launch Power BI and navigate to Get data
- Paste the connection string into the 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
- Click connect, and if the Oracle connection gods are smiling on you today you will see this:
Good luck!!
