topic Re: Dynamic SQL datasource selector in Developer

Dynamic SQL datasource selector

mferraricloudsu — Wed, 21 Jun 2023 09:01:30 GMT

We have this scenario:

1 Azure App Service back-end API service (.NET 7)
1 Azure App Service front-end client App
Azure SQL Server elastic pool with hundreds of databases

Basically, a user login trough front-end to his own company db. Front-end calls back-end service and start to interact with his own company data.

What we are trying to do is to use Power BI in order to create one or more report templates and embed it inside the front-end. The goal is to have a report which dynamically retrieve data from the proper db in according to user credentials.

Example:

user: MrX is logged in to company with id 50 (so all data are retrieved from SQL db MyDatabase_50).
once MrX is logged in, have an app section that shows a Power BI report pre-filtered with data taken only from MyDatabase_50

Another important aspect: new companies will be continuously activated, so it's important that Power BI report data sources list is dynamically synced with the current companies list.

Does exists a way to implement this kind of architecture and which is the best approach to reach this behavior with Power BI ?

Re: Dynamic SQL datasource selector

Anonymous — Fri, 23 Jun 2023 06:47:26 GMT

HI @mferraricloudsu,

I'd like to suggest you binding users with their companies and use company field to link to other fields. Then you can add a RLS filter on the company field based on current username to apply to users.

After above steps and assign role to users, these records will be fileted based on current users’ company.

Solved: RLS with UserName() - Microsoft Fabric Community

Row-level security (RLS) with Power BI - Power BI | Microsoft Learn
Regards,

Xiaoxin Sheng

Re: Dynamic SQL datasource selector

mferraricloudsu — Fri, 23 Jun 2023 06:56:36 GMT

@Anonymous my problem is not to bind record to users, my goal is to create one single Power BI template and attach dynamically new datasources (each one is a SQL database, one for each company). So, if a new company will be created, Power BI report will recognize automatically that a new datasource is available and can show the data of new company/db.

Re: Dynamic SQL datasource selector

Anonymous — Tue, 27 Jun 2023 07:48:08 GMT

HI @mferraricloudsu,

It sounds like indireclty getting data requirement. You can store these company and correspond server information to a table and use power bi to getting data form that table.
Then you can simply use M query to getting data from specific row of the table records. (use these field values as connection strings to getting data)

Regards,

Xiaoxin Sheng