Connecting a Power BI report to a DB Dynamically based on the Logged in user

[AshwinShyam]

How can we configure Power BI reports to connect to the appropriate database for each tenant in our SAS product, where all tenants have separate databases with identical objects and schema?

[Adamboer]

To connect Power BI reports to the appropriate database for each tenant in your SAS product, you can create a parameterized connection string in Power BI that allows you to dynamically switch between databases.

Here's how you can do it:

1. Create a parameter table that contains the database name for each tenant.

2. In Power BI Desktop, click on "Transform Data" to open the Power Query Editor.

3. In the Power Query Editor, select "New Source" and then select "SQL Server."

4. Enter the server name and database name of any tenant in your SAS product. This will create a connection to the database.

5. In the Power Query Editor, right-click on the connection you just created and select "Advanced Editor."

6. In the Advanced Editor, replace the database name with a reference to the database name parameter in your parameter table.

7. Save and close the Advanced Editor.

8. In the Power Query Editor, create a new parameter called "Tenant" that will be used to switch between databases.

9. Use the "Tenant" parameter in your query to dynamically switch between databases based on the value of the "Tenant" parameter.

10. Create a report based on the query.

11. Publish the report to the Power BI service.

12. In the Power BI service, create a new parameter called "Tenant" that will be used to switch between databases.

13. Use the "Tenant" parameter in your report to dynamically switch between databases based on the value of the "Tenant" parameter.

By following these steps, you will be able to configure Power BI reports to connect to the appropriate database for each tenant in your SAS product.