Publishing Oracle data to Power BI without gaining direct access to the SQL database

[RenateBK]

Hello, 

To begin with, my apologies if I write anything incorrect, I am still new to Power BI and I'm not well versed in SQL and Oracle BI.

I need to connect the Oracle BI data to Power BI for cross-reporting. There has already been set up SQL codes for Oracle BI reporting so the specific parts of the data (that I need) is extracted from the SQL database into tables/datasets in Oracle BI. My wish is to extract the same data over to Power BI. 

The issue at hand: the SQL server/database includes highly sensitive data that I should not have direct access to, thus IT is very hesitant regarding directly linking me to the SQL database because of data protection/safety reasons.

Hence, my question is - is there a way to connect to Oracle BI/Oracle BI Reporting in a way where I can aquire the data they have in these reports without gaining full access to the original database where there is sensitive information? 

I know it is possible to do it by downloading the Oracle BI data as an Excel sheet and then uploading in the folder etc. However, my goal here is to automate the process as much as possible so that the data will automatically refresh at the given times instead of whenever I manually upload it. 

Thank you, 

Renate

[AnalyticsNinja6]

Hi @RenateBK, this might be an interesting tool for you.

BI Publisher Connector is a unique solution that directly connects Power BI to Oracle database via OTBI's data models. You can also fetch data directly from Physical Layer of OBIEE/OAS/OAC instances. 

This means you dont have to struggle with Excel extracts or confusing SQL queries. All you have to do is install the tool, select the connector in Power BI & start visualizing data in minutes.

BI Publisher Connector authenticates with OBIEE credentials and users can access only the data that he/she is authorized to view (based on their OBIEE role and user-based access privileges), ensuring secure data communication. It also does not read or store or share the data generated by the queries. 

BI Publisher Connector is available for both Power BI Desktop and Service. It supports Scheduled Refresh of your data via the On-premise Gateway.  

Some more benefits: 

• Error-free reporting & data visualization
• Automate in Power BI Service
• Hassle-free, one-time setup in mins

You can also check out their solution BI Connector which connects Power BI to OBIEE, OAC, OAS, and Oracle Fusion Analytics (HCM, SCM, ERP and CX).

[v-yueyunzh-msft]

Hi, @RenateBK 

According to your description, your question is "is there a way to connect to Oracle BI/Oracle BI Reporting in a way where I can aquire the data they have in these reports without gaining full access to the original database where there is sensitive information?"

Here are my understand :
(1)You want to get Oracle data, but it contains private data, you can't get all the data directly, can you use fixed SQL statements to get the data you can access?

put your query in the SQL statement box, which appears when you expand the Advanced options section of the Oracle database dialog.

For more information, you can refer to :

Connect to an Oracle database with Power BI Desktop - Power BI | Microsoft Learn

(2)For your Oracle BI/Oracle BI Reporting, if it's on your Oracle side, there may not be a way to do it. If you already have a dataset published by someone else on Power BI Service, you can request permission for that dataset and then use Power BI Desktop to connect to this dataset for report development.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[RenateBK]

Hello Aniya, 

Thank you for the reply and great help, and sorry for being unclear.

The data is private/sensitive in the sense that it's an extreme GDPR breach if this is published or becomes accessible to someone who is not supposed to see it - hence why IT is very hesitant about linking the Oracle database to Power BI as they don't know how Power BI interacts with the database.

I think your suggestions regarding using fixed SQL statements sounds like the best solution.

Regarding using fixed SQL statements, do you know how this impacts the access to the Oracle Database? The biggest concern when I mentioned this to IT was that they worry this will still give full access to the Database

Potentially, could IT set up the SQL connection on their PC in Power BI and then share the "workspace" with me, for me to then set up the Dashboard  and edit the data without ever gaining access to the database? Or would this ruin the automatically updating of the data in these reports?

I'm sorry if I'm asking questions outside of your field or if I'm it's still unclear what I'm asking for.

Thank you very much for your time, 

Renate

[v-yueyunzh-msft · Accepted solution]

Hi, @RenateBK 

Thanks for your quick response !

When we use the fixed SQL statements, Power BI will send a SQL query statement by default, and will not send other queries to Oracle.And all the operations you do in Power Query will not cause query folding (that is, no statements other than the default query statement will be sent to the underlying data source).

For this import mode, only when the data is refreshed, you need to access the Oracle data source and send this default query to query the data. All other operations and operations are completed by using Power BI's own built-in engine after obtaining the data.

Secondly, you need to enter the credentials of the data source, that is, the account number and password of your data source, if you connect to the data source for Desktop or Service. However, you can open permissions separately for opening tables that require permissions according to your privacy.

And if you want to connect to the local data source on Power BI Service, you need to download the local data gateway and add the corresponding data source credentials in the local data gateway before you can refresh the plan. And the logic is the same as before, only when refreshing will a fixed SQL statement be sent to the data source, and no other query statements will be sent.

If your IT can't provide the account number, you can try to let IT connect to manage the connection and publication of the data source, then you can use Power BI Desktop to connect to the dataset that has been published on Power BI Service and is configured to be refreshed, and then develop the report.

For more information, you can refer to :

Connect to datasets in the Power BI service from Power BI Desktop - Power BI | Microsoft Learn

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[RenateBK]

Hello Aniya, 

Thank you for taking your time to explain this to me and for your help. I have gotten some great insights that I will bring along to the the IT department. 

Thank you, 

Renate