Dynamic datasource

[minh23tran]

Groups and permissions work great, but the only issue I'm coming across is having dynamic connections. I'm wondering if it's possible with power BI.

Current Setup

DataSource

Apples, Oranges

Report DessertsSold

Each user can only view one of the datasource. Either Apples or Oranges. If they can view apples, when they generate DessertsSold report, it should query only Apples Dataset. Otherwise Oranges. 

I know I can create a content pack but that would virtually mean  N being number of clients, R being number of reports, We'll have N*R items to create. Updates will be a gigantic pain

[v-micsh-msft]

Hi minh23tran,

Could you please share more details about the Data Source, are you using any database as the data source here? Further, what is your data model?

If using database source, then we could create database role to control if a user has permission to access the proper database(or even the table), at the data source side.

For example:

Using the following T-SQL:

exec sp_msforeachtable "DENY SELECT ON '?' TO [username];"

GO

GRANT SELECT ON [schemaName].[tableName] to [username]

Go

Will only give the [username] SELECT permission on [tableName].

If configuring data source permission is currently not available, then please take a look at the role level security under Power BI:

Row-level security (RLS) with Power BI (Preview)

This allows admin to create special role(under Power BI Desktop) which could only see the selected rows or columns of the dataset, then manage the wanted user to the special role under Power BI service. This feature is still in Preview, so there might be some limitations.

See if this would meet your requirements.

If any further help needed, please feel free to post back.

Regards

[minh23tran]

Hi Michael, Maybe it'll be more straight forward if I you detail of my current setup instead of pseudo setup. My server currently contains 25 databases. 

Each database have the same number of tables and table structure. 

I built the reports using Demo Dataset.

I need it to change base on the organization the client is from. That's where it comes to play. Lets say my customer is from Atikokan, when he runs report Business Counselling, it should generate based on Atikokan dataset. I hope this is clear

[v-micsh-msft]

Hi minh23tran,

Apologize for the late response.

Currently I don't think it is possible for Reports to switch Datasets based on user login information.

We could take use of Role Level Security to control which data could be access by who, but that is based on the same data source, changing data source is only available in Query Editor currently.

If data from different data source is needed for one report, then we could append or combine them into one under Query editor, after that, use the customized Query to build the report, and configure the Role Level Security.

Regards