cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Direction on managing multiple datasets

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

 

3 REPLIES 3
healthEteam
Resolver I
Resolver I

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
Not applicable

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.

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

 

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!