Best practice for using workspaces, filestorage and gateway administration

[gregboothdelt]

We are starting to use the Power Bi Service. We are going to setup workspaces for collaboration. Some of the reports access SQL databases on our network and these are going to be accessed via a on premise gateway. Some of our reports are based on data from Excel and we need to store these excel files in either a shared one drive or sharepoint.

For all of the above we need to setup security - to control access to

a) the gateway data sources

b) the excel files - whereever they are stored

c) the workspace itself.

Questions

1) Can you control access to a workspace via AD groups or do you have to use  M365 groups ?

2) Can you control access to the gateway  data source via AD groups or do you have to use  M365 groups ?

3) Can we store the excel files in the workspace ? Do we need to configure a shared onedrive to be able to do this ? 

4) Is it best practicew to setup a shared one drive to store files that will contain data used in Power bi, or would a sharepoint document library be a better ?

[v-luwang-msft]

Hi @gregboothdelt ，

Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.

Best Regards
Lucien

[v-luwang-msft · Accepted solution]

Hi @gregboothdelt ,

1) Can you control access to a workspace via AD groups or do you have to use  M365 groups ?

A: Refer this link,In the new workspaces, you can add multiple Active Directory security groups, distribution lists, or Microsoft 365 groups to these roles, for easier user management.

2) Can you control access to the gateway  data source via AD groups or do you have to use  M365 groups ?

A; Gateway administrator need group has Email address.If we create a mail-enabled security group synced with Active Directory, we can add it under data source Users tab. See:

3) Can we store the excel files in the workspace ? Do we need to configure a shared onedrive to be able to do this ?

There are three ways to connect in Power BI:Direct Query,Import ,Live Connection .When you datasource is excel ,you publish to Power BI Service, except report ,there will also with a dataset.（Data set = data source + user credentials)

You do not need to  configure a shared onedrive and you could open your report anywhere with wlan.And if your datasource will changed，and you want to refersh data，you could  configure a shared onedrive  and use onedrive refresh . To learn more details ,refer:https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-data

4) Is it best practicew to setup a shared one drive to store files that will contain data used in Power bi, or would a sharepoint document library be a better ?

If you’re working on a file by yourself, save it to OneDrive. Your OneDrive files are private unless you share them with others, which is particularly useful if you haven’t created a team yet.

If you’re already working as a team — in Microsoft Teams, SharePoint, or Outlook—you should save your files where your team works, because OneDrive for work or school connects you to all your shared libraries, too.

Refer: https://support.microsoft.com/en-us/office/should-i-save-files-to-onedrive-or-sharepoint-d18d21a0-1f... 

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[Anonymous]

For Excel's, you don't need a gateway unless they are only saved On Prem

• Save the Excel files on a SharePoint Site, avoid personal onedrives (What happens if the person leaves the company?).
• If the Excel files are monthly files, save them in a folder together. If they keep the same column headings any new files added will automaticallly update on the report refresh.
• If it is a single file that's updated, then use the web connection.
• Security is permission based, people will only have access to SharePoint/Workspace if it is granted. My recommendation, is to create an Power Bi App for the workspace to share the reports. Here you grant endusers readonly access
• If there are reports that one person can only view and another not, then you can create Row Level Security within the reports
• Also set in PBI Desktop, not to allow people to export datasets or download PBI