Microsoft Power BI is one of the leading tools for analyzing data and generating insights. Power BI is a unified, scalable platform for self-service and enterprise-level business intelligence (BI) tools which helps in connecting to and visualizing any type of data and generating insights from it which help in making better business decisions. With Power BI, amazing data experiences can be created; sharing and collaborating data across other Microsoft tools become easy. Power BI also has AI capabilities which help to get answers to business questions in a conversational manner. This powerful reporting tool has 150+ connectors. One of the popular data connectors is the Oracle database which is a Java-based object that implements the javax. SQL. The Oracle database is one of the world’s leading converged, multi-model database management system (DBMS) and has in-memory, NoSQL, and MySQL databases. Oracle database products reduce operational costs by up to 90%, protect against data breaches and provide high-performance versions of the Oracle database to its customers.
Pre-requisites:
- Microsoft Power BI Desktop (preferably 64-bit should be installed)
- Oracle Data Access Client (ODAC) software 11.2 or greater
The ODAC is comprehensive client support for advanced Oracle database functionality, including performance, high availability, and security. In order to connect to the oracle database, the Oracle.DataAccess.The client should be installed.
Visit the website below to install it.
https://www.oracle.com/database/technologies/odac-downloads.html
- Oracle Server 9 and later
Oracle server is required for enterprise workloads to achieve high performance, security and reliability when using Oracle’s extensive portfolio of x86 and SPARC servers.
Installation Steps to Connect Oracle database to Power BI
During the installation process, step 8 asks to mention the details as per the client TNSNames.ora file. (If not given ask the client to provide one). This includes the port number, connection string, database, and DNS which will ensure the connection.
A typical tnsnames file could look like as below.
Oracle Power BI Connection:
After the successful installation of ODAC and proper tnsnames.ora file configuration now heads back to Power BI for connection setup.
- Go to Get Data and open More.
- Go to Database and select Oracle Database.
- A pop-up window will appear asking for server details, data connectivity mode and advanced options asking for providing command timeout and SQL script.
(Note: Advanced options are not mandatory)
- In the Server provide the details mentioned in tnsnames.ora file in the format
- Select the connectivity mode. The authentication pop-up window will appear where the authentication mode can be selected, and appropriate credentials can be provided.
- After a successful connection, the tables and views present in the database would be visible and analysis can be done.
Using these steps, the data within an Oracle database can be easily analyzed, visuals can be created, and insights can be generated.
Deepanshi Ranka
Deepanshi Ranka is a Senior Associate in BI & Analytics team at Advaiya. She is a Microsoft certified Data Analyst and has an experience of over 3 years. She specializes in analytics, reporting and analytical tools that work seamlessly with business intelligence, data warehousing, architecture, data modelling, and cloud solutions to create effective solution models and optimize the operations.
