I was recently working in an environment where the requirement was to connect to an Oracle database using the On-Premise Gateway, so that we could either DirectQuery or import the data and then refresh it using the On-Premise Gateway. So the steps below detail how to complete this successfully.

The example below was connecting to an Oracle databased called TRID

Downloading and installing Oracle Data Access Components (ODAC)

The installation of the ODAC is fairly straight forward and already has been very well documented in the Power BI website.

You can view the link below in order to download and install the correct ODAC version for your Power BI Desktop Installation.

Connect to an Oracle database

NOTE: Part of the installation is that you will need to setup and configure your tnsnames.ora file in order to successfully connect to the Oracle Database.

Configuration of tnsnames.ora

These steps below were configured by the Oracle DBA where I am currently working, I thought I would highlight what needs to be configured below in order to successfully connect to the Oracle Database.

I found this out when trying to connect to the Oracle database and it appears that this is what is required to ensure that I could connect successfully.

Below is a working version of my tnsnames.ora file

TRID=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=svr-tridb-01)
(PORT=1521)
)
(CONNECT_DATA=
(SERVICE_NAME=trid.domain.com.au)
)
)

The important things that I needed to have configured were the following:

  • The first part is the name which is highlighted in GREEN above with my server called TRID
  • The other important part is to ensure that you have got the SERVICE_NAME
    configured and resolves back to the actual server with the Fully Qualified Domain Name (FQDN) highlighted in PURPLE.
    • As with my above example this was SERVICE_NAME=trid.domain.com.au

Setting the connection to the Oracle database in Power BI Desktop

The next step was for me to set up or create the connection to my Oracle Database.

NOTE: In my example below we connected using a database user and password and not integrated Windows Authentication.

  • I clicked on Get Data and then selected Oracle database
  • Then I clicked Connect.
  • NOTE: In my instance I got the following window shown, which explains that I have an older version of ODAC, I clicked Ok to continue
  • On the Oracle database window it now prompted me for the Server and if I am going to use the Import or DirectQuery Data Connectivity mode, as well as additional advanced options.
    • In my example I simply put in the Server Name and left it defaulted to Import
    • I then clicked Ok
  • The first time I connected it prompted me for the Security Connection details.
    • As mentioned before I used the Database security connection details
    • I then clicked Connect.
  • This then brought up the Navigator, which from then I could then connect to my required tables.
  • And then loaded the data into my data model and completed my Power BI Desktop File.
  • Once I had completed my Power BI Desktop file I then uploaded it into the Power BI Service.

Configuration of the Power BI Service Gateway

In the steps below I now will show how I created and configured the Gateway to connect to the Oracle database.

NOTE: I had already installed and configured the On-Premise Gateway within the domain I am working in. You can reference this installation guide: On-premises data gateway

  • I logged into the Power BI Service and then clicked on Manage Gateways
  • I then clicked on Add Data Source
  • Now this is one of the MOST IMPORTANT configuration steps when I was configuring the Data Source Name
    • This Data Source Name MUST be identical to the Server Name I created in my Power BI Desktop File Data Source Connection.
    • As I did previously I used the Oracle Server name TRID
    • So now when I created my new Data Source I configured it with the following below, again NOTING that the Data Source Name is identical to my Power BI Desktop File Server Name as highlighted below.
    • Then I clicked Apply.
    • And as you can see above it says Connection Successful which means that my Gateway successfully connected to the Oracle database.
  • The final step that I did was to give the required users access to the Gateway
    • NOTE: You must add the users in here that you want to be able to connect to the Gateway Data Source and configure the data refresh.

Configuring the dataset to use the Gateway connection

The final step that I had to complete was to configure my dataset to use the Gateway connection.

  • I went into the Power BI Service.
  • Then clicked on Settings and selected Settings
  • Then I clicked on Datasets
  • I then went to my dataset that I had previously uploaded, where I wanted to connect to it using the Gateway connection.
    • I then expanded the Gateway connection
    • Now I had the option to Use a data gateway
    • And in here I selected my Gateway that I had previously configured.
    • I then clicked Apply.
  • Then the final step was to test that it was all working and I did this by going into the Datasets and clicking Refresh now
  • Once that had completed I went back into Settings, then Datasets and clicked on my dataset.
    • Then at the top I clicked on Refresh History
    • And then I could view that my refresh completed successfully.

This will hopefully guide you to get your Oracle connected with the On-Premise Gateway.

Another quick note is that the steps when connecting to a SQL Server are very similar, especially the steps in the Power BI Service.

Any comments or suggestions are welcome and please leave them in the section below.