Connecting to data sources through Power BI is an easy way to get analytics and insights on data that is important to you. Power BI is designed to work well with a variety of data sources including Oracle.
Important considerations for connecting to data in Power BIhttps://support.powerbi.com/knowledgebase/articles/475435-important-considerations-for-connecting-to-data-in
Had a Customer that was having issues connecting to his Oracle data source via Power BI Desktop and then publishing it to his Power BI site.
This got me thinking, what is the way to connect to Oracle using the Power BI Desktop and Power BI site?
So I am going to explain how to get Oracle data published from the Power BI desktop to the Power BI site.
On the client machine you are going to create your Oracle report from, make sure you have the correct Oracle Service ID setup as that will be the connection information you will need to connect to Oracle as your data source when creating a connection in Power BI Desktop. In this case I found my Oracle Service Id to be “SPORTS”.
Now that we know what our Oracle Service ID is from the TNSNames.ora file, we can go to Power BI Desktop and connect to the data we want from Oracle.
Go to your Power BI site and download Power BI Desktop
Once you download you should see an icon on your desktop that looks like the one below:
Open the Power BI Desktop application Once the Desktop icon appears.
Once the application opens up you should see a window that gives you an option to “Get Data”. Go ahead and click on that so we can get started creating our data source
Then once you clicked the “Get Data” option you get a list of all the data sources you can choose. Go ahead and choose the “Oracle Database” option then click “Connect”.
Now here is where some people could get confused. As with most oracle type connections all you need for the server name is what is in the TNS.Names file we referenced earlier. So even though my Oracle server name is Oracle11gMG, all I need to connect to my Oracle server is the name “SPORTS”.
Then when it prompts you for access type choose “Database” and type in the in the actual Oracle database credentials to access the database you want.
So if all this was entered correctly you should get a list of tables to choose data from such as below. So now we can start creating a report with an Oracle dataset to Publish to the Power BI site and setup Scheduled Refresh.
We are going to create a visualization using the “Mark.NFL” table. And as you can see when you select a certain table the data is previewed on the right.
Then on the right are the fields you can show and present
You will now see in the space that a visualization will appear based on the Oracle data you chose.
Now we can publish this data to our Power BI site. So after clicking the Publish button at the top save the pbix file. I am naming mine Oracle NFL.pbix.
When completed as you can see the process above you will see on your Power BI Site that you will have a report under “Reports” name “ORACLE NFL” and under the Datasets section you will also have a dataset named “ORACLE NFL” as well
Now we are ready to setup “Scheduled Refresh”. Under the DataSets section, click on the ellipses then SCHEDULE REFRESH:
Then you will you get a screen for Settings for setting up Schedule Refresh:
Verify that your Personal Gateway is online and active and that the credentials are correct:
To verify Credentials, click on “Edit credentials” and fill in what they should be. With Oracle you have to use a Basic Authentication Method. Then after that just fill in the Database’s Username and Password and Sign in.
After that click on the “NO” button (to change it to “YES”) under Schedule Refresh to start configuring then click Apply.
After all that has been setup and configured you will see that Refresh is active.
by: Mark Ghanayem | Microsoft SQL Server Business Intelligence