How to Connect Microsoft Power BI to Oracle Database and Pull Data
Vložit
- čas přidán 5. 02. 2022
- In this short video I am demonstrating how to connect Microsoft Power BI to Oracle Database and Pull Data to display in your report. Microsoft Power BI is a very useful tool that provides business analytics service with interactive visualizations and intelligence capabilities for end users to create their own reports and dashboards.
The desktop version of this tool is free to download and I have given below the link to download it. You can download, install this tool and learn to create informative and interactive reports and dashboards. You need to have proper license to publish your work.
Please download and install appropriate version of ODAC (Oracle Data Access Client) on the PC from were you are trying to connect to the Oracle database. The link to download ODAC is:
www.oracle.com/database/techn...
(I am yet to upload a video on that)
Power BI Desktop version download link:
www.microsoft.com/en-us/downl...
Oracle Express Edition download link:
www.oracle.com/database/techn...
Full video series on Power BI:
• Power BI
This is just one step I was expecting you to showcase the entire Oracle Data Access Client (ODAC) installation
Thanks for your feedback. I will create a separate video on ODAC.
@@YouVolve Thanks 🙏🏻
Actually you don't need an ODAC for the connection. Instead visit the link below, go to the "Oracle Client for Microsoft Tools" section and download the appropriate version (32-bit/64-bit) as per your requirement.
www.oracle.com/database/technologies/net-downloads.html
Thanks alot for such a valuable information
Thanks for your feedback.
Is it also possible in your video you could show the properties dialog box pop up of oracle encircling the different entities used to make up the 'server' connection. In this case its 'Host Name + Port Number + Service Name' that needs to be assembled into the string used to make the connection?
Thanks for your feedback. I will see what additional enhancements can be made for the video. However, there is limited scope to do that for the uploaded videos.
@@nathanbaruah2581 that particular frame solved my problem. i could guess what was wrong with my configs
Thanks for your feedback
Thank you my friend very useful
Thanks Eddy. Glad that it helped.
Thanks
Excelente!
Thanks for your feedback
Thanks for the sharing. Can I use the same approach to access all data available on Oracle Analytics Cloud (OAC)?
Hi @amyyy58, Power BI and OAC are both identical in the sense they can be used as an analytical tool. You have to chose one of these for your purpose and based on which one you select your connection mechanism will vary.
Thanks for the video how to make the same thing for power bi report builder ?
Thanks for watching my video. Power BI and Power BI Report Builder both work nice together. When you start the report builder, it gives the option to import the existing Power BI datasets under the Data ribbon. If you don't want an existing dataset, you can create a new for the report builder by selecting one of the databases listed as SQL Server, Oracle, Teradata etc under the same ribbon under Database section. That will take you to the next screen where you specify the connection string and the credentials almost same way as you did in Power BI.
Awesome.
Glad you think so!
what is the difference between import and direct query??
Hello Humza, thanks for your question. Import means the data will be imported when you refresh the underlying dataset. On the other hand, if you use direct query method, the SQL query that pulls the data from the data source will be executed every time the report is loaded or run. If the underlying SQL query is very expensive and takes time to run, it is better to avoid the direct query method if you know that the underlying data is not changing very frequently or it is within acceptable change limit.
If I only have read-only access for Oracle Database, can I still import data from Oracle database?
Yes, For importing data to PowerBI, read only access will work.
A good video
Thanks for your feedback
I need help to make a connection with Oracle Database and Metabase... Anyone can help?
To connect to any Oracle database, apart from the username and password, you need 3 pieces of information: the server name, port and the service name/sid. You should get these information from the DBA of the database.
While doing this sometimes getting tns error in power bi
@ashwinkumar5223 - Can you elaborate? What error are you getting?
Where to find the address of Oracle server? Thanks
Hi Lily, please ask the Oracle DBA of your organization for the information below to be able to connect:
1) The Oracle server or the host name
2) The listener port number
3) The database service name
4) Database login credentials (Username and Password)
have connected power bi desktop with oracle database and when i try to load data from oracle db to power bi it loads only 4285 rows i expect to load 7lakh rows into power bi desktop
Hi, PowerBI will limit the number of rows only when it shows the preview of data the first time after you establish the connection. When you go to the "Data View", you will see all the rows returned by the SQL query.
@@YouVolve i did not write amy query i directly connected by mentioning the ip address port and host details
Hi, if you do not provide any query then, after you connect, it will display the navigation window from where you have to select the tables that the user owns or where it has SELECT access. It may be confusing to a new user to use the navigation at the very first time if he or she is not familiar with the Oracle database and security features. So, in the video, I suggested to write a simple query against the table(s) from where you actually want to pull the data and use that query in the "SQL statement (optional)" box under "Advanced options" section of the window where you provide the connection details. It will be easier and also you can modify the SQL query if needed later on. Hope this will help.
@@YouVolve i wrote the statement as select table name; but still it dint work
What is the error message? Can you pls share the SQL statament you are using?
am facing an error that oracle data access client is not available on my laptop
Thanks for your question. What platform (32 or 64 bit?) and version of Windows are you using?
@@YouVolve windows 10 enterprise and power bi 64 bit pro version
Please do not install ODAC and instead go to the link below, download and install the Oracle Client for Microsoft Tools and that will work
www.oracle.com/database/technologies/net-downloads.html
or, search for the below text in Goggle and go to the section as mentioned above:
oracle client for microsoft tools ODP.NET
@@YouVolve oracle client has different components which one to select
Once you open the page using the link I have given, go down to the "Oracle Client for Microsoft Tools" section in the page and click on the link "Oracle Client for Microsoft Tools 19c (64-bit)"
Details: "Oracle: ORA-12154: TNS:could not resolve the connect identifier specified"
Error while connecting Oracle to Power BI.
How to resolve this?
Thanks for your question and watching my video.
One or more of your connection parameters are incorrect.
Please doublecheck the below:
1) Host or the Server Name
2) The listener port
3) The SID or Service name of the database that is registered with the listener
In most cases it is the #3 parameter above.
You should get all the above details from your DBA to validate.
@@YouVolve
Now this error has been resolved.
Thank you very much.
But now another one is there while connecting Oracle to Power BI.
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER
@@priyavishalkulkarni2415 you cannot use SYS account to connect PowerBI with Oracle database as that is for the DBAs or administrators of the database to use. You have to use another account that was created for you or the SYSTEM account. If you don't have one or don't know, you may ask your DBA to provide the username and password to connect.
Hi.. I am also getting same in my laptop. How you resloved this
@@ashwinkumar5223 Thanks for your question and watching my video.
This error comes when one or more of your connection parameters are incorrect.
Please doublecheck the below:
1) Host or the Server Name
2) The listener port
3) The SID or Service name of the database that is registered with the listener
In most cases it is the #3 parameter above that causes this error.
background music is ruined my concentration over your video. Silence helps in concentration.
Thanks for your honest feedback. With due respect to viewers like you, I no more add background music to my recent videos. However, this one is an old one. I will see if I can filter it without compromising the overall audio quality.
@@YouVolve thanks dear.