A service provider or vendor might want to publish multiple copies of a report that should connect to different database servers or databases. In a true multitenant service solution, we would have a singe database with row-level user mapping tables that filter data by the logged in user. True multitenant solutions require quite a lot of planning and development work to implement. In smaller-scale or interim solutions, copies of a report can be deployed to different workspaces and then queries can be parameterized to use different database connections.
In this post, I’ll demonstrate deploying and configuring such a solution where the server name and database name have been parameterized and setup to use the on-premises gateway to connect and refresh data. I’ll also setup scheduled refresh. The full video walk-through is below but I’ll do a quick review to set the stage.
This is the Power Query Editor in Power BI Desktop. I have two parameters that are used to specify the ServerName and DatabaseName for each SQL Server query:
Once deployed to a workspace in the service, the gateway must be configured with a data source for every possible server name and database combination. In my example, I can connect to my local server using the NetBIOS name, IP address or LocalHost. These all are acceptable methods but a data source must be added to the gateway configuration for each so the that the connection strings match exactly. Remember that the connection is from the on-prem gateway to the database server so names like LocalHost or an internal IP address will work just fine. In my example, I’m using the IP address of my local loopback adaptor on the local machine to connect to a local instance of SQL Server over the TCP connection.
In the workspace, the dataset is bound to the gateway. Click the ellipsis and choose Settings.
To bind the gateway to the dataset, click to select the radio button next to the gateway. This flips the switch titled “Use a data gateway”. Apply the setting and then you can refresh the data or schedule refresh.
Video Demonstration
