Data On-Premises Vs. SharePoint Online

[Tara_]

Hi everyone,

I am wondering about the best implementation for the below case:

I want to build a report from data from two bi-weekly excel files that is received via email from a customer (someone will update the data sources manually upon receiving the files). In terms of data connection and refresh would these scenario's work and if so which one would you recommend and why?

1. The excel files are added to Sharepoint online, as part of Office 365, would it be better to allow pbi service to automatically update the report every two weeks (after updating the original excel files on sharepoint) or schedule a refresh every two weeks (a date and time after updating the files) on pbi servcie?
2. Or put the excel files on-prem and get the updates via an on-premesis data gateway and a bi-weekly refresh schedule.

Also, I would appreciate any other suggestions u might have, as these are the only ones I know of. 

Thank you

[v-xicai]

Hi @Tara_ ,

Recommend you save your data in SharePoint Online. Then you can use SharePoint Folder connector to get data in Power BI Desktop. See detail steps: Connecting to Files in SharePoint & OneDrive with Power BI / Power Query.  In this way, it doesn't need to install and set gateway if your dataset only contains the cloud data source. Also, there are some advantages using SharePoint Online connector.

1.When you sign in with your Microsoft account, select Keep me signed in. Power BI can then synchronize any updates you make in the file in Power BI Desktop with datasets in Power BI.

Any reports you' ve created in the file are loaded into Reports in the Power BI service. Let's say you make changes to your file on OneDrive or SharePoint Online. These changes can include adding new measures, changing column names, or editing visualizations. Once you save the file, Power BI service syncs with those changes too, usually within about an hour.

2.You can connect to multiple files instead of just one when connecting to your files this way.

3.You reduce the risk of ever hitting a Formula. Firewall since all of the files inside that folder will be set with the same Privacy Level and will fall under the same scope

4.The SharePoint connector was created in a way where it uses the SharePoint API to grab the data. The connector also allows, to a certain degree, Query Folding so when you run your query you’re only getting the files that you’re actually interested in and not all of the files from your SharePoint site. You can read more about Query Folding here.

In addition, you may configure Schedule refresh for the SharePoint file, see Configure scheduled refresh.

Best Regards,

Amy 

Community Support Team _ Amy

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

[Tara_]

Thank you @v-xicai for the response and links. I connected to the Sharepoint Folder successfully, however I have a couple of more concerns:

1. After connecting to the SharePoint folder, in power query I was unable to seperate both files after clicking combine. It only created one table for one of the files. Instead what I did was I copied the query to create two tables and it worked but I don't think this is the correct way plus I don't know how it would effect scheduled refresh on PBI Service. Could you confirm?

2. Assume the two files that will be updated bi-weekly are Sales and Products. Should I put all Sales files in one folder while all the Product files in another, in SharePoint online, and then use two SharePoint folder connectors to connect to Power BI? What would be the best practice for this scenario?

Thank you

[nandukrishnavs · Accepted solution]

@Tara_  I would recommend you to keep the excel files in SharePoint online. So that we can avoid the dependency with Gateway. 

You have to use the SharePoint Folder connector for getting the excel files into Power BI.

You can connect SharePoint online directly with Power BI desktop. If needed, you can use dataflow as well.

Also, you can schedule the daily/weekly data refresh. or you can use Microsoft Power Automate to trigger the data refresh after a new file added to the SharePoint.https://powerbi.microsoft.com/en-us/blog/refresh-your-power-bi-dataset-using-microsoft-flow/

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[Tara_]

@nandukrishnavs 

Thank you for your repsonse. I have a couple of follow up questions. 

1. I was planning on using the web connector to individually connect to each excel file in sharepoint online. Not sure if I will receive both at the same time. Would performance differ in this case from using the Sharepoint folder connector? 

2. The link was very helpful. It did mention though that "Power BI’s existing fixed refresh schedules are not sufficient to accomplish this" , why not? and does this mean it is not generally used? As far as I know using Power Automate will require extra licensing. I will test it nevertheless but I wonder if you have used the existing fixed refresh option before.

Thanks

[nandukrishnavs]

@Tara_ 

You can use Web connectors as well. (Had noticed some connection issues before).

But better option would be SharePoint folder connector. 

Also default scheduled refresh is enough. I just highlighted another option. Ofcourse, additional licence required for Power Automate. 

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[nandukrishnavs]

@Tara_ 

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂