Using Push Dataset from an Oracle Database

[cmzp]

Hi All, I'm looking at using the Power BI Push Dataset feature using data from an Oracle Database. I have an Oracle database that maintains a corporate directory of employees and the group that each individual is in. My goal is to setup a monthly retrieve (either automatic or manual) and pull a count of the number of employees in certain groups. Power BI would then save that count monthly.

The link below describes what I'm looking to do in the "Pushing data to datasets" section and states that Power BI can store the data for historical analysis but does not get into the details on how to connect. Is anyone familiar with this feature and could help?

https://docs.microsoft.com/en-us/power-bi/service-real-time-streaming

I apologize for the lack of technical terms, I'm in finance but hoping to move our function forward when it comes to these tools.

[Mykdiw]

I am facing a simillar problem, my organization also uses an on-premise oracle database. I have to query the database on daily basis for a dashboard in PowerBI. I also dont want the dataset to inculde the enitre data from the beging of time because that would be huge, So to keep track of the performce I have to query the database daily specifing the date which makes a new dataset in PBI Service and I have to delete the old dataset on next day and querry the database again and redo the enitre process for the dashboard I want automate this process where a single query can update the dataet with the new data every day and that day's performace can be reviewed from the Dashboard.

Here is the dashbaord I am using to keep track of the daily performace.

Please help! 🙏🙏🙏

[v-jiascu-msft]

Hi @cmzp,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[v-jiascu-msft]

Hi @cmzp,

You need a bridge to bring the data from Oracle to Power BI. As we can see from the link, we can talk about them one by one.

Quick question: where is the Oracle DB? On-premises or on-cloud?

1. #using-pubnub, but it doesn't store the historical data. Another concern, I don't know if it can connect to your Oracle DB.

2. #using-azure-stream-analytics-to-push-data, It seems it can't connect to Oracle directly. Reference: stream-analytics-define-inputs.

3. The most possible approach could be pushing by using the third application. We need to write several rows of codes or a software like Postman for either the Power BI REST APIs or the Streaming Dataset UI. The difference is the Streaming Dataset UI doesn't require a token. In short, we need a software to get data from Oracle and then push to Power BI. 

BTW, since you are going to do it monthly, why do you want to use the Push Dataset that is used for real-time analysis?

Best Regards,

Dale

[cmzp]

Hi @v-jiascu-msft, thank you so much for your prompt reply! In response to your questions:

The database is on-premise.

I understand that an automated approach for a monthly pull would be overkill for a system designed for real-time analysis. Are you aware of another more appropriate option? I'd prefer not to do it manually since I'm hoping to bring some new capability to the function & organization.

I'm happy to work the code as you've mentioned since I doubt that the company I work for would allow the use of third party software, like Postman, without some rigorous testing. Any advice on where to start?

[v-jiascu-msft · Accepted solution]

Hi @cmzp,

Seems you just want to get this work done automatically. I would suggest you do it in a normal way and set up a scheduled refresh, which can retrieve new data automatically in a predefined interval. Please refer to https://docs.microsoft.com/en-us/power-bi/refresh-scheduled-refresh.

Best Regards,

Dale