Connecting to Oracle Database server is a bit tricky if we do not install ODAC (Oracle Data Access Components) in the server. Without ODAC it will not allow us to connect to the Oracle database. In this blog we will see how to connect to oracle database without ODAC but using ODBC as a channel to connect to the Oracle database.
Do not know how to create a user in the oracle database? Use this link
Open the Oracle Database Server and create a Power Bi User in the Database and the password should be uppercase. Example: ADMIN (If the password is not in Uppercase, we will not be able to setup the ODBC connection. The user highlighted in the picture i.e. testsys is the User which I have created.
Now go to Control Panel Click on System and security > further click on Administrative tools > ODBC Data source 64 bit (in case if you are using Power BI 32bit version then choose 32bit ODBC data source)
Open the ODBC Data Source Administrator.
To create a new Oracle DB ODBC data source, click on the Add button under User DSN or System DSN tab. Both of these tabs are used to create a connection to ODBC data source but the difference is that the User DSN tab will show data source only for a currently logged user on this computer. While creating data sources under the System DSN, it will be available to any user that is logged on to this computer. I’ll choose System DSN.
Click on Add, it will open a (Create New Data Source) Dialog box. Choose an Oracle Database Server and hit Finish
Once we Click on Finish a dialogue box (Oracle ODBC Driver Configuration) pops up. Provide a name to the Data Source and a Service name which should be server/database service name and give the User ID which we have created for Power BI in their respective fields and then Click on Test Connection.
Once we click on Test Connection it opens up a new dialog box (Oracle ODBC Driver Connect) asking for password, now provide the uppercase password.
After entering password click OK , it will show Connection successful message, click on OK to add the data source.
Now the connection is established, and it will sit in the System DSN.
1. Connecting directly to Oracle Database!
Now We can directly connect to the Oracle database by Opening the Power BI Desktop then click on Get Data > click on More and choose Oracle database.
A dialog box pops up, just click OK and it will ask for a Server Name (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL))) Use this string - modify with appropriate details and paste it in the server.
It will ask for credentials so please do provide the User credentials which we have created earlier and click on Connect.
Further, it will open a Navigator here we can select the Tables and click on Load and use the tables to develop a report.
2. Connecting using ODBC Data Source!
We can also connect with ODBC connection; open the Power BI Desktop click on Get data> click on More and choose ODBC which is under Other section and click on Connect.
It opens ODBC dialog box and list all data sources that are listed in the ODBC (Earlier we have created Checking ODBC connection)
Select the ‘ODBC data source connection’ and click OK, it will prompt for database credentials give the Power BI User credential which we have created earlier and click on Connect.
Then it opens the Navigator where we can select the Tables to develop a desired report and click Load.
It loads the data into Power BI Desktop where we can view the tables in the Field Pane.
This is it! Use the #ODBC as a channel to connect to Oracle Database. But if you want to use On-premises data gateway (#PowerBIGateway) to refresh the data in Power BI Service you need to have #ODAC (Oracle Data Access Components) installed and configured in the Oracle database server.
What do you think about it? Please Comment and Share and Follow!!
Comentários