Multi-tenancy in Power BI Embedded

Question

I have a multi-tenant web application and I am using a database per tenant approach. The web application will also use Power BI Embedded to show reports based on the data for that particular tenant and all reports for each tenant will have the same format but the data source will be different.

From what I've seen, there is not straightforward way to implement multi-tenancy in Power BI, such as passing the data source as parameter. I managed to find two ways how to make Power BI embedded multi-tenant. Either use row-level security which would mean that I need to have a single data warehouse for all the tenant's data, and this is not an option for me. The other option would be having a workspace per tenant.

For the second option I would have a template workspace from which a copy will be created for each new tenant. This tutorial here describes how to do it: https://powerbi.microsoft.com/fr-fr/blog/duplicate-workspaces-using-the-power-bi-rest-apis-a-step-by-step-tutorial/ .

Can the same thing be done through the Power BI C# SDK? I would also need to change the data source used per workspace. How can I do this for all reports in my workspace?

Finally, has someone discovered an easier way how to implement multi-tenancy with Power BI embedded or is this it?

Answer 1

It depends on your data source type (SQL Server, SSAS, CSV files, etc.) and data connectivity mode (import, direct query, etc.). If you can use parameters, then one of your options is to allow the newly cloned report to switch it's data source itself by using connection specific parameters. To do this, open Power Query Editor by clicking Edit Queries and in Manage Parameters define two new text parameters, lets name them ServerName and DatabaseName:

Set their current values to point to one of your data sources, e.g. SQLSERVER2016 and AdventureWorks2016. Then right click your query in the report and open Advanced Editor. Find the server name and database name in the M code:

and replace them with the parameters defined above, so the M code will look like this:

Now you can close and apply changes and your report should work as before. But now when you want to change the data source, do it using Edit Parameters:

and change the server and/or database name to point to the other data source, that you want to use for your report:

After changing parameter values, Power BI Desktop will ask you to apply the changes and reload the data from the new data source. To change the parameter values (i.e. the data source) of a report published in Power BI Service, go to dataset's settings and enter new server and/or database name (check the gateway settings too, if this is on-premise data source):

After changing the data source, refresh your dataset to get the data from the new data source. With Power BI Pro account you can do this 8 times per 24 hours, while if the dataset is in a dedicated capacity, this limit is raised to 48 times per 24 hours.

To do this programatically, use Update Parameters / Update Parameters In Group and Refresh Dataset / Refresh Dataset In Group REST API calls. For example, you can do this with PowerShell like this:

Import-Module MicrosoftPowerBIMgmt
Import-Module MicrosoftPowerBIMgmt.Profile

$password = "xxxxx" | ConvertTo-SecureString -asPlainText -Force
$username = "[email protected]" 
$credential = New-Object System.Management.Automation.PSCredential($username, $password)

Connect-PowerBIServiceAccount -Credential $credential

Invoke-PowerBIRestMethod -Url 'groups/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/datasets/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/UpdateParameters' -Method Post -Body '{
  "updateDetails": [
    {
      "name": "ServerName",
      "newValue": "SQLSERVER2019"
    },
    {
      "name": "DatabaseName",
      "newValue": "AdventureWorks2019"
    }
  ]
}'
Invoke-PowerBIRestMethod -Url 'groups/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/datasets/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/refreshes' -Method Post

Disconnect-PowerBIServiceAccount

If you can't use parameters, e.g. Live connection to SSAS, the connection string could be changed using Update Datasources In Group REST API call. In PowerShell this could be done like this:

Import-Module MicrosoftPowerBIMgmt
Import-Module MicrosoftPowerBIMgmt.Profile

$password = "xxxxx" | ConvertTo-SecureString -asPlainText -Force
$username = "[email protected]" 
$credential = New-Object System.Management.Automation.PSCredential($username, $password)

Connect-PowerBIServiceAccount -Credential $credential

Invoke-PowerBIRestMethod -Url 'groups/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/datasets/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/Default.UpdateDatasources' -Method Post -Body '{
  "updateDetails": [
    {
      "datasourceSelector": {
        "datasourceType": "AnalysisServices",
        "connectionDetails": {
          "server": "My-As-Server",
          "database": "My-As-Database"
        }
      },
      "connectionDetails": {
        "server": "New-As-Server",
        "database": "New-As-Database"
      }
    }
  ]
}'

Disconnect-PowerBIServiceAccount

Note, that you need to provide both old and new server and database names.

In C# you can do the same in a very similar way, even without Power BI Client:

var group_id = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx";
var dataset_id = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx";
var client = new HttpClient();
client.DefaultRequestHeaders.Add("Accept", "application/json");
client.DefaultRequestHeaders.Add("Authorization", "Bearer " + accessToken);

var restUrlUpdateParameters = $"https://api.powerbi.com/v1.0/myorg/groups/{group_id}/datasets/{dataset_id}/Default.UpdateParameters";
var postData = new { updateDetails = new[] { new { name = "ServerName", newValue = "NEWSERVER" }, new { name = "DatabaseName", newValue = "Another_AdventureWorks2016" } } };
var responseUpdate = client.PostAsync(restUrlUpdateParameters, new StringContent(JsonConvert.SerializeObject(postData), Encoding.UTF8, "application/json")).Result;

var restUrlRefreshDataset = $"https://api.powerbi.com/v1.0/myorg/groups/{group_id}/datasets/{dataset_id}/refreshes";
var responseRefresh = client.PostAsync(restUrlRefreshDataset, null).Result;

Using the Power BI C# client can make you life easier, e.g. refreshing the report can be made this way:

var group_id = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx";
var dataset_id = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx";
var credentials = new TokenCredentials(accessToken, "Bearer");

using (var client = new PowerBIClient(new Uri("https://api.powerbi.com"), credentials))
{
    client.Datasets.RefreshDatasetInGroup(group_id, dataset_id);
}

When calling the API, you need to provide an access token. To acquire it use ADAL or MSAL libraries, e.g. with code like this:

private static string resourceUri = "https://analysis.windows.net/powerbi/api";
private static string authorityUri = "https://login.windows.net/common/"; // It was https://login.windows.net/common/oauth2/authorize in prior versions
private static string clientId = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"; // Register at https://dev.powerbi.com/apps
private static string groupId = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx";
private static string reportId = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx";

private static AuthenticationContext authContext = new AuthenticationContext(authorityUri, new TokenCache());


public string Authenticate()
{
    AuthenticationResult authenticationResult = null;

    // First check is there token in the cache
    try
    {
        authenticationResult = authContext.AcquireTokenSilentAsync(resourceUri, clientId).Result;
    }
    catch (AggregateException ex)
    {
        AdalException ex2 = ex.InnerException as AdalException;
        if ((ex2 == null) || (ex2 != null && ex2.ErrorCode != "failed_to_acquire_token_silently"))
        {
            MessageBox.Show(ex.Message);
            return;
        }
    }

    if (authenticationResult == null)
    {
        var uc = new UserPasswordCredential("[email protected], "Strong password");
        try
        {
            authenticationResult = authContext.AcquireTokenAsync(resourceUri, clientId, uc).Result;
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message + ex.InnerException == null ? "" : Environment.NewLine + ex.InnerException.Message);
            return;
        }
    }

    if (authenticationResult == null)
        MessageBox.Show("Call failed.");
    else
    {
        return authenticationResult.AccessToken;
    }
}