Direction on managing multiple datasets

[Anonymous]

Hello all,

I am pretty new to PBI. I have been working with it long enough to make some decent reports but I am running into an issue now. I am trying to figure out the best way to manage and share multiple reports with their own datasets. I have 10+ business locations that all collect data on a daily basis. So far my approach has been the following - create a report for each location showing all the KPIs I want to review. Each report is querying data from excel tables on their respective SharePoint sites. The excel tables are again, updated daily by each location via syncing between OneDrive for Business on their machines and their SharePoint page. Ideally I would like the PBI reports to update automatically on a scheduled refresh which would then update the reports/dashboards on Power BI service. The goal is that our group of managers would be able to log on to SharePoint, go to the powerBI service (or maybe have these reports embedded in a common SharePoint page) and view/use these reports with the most up to date data. Am I going about this correctly or is there another way entirely to accomplish this?

Thanks for any help!

Alex

[healthEteam]

Are the excel spreadsheets that each one is completing the same?

If so you could possibly use the append feature in the Power Query Editor to combine each of the datasets into one.  You would want a column either added to the excel spreadsheets that designates which facility the data was coming from or add a custom column on each of those datasets in power query prior to the append.

Then you can have one report.

Grant the users access to the report and then in security you could limit each of them to their respective facility using row level security.

I havent setup the online scheduled refresh from the one drive folder but I think that is pretty common.

The new dataflows could possibly play a role here as well if you wanted reuse that dataset with multiple reports.

[Anonymous]

Thanks for the response @healthEteam. I'll check out the append feature. I would worry about the size of the report though if I combined each dataset into one. Right now with just one report with one dataset it takes roughly 1-2 mins to refresh all the data when manually refreshing. I'm still having trouble with the scheduled refresh so I have not done that yet but I imagine it would be similar. Each dataset source I'm pulling from is roughly 1-2MB with multiple excel tables from multiple workbooks. The .PBIX report file itself, again with just one of those datasets, is about 1MB too for reference. If I combined all 10 locations I worry that size might go up to 10-15MB.

[v-shex-msft]

Hi @Anonymous,

I'd like to suggest you create a template pbix report with visuals which you wanted and parametrize connection string as query parameters.

Deep Dive into Query Parameters and Power BI Templates

Using the Power BI Service Parameters to change connection strings (To possibly change between Dev,

Then you can simply use that template to generate multiple reports with different connection parameters.

BTW, power bi not support store multiple sharepoint credentials at same report, the old one will been replace when you signed in new credentials.

Regards,

Xiaoxin Sheng