Forum Discussion
report + excel files to allow users input their own data
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.
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.
Most Valuable ProfessionalSounds 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.
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
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.