Microsoft Power BI Desktop can connect to multiple data sources, one of the most popular datasources is Oracle Database. There are serveral client softwares can be used to connect to Oracle Database, Oracel SQL Developer might be the most popular one. Custom JDBC URL/Connection string with LDAP authentication is one of the multiple connections ways in SQL Developer to connect to an Oracle Database. Intead of installing and configuring an Oracle Data Access Client (ODAC) that needs to be compatible with your Oracle Server described in Power BI’s official document, this post describes a way of figuring out the
ServerName/ServiceName through the custom JDBC connection string in SQL Deverloper and use it to connect to an Oracle Database in Power BI Desktop.
Picture comes from (https://unsplash.com/@goumbik)
Custom JDBC URL
The picture above shows an example of settings of Custom JDBC connection to an Oracle Database. The conncetion string in the Custom JDBC URL box usually has the format below:
Transfer Custom JDBC URL to LDAP Connection
With the above custom JDBC URL, following the steps below, you can create a new LDAP connection which can show the real
ServerName/ServiceName information that is required by Power BI to connect to the Oracle Database:
- Create a new connection with a new Name in SQL Developer
- Fill in your Username and Password
LDAPin the Connection Type drop-down list
- Put the
ldapservername:portfrom the above custom JDBC URL into the LDAP Server field
- The Context should be loaded autoamatically
- Load the
DBservicenameas the DB Service
- Test the connection, if succeffuly, click the Save button.
After create the new connection, if you can expand the left panel, you will find the Connection Details of the new LDAP connection, shown as the highlighted area below:
The Connection Details has the following format:
Connect to Oracle Database in Power BI Desktop
With the above connection details information, you can take the following steps to conncet to the Oracle Database in Power BI Desktop:
- From the Home tab, select Get Data
From the Get Data window that appears, select More (if necessary), select Database > Oracle database, and then select Connect
You might get the warning message on recommended provider shown as below. Then click on the
Learn morelink to download and install ODAC. I have installed 64-bit ODAC 12.2c Release 1 (184.108.40.206.1) for Windows x64 using all default settings.
- Copy the
[servername]:[serverport]/[servicename]part from the above connection details in SQL Developer to the Server field shown as below, then click OK.
The process described in this post is not the best way to connect to Oracel Database. You might notice that the
[servername]:[serverport]/[servicename] in the connection details of the LDAP connection might change from time to time, because the Oracel server might be behind a network Load Balancer. In this case, you can refer to this post, this post, and this post for the settings of ldap.ora for an Oracel client.
Power BI Official Document: https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-connect-oracle-database#installing-the-oracle-client
Making Database Connections from Oracle SQL Developer: https://blogs.oracle.com/oraclemagazine/making-database-connections