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”.

image

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

clip_image002

Once you download you should see an icon on your desktop that looks like the one below:

clip_image003

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

image

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”.

image

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”.

image

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.

clip_image008

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.

image

Then on the right are the fields you can show and present

clip_image011

You will now see in the space that a visualization will appear based on the Oracle data you chose.

clip_image013

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.

clip_image014

clip_image015

clip_image016

clip_image017

clip_image018

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

image

Now we are ready to setup “Scheduled Refresh”. Under the DataSets section, click on the ellipses then SCHEDULE REFRESH:

clip_image020 

Then you will you get a screen for Settings for setting up Schedule Refresh:

clip_image021

Verify that your Personal Gateway is online and active and that the credentials are correct:

clip_image022

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.

clip_image023

After that click on the “NO” button (to change it to “YES”) under Schedule Refresh to start configuring then click Apply.

clip_image024

clip_image025

After all that has been setup and configured you will see that Refresh is active.

by: Mark Ghanayem | Microsoft SQL Server Business Intelligence