cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gregboothdelt
Helper I
Helper I

Best practice for using workspaces, filestorage and gateway administration

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 ?

1 ACCEPTED SOLUTION
v-luwang-msft
Community Support
Community Support

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.

vluwangmsft_0-1640587868066.png

 

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:

vluwangmsft_1-1640588292871.png

 

vluwangmsft_2-1640588431523.png

 

vluwangmsft_3-1640588431603.png

 

vluwangmsft_4-1640588431137.png

 

 

 

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

View solution in original post

3 REPLIES 3
v-luwang-msft
Community Support
Community Support

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
Community Support
Community Support

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.

vluwangmsft_0-1640587868066.png

 

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:

vluwangmsft_1-1640588292871.png

 

vluwangmsft_2-1640588431523.png

 

vluwangmsft_3-1640588431603.png

 

vluwangmsft_4-1640588431137.png

 

 

 

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

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

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors