How to Access Oracle from BI Tools: Tableau, Power BI, DBxtra
Tableau, Power BI, and DBXtra are powerful business intelligence and reporting tools that you can use to create dashboards and perform analytics. To connect these tools to an Oracle server and retrieve raw data, you need to use a data interface (API) that is understandable by both the BI tool and database server. One of such APIs for accessing database management systems is Open Database Connectivity (ODBC), widely supported by vendors of database systems and third-party tools.
The tool that serves as middleware between an Oracle server and an external application is called an ODBC driver. You can choose a free Oracle ODBC driver from the database vendor or a commercial driver from a connectivity solution vendor like Devart.
This tutorial assumes that you’re using Devart ODBC Driver for Oracle on Windows. If you would like to know the pros and cons of the free driver and the commercial driver, see the link to the comparison table at the end of this article.
To install Devart ODBC driver on Windows, simply run the installer and follow the steps. If you're going to install the driver on Linux or macOS, read the tutorials for Linux or macOS, respectively.
After successfully installing the driver, create a DSN for Oracle:
- Type ODBC data sources in the Windows search box.
- Select the ODBC Data Source Administrator (the bitness of the ODBC Administrator must match the bitness of your BI tool).
- Click Add… and select Devart ODBC Driver for Oracle from the list of installed drivers, then click Finish.
- Give a name to your DSN in the Data Source Name field.
- Check the Direct option. You can connect to Oracle Database in two modes: the Direct mode and the OCI mode. In the Direct mode, you don’t have to install Oracle client software on the client machine.
- Specify your host name or IP address, port, service name, username and password. Optionally, specify the Connect role.
- Test the connection and click OK to save the DSN.
Connecting to Oracle from Tableau
- Run Tableau and click More in the Connect menu.
- Select Other Databases ODBC.
- Expand the DSN list and select the previously configured DSN.
- Click Connect.
- After a successful connection, click Sign in.
- Enter the name of the database schema in the corresponding field. Now you should be able to see the list of all tables you have access to in Oracle.
- Drag-and-drop the table name to the Drag tables here to retrieve data area.
- Click Update Now to retrieve the data.
Connecting to Oracle from Power BI
- Run Power BI and click Get Data.
- Select the Other category in the list of data sources, then choose ODBC.
- Expand the list of data source names and locate the previously configured DSN.
- You should now be able to see the structure of your Oracle database and preview the data in it.
- To load the data into Power BI, select a table and click Load.
Connecting to Oracle from DBxtra
- Run DBxtra and select Project on the Home tab.
- Name your new project and click Create.
- Select ODBC Connection as the Data Connection Type.
- Click the browse button … and select Microsoft OLE DB Provider for ODBC drivers on the Provider tab.
- On the Connection tab, select the previously configured DSN from the Use data source name dropdown.
- Name your connection and select User Groups who can view this connection.
- Double-click the newly created connection in Project Explorer to connect to the data.
An Example of ODBC Connection String for Oracle in the Direct Mode:
Using Service Name:
DRIVER={Devart ODBC Driver for Oracle};Direct=True;Host=myServer;Service Name=myServiceName;User ID=myUsername;Password=myPassword
Using SID:
DRIVER={Devart ODBC Driver for Oracle};Direct=True;Host=myServer;SID=mySID;User ID=myUsername;Password=myPassword
An Example of ODBC Connection String for Oracle in the OCI Mode:
DRIVER={Devart ODBC Driver for Oracle};Data Source=myServer;Home Name=myHomeName;User ID=myUsername;Password=myPassword
Devart ODBC Drive Against Database Vendor Driver: Comparison Table