Set Fabric semantic model datasource credentials via Power BI REST API
Sachin Nandanwar
My earlier article focused on auto generating semantic model through TOM(Tabular Object Model) libraries. However there is a minor limitation with TOM libraries that prevents the complete automation of the process.
TOM libraries are unable to set the data source credentials of the newly created and deployed semantic model. Further details are available here.
If you refer to my previous article, the M query defined in the resource file of the application did not have the source database credentials. Post generation and deployment of the semantic model, I had to manually set them.
This article is focused on auto setting the data source credential through PowerBI REST API’s. Once the semantic model is autogenerated through TOM libraries, you can call the process described in this article to complete the automation of the semantic model generation through TOM libraries.
Note : With this process we are only setting the Data source credentials of the data source from which the semantic model was derived and not modify or update the data sources.
To change the data source settings, there exist a separate set of API’s. API details are available here.
As the semantic model is deployed on a Fabric workspace, we will have to use the PowerBI’s group API’s to traverse through list of datasets to find the dataset assigned to our semantic model.
After fetching the details of the group(workspace) through group API’s, we make call to this API to fetch both the datasourceId and the gatewayId (gateway here is a misnomer as it is just an id for the cloud connection).
Once we have the datasourceId and gatewayId details, we then use the Http PATCH method to update the data source credentials through this API. Successful call to the API returns an empty string in the response with status code 200.
SetUp
To get started, our service principal should have Dataset.Read.All and Dataset.ReadWrite.All delegated permissions for the Power BI service
We will have to use the https://analysis.windows.net/powerbi scope.
Create a new C# console application and add the following settings to the local.settings.json file
{
"AllowedHosts": "*",
"ClientId": "CliendId of the service prinicipal",
"Workspace": "Workspace where the semantic model resides",
"Semantic Model": "Name of the semantic model",
"Semantic Model Database": "Database connected to the semantic model"
}
Add the following Nuget packages to the console application
dotnet add package Azure.Identity --version 1.8.0
dotnet add package Microsoft.Extensions.Configuration --version 6.0.0
dotnet add package Microsoft.Identity.Client --version 4.47.0
dotnet add package Newtonsoft.Json --version 13.0.4
Code
Add required references to the code
using Microsoft.Extensions.Configuration;
using Microsoft.Identity.Client;
using Newtonsoft.Json.Linq;
using System.Net.Http.Headers;
using System.Text;
Declare a bunch of variables
private static string[] scopes = new string[] { "https://analysis.windows.net/powerbi/api/.default" };
private static string Authority = "https://login.microsoftonline.com/organizations";
private static string RedirectURI = "http://localhost";
public static string clientId = "";
public static string workspace = "";
public static string semantic_model = "";
public static string database = "";
public static string url = "";
public static string response = "";
private static readonly HttpClient client = new HttpClient();
Read the localsettings.json file
public static void ReadConfig()
{
var builder = new ConfigurationBuilder()
.AddJsonFile($"local.settings.json", true, true);
var config = builder.Build();
clientId = config["ClientId"];
workspace = config["Workspace"];
semantic_model = config["Semantic Model"];
database = config["Semantic Model Database"];
}
Authenticate the user and generate the bearer token
public async static Task<AuthenticationResult> ReturnAuthenticationResult()
{
string AccessToken;
PublicClientApplicationBuilder PublicClientAppBuilder =
PublicClientApplicationBuilder.Create(clientId)
.WithAuthority(Authority)
.WithCacheOptions(CacheOptions.EnableSharedCacheOptions)
.WithRedirectUri(RedirectURI);
IPublicClientApplication PublicClientApplication = PublicClientAppBuilder.Build();
var accounts = await PublicClientApplication.GetAccountsAsync();
AuthenticationResult result;
try
{
result = await PublicClientApplication.AcquireTokenSilent(scopes, accounts.First())
.ExecuteAsync()
.ConfigureAwait(false);
}
catch
{
result = await PublicClientApplication.AcquireTokenInteractive(scopes)
.ExecuteAsync()
.ConfigureAwait(false);
}
return result;
}
GetAsync method
public async static Task<string> GetAsync(string url)
{
AuthenticationResult result = await ReturnAuthenticationResult();
client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", result.AccessToken);
HttpResponseMessage response = await client.GetAsync(url);
try
{
response.EnsureSuccessStatusCode();
return await response.Content.ReadAsStringAsync();
}
catch
{
Console.WriteLine(response.Content.ReadAsStringAsync().Result);
return null;
}
}
PatchAsync method
public async static Task<string> PatchAsync(string url)
{
AuthenticationResult result = await ReturnAuthenticationResult();
client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", result.AccessToken);
var credentialDetails = new JObject
{
["credentialDetails"] = new JObject
{
["credentialType"] = "Basic",
["credentials"] = "{\"credentialData\":[{\"name\":\"username\", \"value\":\"_sql_username\"},{\"name\":\"password\", \"value\":\"******\"}]}",
["encryptedConnection"] = "Encrypted",
["encryptionAlgorithm"] = "None",
["privacyLevel"] = "None",
["useEndUserOAuth2Credentials"] = "False"
}
};
string json = credentialDetails.ToString();
HttpRequestMessage credentials = new HttpRequestMessage
{
Method = HttpMethod.Patch,
RequestUri = new Uri(url),
Content = new StringContent(json, Encoding.UTF8, "application/json")
};
HttpResponseMessage response = await client.SendAsync(credentials);
try
{
response.EnsureSuccessStatusCode();
return response.StatusCode.ToString();
}
catch
{
Console.WriteLine(response.Content.ReadAsStringAsync().Result);
return null;
}
}
The interesting part of the code above, is the json format that acts as a Httpcontent for HttpRequestMessage
var credentialDetails = new JObject
{
["credentialDetails"] = new JObject
{
["credentialType"] = "Basic",
["credentials"] = "{\"credentialData\":[{\"name\":\"username\", \"value\":\"_sql_username\"},{\"name\":\"password\", \"value\":\"******\"}]}",
["encryptedConnection"] = "Encrypted",
["encryptionAlgorithm"] = "None",
["privacyLevel"] = "None",
["useEndUserOAuth2Credentials"] = "False"
}
};
There are different sets of jsonformat based on the underlying data source type. As our data source is of the type Basic , the above json format is specific to the the Basic type.
You can find more details on the data source types supported here and also please be aware of the limitations. More details here.
As previously mentioned, the response of the PATCH method is an empty string when the call is successful and returns a 200 OK status code.
Main method
static async Task Main(string[] args)
{
ReadConfig();
Console.WriteLine("Process initialized...");
Console.WriteLine("");
Console.WriteLine("Please enter your Fabric credentials in the new browser window");
Console.WriteLine("");
response = await GetAsync("https://api.powerbi.com/v1.0/myorg/groups");
JObject jobject_groups = JObject.Parse(response);
JArray jArray_groups = (JArray)jobject_groups["value"];
foreach (JObject path in jArray_groups)
{
if (path["name"].ToString() == workspace)
{
url = "https://api.powerbi.com/v1.0/myorg/groups/" + path["id"].ToString();
break;
}
}
url = url + "/datasets/";
response = await GetAsync(url);
JObject jobjectdatasets = JObject.Parse(response);
JArray jArraydatasets = (JArray)jobjectdatasets["value"];
foreach (JObject path in jArraydatasets)
{
if (path["name"].ToString() == semantic_model)
{
url = url + path["id"].ToString();
break;
}
}
url = url + "/datasources/";
response = await GetAsync(url);
JObject jobjectdatasources = JObject.Parse(response);
JArray jArraydatasources = (JArray)jobjectdatasources["value"];
foreach (JObject path in jArraydatasources)
{
if (path["connectionDetails"]["database"].ToString().ToLower() == database.ToLower())
{
url = "https://api.powerbi.com/v1.0/myorg/gateways/" + path["gatewayId"].ToString() + "/datasources/" + path["datasourceId"].ToString();
}
break;
}
response = await PatchAsync(url);
if (response == null)
{Console.WriteLine(response);}
else
{ Console.WriteLine("Datasource set with response code " + response); }
}
Complete Code
using Microsoft.Extensions.Configuration;
using Microsoft.Identity.Client;
using Newtonsoft.Json.Linq;
using System.Net.Http.Headers;
using System.Text;
namespace PowerBI_API_Update_DataSource
{
internal class Program
{
private static string[] scopes = new string[] { "https://analysis.windows.net/powerbi/api/.default" };
private static string Authority = "https://login.microsoftonline.com/organizations";
private static string RedirectURI = "http://localhost";
public static string clientId = "";
public static string workspace = "";
public static string semantic_model = "";
public static string database = "";
public static string url = "";
public static string response = "";
private static readonly HttpClient client = new HttpClient();
static async Task Main(string[] args)
{
Console.WriteLine("Process initialized...");
Console.WriteLine("");
Console.WriteLine("Please enter your Fabric credentials in the new browser window");
Console.WriteLine("");
response = await GetAsync("https://api.powerbi.com/v1.0/myorg/groups");
JObject jobject_groups = JObject.Parse(response);
JArray jArray_groups = (JArray)jobject_groups["value"];
foreach (JObject path in jArray_groups)
{
if (path["name"].ToString() == workspace)
{
url = "https://api.powerbi.com/v1.0/myorg/groups/" + path["id"].ToString();
break;
}
}
url = url + "/datasets/";
response = await GetAsync(url);
JObject jobjectdatasets = JObject.Parse(response);
JArray jArraydatasets = (JArray)jobjectdatasets["value"];
foreach (JObject path in jArraydatasets)
{
if (path["name"].ToString() == semantic_model)
{
url = url + path["id"].ToString();
break;
}
}
url = url + "/datasources/";
response = await GetAsync(url);
JObject jobjectdatasources = JObject.Parse(response);
JArray jArraydatasources = (JArray)jobjectdatasources["value"];
foreach (JObject path in jArraydatasources)
{
if (path["connectionDetails"]["database"].ToString().ToLower() == database.ToLower())
{
url = "https://api.powerbi.com/v1.0/myorg/gateways/" + path["gatewayId"].ToString() + "/datasources/" + path["datasourceId"].ToString();
}
break;
}
response = await PatchAsync(url);
if (response == null)
{Console.WriteLine(response);}
else
{ Console.WriteLine("Datasource set with response code " + response); }
}
public async static Task<AuthenticationResult> ReturnAuthenticationResult()
{
string AccessToken;
PublicClientApplicationBuilder PublicClientAppBuilder =
PublicClientApplicationBuilder.Create(clientId)
.WithAuthority(Authority)
.WithCacheOptions(CacheOptions.EnableSharedCacheOptions)
.WithRedirectUri(RedirectURI);
IPublicClientApplication PublicClientApplication = PublicClientAppBuilder.Build();
var accounts = await PublicClientApplication.GetAccountsAsync();
AuthenticationResult result;
try
{
result = await PublicClientApplication.AcquireTokenSilent(scopes, accounts.First())
.ExecuteAsync()
.ConfigureAwait(false);
}
catch
{
result = await PublicClientApplication.AcquireTokenInteractive(scopes)
.ExecuteAsync()
.ConfigureAwait(false);
}
return result;
}
public static void ReadConfig()
{
var builder = new ConfigurationBuilder()
.AddJsonFile($"local.settings.json", true, true);
var config = builder.Build();
clientId = config["ClientId"];
workspace = config["Workspace"];
semantic_model = config["Semantic Model"];
database = config["Semantic Model Database"];
}
public async static Task<string> PatchAsync(string url)
{
AuthenticationResult result = await ReturnAuthenticationResult();
client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", result.AccessToken);
var credentialDetails = new JObject
{
["credentialDetails"] = new JObject
{
["credentialType"] = "Basic",
["credentials"] = "{\"credentialData\":[{\"name\":\"username\", \"value\":\"_sql_username\"},{\"name\":\"password\", \"value\":\"*******\"}]}",
["encryptedConnection"] = "Encrypted",
["encryptionAlgorithm"] = "None",
["privacyLevel"] = "None",
["useEndUserOAuth2Credentials"] = "False"
}
};
string json = credentialDetails.ToString();
HttpRequestMessage credentials = new HttpRequestMessage
{
Method = HttpMethod.Patch,
RequestUri = new Uri(url),
Content = new StringContent(json, Encoding.UTF8, "application/json")
};
HttpResponseMessage response = await client.SendAsync(credentials);
try
{
response.EnsureSuccessStatusCode();
return response.StatusCode.ToString();
}
catch
{
Console.WriteLine(response.Content.ReadAsStringAsync().Result);
return null;
}
}
public async static Task<string> GetAsync(string url)
{
AuthenticationResult result = await ReturnAuthenticationResult();
client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", result.AccessToken);
HttpResponseMessage response = await client.GetAsync(url);
try
{
response.EnsureSuccessStatusCode();
return await response.Content.ReadAsStringAsync();
}
catch
{
Console.WriteLine(response.Content.ReadAsStringAsync().Result);
return null;
}
}
}
}
Walkthrough
Conclusion
Through the above approach, we can achieve an end to end semantic model automation once the TOM libraries are deployed to the Fabric workspace. With this approach we can ensure deployment consistency and accuracy across the overall automation process
Thanks for reading !!!
Subscribe to my newsletter
Read articles from Sachin Nandanwar directly inside your inbox. Subscribe to the newsletter, and don't miss out.
Written by