Joining and Syncing disparate Excel and Oracle in Power BI

[Anonymous]

Joining and Syncing disparate Excel and Oracle in Power BI

Using this aproach we can successfully edit and sync Excel data within Power BI

https://powerbi.microsoft.com/en-us/documentation/powerbi-service-excel-workbook-files/

https://powerbi.microsoft.com/en-us/documentation/powerbi-refresh-data/

Where we have one source of data be it Oracle, Excel or SQL we can successfully sync up our datasets within PowerBI. Where we have mixed datasets for example Oracle and Excel we are struggling to find an effective working method to sync the two data sources within Power BI. 

The basic requirement is to have two datasets, one oracle table and an excel sheet with a join between the two presented and synced up into one Power BI report. 

Our current working options are to pull everything down to either Oracle or Excel, but I cannot seem to find a method of referencing and syncing the source data as separate entities in Oracle and Excel. 

[v-qiuyu-msft]

Hi @Anonymous,

After you create a report in Power BI desktop follow @Anonymous's suggestion, you can publish the report to Power BI, then create Oracle and Excel data sources under data gateway use the same connection information as in desktop. Configure the dataset use that data gateway and set schedule refresh.

Best Regards,
Qiuyun Yu

[Anonymous]

Thanks to your both for your reply. The bits I am missing is the ability to add a data Gateway connection dataset from within the portal itself and the ability to link directly to an excel file held in One Drive.

Using the portal I can create a new App Workspace and I can create a dataset based upon a file held in OneDrive. I cannot seem to see a way to bring in a dataset set up in our Gateway.  Is this possible?

Using the Desktop client, the working method we have used is to connect up a data connection to EXCEL using a UNC path and set up this file within the Enterprise Gateway. We can connect to Oracle data and then manage the joins / relationship between the Excel and Oracle datasets. 

What we wanted to do is have the App Workspace as the entity storing the Excel sheet.  This would have allowed us edit the source excel file and report both the Oracle data synced through the gateway and the Excel file directly through to One Drive as a live connection rather than going through the gateway. 

What are the best ways to reference Excel Data held in OneDrive or Document Libraries? Can you reference a file held within an App Workspace File location directly in Power BI?

[v-qiuyu-msft]

Hi @Anonymous,

Assume the dataset contains both Excel in OneDrive and Oracle database, after publish to service, then you will need the personal gateway to refresh data. See: On-premise data gateway is not available for the combined dataset .

While if the dataset only contains Excel in OneDrive, and it doesn't connect to external data source, the gateway is not required. You can refer to this article: Refresh a dataset created from an Excel workbook on OneDrive, or SharePoint Online.

If you connect to Excel OneDirve in Power BI service via Get Data-> Files-> OneDrive for Business -> Connect, Manage, and View Excel in Power BI, it's not able to reference the that Excel in Power BI service. But if you import the Excel as a dataset, you can use Power BI Service data source in desktop to get data from the Excel in Power BI service, see: Connect to datasets in the Power BI service from Power BI Desktop (Preview).

Best Regards,
Qiuyun Yu

[Anonymous]

I'm not sure I 100% understand the problem, however what i'm imaging you need to do is create 2 tables under Edit Queries, each connected to your separate data sources, in your case 1 for Oracle and 1 for Excel.

Within "Edit Queries" you will have the option to append or merge your data sources should you wish to combine them into a single table. If not, once you close and apply you will be able to set up any table relationships you need between the Oracle and Excel table.

If you need multiple tables from either source, the same applies.