report + excel files to allow users input their own data

[lgre]

Hello all,

I have some questions about how to best design a system meeting my requirements.

I want to have a report integrating data from a few different sources

1. web API, publicly accessible without authentication. data refresh once a week or so.
2. authoritative data tables edited by finance department. it's just a few users who can edit, they should stay secure, excel is by far the tool they are most comfortable with. daily refresh needed.
3. additional tables where many internal users can enter their own data. there'll be many editors, again they are most comfortable with excel. changes to those tables ideally should be seen in the report in minutes.

The naive proof of concept is to build everything in PBI desktop, connecting the web API directly for 1, and setting up Excel files in an organisational SharePoint location for 2 and 3. I'm using a Power BI workspace and all users will have Power BI Pro licences.

My questions are:
a. Is Excel a bad idea? If so, why exactly, and what could replace it?
b. Is there any way to have refreshes for 3 happen in minutes with Excel? Or do I need to set up something like a SQL Server with Power Apps frontend?
c. Is scheduled refresh with Excel in SharePoint reliable? As far as I can tell, the PBI service would need to use my user account credentials to access the data. Doesn't MFA prevent that? Is it common practice to set up a dedicated non-MFA account with SharePoint permissions?

Thanks in advance for any and all suggestions.

[christinepayton · Accepted solution]

Sounds reasonable. Make sure you have some sort of key column to match up the data between the sources. If you can get your users to input data in a SharePoint list instead, that's preferable to Excel. This way you can set their permissions to "contribute" instead of "edit" and they won't be able to do things like rename columns or mess up the table structure (this is where Excel often runs into issues... people don't realize that column/sheet references are by name and/or put text in a number column both of which break refresh). 

Not sure what you mean by 3 minute refresh. How long the refresh takes depends on the size of the dataset. You get 8 refreshes per day with pro licenses. If you want direct-query, so "live" data, you need to use Dataverse or SQL. 

Scheduled refresh on SharePoint is pretty reliable. There are occasional service outages where the refresh will fail, but it usually picks it back up the next day. MFA is fine, you do your MFA when you set up the refresh and it "keeps" that authentication when it refreshes, you don't have to redo it every time. I DO recommend using a service account, meaning not your account but a separate account with all the required licenses/permissions, that will keep the refresh running if you leave your org and allow other people to troubleshoot down the road if needed. People do usually turn off MFA on service accounts, and just set a really long password - not because the refresh can't handle it, just because transitioning it between people is rough. 

[christinepayton · Accepted solution]

Sounds reasonable. Make sure you have some sort of key column to match up the data between the sources. If you can get your users to input data in a SharePoint list instead, that's preferable to Excel. This way you can set their permissions to "contribute" instead of "edit" and they won't be able to do things like rename columns or mess up the table structure (this is where Excel often runs into issues... people don't realize that column/sheet references are by name and/or put text in a number column both of which break refresh). 

Not sure what you mean by 3 minute refresh. How long the refresh takes depends on the size of the dataset. You get 8 refreshes per day with pro licenses. If you want direct-query, so "live" data, you need to use Dataverse or SQL. 

Scheduled refresh on SharePoint is pretty reliable. There are occasional service outages where the refresh will fail, but it usually picks it back up the next day. MFA is fine, you do your MFA when you set up the refresh and it "keeps" that authentication when it refreshes, you don't have to redo it every time. I DO recommend using a service account, meaning not your account but a separate account with all the required licenses/permissions, that will keep the refresh running if you leave your org and allow other people to troubleshoot down the road if needed. People do usually turn off MFA on service accounts, and just set a really long password - not because the refresh can't handle it, just because transitioning it between people is rough. 

[lgre]

Hi Christine, thanks for the reply - this is spectacularly helpful!

Good point on list 'contribute' permission. Indeed I am worried about people messing up the table structure.

Sorry, to rephrase my question B: I don't need a 3-minute refresh, I mean that the Excel files where I let people input their own data - perhaps to be actually done through lists, as you suggest - should be made available to viewers a.s.a.p., ideally minutes rather than hours later. Setting up a dedicated SQL Server for this seems like an overkill though, and Dataverse would end up being pretty pricey. Is there any other alternative?

[christinepayton · Accepted solution]

Yeah, you will not get timeliness in Power BI without a source that supports direct query unless people are not editing frequently (8 refreshes per day on pro). You can trigger a refresh when someone modifies a list item in SP, but if they edit more than 8x a day it's not going to keep updating the dataset when you hit the limit. Dataverse alone doesn't cost, it's whatever you build on top of it - e.g. an app would trigger premium per-user Power Apps license for people interacting with it. Even SQL will trigger the same, because it's a premium source for Power Apps. 🙂

BUT, depending on what exactly your PBI report is doing, you might be able to do the same joins/lookups inside the Power App and use SharePoint as the source, and not use Power BI for whatever it is that needs to be live... that would be covered in normal licensing.